Express Edition Tips

As a lot of line-of-business applications are being built with SQL Server 2005 Express Edition as their backend database, we need to make sure that we backup the system and the user databases running on these instances. Unfortunately, SQL Server 2005 Express Edition does not come with SQL Agent which we would normally use to create a database maintenance plan to backup all the databases. How do we perform a backup of our system and user databases in SQL Server 2005 Express Edition similar to how we do it in other editions?

One very nice feature that was introduced in SQL Server 2005 was the Dedicated Administrator Connection (DAC). This gives you the option to always have a way to connect to SQL Server if SQL Server is unresponsive. In the Standard and Enterprise versions of SQL Server this is enabled by default, but you have to make some configuration changes for this to work for SQL Server Express. In this tip I walk through how to set this up for SQL Server 2008 R2 Express, but the same steps can be followed for SQL 2005 Express and SQL 2008 Express.

With the release of SQL Server 2005, Microsoft also released SQL Server 2005 Express Edition. This is the free version of the SQL Server engine that existed in prior releases under the MSDE namesake. While there were several great enhancements with SQL Server 2005, one thing that is still lacking is the ability to schedule jobs. Although there is not the direct ability to schedule jobs in the Express Edition there are third party tools that give you this capability.

With the release of SQL Server 2005 Express, Microsoft has offered a lot of new features that did not exist with MSDE. A couple of these things include Reporting Services features as well as a GUI management tool to manage SQL Server instead of having to do everything via command line. Although they have added a lot of new features, not all of the tools needed to maintain and troubleshoot SQL Server are included. One of these tools that is not included is Profiler. You can still run a server side trace on your SQL Server Express databases as well as import the data into a table for analysis, but sometimes having a GUI tool to troubleshoot an issue is much simpler than using a server side trace. So what are the options?

Developers using SQL Server Express face a few challenges in their day to day work. One is that setting up and maintaining Express can be a daunting task. Another is that using the "User Instances" feature (which has been deprecated) leads to a lot of confusion - developers connect to one instance of the database through their tools, and another instance of the database through their program, and don't understand why updates to one aren't reflected in the other. This is actually due to the AttachDbFileName setting, but in most examples I've seen, the two seem to go hand in hand.

I've been taking advantage of the free SQL Server Express edition for some time. I see there is a new release of SQL Server Express 2008 R2. In this tip I'll discuss the features, limitations, requirements and installation experience.

Have you heard of the SQL Server 2005 Express edition, but not sure where it fits into your overall architecture? Are you concerned it is too constraining and will not meet your business needs? Are you looking for ways to continue to use SQL Server in niche capacities? Are you trying to balance distributing your applications without causing a future server consolidation project? If so, this tip will outline the opportunities with SQL Server 2005 Express edition in addition to considerations when adopting this version of SQL Server 2005.

One problem with SQL Server 2005 Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server.

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Developers using SQL Server Express face a few challenges in their day to day work. One is that setting up and maintaining Express can be a daunting task. Another is that using the "User Instances" feature (which has been deprecated) leads to a lot of confusion - developers connect to one instance of the database through their tools, and another instance of the database through their program, and don't understand why updates to one aren't reflected in the other. This is actually due to the AttachDbFileName setting, but in most examples I've seen, the two seem to go hand in hand.

One problem with SQL Server 2005 Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server.

I've been taking advantage of the free SQL Server Express edition for some time. I see there is a new release of SQL Server Express 2008 R2. In this tip I'll discuss the features, limitations, requirements and installation experience.

As a lot of line-of-business applications are being built with SQL Server 2005 Express Edition as their backend database, we need to make sure that we backup the system and the user databases running on these instances. Unfortunately, SQL Server 2005 Express Edition does not come with SQL Agent which we would normally use to create a database maintenance plan to backup all the databases. How do we perform a backup of our system and user databases in SQL Server 2005 Express Edition similar to how we do it in other editions?

With the release of SQL Server 2005, Microsoft also released SQL Server 2005 Express Edition. This is the free version of the SQL Server engine that existed in prior releases under the MSDE namesake. While there were several great enhancements with SQL Server 2005, one thing that is still lacking is the ability to schedule jobs. Although there is not the direct ability to schedule jobs in the Express Edition there are third party tools that give you this capability.

