SQL Server Express UPDATE--May 1, 2006: Upgrading to SQL Server 2005 Express with Advanced Services


This Issue Sponsored By

This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.

SPI Dynamics
&nbsp &nbsp &nbsp https://download.spidynamics.com/1/ad/sql.asp?Campaign_ID=70130000000CK1E

Quest Software
&nbsp &nbsp &nbsp http://ads.quest.com/SQLServerMagExpressNLTopToadSQLServer050106

&nbsp &nbsp &nbsp http://www.windowsitpro.com/go/essential/xosoft/sqlserverha/?code=SQLExpHot0501


April 17, 2006

1. Commentary
&nbsp &nbsp &nbsp - Upgrading to SQL Server 2005 Express Advanced Services

2. Jump Start
&nbsp &nbsp &nbsp - Using the Query Editor

3. Check It Out
&nbsp &nbsp &nbsp - Visual Studio Express is Free

4. Events and Resources
&nbsp &nbsp &nbsp - The Essential Guide to Infrastructure Consolidation
&nbsp &nbsp &nbsp - Configuring and Managing a Virtual Lab for the Enterprise
&nbsp &nbsp &nbsp - Disaster Recovery for Your Windows-Based Applications
&nbsp &nbsp &nbsp - Is Your SQL Database Working at Peak Efficiency?

5. Featured White Paper
&nbsp &nbsp &nbsp - Do You Have 100 Percent Uptime for Your Mission Critical
&nbsp &nbsp &nbsp Email?

6. New Product
&nbsp &nbsp &nbsp - Manage Your Entire Database With One Tool

7. Announcements
&nbsp &nbsp &nbsp - SQL Server Magazine Master CD--SAVE 50%!
&nbsp &nbsp &nbsp - Save $100 On the Exchange & Outlook Newsletter



Sponsor: SPI Dynamics

ALERT: "How a Hacker Launches a SQL Injection Attack!"
&nbsp &nbsp &nbsp It's as simple as placing additional SQL Server commands into a Web Form input box--this action can give hackers complete access to all your back-end systems and firewalls. And your intrusion detection system (IDS) won't stop such attacks because SQL Injections are not seen as intruders. Download this *FREE* whitepaper from SPI Dynamics for a complete guide to protection!
&nbsp &nbsp &nbsp https://download.spidynamics.com/1/ad/sql.asp?Campaign_ID=70130000000CK1E



Upgrading to SQL Server 2005 Express Advanced Services
&nbsp &nbsp &nbsp 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.
&nbsp &nbsp &nbsp 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.
&nbsp &nbsp &nbsp 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.
&nbsp &nbsp &nbsp 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.
&nbsp &nbsp &nbsp 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.


****** Sponsor: Quest Software ******

Hit Your SQL Server 2005 Performance Target
&nbsp &nbsp &nbsp Aim for optimal SQL Server 2005 performance with solutions from Quest Software. Automate the time-intensive process of identifying, re-writing and benchmarking poor SQL with Toad for SQL Server Xpert. Isolate SQL Server stress factors with Benchmark Factory for Databases. Discover, diagnose, and resolve performance issues and database bottlenecks in real time with Spotlight on SQL Server. And forecast and trend storage requirements with Capacity Manager for SQL Server. Hit a performance bull’s-eye. Quest Software has the tools to help you optimize your SQL Server 2005 platform. Download one or all of our free trial products now at:
&nbsp &nbsp &nbsp http://ads.quest.com/SQLServerMagExpressNLTopToadSQLServer050106



SQL Server Express Jump Start

Using the Query Editor
&nbsp &nbsp &nbsp 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.
&nbsp &nbsp &nbsp 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
&nbsp &nbsp &nbsp http://msdn.microsoft.com/vstudio/express/sql/download .
&nbsp &nbsp &nbsp 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.
&nbsp &nbsp &nbsp 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



Hot Spot--XOSoft

Download this Essential Guide to learn the essentials of high availability for SQL Server 2005, including cluster services, replication, and log shipping.
&nbsp &nbsp &nbsp http://www.windowsitpro.com/go/essential/xosoft/sqlserverha/?code=SQLExpHot0501


