Data Management

Make the jump to SQL Server 2005 with these notes and tips

There are a number of changes you should understand before you begin planning the leap from SQL Server 2000 or SQL Server 7 to the recently released SQL Server 2005.

SQL Server isn't a product that Microsoft updates very often, so when they do release an upgrade, it has the potential to be significant. As such, there are a number of changes you should understand before you begin planning the leap from SQL Server 2000 or SQL Server 7 to the recently released SQL Server 2005.

A warning: SQL Server 2005 has been designed from the ground-up to be a true enterprise-grade product capable of supporting even the largest data warehouse. While older versions of SQL Server were good and fairly scalable, SQL Server 2005 takes the product to these heights. As such, expect a steep learning curve for the product.

Management changes

You have probably heard that Microsoft jettisoned Enterprise Manager and Query Analyzer in SQL Server 2005. Database administrators everywhere may have cried out in pain, but it's actually a good move. The new SQL Server Management Studio (Figure A), while more complex than the former tools, is, from a management perspective, a much better tool for the task. Now, from a single tool, you can make changes to the database structure, and run complex queries against the database, along with a whole host of other tasks, including creating Business Intelligence and Analysis Service projects.

Figure A

The SQL Server 2005 Management Studio

Even users of the SQL Server 2005 Express Edition (formerly MSDE) can rejoice at finally having a GUI to manage this free version of SQL Server. While the Express Edition does have limitations (a 4GB maximum database size, for example), it can be extremely useful for testing and for building small applications. The addition of a GUI, called the SQL Express Management Studio (in beta as of this writing) to this product is sure to make it a much more popular choice in Windows environments.

A watch out

If you peruse the various SQL Server forums, you are likely to see a number of messages from people indicating stability issues with Management Studio. I don't have an answer to these problems, but can offer this advice: Get SQL Server 2005 installed in your lab environment and start testing now so you can run through the potential problems and thoroughly test your applications against this new product and familiarize yourself with the new administrative tools.

SQL Server Upgrade Advisor

Even though nothing can replace real, intensive testing of each of your applications, Microsoft has provided the SQL Server Upgrade Advisor (also available on the SQL Server 2005 distribution media), a tool that can help give you a jump start on your testing. The sole purpose of this tool is to help you find and identify potential problems you may run into as you upgrade your database servers and related applications. Upgrade Advisor can run against both SQL Server 7 and 2000 servers.

I'll go over the installation and use of the SQL Server Upgrade Advisor. I recommend downloading the tool rather than using the version on the distribution media since you can then be sure that you're using the latest edition.

Upgrade Advisor has a few prerequisites that you need to observe:

  • Windows 2000 Service Pack 4; Windows Server 2003 Service Pack 1; Windows XP Service Pack 2
  • The Microsoft .NET Framework 2.0.
  • SQL Server 2000 Decision Support Objects (DSO) if you are analyzing Analysis Services.
  • SQL Server 2000 client components if you are analyzing Data Transformation Services.
  • A processor speed of at least 500 MHz.
  • 15 MB of available hard disk space.

Install the tool to a client machine rather than to a production database server. While Upgrade Advisor does not make any changes to your configuration, why add another component to the server to troubleshoot? Further, the .NET Framework 2.0 is known to break some applications and it is required in order to use the Upgrade Advisor.

I'm not going to show a screen-by-screen installation of the Upgrade Advisor since it's a typical Windows software installation with nothing unusual. For this article's demonstration, I have installed the Upgrade Advisor to a server (Windows Server 2003 R2) separate from the server running SQL Server 2000 SP4. My database installation is very typical.

Start the Upgrade Advisor by going to Start | All Programs | SQL Server 2005 | SQL Server 2005 Upgrade Advisor. Figure B shows you the Upgrade Advisor introductory screen.

Figure B

The SQL Server 2005 Upgrade Advisor introduction.

As indicated on the screen, there are two options from which you can choose:

  • Upgrade Advisor Analysis Wizard: Performs an analysis of your database service in order to find any issues that may prevent you from upgrading from SQL Server 2005.
  • Upgrade Advisor Report Viewer: Shows you the results of your analysis.

