If you haven’t yet installed SQL Server 2005, you’ll want to make the right choices the first time and minimize the number of steps. The installation process is slightly different depending on whether it is an upgrade from 2000 or a clean install. SQL Server 2005 Express has some differences from the purchased version of SQL Server 2005. By understanding documented, undocumented, and obscure features, you can get relief from installation pain points.

Copying the Installation Files to Disk

One of the first steps in having a smooth installation experience is to copy the SQL Server 2005 installation media (Developer, Workgroup, Standard, or Enterprise) to disk. This allows you to replace the Books Online (BOL) msi file from the original installation media with the latest and greatest BOL msi file. Updating installation media with later versions of files is sometimes referred to as slipstreaming.

If you have a DVD, copy all of the files to either a local or network disk. You must maintain the same directory structure as the DVD or you may experience an error such as the one described in Knowledge Base article 916760.

If you have an iso image file of a DVD, you’ll need to get access to the individual files contained within the iso file. You can burn a DVD from the iso file, extract the individual files contained with the iso file, or mount the iso file as if it is a normal drive letter in Windows. Once an iso file is burned to a DVD, the files can be copied from the DVD to disk. Extracting individual files from an iso file is similar to extracting files from a zip file. IsoBuster is a free utility for extracting from an iso file. To mount an iso file as a drive letter, you can use Microsoft’s reliable but officially unsupported VirtualCD or use the freeware tool Virtual CloneDrive.

Updated Books Online File

Of course, you’ll need to download the latest version of BOL from Microsoft. At the time of publication, the latest BOL was available here and contained in an msi file named SqlServer2K5_BOL_Jul2006_v2.msi. Since new updates are released regularly, make sure you get the latest version.

Updating the Installation Files

Once all of the files are available on disk, just a simple file copy and rename is all that’s needed to slipstream the updated BOL into the SQL Server 2005 installation media. Copy the SqlServer2K5_BOL_Jul2006_v2.msi file to the ...\Tools\Setup folder underneath the top-level folder containing your installation files, delete the original SqlServer2K5_BOL.msi, and rename the updated BOL msi (e.g., SqlServer2K5_BOL_Jul2006_v2.msi) to SqlServer2K5_BOL.msi. When a new version of BOL is released, repeat the process.

You have multiple options for installing from your updated version of the SQL Server 2005 installation media. Go to your ...\Servers\Setup folder underneath the top level folder containing your installation files. You can run setup.exe from a command prompt or from Windows Explorer by double-clicking either setup.exe, default.hta, or splash.exe.

It’s also possible to create a new DVD containing your updated installation files. Some companies like to keep master copies of installation CDs and DVDs in a fire resistant safe so that any system configuration can be recreated after a disaster.

By updating your SQL Server 2005 installation media, you can save yourself a post-installation step and reduce the likelihood of an outdated Books Online being installed. Unfortunately, you can’t slipstream SQL Server 2005 Service Pack 1 files into your SQL Server 2005 media files. You’ll have to install Service Pack 1 as a separate step.

Making an Installation CD from an Installation DVD

The non-Express versions of SQL Server 2005 have installation DVDs and not all machines have DVD drives. If you absolutely must have an installation CD for installing on 32-bit hardware, you can make your own installation CD. The installation DVDs contain both 32-bit and 64-bit installation files as well as other files that don’t really need to be on an installation CD. Here’s a list of the files and folder on the DVD that you don’t need on a 32-bit installation CDs:

Folders

..\Servers\redist\2.0
..\Servers\redist\Windows Installer
..\Tools\redist\2.0
..\Tools\redist\Windows Installer

Files

..\Servers\Setup\*_x64.msi
..\Tools\Setup\*_x64.msi
..\Tools\Setup\SqlServer2K5_BOL.msi

After you copy an installation DVD to disk, you can delete all of the files and folders detailed above and fit the remaining files onto a CD.

Extracting SQL Server 2005 Express Files

SQL Server 2005 Express presents a special challenge because it comes packaged in a self-extracting executable that extracts the files, does an unattended installation, and then deletes the installation files. To take control over an Express installation, you’ll need to extract all of the files out to disk. Microsoft self-extracting installation executables can be run from the command line. By using the /x switch on the command line, the installation is not performed and instead you are prompted for a directory to which the installation files are extracted.

