Commentary
by Michael Otey, [email protected]
The recently released SQL Server 2005 Express with Advanced Services might cause a dilemma for existing SQL Server 2005 Express users. The new upgrade is a superset of SQL Server Express that provides new functionality. SQL Server Express with Advanced Services and SQL Server Express share the same database engine and client components and tools, such as SQL Server Configuration Manager and SQL Server Surface Area Configuration tools. However, SQL Server Express with Advanced Services adds Reporting Services, full-text search, and SQL Server Management Studio for Express--the new graphical management tool.
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
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 [email protected] or [email protected].
