This chapter introduces you to SQL Server installation procedures, including the steps that should be taken before and after installation so that the installation process is smooth and uninterrupted.
SQL Server software is available in two configurations: Server Software and Workstation Software. Both configurations provide the same SQL Server features. There are some differences between the two, however:
SQL Server does not require many resources to run. If more resources (such as number of CPUs, memory, and so forth) are assigned to SQL Server, however, better performance gains are achieved. The following are the minimum requirements necessary for a
successful installation of SQL Server:
Faster processors and more memory are two of the most important factors governing the performance of a system. If more memory is allocated to SQL Server, it can cache large amounts of data, and many requests for data can be satisfied from memory rather than doing physical I/Os. CPU-bound queries (such as queries performing complex mathematical computations) are greatly benefited by faster processors.
In order to have a reliable, stable, and high-performance database engine, it is important that each step of SQL Server installation is carefully planned with good understanding of the operating environment (hardware resources, network type, and so
forth). The SQL Server SETUP program prompts you to enter values for a number of parameters during the course of installation. Before you start the installation, it is generally a good idea to have a list of these parameters and their values ready. This
list contains information such as SQL Server software location, location of the MASTER device, character set information, user account name under which SQL Executive will run, and so forth. Having this list ready before the setup saves a lot of time during
SQL Server installation This list can also be used as a reference for future SQL Server installations. The following sections explain the factors that should be considered and planned before you start installation of SQL Server.
SQL Server can run with both FAT and NTFS file systems. There are no significant performance improvements by choosing one over the other. In a mainly read-only environment, however, NTFS is normally faster because it does transaction logging. The NTFS
file system also provides better security (C2 level) and recoverability. The FAT file system may be faster in write operations because it does not perform any transaction logging. Given these facts, you should choose a file system that best suits your
needs. From the security and recoverability points of view, it is prudent to have your production environment running on the NTFS file system.
An individual who is responsible for installing SQL Server should have Administrative privileges on the machine.
SQL Server’s scheduler component, SQL Executive, runs as a service under Windows NT. This service can run either under LocalSystem or under a user account. If the service runs under a LocalSystem account, SQL Executive will not be able to perform
various jobs, such as task scheduling and data replication. Assigning a user account allows the service to do network connectivity. Windows NT will not allow a service that is running under the Local System account to communicate with other servers on the
network. Therefore, if you need to schedule tasks through SQL Executive or set up data replication, be sure that SQL Executive service runs under a user account. This user account can be created on the machine on which SQL Server will be installed or at a
domain level.
The user account for SQL Executive should be a part of Administrative group and should be created with Log on as a Service privilege. It is generally a good idea to create a user account at the domain level; if you have multiple SQL Servers, they can all share the same account name. It is also convenient from a maintenance point of view to select the password never expires option at the time of creating the user account.
SQL Server can use a number of network protocols to communicate with the clients. Verify that the network is configured properly and that you can ping to the machine from a client, using either an absolute IP address or a friendly name using DNS. You
should also choose the network protocols that will be used by SQL Server. If you are using TCP/IP protocol, for example, make sure that the socket number that will be used by SQL Server is not in use by any other application on the same machine.
SQL Server software takes up 55–60M of disk space (not including the space used by the MASTER device and online books). Therefore, you should have enough disk space available on the drive on which you plan to install SQL Server. The SETUP program
creates all the necessary directory structures for SQL Server software during the installation.
During SQL Server installation, master, model, and tempdb databases are created on the MASTER device. The MASTER device is a file created during SQL server installation and is critical to the operation of SQL Server. Choose the location of the MASTER
device carefully, preferably on a drive that is mirrored. Many third-party products install their stored procedure in the master database. If you plan to use such tools, you should estimate size of the MASTER device accordingly.
SQL Server supports character sets for many languages. Each character set is given a standard code called code page. For example, the ISO character set is called code page 8859-1. SQL Server's default character set is ISO. If your application can
include words and letters from different languages, you must choose an appropriate character set.
If North American and European characters are used in an application, for example, you should use the 850 Multilingual character set. The following character sets are available with SQL Server:
Sort order is the collate sequence in which data is presented to the user (for example, display Tom before tom or display Tom afer tom). A number of T-SQL commands (such as ORDER BY, GROUP BY, and so forth) are affected by the way a sort order is
defined on a SQL Server. SQL Server's default sort order is dictionary order with case-insensitive. A dictionary order is one in which data is displayed to the user based on dictionary collate sequence. Case-insensitive sort order signifies that SQL Server
does not distinguish between an uppercase or lowercase letter. Therefore, such a SQL Server will treat a column value of tom, TOM, and tOM identically during comparisons (in a WHERE clause). When the data is displayed using ORDER BY, it is displayed in the
dictionary order with no preference for an upper- and lowercase letter. Depending on the nature of your application, you must choose an appropriate sort order. SQL Server provides a number of sort order options. Some of the most commonly used sort orders
are as follows:
If the language installed on SQL Server is Code page 850 (Multilingual character set), which includes a number of characters with accents (diacritical marks such as è, é, ê, ë, and so forth), dictionary order, case-insensitive, accent-insensitive sort order is more useful than dictionary order, case-insensitive sort order from an application perspective.
SQL Server installation includes installing SQL Server software in a designated directory and creating MASTER device and system databases, tools and utilities, and so forth. After a successful SQL Server installation, the installation procedure creates
a program group called Microsoft SQL Server 6.5. The default directory of SQL Server software is C:\MSSQL. The SQL Server installation procedure creates the following directories under the installation directory:
Administrative privileges are required by the user on the machine on which SQL Server is going to be installed. The SETUP program enables you to install SQL Server on a local machine or on a remote machine. Most of the steps are common to both types of
installations. For remote installation of SQL Server, there are some additional steps required that are discussed in a later section.
The following steps are required to install SQL Server on a machine running Windows NT.
You can install SQL Server either from the CD or from a network drive containing SQL Server software by running the SETUP.EXE program. Based on the platform type, the SETUP program is executed from the following locations:
Once you run the SETUP program, you are presented with the starting installation screen. This screen basically greets you with a message indicating the version number of SQL Server you are about to install.
You can always use online help by clicking on the Help button (or by pressing F1). Click on the Continue button to go to step 2.
This step requires you to enter your name or the name of the person responsible for managing this SQL Server in the Name textbox. This is a mandatory field. Although entries in the Company and Product ID fields are optional, it is a good idea to provide
these values from product support standpoint. Product ID can be located on the software distribution CD. Enter the relevant information and click on the Continue button to go to the next step.
This screen presents various installation options. For a new installation the following three options are activated:
The other options (Change Network Support, Add Language, Rebuild Master Database, Set Server Options, Set Security Options, and Remove SQL Server) are activated when SQL Server is already installed on the server. For example, Change Network Support
option is required when you want to add another network protocol or modify values of the existing ones (for example, changing the socket number for TCP/IP protocol from the current value to a new value). To continue installation of SQL Server, click on the
Continue button.
At the time of purchase of SQL Server, your company must have agreed to either per-server or per-seat licensing mode.The per-server licensing mode requires that each client license access only a specific SQL Server, and there is a limit on the number of
concurrent connections to SQL Server. The per-seat license mode requires that each client connecting to SQL Server has a client access license. A client access license is not tied to any SQL Server. Based on the licensing agreement, you must choose a valid
licensing mode. Choosing either of the two options does not change the installation procedure in any way. Select the valid licensing mode and click on the Continue button to go to the next step. It is important to remember that if you choose per-seat mode,
you can’t change back to per-server licensing mode. Therefore, if you are not sure about the licensing mode, choose per-server mode to continue with installation of SQL Server.
Before you can proceed with the actual installation, you must comply with the licensing agreement. If you agree with the licensing agreement, check the box that says ‘I agree that:’, and click on the OK button; otherwise, click on the Cancel
button and go back to the previous screen to choose the correct licensing mode.
This is the first real step toward the installation of SQL Server. This dialog box prompts you for the drive and the directory location where the SQL Server software will be copied. The default drive for the installation is C, and the default directory
is \MSSQL. SQL Server software requires about 56M of disk space for the software, 25M for the MASTER device, and 1–15M for online books, depending on whether online books are installed on the hard drive or run from the CD ROM drive. Choose a different
drive letter if there is not enough space on the C drive or if you wish to install the software on a different drive. There is seldom a reason to change the directory name for a new installation. If you want to continue installation with the default
selection, click on the Continue button; otherwise, enter the valid drive letter and/or the directory name and then click on the Continue button.
The MASTER device is a physical file on which master, model, msdb, and tempdb databases are created during the installation process. The dialog box displays default values for the drive (C:), location (\MSSQL\DATA), filename (master.dat), and size (25M)
for the MASTER device. If you want to continue with the default values, click on the Continue button; otherwise, select the desired drive letter, directory location, name of the MASTER device file, and size of the MASTER device; then click on the Continue
button to go to the next step of installation. SETUP program creates the directory path if the specified directory does not exist. It is also important to note that the name of the MASTER device can have a maximum of eight characters with a three-character
file extension.
Some third-party tools create stored procedures in the master database. Therefore, to provide space for growth of objects in the master database, it is advisable to create the size of the MASTER device greater than the default value (25M). If you do not wish to increase the size of the MASTER device at the time of installation, it is possible to increase the size at a later stage using the DISK RESIZE T-SQL command.
This step enables you to decide whether you want SQL Server and SQL Executive to start automatically after the machine reboots. It is a good idea to select these options, especially in case of a production environment where you want SQL Server and the
SQL Executive to come up as soon as possible to minimize downtime.
SQL Server also provides the facility to execute one or more stored procedures at the time when SQL Server is started. These stored procedures are executed after all the databases have been recovered. Execution of these stored procedures can be very useful in performing certain housekeeping tasks and for notification purposes. Startup procedures can not accept parameters and run under the login ID of sa.
This step involves choosing a language character set for the SQL Server. Click on the Sets button to choose the character set you want to install on your server. The default character set is ISO, which is compatible with ANSI and Windows NT character
sets. After selecting an appropriate character set, click on the OK button to continue with the installation.
Click on the Orders button to choose the sort order you want to install on your server. SQL Server provides a number of sort options. The default Sort order for SQL Server is dictionary order, case-insensitive. After selecting the sort order of your
choice, click on the OK button to continue with the installation.
Be very careful when selecting a sort order or a character set for the SQL Server. Once a character set or a sort order is installed on a server, the only way to change it is by rebuilding the master database. Database backups taken from one SQL Server with a particular character set/sort order are not compatible with those taken from a SQL Server running with another character set/sort order. If the character set/sort order is changed on a SQL Server, database dumps prior to the change are not recognized by the SQL Server with the new character set/sort order. Therefore, it will not be possible for you to load the databases from the previous backups. To avoid such a situation, it is important that, before you change the character set or sort order, you save all the database object definitions (such as table, trigger, procedure, and so forth) and transfer the data to operating system files. After the new language or new sort order is installed on the server, you recreate your databases using these files and reload the data.
It is important to note that character set and sort order options are available at the SQL Server level, and all the databases on that particular server use the same character set and sort order.
By default, SQL Server listens on the Named Pipes network protocol. The default pipe name for SQL Server is \\pipe\sql\query. However, it is possible to install various other Net-Libraries for SQL Server. Using these libraries, SQL Server can listen on
multiple protocols at the same time. Click on the Networks button to choose network protcols for SQL Server. Choose the protocols based on your environment, and click on the OK button to continue with the installation.
It is important that you do not deselect the Named Pipes protocol option during the installation, because the SETUP program uses the Named Pipes protocol for SQL Server installation. It is generally not a good idea to disable Named Pipes, because SQL Executive connects to SQL Server through this protocol. Although there is seldom a reason to change the name of a pipe, you can do so after the successful installation of SQL Server. You are then required to configure SQL Executive to connect to SQL Server using the new value of the pipe.
At this time, you have already selected a character set, sort order, and network protocols for SQL Server installation. If you need to change any of these options, you can go back to the previous steps and make necessary changes. If there are no changes
to be made, click on the Continue button to proceed with the installation.
SQL Executive runs as a service under Windows NT. This service can run under Windows NT either by using a system account or a user account name. A system account is a LocalSystem account on NT. If a LocalSystem account is used, certain scheduled and
data replication tasks will not work. However, this can be a useful option in cases where you have not defined a user account for SQL Executive service. Until you create a user account, you can leave SQL Executive running under LocalSystem account and then
change it at a later stage to run under a user account. A user account can be a local account or a domain account. It is necessary for the user account to be a part of the Administrator group on the machine on which SQL Server is running. This dialog box
prompts you to enter the name of user account and the password. Alternatively, you can choose the Install to log on as LocalSystem account radio button, if you want SQL Executive to run under LocalSystem account. Click on Continue after providing the
relevant information on the dialog box.
This dialog box appears only if you select a network protocol other than the Named Pipes protocol in step 11 (for example, TCP/IP network protocol). This screen prompts you to enter additional information required by the network protocol you selected
for SQL Server. If you had selected, for example, TCP/IP as a network protocol for SQL Server, this screen prompts you for the TCP/IP socket number. Enter the relevant information on this screen and click on Continue to finish the installation.
TCP/IP socket number 1433 is officially assigned for MS SQL Server by Internet Assigned Number Authority. However, you should still make sure that there is no other application on the machine using this socket number. SQL Server will fail to listen on the TCP/IP port if there is another application using the same TCP/IP socket number. You can run the netstat command from the command prompt to check the TCP/IP ports that are currently in use. It is generally a good idea to use a standard socket number for SQL Server throughout the enterprise to maintain consistency.
After step 14, the SETUP program copies the software in the designated directories, creates the MASTER device, creates master, model, and tempdb databases, installs ODBC drivers, and installs tools and utilities. Status of each of these actions is
provided on the screen.
After completion of installation, reboot the machine on which SQL Server is installed. SQL Server and SQL Executive should start automatically if the auto startup options were selected during the installation procedure. If not, SQL Server and SQL
Executive can be started by using the SQL Server Manager tool.
It is also possible to install SQL Server on a remote machine. Remote installation of SQL Server is quite similar to a local installation. The only difference is that with remote installation, you are required to provide the name of SQL Server
installation drive, the drive on which Windows NT is installed, and the location for the MASTER device. To perform remote installation, follow steps 1 and 2 of local installation. Click on the Remote button to continue with remote installation. Select the
Remote Installation checkbox to indicate that you want to perform remote installation of SQL Server. Enter the name of the remote server in the Remote Server Name textbox. Provide the drive letter information for SQL Server installation drive, Windows NT
installation drive, and the MASTER device. Click on OK to continue with the remote installation. From this point onward, all the steps are similar to local installation.
In a large organization, it may be required to install multiple SQL Servers with identical configurations. In such cases, it could be very time-consuming to install all the SQL Servers interactively by using the SETUP graphical user interface.
Unattended installation is a more efficient method of installing SQL Servers, where one does not need to provide any input to the user prompts. Unattended installation is accomplished by using the SETUP program and providing the name of an input file that
contains all installation parameters in a specified format. The command syntax for unattended installation is the following:
SETUP /t IniFilePath = "initialization file name"
Here is the text of a typical initialization file:
[License]
FullName=Test User
OrgName=XYZ Co.
ProductID=!@#$
Mode=1
[SQLPath]
SQLPath=\MSSQL
LogicalSQLDrive=C:
[MasterPath]
MasterSize=30
LogicalDBDrive=C:
MasterDBPath=\MSSQL\DATA
MasterDBFileName=MASTER.DAT
[NewOptions]
AutoServerService=CHECKED
AutoExecutiveService=CHECKED
BooksOnline=1
[CharSet]
CharSet=cp 850
[SortOrder]
SortFileName=noaccent.850
SortConfigValue=44
[Network]
NetLibList={"SSNMPN60"}
ServerNMPipe=\\.\pipe\sql\query
[LogonAccount]
LocalSystem=NOTCHECKED
Username=devdomain\sqlserver
Password=*******
The password for the user account for SQL Executive is written in the initialization file in plain text. Because this user account has administrative privileges, it is important to keep this file in location with appropriate security access.
SQL Server has a generic login called sa (system administrator). This login is responsible for managing SQL Server. An sa login is required to initialize the database device, create databases, create SQL Server logins, and so forth. Any person who knows the sa password can log on to SQL Server with all the privileges on the system. When SQL Server is installed for the first time, there is no password assigned to sa. Therefore, it is important to change it as soon as possible after the installation.
The SETUP program provides a friendly and reliable graphical user interface to install SQL Server. Typically, you will not have any problems during the installation process. However, there are certain issues that can result in installation failures.
Insufficient disk space, probably, is the most common cause of installation failure. Be sure that you have enough disk space for SQL Server software, MASTER device, and online books. SQL Server SETUP program also writes messages to a number of output files
during various steps of the installation. All these files have .OUT extensions, and the default location of these files is C:\MSSQL\INSTALL, unless a different root directory was specified during SQL Server installation. In that case, the location of the
files are DRIVE:\ROOTDIR\INSTALL. You can view the most recently created .OUT file to identify the cause of the failed installation. The SETUP program also writes all the messages to an errorlog file. Default location of the errorlog file is
C:\MSSQL\LOG\ERRORLOG, unless the default software location was changed during the installation process. The SQL Server SETUP program also writes to the Windows NT event log. You can view the SQL Server errorlog by using a text editor. The Windows NT event
log can be viewed by using the Event Viewer graphical tool. By looking at these errorlogs, you can get some information about the cause of installation failure.
SQL Server is a highly configurable database engine. There are a number of configuration parameters that can be set by the database administrator for optimal performance of SQL Server. The SETUP program installs SQL Server with reasonable default values
for these configuration parameters. However, there are certain parameters, such as memory, user connections, number of devices, open databases, and so forth, where the choice made by the SETUP program may not be suitable for a specific environment.
Therefore, after the installation, it is important to configure these parameters. Some of the advanced configuration parameters are discussed at length in Chapter 31, "SQL Server Monitoring and Tuning." Use the following
steps to configure a SQL Server after a successful installation, by using the SQL Enterprise Manager graphical administration tool:
Passwords for all SQL Server logins can also be changed using the sp_password system stored procedure from an ISQL session.
Some of the parameters are dynamic in nature and take effect immediately (such as allow updates). Some take effect upon the next start of SQL Server (such as memory, user connections, and so forth).
SQL Server allows tempdb to be resident in RAM. This results in a big performance boost for applications that use tempdb quite extensively. You can have tempdb either RAM-resident or created on a physical device. It is possible to expand tempdb across multiple physical devices.
Many Database Administrators export information directly from some of the system catalog tables (such as sysdevices, sysusages, syslogins, and so forth) of the master database into ASCII files. In case of a damaged master database, sometimes these files provide extremely valuable information regarding device sizes, database storage allocations, user login information, and so forth.
A SQL Server installation can be verified by trying to connect to the SQL Server from a client—such as ISQL/w on all the active network protocols (the protocls SQL Server is listening on). The following steps help determine whether the installation
is successful:
If you are able to perform all these tasks, you have successfully installed a SQL Server.
Installing SQL Server on a Windows NT machine is a relatively simple task. Careful planning will always result in a SQL Server installation that is high-performance, reliable, and does not require frequent configuration changes. If you have many SQL Servers to install, it is beneficial to have an initialization file and run the unattended setup. In the next chapter, you explore the roles and responsibilities of a database administrator and effective database administration techniques.
