The ins and outs of installing SQL Server 2000

Microsoft tries to make installation of its software as smooth and easy as possible, and SQL Server 2000's installation is no exception. From the installation CD-ROM, you load setupsql.exe from the x86\setup folder, fill in a few details in the setup dialog screens, and within a few minutes, the installation continues with no user intervention. You can even successfully install SQL Server 2000 without understanding what the choices mean, just by clicking Next in most of the setup dialog boxes. However, I strongly advise you not to treat the installation lightly; pay attention to each option and make sure you thoroughly understand the implications of each choice you make. Some bad decisions, such as wrong collation settings, might be hard to fix; others, such as accepting the default authentication, might create security holes.

Let's look at some important points about standard installation, including instance configuration, security, collations, and Network Libraries. Then, let's explore the advanced options of unattended and remote installations. The sidebar "Rebuilding the Registry," page 37, gives details about what to do when non—system-table metadata becomes corrupted.

Instances

When you're physically at the installation site, you usually perform standard installation (as opposed to remote or unattended installation, which I discuss later). You invoke the setupsql.exe program, which launches the Installation Wizard. After the first two dialog boxes—Welcome and Computer Name—you need to make choices about your instance configuration. (Note that I don't cover every dialog box that appears during the setup program because some of them are fairly straightforward.) SQL Server 2000 supports installing multiple instances of SQL Server on one machine. (For details about SQL Server instances, see Kalen Delaney, Inside SQL Server, "Multiple Instances," July 2000, and "Managing Multiple Instances," August 2000.) The installation program displays two dialog boxes that give you options for installing instances.

First, the Installation Selection dialog box that Figure 1, page 32, shows lets you choose whether to install a new instance or upgrade an existing installation. If you choose to install a new instance, you'll see the Instance Name dialog box that Figure 2, page 32, shows. You can either specify an instance name or select the Default check box to install a default instance if a default instance isn't already installed on the machine.

You need to consider several things as you make choices about installing instances. If the machine has no default instance and you're planning to host both SQL Server 2000 and 7.0 on the same machine, make sure you don't install SQL Server 2000 as a default instance. SQL Server 7.0 doesn't support named instances, so it has to be the default instance. You can't change a named instance to a default instance or vice versa, except by uninstalling and reinstalling SQL Server. You also can't change an instance name after the instance is installed. However, you can install SQL Server 7.0 after installing SQL Server 2000 if you haven't installed a default instance yet.

If a SQL Server 7.0 installation already exists, you can upgrade it by choosing the upgrade path in the Installation Selection dialog box and specifying in a later dialog box that you want to upgrade the default instance. However, SQL Server 2000 will then be the default instance and SQL Server 7.0 will no longer exist on the computer. To keep both versions, install SQL Server 2000 as a named instance.

After installing SQL Server 2000, you can load the SQL Server 7.0 databases to SQL Server 2000 by using backup and restore, detach and attach, Data Transformation Services (DTS), or the Copy Database Wizard. (For information about loading databases, see Kalen Delaney, "All About Restore," page 39.) Note that regardless of which path you chose when upgrading a previous release to SQL Server 2000, you can't point more than one installation to the same copies of the databases, so each installation must maintain its own copy of each database.

Another consideration concerns the SQL Server 7.0 feature called version switch, which lets SQL Server 7.0 coexist with an installation of SQL Server 6.5 on the same machine. However, only one of the installations can be active at a time; the other is dormant. When you invoke the version switch, it activates the dormant installation and deactivates the active one. If the machine contains a SQL Server 6.5 installation that doesn't coexist with a SQL Server 7.0 installation by means of a version switch, the installation program asks you to choose one of two options: Upgrade the SQL Server 6.5 installation to a default instance of SQL Server 2000 and maintain a version switch between SQL Server 6.5 and SQL Server 2000, or upgrade it to a named instance of SQL Server 2000. Unlike the upgrade from SQL Server 7.0, which overwrites the current installation, the SQL Server 6.5 installation remains in the computer regardless of the path you choose for upgrading to SQL Server 2000.

