Selecting Installation Configuration Options

Having met all the hardware and software prerequisites, you are now ready to begin the install. The installation program is a wizard that steps you through a number of configuration selections, allowing you to customize your SQL Server installation.

Selecting SQL Server Components and File Locations

You will be required to choose an instance name for the SQL Server you are about to install. Use common sense when coming up with server instance names (especially if you intend to have multiple SQL Server instances up and running at the same time on a single machine). You will also choose the type of installation and the locations for the installation and data files. Figure 8.1 shows the dialog box for these selections.

Figure 8.1. The Installation Type and Destination Folder dialog box.


Installation Type

The selections for installation type are Typical (the default), Minimum, and Custom. Table 8.7 compares the options available with each type.

Table 8.7. Comparison of Installation Options
Installation Option Typical Minimum Custom
Database Server Yes Yes Optional
Upgrade Tools Yes No Optional
Replication Yes Yes Optional
Full-Text Search Yes No Optional
Client Management Tools All None Optional
Client Connectivity Yes Yes Yes
Books Online Yes No Optional
Development Tools Debugger only None Choice of tools
Code Samples None None Choice of samples
Collation Settings Yes Yes Choice of settings

File Location

It is at this point where you must also choose the location for the SQL Server program and data files. The default location for both the binaries and the data files is systemroot\Program Files\Microsoft SQL Server.

Accepting the default location for the program files is usually acceptable from a performance point of view; however, I usually prefer at least a separate partition for the SQL installation. I find it to be a cleaner separation of O/S and application from a management point of view. A 512MB partition should be fine. Note that even if you select another partition for the SQL install, some files will be installed on the system partition. Whichever drive you choose, it is up to you whether to accept the default path. The default has the advantage of being consistent with other installs; the disadvantage is that it contains spaces, which can be a nuisance in scripting and command-line work.

On the other hand, the data files should almost always be installed on a separate disk subsystem for performance reasons. After the install, you might opt to move the Tempdb to its own disk system as well. For your user databases, the path you choose at this point will be the default file location when creating them through the GUI; this, of course, can be overridden. If you are installing on Windows 2000, you might also opt to pre-create a folder hierarchy using multiple disk subsystems mounted to logically grouped folders.

SQL Server and SQL Server Agent User Accounts

SQL Server runs as a service on Windows NT and Windows 2000, and as such, it can run under the context of the local system account or as a domain or local user account. If it is configured to run as the local system account, operations will generally be limited to the local server without access to network operations such as SQL mail and replication. Figure 8.2 shows the dialog box for setting up the services accounts.

Figure 8.2. The Services Accounts dialog box.


The default is to set up both the SQL Server service and the SQL Server Agent service to run as the currently logged-on user. This is fine; they can share an account. This user should be a member of the domain administrators group to provide full functionality across the network. If using a domain user account, use one that is dedicated to SQL Server, not the local or domain administrator account. Using the administrator account works just fine until you change the password and discover that SQL Server can't start because it has the wrong password! If you have configured your server this way, you can fix it easily; the Services applet in the Control Panel (in Windows 2000, it is Control Panel/Administrative Tools) allows you to configure the user account and change the password that the services use to log on. When creating an account for SQL Server, make sure you clear the User Must Change Password at Next Login and select Password Never Expires. SQL Server has no way of dealing with an expired password or with a request to change its password.

At this point, you should also ensure that the Auto Start Service check box is selected. This will set the services so that they start automatically when the operating system starts up. This can also be reset through the Services applet of the operating system after the install is complete.

Selecting the Authentication Mode

Two authentication methods are available: Windows authentication mode and mixed mode. If Windows authentication is selected, then a user connecting to SQL Server is validated through his Windows user account. In mixed mode, the user can be validated by his Windows account or by providing a SQL Server login ID. When selecting mixed mode, you will also be prompted to provide a password for the sa, or SQL Server system administration account. You can check the box to allow a blank sa password, but if you do this, you better have good backups if someone uses it to drop all your databases. The Authentication Mode dialog box is shown in Figure 8.3.

Figure 8.3. The Authentication Mode dialog box.


Selecting a Default Collation

During installation, you are requested to specify a default collation. The collation refers to the code page (character set) for non-Unicode data and the rules, such as sort order, that will be applied for both Unicode and non-Unicode data. In previous versions of SQL Server, after the character set and sort order were set, they applied to all databases. In SQL Server 2000, the collation you select during install is used for the system databases and (based on the model) is the default collation unless otherwise specified at object creation. SQL Server now allows different databases?as well as tables and columns?to have different collations.

The two types of collation offered during installation are Windows locale and SQL collation. SQL collation is included for backward compatibility to allow you to match a previously installed database version 6.5 or 7.0 with which you must replicate, or if your application depends on the behavior of a previous collation. Windows locale maps to a Windows locale; by default, it maps to the one selected for the server in the Control Panel. The locale can be selected to match another server if required through the Collation Designator drop-down box, and the default sort values can be changed as required by your application. For example, the default locale for the server illustrated in Figure 8.4 is Latin1_General, and the sort order is accent sensitive.

Figure 8.4. The Collations Settings dialog box.


Network Libraries

The network libraries are a set of DLLs that allow SQL Server to communicate over the network using a variety of protocols. Multiple network libraries can be active simultaneously to allow SQL Server to service requests from clients in a heterogeneous network environment. On Windows 2000 and NT 4.0 computers, the default network libraries are Named Pipes, TCP/IP Sockets, and Shared Memory. Shared Memory cannot be configured and is used for local client connections (same machine). If required, SQL Server also supports NWLink IPX/SPX, Banyan Vines, AppleTalk, and VIA GigaNet SAN (supported on servers only). The library the client uses to communicate with SQL Server depends on the library configuration at the client end, which must, of course, match a configured server-side library. The TCP/IP Sockets library is generally considered to be the most efficient. The Network Libraries dialog box is shown in Figure 8.5.

Figure 8.5. The Network Libraries dialog box.


Licensing Options

During the installation, you will be prompted to select a licensing mode. After SQL Server is installed, you cannot change the licensing mode. Figure 8.6 shows the Installation Licensing dialog box.

Figure 8.6. The Licensing dialog box.


Per Seat Licensing

The Per Seat licensing option requires a seat license for each SQL Server installation and a client access license (CAL) for each workstation or "seat" that will connect to SQL Server. Per Seat licensing doesn't limit connections; each workstation can connect with multiple user connections. The Per Seat licensing mode is considered appropriate for smaller organizations, or ones with a limited, known number of users.

Per Processor Licensing

With Per Processor licensing, you acquire a license for each processor running SQL Server. No additional licenses are required, and an unlimited number of users can connect to SQL Server. This is the appropriate license mode for Web-based access or for enterprise-level access in large organizations.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features