Installing and Upgrading to SQL Server 2005


SQL Server 2005 RoadShow - Installing & Upgrading

to SQL2K5 (Part II in the series)

In my last article on the SQL

Server 2005 Roadshow (click link to read), I gave you an overall look at the

event itself, and a prime on the content presented there. I provided some background on the keynote, the speaker presentations, the vendor exhibits, and hopefully, a taste of what it was like to attend such a marvelous event. The main breakout session that I cover is the administrative track, which will supply us with the material in our follow-up articles. So, let’s ease on down the road and talk about what you’ll need to know before upgrading and installing SQL Server 2005 in your company.

Note: You can get more information about the SQL Server Road Shows here.

Continuing our series, the first of the administration

sessions, we set out to learn “What will it take to Upgrade to SQL Server

2005.” Indeed there’s a lot to

learn, and I will highlight some of the main differences from 2000, known issues

and workarounds, as well as discuss the methods for installing and upgrading.

Since the agenda here was very organized, I will stick to it as follows:

  • SQL

    Server 2005 installation enhancements and changes

  • Known

    issues, solutions and workarounds

  • Installation


  • Upgrading

    from previous versions

Installation Enhancements &


Before you go anywhere with SQL 2K5, you must be aware of

the many changes and improvements to installing the software, as well as the

types of installations used in various scenarios. There is a host of new enhancements that differ from SQL 2000, such as the installation and upgrade of individual components, improved

error logging and failure reporting, instance directory structure, a new system

configuration checker to ensure proper prerequisites, easier deployment for

remote sql servers, as well as unattended and silent installs.

For the new incarnation of our beloved SQL Server,

installation will be windows installer-based, which combines all of its

components into a single product, making each component easily selectable and

configurable, per user preference. In

addition to Analysis Services, Reporting Services, Notification Services being a

separate install from the Database Engine, DTS (now Integration Services), SQL

Profiler, and Database Tuning Advisor are optional installs as well.

Some features and components by default will be disabled or not

automatically installed. This

includes DTS, Replication, Full-Text Search, Service Broker, and SQL Mail.

New to the list of installs, (not discussed in this article) are SQL

Debugging, SQLiMail, and Database Mirroring. The user will have the ability to

add and remove all or any of these components via the Add/Remove Programs


The structure of the setup program reflects its Windows

Installer-based character, and is sure to make installation more

straightforward. Although the

options for the user will be much greater, by compartmentalizing the

installation components, it will certainly streamline management and maintenance

of your SQL Server deployment.

For example, applying patches and services packs will

become easier to implement and maintain than previous versions. With

improvements to SQL Server 2005, you can incorporate hot fixes and service packs

by not only replacing older versions of files in their source, but also download

updates automatically at the time of the installation.

Now, with improved error log reporting, alerts are more

detailed, and each component install has a separate log.

You’ll be in a better position to not only understand why your sql

server installation has failed, the setup program will even make suggestions on

what corrective action to take based on the log output.

The directory naming convention has changed, where

different instance directories are created for each individual service (ie

Engine, Analysis Services, Reporting Services, etc.)

In addition, instance names in the registry will be referenced with its

corresponding directory. The new file structure also helps support high

availability upgrades, where a new instance is created in parallel with the old

instance staying online. It will

continue to run until the new instance is ready, and only then will the service

be stopped and cut over to the new one. One

contrary aspect to point out is that the ability to rename instances is not

supported in SQL2K5.

So, how many SQL2K5 instances can reside on one

server? Microsoft says a maximum of

50 instances per server are supported, but that’s not all.

It is possible to have running at the same time in addition to the 50 SQL

Instances, 50 instances of Analysis Services, 50 Full-Text, and 50 Reporting

Services! That’s a lot of

instances, but with 64-bit technology and WOW, if you’ve got the hardware,

then such an implementation could be manageable.

You can also have installed different versions (2000 and 2005), as well