If both SQL Server 7.0 and 6.5 are installed on the computer and coexist by means of a version switch and you don't want to upgrade an existing installation, you can safely install named instances of SQL Server 2000 on the same machine and host all three versions on the same computer. However, only one of the versions (i.e., 7.0 or 6.5) that coexist by means of a version switch can run at one time, whereas all named instances can run simultaneously. After specifying your instances options, you move on to the Setup Type dialog box.

Custom Installation

In the Setup Type dialog box, which Figure 3 shows, the Installation Wizard asks you to choose among three installation types: Typical, Minimum, and Custom. If you choose Typical or Minimum, SQL Server uses default options for components and subcomponents, collations, and Network Libraries. Because of the Typical installation's potential to cause tricky problems, I strongly recommend that you always choose Custom even if you think that the defaults meet your installation needs. Some of the aforementioned options—particularly collation settings—are very hard to change after installation if the defaults turn out not to meet your needs. Custom installation lets you double-check those options. Walking through a few more dialog boxes is a small price to pay to make sure that you select the right installation options.

Security

During the installation process, you specify security-related information in two dialog boxes: Services Accounts (which Figure 4 shows) and Authentication Mode (which Figure 5 shows). In the Services Accounts dialog box, you fill in the service account details for the SQL Server and SQL Server Agent services. Each service loads to the OS with the account specified for it in this dialog box and runs in the OS in the security context of that account. When you back up to a disk device, for example, SQL Server checks whether the login you used to log in to SQL Server has the appropriate BACKUP DATABASE permission. However, to create the backup file device and write to it, SQL Server has to create a file on disk or in a network share, and this operation uses the SQL Server service account's security context.

Similarly, the SQL Server Agent service runs processes in SQL Server and in the OS or network under the security context of the SQL Server Agent's service account. Although an account that doesn't have administrative privileges in the machine can start the SQL Server service, making the SQL Server service account a member of the local Administrators group is a good idea. Otherwise, you'd need to explicitly grant the account all required permissions (e.g., to create database files). You also need to grant the service account the appropriate network permissions (e.g., to create and write to files on network shares that SQL Server uses as backup destinations).

SQL Server Agent, however, won't even start if you try to load it along with a service account that doesn't have administrative privileges on the machine. And if SQL Server Agent performs activities on other machines on the network, such as replication or multiserver jobs, you should use a domain account that also has the appropriate permissions on the other machines. Figure 6 shows a domain with three SQL Server machines in a multiserver environment in which a master server controls automation activities on target servers. Srv1 acts as the master server, and Srv2 and Srv3 act as the target servers. Because both sides (master and target) need to communicate with each other, you need to make sure that the SQL Server Agent service account on the master server has the appropriate privileges on the target server and vice versa. The easiest way to configure such an environment is to create one domain account (e.g., Domain1\SQLService, as Figure 6 shows), make it a member of the local Administrators group in all servers, and load all SQL Server Agent services through that account. (For more information about the multiserver environment, see Michael D. Reilly, "Multiserver Task Administration," April 2002.)

In the Authentication Mode dialog box that Figure 5 shows, you can choose whether to allow only Windows-authenticated logins (Windows Authentication Mode) or both Windows and SQL Server logins (Mixed Mode). You can also specify a password for the sa SQL Server login. Windows-only authentication is the default and commonly recommended security mode. However, for security reasons, I recommend that you choose Mixed Mode and supply a password for the sa account, changing the authentication mode to Windows-only after installation and after handling a few other security items. The installation program creates a SQL Server login for sa and makes it a member of the sysadmin server role. If you choose Windows-only authentication as your server's security mode, the installation process creates the sa login as disabled (because SQL Server authentication is disabled) and with no password. You can change sa's password after installation—and I strongly recommend that you do—but choosing Windows authentication initially is dangerous because you might forget to change the password or leave it blank, trusting that sa is disabled.