With the release of SQL Server 2005 Express, Microsoft has offered a lot of new features that did not exist with MSDE. A couple of these things include Reporting Services features as well as a GUI management tool to manage SQL Server instead of having to do everything via command line. Although they have added a lot of new features, not all of the tools needed to maintain and troubleshoot SQL Server are included. One of these tools that is not included is Profiler. You can still run a server side trace on your SQL Server Express databases as well as import the data into a table for analysis, but sometimes having a GUI tool to troubleshoot an issue is much simpler than using a server side trace. So what are the options?

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

One very nice feature that was introduced in SQL Server 2005 was the Dedicated Administrator Connection (DAC). This gives you the option to always have a way to connect to SQL Server if SQL Server is unresponsive. In the Standard and Enterprise versions of SQL Server this is enabled by default, but you have to make some configuration changes for this to work for SQL Server Express. In this tip I walk through how to set this up for SQL Server 2008 R2 Express, but the same steps can be followed for SQL 2005 Express and SQL 2008 Express.

Have you heard of the SQL Server 2005 Express edition, but not sure where it fits into your overall architecture? Are you concerned it is too constraining and will not meet your business needs? Are you looking for ways to continue to use SQL Server in niche capacities? Are you trying to balance distributing your applications without causing a future server consolidation project? If so, this tip will outline the opportunities with SQL Server 2005 Express edition in addition to considerations when adopting this version of SQL Server 2005.

Developers using SQL Server Express face a few challenges in their day to day work. One is that setting up and maintaining Express can be a daunting task. Another is that using the "User Instances" feature (which has been deprecated) leads to a lot of confusion - developers connect to one instance of the database through their tools, and another instance of the database through their program, and don't understand why updates to one aren't reflected in the other. This is actually due to the AttachDbFileName setting, but in most examples I've seen, the two seem to go hand in hand.

One very nice feature that was introduced in SQL Server 2005 was the Dedicated Administrator Connection (DAC). This gives you the option to always have a way to connect to SQL Server if SQL Server is unresponsive. In the Standard and Enterprise versions of SQL Server this is enabled by default, but you have to make some configuration changes for this to work for SQL Server Express. In this tip I walk through how to set this up for SQL Server 2008 R2 Express, but the same steps can be followed for SQL 2005 Express and SQL 2008 Express.

I've been taking advantage of the free SQL Server Express edition for some time. I see there is a new release of SQL Server Express 2008 R2. In this tip I'll discuss the features, limitations, requirements and installation experience.

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

As a lot of line-of-business applications are being built with SQL Server 2005 Express Edition as their backend database, we need to make sure that we backup the system and the user databases running on these instances. Unfortunately, SQL Server 2005 Express Edition does not come with SQL Agent which we would normally use to create a database maintenance plan to backup all the databases. How do we perform a backup of our system and user databases in SQL Server 2005 Express Edition similar to how we do it in other editions?

With the release of SQL Server 2005 Express, Microsoft has offered a lot of new features that did not exist with MSDE. A couple of these things include Reporting Services features as well as a GUI management tool to manage SQL Server instead of having to do everything via command line. Although they have added a lot of new features, not all of the tools needed to maintain and troubleshoot SQL Server are included. One of these tools that is not included is Profiler. You can still run a server side trace on your SQL Server Express databases as well as import the data into a table for analysis, but sometimes having a GUI tool to troubleshoot an issue is much simpler than using a server side trace. So what are the options?

One problem with SQL Server 2005 Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server.

Have you heard of the SQL Server 2005 Express edition, but not sure where it fits into your overall architecture? Are you concerned it is too constraining and will not meet your business needs? Are you looking for ways to continue to use SQL Server in niche capacities? Are you trying to balance distributing your applications without causing a future server consolidation project? If so, this tip will outline the opportunities with SQL Server 2005 Express edition in addition to considerations when adopting this version of SQL Server 2005.

With the release of SQL Server 2005, Microsoft also released SQL Server 2005 Express Edition. This is the free version of the SQL Server engine that existed in prior releases under the MSDE namesake. While there were several great enhancements with SQL Server 2005, one thing that is still lacking is the ability to schedule jobs. Although there is not the direct ability to schedule jobs in the Express Edition there are third party tools that give you this capability.