as varying editions of SQL Server – (ie: Enterprise, Developer and Express can

play nicely together on the same box.

Always a bit tense when first performing a new sql

installation, hoping and crossing your fingers that all the prerequisites have

been met, and that the OS and H/W environment measures up?

Well, have no fear; a neat new utility will check all this out for you

– in advance. The System

Configuration Checker will be your guide and ensure that all the installation

prerequisites are in order, before the installation itself begins.

Taking advantage of Windows Management Instrumentation (WMI) technology,

it will check for all the necessary requirements, and if they are not all met,

will prevent the installation from taking place.

These blocking requirements include: Availability of WMI Service, OS

service pack level, minimum requirements and SQL Server compatibility with OS,

check H/W specs, pending reboots (files required for setup locked), availability

of performance counters, path permissions, and no compression.

The SCC also identifies other requirements, which do not block

installation. For example, IIS, MDAC, and other existing sql server editions.

Furthermore, missing components such as .NET Framework v.2.0, Native Client and

Setup Support files are detected and automatically installed for you.

One of the other cool things I want to mention is

support for Remote Installation. Via

the command line you can enter parameters for the destination computer, with

admin account and password. If

you’ve ever had to deal with image-based deployment of SQL Server on multiple

machines, based on your predefined installation, you most likely ran into the

headache of having to manually change the server name.

Well, now upon immediate completion of installation, SQL Server will

check if the computer name has changed, and update this automatically.

Moreover, the need to run setup to reset the computer name is eliminated

– simply change the name, restart the service, and update the sysservers table

using sp_dropserver/sp_addserver.

Known Setup Issues

What would a Microsoft release be, without some known setup

issues? Many of these you may be

aware of from previous versions, and some new ones to look out for. Let us

review all of them - just in case you memory fails you after waiting so long to

install a new version of SQL Server 😉

First some setup defaults, which being security conscious

as MS is, the following SQL Server 2005 features and components are disabled by


  • Analysis Services
  • Database Mirroring
  • SQL Debugging
  • DTS Service
  • Notification Services
  • Replication
  • Reporting Services
  • Service Broker
  • SQLiMail
  • Sample Databases
  • Xp_web

And, not too mention, which wasn’t, and I actually brought it up during the session,

  • Xp_cmdshell

If you try to use xp_cmdshell, you may receive this: “Msg 15501,

Level 16, State 1, Procedure xp_cmdshell, Line 1 This module has been marked

OFF. Turn on 'xp_cmdshell' in order to be able to access the module.”

Definitely, a step forward in making our SQL Server implementations more

secure. So it appears in order to further their efforts in making their server

products more secure, Microsoft has graciously turned off xp_cmdshell by

default. Not a bad idea. If you never use it, then you’ve got a

little bit of added security by default. And, I guess if you can figure out

how to turn it on, then you know enough about Sql Server to know how to secure

it too.

Things to avoid when running setup to install SQL Server across the network,

is NOT to use mapped drives. Be

careful to make sure that if you use a UNC path, that it has not been mapped to

a drive letter, as it may cause setup failure.

As of Beta 2, changing the Windows service account under which any SQL Server

Service runs is not supported in this release. This should change by the time RTM rolls around.

Speaking of Service Accounts, don’t forget about the required

permissions for the SQL Server Agent. It must be a member of the sysadmin fixed server role, and have the usual Windows permissions:

  • Log on as a service;
  • Log on as a batch job;
  • Replace a process level token;
  • Adjust memory quotes for a process;
  • Act as part of the OS;
  • Bypass traverse checking.

With all the talk (see above) of how neat the System Config Checker is, it does come with a caveat. The SCC may indeed fail with a “Performance Monitor Counter Check Failed” message, and will block the Setup program from continuing with installation. This is because it cannot verify an existing registry key or run the lodctr.exe to load the counters. To continue the setup, you must manually increment the registry key. (There is a help file in \setup\help\1033\setupsql9.chm to assist)

Also, although I mentioned earlier that multiple instances of varying editions can reside on the same server, the client installs are not as accommodating. Specifically, and while occurrence of this is sporadic, the client components for SQL Server Express may prevent installation of other edition client components.

For Reporting Services, ASP.NET must be enabled on Windows 2003 before installation. In addition, it may fail to install on a server that is case-sensitive, if the wrong case for the account name is used. Another gotcha is if the CSP service (Cryptographic Services is stopped or disabled on Win2K3, a Windows Logo requirement message appears and setup fails. Finally, on this topic, no longer are the old network protocols like Banyan Vines, Multi-Protocol and AppleTalk supported. If you’re using anything other than Named Pipes and TCP/IP by now, then perhaps a new line work is in order :-). Since the presenter brought it up, I figured I might as well mention it too.

Installation Checklist

Now, on to something that every manager asks you to provide, and every DBA should have – an installation checklist. There are several important things to consider each time whether you are ready to install a new sql server engine or component, or upgrading from previous versions. Some are very typical and obvious, such as checking system requirements, reviewing security issues, and taking a backup of your current sql server instance if you are upgrading. Others, such as shutting down anti-virus programs, ensuring administrator permissions on the target machine and proper service account rights, are not always obvious. Once your setup is complete, and the latest service packs applied, you’ll want to verify your installation, by checking the summary log file for output, make sure the services are running, and launching the program. Rather than listing each bullet point here, I have posted the DBA’s

checklist in the Contact Us/Resource Section of my site ( for your acquisition. You can use this as your prime for SQL2K5, and incorporate it with your company’s specific business requirements.

Upgrading from Previous Versions

Finally, in our last discussion topic, we will review all the things you need

to know to get to SQL2K5, and the proper upgrade paths.

Let’s break it down as follows, by talking about:

  • Supported & Unsupported Upgrades
  • Edition Upgrade
  • Binaries and Data Files Upgrade
  • Upgrade Methods
  • Upgrading In-Place
  • Component Upgrade
  • Upgrade Issues and Considerations
  • What is a supported upgrade? To upgrade to SQL2K5, you will need to be coming from a SQL Server 2000 with SP3/SP3a or later. Existing SQL Server instances will be upgraded and migrated to new folder and registry locations. Build-to-build upgrades will allow you to upgrade from one edition to another, as well as from your Beta2 install. (This will save time, rather than having to reinstall the whole

    kit and caboodle). Upgrades from SQL2K5 Beta software prior to version 2 is not

    supported. (In fact, Beta 1 users are not at all supported on this release, and

    there are several data access failure points from connecting with via MDAC 9.0,

    the Native SQL Client, or previous MS VS 2005 versions – so don’t bother)

    Any edition from SQL Server 2000, can be upgraded to SQL2K5.

    These include Enterprise, Developer, Standard, Personal, and Evaluation

    editions. In order of precedence,

    low to high, Desktop can be upgraded to Express, Standard, Developer and

    Enterprise Editions (XSDE); Personal to S,D,E; Standard to D and E, and

    Developer to Enterprise edition.

    A straight upgrade of the installation files (the binaries), from SQL2K to

    SQL2K5 is no problem and the quickest upgrade path during setup. As in v2000,

    the upgrade of the data files is supported for version 7.0, but no direct

    upgrade available. This is accomplished by using upgrade methods outside of

    setup, such as the traditional backup and restore, detach and attach (excluding

    system db’s), and the copy database wizard.

    However, if you still have legacy installs out there lurking on v.6.5,

    you will not be able to directly upgrade to SQL2K5 – no surprises there.

    You won’t be able use the setup to upgrade instances that reside on

    clustered nodes, and no direct upgrades from MSDE.

    And just in case your down with 64-bit implementations of SQL 2000,

    that’s not supported either.

    SQL2K5 will allow in-line upgrade wherein you can perform side-by-side

    installation of the newer binaries, to reside along side with the existing older

    ones. Older and newer registry keys

    will also exist side-by-side. As

    discussed above earlier, this will allow for very limited downtime when

    upgrading, and comes online as soon as the services are restarted, now using the

    new files. The user may now

    uninstall or delete the old binaries and registry keys at his or her


    Another useful aspect of the in-place upgrade is the unique instance ID’s

    are generated at the time of install. The

    directory and registry naming convention will allow the upgrade of a single

    component to future SQL versions without dependencies on other SQL Server

    components. The previous directory structure gets copied into the new one, using

    the instance id created. For example, you will see an instance ID like MSSQL.x

    for each service component.

    SQL server components such as Replication, SQL Agent, Full-Text Search, and

    Analysis Service are part of the core upgrade.

    For Analysis services, data migration to a new instance is required as

    part of the upgrade, using the migration wizard.

    In addition, some other upgrade issues and considerations to keep in mind.

    Where the data integrity of index can no longer be guaranteed, or a

    collation that changes during the upgrade wherein an index is no longer sorted

    correctly, constraints and indexes will be automatically disabled.

    “Sys” is now a reserved name in SQL2K5, thus eliminating the system

    tables, and instead creating catalog views prefaced with “sys”.

    Therefore, due to the new engine design, (which we will discuss in a

    future follow-up article), any user-defined users with the name “sys” will

    cause the upgrade to fail. Be sure to drop any user-defined login first, that has the

    same name as one of the fixed server roles (sysadmin, serveradmin, setupadmin,

    etc.) Also, any maintenance scripts

    you may have that query system tables should be modified to reflect the new

    catalog views.

    Those using Full-Text Search need to know that its indexes will be rebuilt

    during the upgrade, and downtime should be planned, especially if the catalog is

    very large. MS does say, however,

    that the rebuild times have been remarkably improved 400% faster.

    For a more in-depth analysis of the setup process, click on the hyperlink for

    another good article on Setup

    and Deployment. To check for

    updates and changes in the SQL2K5 code base before final RTM, you visit the

    newsgroups at

    Well, that’s a lot of material we covered, and that’s just installing and

    upgrading. By reengineering the

    setup process, installation is much more flexible and straightforward.

    Do pay attention to detail, and take the time to plan your upgrade

    strategy. Hopefully, this latest

    article will have provided you with the foundation to begin thinking about and

    planning to install and upgrade to SQL2K5.

    Please stay tuned to more of my follow-up articles from the 2005 RoadShow

    – that’s all for now :-).

    Written by: Robert

    Pearl, President

    Pearl Knowledge Solutions, Inc.

    Copyright © 2005 - All Rights Reserved.

    Note: Not to be reprinted or published without express permission of the



    SQL is the Center of Your Universe!


4.4 (10)




4.4 (10)