No matter which mode you select, the installation program also creates a Windows-authenticated login for the BUILTIN\Administrators group, which is mapped to the local machine's Administrators group. The creation of this login means that all members of the local Administrators group, including the domain group Domain Admins, are also members of your SQL Server's sysadmin role. Giving the network and machine administrators unrestricted permissions on SQL Server isn't always a good idea because it introduces security risks, so you might decide to remove BUILTIN\Administrators from the sysadmin SQL Server role. Or you could completely remove the automatically created login from SQL Server and create a login with sysadmin membership for a group whose members are DBAs—not network administrators.

If you decide to follow either of those recommendations, it's essential that you first create the login with the sysadmin membership for the DBA group, then remove the BUILTIN\Administrators login. If your server's authentication mode is Windows-only and you remove all logins that have sysadmin membership before creating the login for the DBAs, you might find yourself locked out of SQL Server with no way to perform administrative tasks—such as creating new logins. If you fall into that trap, you can still change SQL Server's authentication mode to Mixed through the registry by editing the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName> \MSSQLServer\LoginMode registry subkey. Change the subkey value to 2, then restart the SQL Server service.

Although being able to control SQL Server's login mode through the registry is convenient, it has a disadvantage as well. Anyone who has permissions to edit that registry subkey, including the network and machine administrators, can change SQL Server's authentication mode. If you install SQL Server with Windows Authentication Mode, sa is disabled but still has a blank password. If you then change SQL Server's authentication mode to Mixed (thus enabling the sa login), anyone can log in as sa. So, make absolutely sure you either change the sa password as soon as you finish the installation or choose Mixed Mode and provide a password for sa during the installation.

Collation

Next, you need to choose collation settings. Collation settings in SQL Server 2000 govern language-related behavior, uniqueness of object names and column values, and sorting rules. In the Collation Settings dialog box, which Figure 7 shows, you specify collation settings and choose either a SQL Server collation or a Windows collation. Choose SQL Server collation if you need backward compatibility with previous releases of SQL Server—for example, if you're going to use replication between SQL Server 2000 and a previous SQL Server release. Otherwise, choose a Windows collation. SQL Server 2000's collation settings, both Windows and SQL Server, merge three independent settings that existed in previous releases: Character Set, Sort Order, and Unicode Collation. Besides encapsulating the older three settings into one, SQL Server 2000 also gives far greater flexibility in the collation settings than previous releases. A discussion of the older settings is out of the scope of this article, but you can find the details in SQL Server 7.0 and 6.5 Books Online (BOL).

The collation settings you choose during SQL Server 2000 installation determine the collation settings of the system databases. To change the collation settings of the system databases after installation, you need to script all your system objects (e.g., logins, messages, jobs) and run rebuildm.exe, which rebuilds all the system databases with new collation settings. However, you don't need to export all the data in the user databases out of SQL Server and import it back after running rebuildm.exe, as you do in SQL Server 7.0. You only need to reattach the user databases to SQL Server. You can configure your user databases with a different collation than the default server's collation (that of the model system database) or even attach or restore a database with collation settings different from the server's settings. You can alter a user database's default collation later on. For a specific column, you can specify a collation that's different from the default, which is the database collation; you can even alter the column's collation later if no index has been created on the column.

Although SQL Server 2000 is flexible in regard to collations, don't underestimate the choices you make during installation. As I mentioned earlier, the server's collation applies to all system databases and determines collation rules for all objects recorded in system databases (e.g., login names, database names). Furthermore, tempdb's collation is also the server collation you chose during installation. When you create a temporary table, the table's columns use tempdb's collation unless you specify COLLATE database_default in each column definition to apply the collation of the database you're connected to.

Network Libraries