C:\Media\SQL>sqlexpr.exe /x

Figure 1: Dialog box for SQL Express file extraction.

As with purchased versions of SQL Server, you can install SQL Server Express by running setup.exe from a command prompt or from Windows Explorer by double-clicking either setup.exe, default.hta, or splash.exe. You will find these files in the top level directory you specified when you did the extraction. Using the extracted files in this manner makes installing SQL Server Express follow the same process as any other version of SQL Server 2005.

Preparing a Machine for Installation

Before you can install any version of SQL Server 2005 (including the Express edition), the host machine must have Windows Installer 3.1 and the .NET Framework 2.0 installed. In a large enterprise, you can use Microsoft SMS or a third party deployment tool to install these two items prior to installing SQL Server 2005, perhaps scheduling the task for off hours. Installing Windows Installer 3.1 (read more about it and download it) requires a reboot, so you’ll want to plan accordingly.

Upgrading from SQL Server 2000

Upgrading from SQL Server 2000 to SQL Server 2005 requires a minimum of Service Pack 3 on SQL Server 2000. If your SQL Server 2000 instances are below Service Pack 3, you must upgrade them. I found it slightly easier to upgrade to SQL Server 2005 from SQL Server 2000 with Service Pack 4 than from SQL Server 2000 with Service Pack 3. If you don’t have the proper SQL Server 2000 service pack level, you will be blocked from upgrading to SQL Server 2005. You will not be able to select the instance as the following screen capture illustrates.

Figure 2: Upgrade blocked because a service pack is needed.

You can install SQL Server 2000 Service Pack 4 from a command line. You can also use a setup.iss file to do an unattended installation of it as well. If you do apply the service pack from a command line or script, don’t forget to check the completion status.

E:\Media\SP4\x86>start /wait setupsql.exe
E:\Media\SP4\x86>echo %errorlevel%
3010

An errorlevel of 3010 indicates that a reboot is required. This is a standard Windows installer return code, which you can read more about in Knowledge Base article 229683. Out in the field, I also found that 50071 indicates a reboot is required.

If you need to apply a service pack before you can upgrade to SQL Server 2005, you may want to consider forgoing the service pack, uninstalling SQL Server 2000 and performing a clean installation of SQL Server 2005. If you decide to use this approach and need to preserve your data, don’t forget to backup your 2000 databases before uninstalling 2000.

Customizing an Installation

Default installations do not expose the Feature Selection dialog box. If you are installing a purchased product, click the Advanced button on the Components to Install dialog box as shown.

Figure 3: Click the Advanced button to expose the Feature Selection dialog box.

If you are installing Express, uncheck the Hide advanced configuration options checkbox.

Figure 4: Uncheck the checkbox to expose the Feature Selection dialog for an Express setup.

If you want to install all or part of SQL Server 2005 to locations other than the default locations, you’ll need to expand the categories in the Feature Selection tree. This is a little bit tricky, so you’ll need to pay close attention. Since the Data Files item is subordinate to the Database Services category, it inherits its installation path from Database Services. If you want your database files in a different location, you must select Data Files and then click the Browse button. Making such a change does not affect the installation path of the database engine. The first time you install SQL Server 2005, you should expand all of the items in the tree and select each lowest level item one at a time to find out where installation paths can and cannot be set.

In our example, the SQL Server data files are installed to a different path entirely (i.e., S:\Data).

Figure 5: Feature Selection with Data Files installing to an alternate location.

Notice that SQL Server 2005 Express has fewer features to choose from. The exact list of features is different depending on which version of Express is being installed. Notice that although Express Database Services are installed to C:\Program Files\Microsoft SQL Server by default, the Client Components are installed to C:\Program Files. You can accept these defaults or override them independently should you desire.

Figure 6: Feature Selection for Express.

Unattended Installations

Unfortunately SQL Server 2005 does not have a setup.iss file. Instead, it has a template.ini file for controlling an unattended installation. With SQL Server 2000, a GUI installation was recorded into the setup.iss file, which could be used as input to an unattended installation. Using a setup.iss file to replay an installation was very convenient. There is an undocumented LOGNAME command line parameter in SQL Server 2005 than can be used to record an installation into a Datastore.xml file. As a workaround for the lack of a setup.iss file, you can map the contents of a Datastore.xml file to a SQL Server 2005 template.ini file. For more information, read my article on LOGNAME. You can do an unattended installation from a command line without using a template.ini file by supplying the template.ini keywords and values as command line parameters.