Check It Out

Visual Studio Express is Free
&nbsp &nbsp &nbsp 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.
&nbsp &nbsp &nbsp 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 at:
&nbsp &nbsp &nbsp http://msdn.microsoft.com/vstudio/express/


Events and Resources

The Essential Guide to Infrastructure Consolidation
&nbsp &nbsp &nbsp Learn the essentials of how consolidation and selected technology updates build an infrastructure that can handle change effectively.
&nbsp &nbsp &nbsp http://www.windowsitpro.com/go/essential/hp/infrastructure/?code=0503emailannc

Configuring and Managing a Virtual Lab for the Enterprise
&nbsp &nbsp &nbsp Use virtual server technology to consolidate your production environment by using only a fraction of the server hardware in the data center. Live Event: Thursday, May 18.
&nbsp &nbsp &nbsp http://www.windowsitpro.com/go/seminars/surgient/virtuallab/?partnerref=0503emailannc

Disaster Recovery for Your Windows-Based Applications
&nbsp &nbsp &nbsp Learn how to differentiate alternative solutions for disaster recovery for your Windows-based applications and to determine what works to ensure seamless recovery of your key systems--whether a disaster strikes just one server or the entire network. Live event: Thursday, May 11
&nbsp &nbsp &nbsp http://livemeeting.viewcentral.com/reg/Neverfail/WindowsITPro

Is Your SQL Database Working at Peak Efficiency?
&nbsp &nbsp &nbsp Increase administration efficiency, build flexible yet inexpensive file-server environments, and increase administration by consolidating your SQL Server environment. Make the most of your resources today!
&nbsp &nbsp &nbsp http://www.windowsitpro.com/go/toolkit/polyserve/?code=0503emailannc


Featured White Paper

Do You Have 100 Percent Uptime for Your Mission Critical Email?
&nbsp &nbsp &nbsp Learn how to address challenges such as making email truly available 24x7x365, securing against viruses, comprehensively backing up email data, and more.
&nbsp &nbsp &nbsp http://www.windowsitpro.com/go/whitepapers/123together/emailhighavailability?code=0503featwp


New Product

Manage Your Entire Database With One Tool
&nbsp &nbsp &nbsp by Blake Eno

&nbsp &nbsp &nbsp 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]
&nbsp &nbsp &nbsp http://www.teratrax.com/



SQL Server Magazine Master CD--SAVE 50%!
&nbsp &nbsp &nbsp Subscribe today and get portable, high-speed access to the entire SQL Server article database on CD--a searchable library that includes every SQL Server Magazine issue ever published. The newest issue also includes BONUS SQL Server 2005 tips. Order now and save:
&nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=9&promocode=eu2865uc

Save 44% On the Windows IT Security Newsletter
&nbsp &nbsp &nbsp For a limited time, order the Windows IT Security newsletter and SAVE up to $80! You'll get 12 helpful issues loaded with endless fundamentals on building and maintaining a secure enterprise, in-depth product coverage of the best security tools available, and expert advice on the best way to implement various security components. Subscribe now at
&nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=1&promocode=eu2564us


Contact Us ====

About the [email protected]
About the [email protected]
About technical questions-- http://sqlforums.windowsitpro.com/web/forum/default.aspx?forumid=10
About product [email protected]
About your [email protected]
About sponsoring an issue of SQL Server Express UPDATE--Richard Resnick, [email protected]

SQL Server Express UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
&nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=9&cid=51&promotionid=1491

Manage Your Account You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here
&nbsp &nbsp &nbsp http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%

To manage your email account, simply log on to our Email Preference Center.
&nbsp &nbsp &nbsp http://www.sqlmag.com/email

View the SQL Server Magazine Privacy Policy.
&nbsp &nbsp &nbsp http://www.sqlmag.com/aboutus/index.cfm?action=privacy

SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street
Loveland, CO 80538
Attention: Customer Service Department

Copyright 2006, Penton Media, Inc. All Rights Reserved.



Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.