After specifying collation settings, you move on to the Network Libraries dialog box, which Figure 8 shows. Network Library is the protocol the client application uses to communicate with SQL Server. Both the client and SQL Server must have at least one matching Network Library through which they can communicate. In the Network Libraries dialog box, you set the Network Libraries SQL Server will use to communicate with clients.

In SQL Server 6.5, only Named Pipes and Multi-Protocol allow Windows authentication; all other Network Libraries allow only SQL Server authentication. So, for SQL Server 6.5, the type of logins you want to support is a factor in which Network Library you choose. Furthermore, only Multi-Protocol allows data encryption, so you have to choose that Network Library if you want SQL Server 6.5 to support data encryption. In SQL Server 7.0, all Network Libraries support Windows authentication, so you have more flexibility in that sense, but Multi-Protocol remains the only protocol that allows data encryption.

In SQL Server 2000, you can force encryption by using Secure Sockets Layer (SSL) with all Network Libraries through the SQL Server Network Utility and the SQL Server Client Network Utility, so encryption doesn't determine Network Library. Also, in SQL Server 2000, Multi-Protocol doesn't support the instance-naming scheme SERVERNAME\INSTANCENAME, so Multi-Protocol isn't a good choice when you have named instances. The most commonly used Network Library in SQL Server 2000 is probably TCP/IP Sockets. It provides good performance, allows Windows authentication, and you can enforce SSL encryption with it if you need to.

Most people working with previous releases of SQL Server know that SQL Server's default TCP port is port 1433. When using the default port, client connections don't need to provide the port number aside from the server name or IP address. However, SQL Server 2000 supports multiple instances, which can't all use the same port number. So when you install a named instance, the installation program suggests 0 as the port number. Port number 0 means that when SQL Server starts for the first time, it dynamically chooses a free port number and keeps it permanently or until you manually change it later. So how can client connections continue referring to SQL Server by providing its name/IP+instance name alone without specifying the port number? A listener service in SQL Server 2000 listens on port 1433 for client requests to connect to SQL Server, then redirects the request to the appropriate instance by examining the instance name in the request and matching it with the instance's port number.

Unattended and Remote Installation

Now that we're done with standard installation, let's discuss unattended installation. The setupsql.exe program lets you record an answer file containing all the answers for the various installation options that you usually choose in the installation-program dialog boxes. Later, you can run an installation by executing one command that invokes setupsql.exe with the answer file as an argument. This type of installation, which doesn't require any user intervention, is called unattended installation.

To prepare the answer file, start the installation program, choose Advanced Options in the Installation Selection dialog box, then choose Record Unattended .ISS file in the dialog box that Figure 9 shows. The installation program walks you through the regular setup dialog boxes, where you can fill in all the options you want to record. When you're finished, the installation program creates a file called setup.iss in the \WINNT folder.

To invoke an unattended installation, run the setupsql.exe program with the -s option for silent installation and the -f1 option to specify the answer file. For example, to invoke a silent unattended installation that doesn't inform you when the installation is finished, you can use the following command:

<i><path></i>\setupsql.exe -s -f1 <i><path></i>\setup.iss

If you want to know when the installation finished, invoke the setupsql.exe program from a command prompt or in a batch file as follows:

start /wait <i><path></i>\setupsql.exe -s -f1 <i><path></i>\setup.iss

Control doesn't pass to the next command until the installation is finished. Using the start /wait option is especially important when you invoke the installation from a batch file that includes other activities that are dependent on the installation. For example, suppose you want to write a batch file that performs an unattended installation of an instance named INST1, starts the SQL Server service, and runs a .sql script file that creates a database and its objects (e.g., tables, stored procedures). The batch file might look like this:

start /wait D:\X86\Setup\setupsql.exe -s -f1 C:\WINNT\setup.iss
NET START MSSQL$INST1
OSQL /E /I "c:\data\scripts\createappdb.sql"

If you don't use the start /wait option, control moves to the second command in the batch as soon as the installation starts, and the NET START command tries to start a service that doesn't exist yet.

