DBA Tips Archive for SQL Server


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Jeff Hunter, Sr. Database Administrator



This article details the steps for installing Microsoft SQL Server on to the Windows XP Professional operating environment. Installing SQL Server is not a difficult task by any means. There are, however, critical decisions that have to be made during the setup that will have a great impact on your installation.

For the purpose of this article, I will be installing Microsoft SQL Server 2000 (Developers Edition) on the Windows XP operating environment.

Before going into the installation details, let's first talk about the different versions (or editions) of Microsoft SQL Server.

SQL Server Editions

Microsoft released six different editions of their SQL Server product. In most cases, it will be apparent which edition of the SQL Server you will need to install. Do keep in mind, however, that all editions of SQL Server are built from a comman code base (with the exception of Windows CE).

Installation Pre-requisites

Although I will not go into great detail about pre-requisites for installing SQL Server 2000, I will mention several considerations that should be made at the operating system level.

Obviously, the first choice to be made is which operating system you will be using. As mentioned in the previous section, SQL Server 2000 has strict requirements for which operating system it will run on. You will not be able to utilize Windows 98 if your needs require SQL Server Enterprise Edition.

The second issue to consider is security. For this reason, it is probably best to stick with an operating system that supports a high degree of security like found in Windows NT Server 4.0 and Windows 2000. Both of these are considered secure operating environments and include features like access control as well as file restrictions. SQL Server 2000 includes a feature known as Windows Authentication Mode that allows database users to be authenticated at the operating system level (by their Windows user id or group). After being authenticated with the Windows operating system, users can obtain direct access to the SQL Server database through their operating system account.

Another consideration is the file system you will be using. SQL Server 2000 can use the File Allocation Table (FAT), FAT32 (Windows 2000 only), and the New Technology File System (NTFS). It is strongly recommended to use NTFS. This allows SQL Server to secure your installation files, as well as your system database files. It is also a much faster file system. Included with Windows 2000 is a feature named Encrypted File System (EFS) that allows you to encrypt database files. Users will be unable to copy or view these files without the username and password for the SQL Server service account.

Install SQL Server 2000

As mentioned at the beginning of this article, we will be installing Microsoft SQL Server 2000, Developer Edition on to Windows XP Professional.

The following table lists the hardware and software I will be using for this installation:

SQL Server Edition Developer Edition
Machine Name melody.idevelopment.info - (
Operating Environment Windows XP Professional
Machine Dell Inspiron 8600 Laptop
Memory 2 GB
Hard Drive 60 GB
Processor 2.0 GHz.
File System NTFS

Now let's get started with the installation!

To start the installation, insert the SQL Server 2000 CD into your CD-ROM drive. You will then be presented with the autoplay dialog box listing all installation options.


If the autoplay dialog does not display after inserting the SQL Server 2000 CD, you can manually start it by using Windows Explorer and navigating to the your CD-ROM drive. Double-click (run) the file autorun.exe found in the root directory of the CD.

It is recommended to first read over the readme.txt file. This file contains information that may not have been available at the time the manuals were being published. After reading over this file, click the SQL Server 2000 Components option.

Figure 1: Autoplay Dialog

On the Install Components screen, click the Install Database Server option.

Figure 2: Install Components

You are now brought to the Welcome Screen. Click Next to start the installation.

Figure 3: Welcome

You are now asked if you would like to install SQL Server 2000 on this computer (Local Computer) or to a Remote Computer. After verifying that the installation process determined the correct name for your machine, ensure that the Local Computer option is selected and click Next to continue.

Figure 4: Computer Name

SQL Server 2000 is now asking for the type of installation to perform. By default, the option is to "Create a new instance of SQL Server or the Client Tools". This is the option we will be using for this installation. Before displaying this window, the installation process determines if there is a previous installation of SQL Server on the computer you are installing to. If there were a previous installation, the second radio button, "Upgrade, remove, or add components to an existing instance of SQL Server", would be enabled. This is a new installation and we will be using the Create a new instance of SQL Server or the Client Tools option. Click Next to continue.

Figure 5: Installation Selection

You are now being asked for your user and company information. After entering this information, click Next to continue.

Figure 6: User Information

Read over the license agreement. If you agree with the license agreement, click Yes to continue.

Figure 7: Software License Agreement

Enter the 25-character CD license key. You can find the this key on the yellow sticker on the CD. Click Next to continue.

Figure 8: CD-Key

We will be accepting the default selection, "Server and Client Tools". If you were preparing a workstation for the sole purpose of administrating a SQL Server database on a remote machine, you select the "Client Tools Only" option. Also, if all you need is the updated data access components (commonly known as MDAC 2.6) that comes with SQL Server 2000, you can use the "Connectivity Option" option. Keep the default selection of Server and Client Tools and click Next to continue.

Figure 9: Installation Definition

This screen allows you to select a "Default Instance" or to manually define a "Named Instance". For this installation, we will be accepting the Default Instance. Click Next to continue.

Figure 10: Instance Name

This screen allows you to select which SQL Server components (options) should be installed. The default is a "Typical" installation. With a typical installation, all default options are taken and the installation process jumps to the dialog seen in Screen 13 - (Services Accounts). All components will be selected in a typical installation with the exception of development tools (header files, APIs) and code examples. With a "Minimal' installation, only client access components (ODBC, etc.) and the SQL Server core services are selected. None of the management tools (or even Books Online) is selected.

When I install SQL Server for development purposes, I typically want everything. For this reason, I choose the Custom option. Click Next to continue.

Figure 11: Setup Type

If you selected the "Custom" installation type in the previous screen, the installation process will present you with the "Select Components" dialog. There are three Component options you should select to ensure you are getting all of the code examples, development libraries and header files. The three components that include sub-components NOT selected by default are:

After selecting all components (and sub-components), click Next to continue.

Figure 12: Select Components

The following dialog will only appear on Windows NT 4.0 and Windows 2000 systems. Windows9x does not support the concept of operating system services and will only run SQL Server in the context of the logged-in user. With Windows NT 4.0 and Windows 2000, however, SQL Server is run as a background service. No user has to actually be logged in to enable SQL Server services to be running. Even services, however, need to log in to the O/S and that is what this screen is asking for.

For Windows NT and 2000, the "Local System" account is understood to mean the actual operating system itself. Using the local system account will allow SQL Server to always work. (No log in issues should ever be encountered.) The local system account, however, does not have access rights to networking services.


The above statement is not entirely true. The local system account does have network access rights in a Windows 2000 network.

This will be a problem if you want to access with this SQL Server instance on your network. If you will require access to this SQL Server instance from a network, you will need to supply a user account (and password) for the SQL Services to run under. By default, the installation process chooses the user who is performing the install. That is the selection I will use for this installation. If this were a production installation, however, you will want to ensure that the services are run under a special account for the sole purpose of running SQL Server. If you need to, you can use User Manager to create this special user. Here are the requirements to keep in mind when creating this special user:

Click Next to continue.

Figure 13: Services Accounts

After entering a valid login option for the services account, you are now asked on how SQL Server will authenticate database users. Although this dialog will appear on all Windows platforms, Windows9x does not support "Windows Authentication". For Windows NT 4.0 and Windows 2000, the "Windows Authentication Mode" is selected by default.

There are two modes for SQL Server authentication. In "Windows Authentication Mode", SQL Server depends on the Windows operating system to create and manage database users. You can also configure SQL Server to use "Mixed Mode". In this mode, SQL Server can use both Windows operating system accounts as well as accounts created directly within SQL Server.

Select Mixed Mode and supply a password for the sa account. When finished, click Next to continue.

Figure 14: Authentication Mode

This is an extremely important screen as it defines the character set and sort order by default for databases. For the purpose of this example, I will simply select the default option. Click Next to continue.

Figure 15: Authentication Mode

The "Network Libraries" screen allows you to select which networking protocols you want to support for this SQL Server instance. Unless you have specific reasons, there should be no reason to change anything on this screen. By default, the installation process selects Named Pipes and TCP/IP as the only networking libraries that clients can use to talk to this instance. Under Windows9x, only TCP/IP is selected by default. Click Next to continue.

Figure 16: Network Libraries

Click Next to continue to start the installation process.

Figure 17: Start Copying Files

Figure 18: Installation Process

Click Finish to complete the installation.

Figure 19: Setup Complete

Post-Installation Steps

When the installation process is complete, SQL Server will have installed all necessary files, created several new services, and updated your registry. Within the new SQL Server instance, you will also have several pre-created databases.

SQL Server Default Databases

By default, SQL Server creates six databases:

Using Enterprise Manager, open the master or Northwind database and look around at the database objects. For a detailed look at what these database are used for, see my article entitled Default SQL Server Databases.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 12-Mar-2013 00:54:39 EDT
Page Count: 7792