SQL Server 2005 Service Packs

The general impression you get from reading a vendor’s documentation is that service packs should be applied without delay. This is particularly true if the service pack fixes a serious security vulnerability. Service packs should be applied quickly, but careful consideration should be undertaken first. Any change to a production system introduces risk. Changing a production system may cause it to either stop working or possibly start working incorrectly. At the time of publication, SQL Server 2005 Service Pack 1 was the most recent service pack. A bug report was filed claiming Service Pack 1 causes dates in SSIS to be misinterpreted. I have not confirmed this, but the point is that any change to a system can potentially be a breaking change. If you do install Service Pack 1, be aware that there is already a hotfix to be applied after Service Pack 1. Find out more about the hotfix and download it from Knowledge Base article 918222.

Unlike operating system service packs, SQL Server 2005 Service Pack 1 cannot be slipstreamed into the original installation media. Keep in mind that applying this service pack will probably require a reboot, so plan accordingly.

When SQL Server 2005 Express was originally released, it did not come with Service Pack 1. The Express editions were later re-released with Service Pack 1 already built-in, but they do need the previously mentioned post-Service Pack 1 hotfix.

SQL Server 2005 Express Faulty Defaults

If you extract the SQL Server 2005 Express installation files as previously shown, you can take control over the installation process. Some of the SQL Server 2005 Express installation defaults may very well be not what you want. By default, SQL Server 2005 Express creates a named instance of SQLExpress instead of a default instance like all other versions of SQL Server 2005 do. If you prefer working with a default instance, you’ll need to extract the Express installation files and specify a default instance.

Figure 7: Overriding the SQLExpress named instance default.

SQL Server 2005 Express defaults to allowing “normal users” (i.e., those without Administrator privileges) to run separate instances of the database engine. This is known as Run As Normal User (RANU) and is a departure from the traditional SQL Server model of one database shared by all. If RANU is not for you, uncheck the Enable User Instances checkbox.

Figure 8: Uncheck the checkbox to allow for traditional server-mode operation.

Uninstalling SQL Server 2005

Under normal circumstances, all versions of SQL Server 2005 can be uninstalled using Add/Remove Programs and following these steps How to: Uninstall an Existing Instance of SQL Server 2005 (Setup) in SQL Server BOL. However, sometimes automated uninstall attempts do not work. Knowledge Base article 909967 details the process of performing a manual uninstall.

Some installation problems are actually caused by a failure to fully uninstall other versions. If a beta or CTP version of SQL Server 2005 is not fully uninstalled before attempting an installation of SQL Server 2005, the installation may fail until the previous version is completely uninstalled. Additionally, an incomplete uninstall of SQL Server 2005 Express can cause problems when attempting to install one of the other versions of SQL Server 2005.

Post-Installation Configuration

After you install SQL Server 2005, depending on your needs and requirements, you may need to perform post-installation configuration of your installation. If you have only a few servers, you can use the SQL Server Surface Area Configuration tool. But if you need a scripted, automated, or repeatable process, you’ll need to use the sac utility, which is documented in BOL here. The sac command line utility generates an XML file of SQL Server 2005 configuration settings.

C:\Program Files\Microsoft SQL Server\90\Shared\>sac out C:\sac.xml

You can apply this configuration file to other SQL Server 2005 instances. If you’ve hardened a particular SQL Server instance, using sac is a great way to propagate those settings to other instances.

Getting Help

One of the best resources for getting help with SQL Server 2005 is at Microsoft Connect. You can search for answers to questions already posted and answered by others, post a new question, or enter a bug report. SQL Server product team members actively monitor and respond to the posts. Another good resource is MSDN Forums. SQL Server product team members also respond to posts made there.

Conclusion

The better informed you are, the better your installation processes will be. Keep watching the Microsoft SQL Server site for new service packs, the Knowledge Base for new hotfixes, and Microsoft Connect for bug reports.

--

John Paul Cook is a database and systems architect in Houston, Texas. His primary focus is helping large enterprise customers succeed with SQL Server, C#, Oracle, and the .NET framework. He has multiple certifications and is a Microsoft MVP.