Troubleshooting an unattended installation is much trickier than troubleshooting a standard installation. A standard installation usually informs you when the setup program experiences a problem by displaying a dialog box containing an error message accompanied by a loud beep. An unattended installation simply terminates with no interactive notice.

To see how you can troubleshoot an unattended installation, let's look at a couple of problems I've encountered. Suppose you've just finished installing another product on the server, then you attempt to run an unattended installation of SQL Server. The setup program detects that the server wasn't restarted after the previous installation and aborts with no notice. If you keep an open Task Manager window, you'll notice that the setupsql.exe program isn't active and that SQL Server wasn't installed. You should also examine the log files. Upon successful installation, the \WINNT\setup.log file should show 0 as the error code; however, in the scenario I described, it would probably show -1, which signifies a General error. Also, you should see the error message A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup.

Another error I've encountered several times when performing unattended installation is Dialog boxes are out of order. I realized that a problem existed soon after I invoked the installation, when I opened the Task Manager and saw that setupsql.exe was running but no processes were loading and unloading as is typical with an unattended installation that's running properly. Also, the folders that installation usually creates under \Program Files\Microsoft SQL Server hadn't been created. The setupsql.exe program didn't seem to be using CPU or I/O resources and simply disappeared after about 10 minutes. The setup.log file showed a -12 error code, and the sqlstp.log file showed no error—in fact, it seemed unfinished. Sqlstp contained no completion notification, just a message saying Begin Action: DialogShow<dialogname>. BOL says the following about error code -12: "Dialog boxes are out of order. This is a common error, caused when a dialog box appears out of order in the setup initialization file (Setup.iss). This can occur due to a system problem during the creation of Setup.iss." Sure enough, after I re-recorded a setup.iss file, the unattended installation completed successfully.

In addition to a full installation, you can also perform an unattended installation of a SQL Server 2000 service pack. To apply a service pack to the default instance, invoke the setupsql.exe program from the service pack installation folder, and specify the answer file sql2kdef.iss, which resides in the root service pack installation folder. For example, if the service pack installation files are in c:\sql2ksp2, issue the following command:

start /wait c:\sql2ksp2\x86\setup\setupsql.exe -s -f1 c:\sql2ksp2\sql2kdef.iss

To apply a service pack to a named instance, use the sql2knm.iss answer file, but first revise the following two lines in the file to reflect the appropriate instance name:

InstanceName=INSTANCE_NAME
NMPPipeName=\\.\pipe\MSSQL$INSTANCE_NAME\sql\query

Another advanced option, remote installation, lets you install SQL Server 2000 on a remote computer. You could manually record a setup.iss file from a local computer, copy it to the remote computer, and activate the setupsql.exe program and the setup.iss file on the remote computer. However, you can save yourself the trouble by running the setupsql.exe program on a local computer, choosing Remote Computer in the Computer Name dialog box, and specifying the computer name you want to install to. When you click Next, the Remote Setup Information dialog box shown in Figure 10 appears.

You first provide account details (user, password, domain), the Universal Naming Convention (UNC) path to the target folder (e.g., \\LABYRINTH\C$\Program Files\Microsoft SQL Server), and the UNC path to the source installation files (e.g., \\SERVER1\Install\SQL2KE\X86\Setup). Next, the installation program walks you through the regular installation dialog boxes and records the setup.iss file according to your selections. The program then copies the setup.iss file to the target computer's \WINNT folder and activates setupsql.exe along with the copied setup.iss file.

If you open Task Manager on the remote computer, you can see the setupsql.exe process loading and unloading other processes during the installation. On the local computer, the setup program shows that the remote installation is in progress and notifies you when it's finished.

The Final Word

Although a basic installation can be simple, you need to pay close attention to the installation options you choose, and understand them thoroughly. Good installation choices provide a firm foundation for running and administering SQL Server. And if you thought performing unattended and remote installations sounded complex, I hope that this article helps you feel more comfortable with them.