Obviously, before you can see a report, you need to perform an analysis. The analysis is performed in five steps:

  1. Identify which SQL Server 7 or 2000 components you want to analyze.
  2. Provide credentials that the Upgrade Advisor can use to scan your SQL Server.
  3. Select databases and provide any additional parameters that may be needed.
  4. Perform the actual analysis.
  5. View the results and any documentation that Microsoft may have to help you correct any potential problems.

Component selection

Your first action is to select the components you want to analyze (Figure C). This step is really easy since the Upgrade Advisor does all the work. Just type the name of the server on which you run SQL Server 7 or 2000 and click the Detect button. The Upgrade Advisor does the rest and fills in the appropriate boxes. If, for some reason, the wrong boxes are selected, make the necessary corrections and click the Next button. On my lab server, the Upgrade Advisor found that I am running SQL Server (imagine that!) and the Data Transformation Services service.

Figure C

Choose the SQL Server components that you want the Upgrade Advisor to analyze.

Authentication against SQL Server

In order to move ahead, the Upgrade Advisor has to be able to get at the nitty-gritty details of your SQL server; in order to do this, the tool needs to log in to the server.

On the next screen of the advisor wizard (Figure D), provide your authentication method (Windows Authentication or SQL Server authentication) and select the SQL Server instance on which you want Upgrade Advisor to report. If you're running SQL Server 7, you won't be able to select other instances since that version only supports a single instance. If necessary, provide the user name and password for a Windows or SQL Server user that has a reasonable level of access to the SQL Server.

If you have trouble connecting to your server, make sure that the SQL Server Agent is running and try again.

Figure D

Provide an appropriate authentication method and credentials so the Upgrade Advisor can work its magic.

Select databases and choose options

You can limit the Upgrade Advisor to reporting against specific databases if you like. You can also include trace and SQL batch files in your analysis, if you have them. For this example (Figure E), I'm going to allow the tool to analyze all of the databases on my lab system, but will not include any trace or batch files.

Figure E

Select the databases you want to analyze and decide whether to include trace or SQL batch files.

On the next screen of the wizard (Figure F), choose how you want to handle the analysis of your SQL server's Data Transformation Services. Data Transformation Services has undergone a complete—err—transformation in SQL Server 2005 and is one of the foundations of SQL Server 2005's Business Intelligence functionality.

Figure F

Choose your DTS transformation options.

Perform the analysis

With your selections made, the Upgrade Advisor is ready to do its thing. Before it does so, the tool provides you with a quick summary of your selections, as shown in Figure G. Click the Run button to proceed with the analysis. Figures H & Ishow your progress during and after the analysis.

Figure G

Click the Run button to begin the analysis.

Figure H

The Upgrade Advisor keeps you apprised of its progress.

Figure I

The analysis completed on my lab server with two warnings.

View the analysis results

Once the analysis completes, click the Launch Report button to see what errors were found and the warnings issued. The Upgrade Advisor provides a separate report for each analyzed component. For my lab server, two reports were generated—one for SQL Server and one for DTS. The SQL Server report is shown in Figure J and the DTS component in Figure K. Note that the Database Engine report provides just a generic statement indicating that further investigation should be undertaken before upgrading. The DTS report indicates that there are no DTS packages on my lab server. The analysis was run because the DTS service itself is installed, though.

Figure J

The Database Server report provides a general message indicating that there are several upgrade issues that can't be detected by the Upgrade Advisor.

Figure K

For my lab server, there are really no DTS issues to deal with.

In short, I'm good to go with an upgrade, assuming that nothing from Microsoft's list of other issues (as shown on the Database Engine report) bars an upgrade.

Upgrade Advisor summary

The Upgrade Advisor is intended to be a starting point for you to help locate the major issues that could keep you from performing a successful upgrade to SQL Server 2005. It's not intended to be the end-all-be-all tool, though. The only safe way to upgrade is to test the heck out of your applications after addressing the major issues identified by the Upgrade Advisor.

A lot has been deprecated

