So how do you decide whether you want to download the upgrade with Advanced Services? If you’re using SQL Server Express as a simple back-end database for a single user application, you probably don’t need the new features in the advanced services release. However, if you’re using SQL Server Express as a database back end for a multiuser database application and you want to add Web-based reporting or the ability to perform full-text search, then you’ll want to download SQL Server Express with Advanced Services.

Before jumping right into the upgrade process, you need to either uninstall the existing SQL Server Express installation or delete all the template database files from the SQL Server Template Data directory and then perform an in-place upgrade. If you’ve been using SQL Server Express as a production database, you’ll probably want to delete your existing template database files because that technique is quicker than uninstalling SQL Server Express. In addition, all of your databases will automatically be attached at the end of the upgrade process. If you first uninstall SQL Server Express and then install SQL Server Express with Advanced Services, you'll have to manually attach all your user databases.

For those of you upgrading production systems to advanced service, you might wonder if you need to delete the Template Data directory. The Template Data directory contains the templates for all of the SQL Server system databases--by default, the directory path is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data. As a safety measure, Microsoft recommends that you back up the template files before you delete them. Probably the best way to do so is to use Windows Explorer, then navigate to the Template Data directory, and select all 10 of the files in the directory. Right-click and select "Send To, Compressed (zipped) folder," to create a zipped file named tempdb.zip that contains all the old .mdf and .ldf files. Copy the tempdb.zip file to another directory and delete all the files in the Template Data directory.

To perform the in-place upgrade first download SQL Server Express with Advanced Services from http://msdn.microsoft.com/vstudio/express/sql/download . Then run the SQLEXPR_ADV.EXE setup program to install SQL Server Express with Advanced Services. After accepting the End User License Agreement (ELUA) the installation will copy the native SQL Server Client along with the prerequisite setup files to your system. Next, a system configuration check will run. If your system passed this check when you installed SQL Server Express, it should pass again. Click Next to display the Registration Information window and uncheck the "Hide advanced configuration options" box. Then, click Next. To make sure you get all the new SQL Server Express with Advanced Services features, select Client Components and choose the "Entire feature will be installed on local hard drive" option. In the Installed Instances dialog box, select the instance to upgrade--by default this is the SQLEXPRESS named instance. Click Next and on the Existing Components screen, check the SQL Server Database Services and Workstation components and development tools boxes. Click through the remaining dialog boxes to start the upgrade process. Depending on the speed of your PC, the upgrade should take 10 minutes or less and there’s no need to reboot your PC.

SQL Server Express Jump Start

Using the Query Editor by Michael Otey

If you’ve been following some of my previous Jump Start columns, you’ll know that we’ve covered the basics of creating databases, tables, and columns by using the various SQL Server 2005 Express data types. In the process, I’ve shown you some of the T-SQL code required to create these databases and database objects. In the next issue, I'll continue to expand on our example of building up a media collection database. But, in this issue, let’s take a short detour and discuss how to create and execute a T-SQL script.

The short answer is Query Editor. Query Editor is included with SQL Server Management Studio Express (SSMSE). You could also use the text-oriented SQLCMD tools, but if you’re like me, you’ll prefer the graphical editing environment that SSMSE and Query Editor offer. Although SSMSE was a Community Technology Preview (CTP) for quite some time, you can download the recently released finished version as a part of SQL Server 2005 Express Advanced Services or as a standalone tool. You can find both at http://msdn.microsoft.com/vstudio/express/sql/download.

However, if you happen to be running SQL Server Express on an x64 OS (like I am), for some inexplicable reason, SSMSE won’t install on the x64 OS even though SQL Server Express can be running in the WOW64 layer.

Once you’ve downloaded and installed SSMSE, you can start Query Editor by using the Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio Express option. When the program starts, enter your database connection information. To begin running the T-SQL code, click the New Query button to start Query Editor. Enter the T-SQL code in the blank editing window, then click Execute or press F5 to run the code. Be aware that the current database is displayed in a drop-down menu in the left part of the toolbar. You can change the current database by using that drop-down menu or entering and running the USE command. To use T-SQL change to the AdventureWorks database, use the following command:

USE adventureworks

Check It Out

Visual Studio Express is Free by Michael Otey

On April 19, 2006, Microsoft announced that all Visual Studio Express products would be free. Originally, Microsoft announced that this product line would be free for a limited introductory period that was originally scheduled to end November 2006. This news probably isn't a tremendous surprise considering the effect that free, open-source tools are having on the market, but it’s still a cool bonus for beginner and hobbyist developers.

Don’t be fooled by the free price tag--Visual Studio Express products are powerful development systems. More than 5 million copies of the Visual Studio Express products have been downloaded from the Microsoft site. Check out the free Visual Studio Express products.

New Product

Manage Your Entire Database With One Tool by Blake Eno

Teratrax announced Database Manager 4, a database administration tool for all SQL Server editions, including SQL Server 2005 Express. Database Manager lets you locate database objects by providing object filtering and text-based code searching. Database Manager's Intellisense query editor provides you with built-in T-SQL reference and lets you query and edit data. You can then save results in Microsoft Excel, HTML, and XML formats. The product also lets you schedule SQL database jobs and perform backups and restores. Additional features include one-click indexing of tables, individual indexes, and individual primary keys. For more information, contact Teratrax at sales@teratrax.com or support@teratrax.com.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More