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
      https://download.spidynamics.com/1/ad/sql.asp?Campaign_ID=70130000000CK1E

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

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

=============================

April 17, 2006

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

2. Jump Start
      - Using the Query Editor

3. Check It Out
      - Visual Studio Express is Free

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

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

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

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

Sponsor: SPI Dynamics

ALERT: "How a Hacker Launches a SQL Injection Attack!"
      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!
      https://download.spidynamics.com/1/ad/sql.asp?Campaign_ID=70130000000CK1E

1.

Commentary

Upgrading to SQL Server 2005 Express Advanced Services
      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 FilesMicrosoft SQL ServerMSSQL.1MSSQLTemplate 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.

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

Hit Your SQL Server 2005 Performance Target
      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:
      http://ads.quest.com/SQLServerMagExpressNLTopToadSQLServer050106

******************************

2.

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

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.
      http://www.windowsitpro.com/go/essential/xosoft/sqlserverha/?code=SQLExpHot0501

3.

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 at:
      http://msdn.microsoft.com/vstudio/express/

4.

Events and Resources

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

Configuring and Managing a Virtual Lab for the Enterprise
      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.
      http://www.windowsitpro.com/go/seminars/surgient/virtuallab/?partnerref=0503emailannc

Disaster Recovery for Your Windows-Based Applications
      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
      http://livemeeting.viewcentral.com/reg/Neverfail/WindowsITPro

Is Your SQL Database Working at Peak Efficiency?
      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!
      http://www.windowsitpro.com/go/toolkit/polyserve/?code=0503emailannc

5.

Featured White Paper

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

6.

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

7.

Announcements

SQL Server Magazine Master CD--SAVE 50%!
      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:
      https://store.pentontech.com/index.cfm?s=9&promocode=eu2865uc

Save 44% On the Windows IT Security Newsletter
      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
      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.
      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
      http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%

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

View the SQL Server Magazine Privacy Policy.
      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.