You know that Microsoft has made a lot of changes in SQL Server 2005. In short, they're deprecated a lot of older commands and features. For example, in order to maintain compatibility, system tables have been replaced with views into the new system catalog. However, don't expect these views to be available forever. Another example lies in the deprecation of the DUMP, LOAD, and sp_addtype commands, which have been replaced with BACKUP, RESTORE, and CREATE TYPE, respectively. There's a lot more, so I suggest you review the SQL Server 2005 release notes for a complete list of changes.

One more thing to note:Northwind and Pubs, the sample databases used since creation itself, are history, although you can still download them from Microsoft's Web site and load them into your SQL Server 2005 installation. Microsoft has replaced these relics with a new database named AdventureWorks.

Upgrading to SQL Server 2005

There are two ways you can upgrade to SQL Server 2005: Side-by-side (migration), or in-place (upgrade). In a side-by-side upgrade, SQL Server 2005 is installed along with SQL Server 2000 as a separate instance. Under such a scenario, you need to manually detach your database from the old instance and reattach your database to the new instance.

Under the in-place upgrade path, SQL Server 2005 is installed right over SQL Server 7 or 2000, and all of your databases and accounts are moved. Table A is from Microsoft's Web site and it outlines the tools and methods you can use to upgrade your old SQL Server. Note that, under the Upgrade Tool column, "Setup" refers to the SQL Server 2005 installer.

Table A

SQL Server 2000/7 Component

Upgrade Method

Migration Method

or Tool

Database Engine


Side-by-side installation and then database backup/restore, detach/attach.

Analysis Services


Migration Wizard migrates objects, requires optimization and client provider upgrades.

Integration Services


DTS Migration Wizard. Migration Wizard converts 50-70 percent of tasks, requires some manual migration; runtime DTS DLLs available in SSIS; package re-architecture is recommended.

Reporting Services


Side-by-side installation and deployment of reports on new instance.

Notification Services


Upgrade of Notification Services instances during install.

For this example, I'm going to do an in-place upgrade of my SQL Server 2000 system to SQL Server 2005.

SQL Server 2005 in-place upgrade

Keep in mind that SQL Server 2005 has a number of prerequisites, such as IIS for Reporting Services, which need to be met before you can upgrade. Review my previous article on SQL Server 2005 installation for more details.

As with a normal installation, an upgrade performs a scan of your system before continuing to make sure you meet the prerequisites. As usual, the first screen of the actual installer asks you for your registration information. I have not shown this screen here.

Your first task: choose your installation/upgrade components (Figure L). I'm installing the database server, Reporting Services and the Workstation components.

Figure L

Choose the components you want to upgrade.

This next screen (Figure M) is included even though it's a part of the installation whether you're upgrading or performing a clean installation. In short, you will probably choose the Default Instance option. If you want to create a new instance, or do a side-by-side installation, you can choose to create a secondary instance.

Figure M

Provide a name for the instance you want to create or choose to install the server as the default SQL Server instance.

If you have SQL Server 7 or 2000 installed on the server to which you're installing SQL Server 2005, the installer will let you know that it found existing components. Select the check box next to the old instance to upgrade it (Figure N). If you want to double-check what you're upgrading, click the Details button (Figure O).

Figure N

Choose the components that you want to upgrade.

Figure O

This is information about the components that you will be upgrading.

During the upgrade process, the installer needs to log in to your old SQL Server system in order to upgrade the various components, including the databases (Figure P). The installer can log in using either Windows or SQL Server authentication. Provide the appropriate authentication details regarding your old database system.

Figure P

Choose the authentication mode that the upgrade tool should use to log in to your SQL Server 2000/7 system.

Once you've made these selections, the remainder of the installation is the same as a new installation. Refer to my previous article on the subject for more information.

Now, SQL Server 2005 is installed and SQL Server 2000 is gone.


SQL Server 2005 really is a new product in every sense. From a completely rewritten database engine, to new components, to a new paradigm in database services, Microsoft has aimed this newest release at every major database out there, from the open source MySQL al the way up to the likes of Oracle and DB2.

Editor's Picks

Free Newsletters, In your Inbox