Microsoft SQL Server 7.0 System Administration Training Kit
Knowing the hardware and software installation requirements before you install SQL Server enables you to select the appropriate platform on which to install it.
The Setup program presents several installation options. Identifying the appropriate settings for these options ensures that your system is configured to meet your needs.
After this lesson, you will be able to
- Determine hardware and software requirements for SQL Server version 7 and the SQL Server management tools
- Determine the SQL Server installation options that are appropriate for your system
Estimated lesson time: 60 minutes
Hardware and Software Requirements
The following table describes the hardware and software requirements for a SQL Server installation. For more information about supported hardware, see the Microsoft Windows NT Hardware Compatibility List (HCL) at http://www.microsoft.com/isapi/hwtest/hcl.idc.
| Component | Requirements |
|---|---|
| Computer | Intel and compatible systems. DEC Alphatmand compatible systems. |
| Memory | A minimum of 32 MB additional memory is recommended for large databases and replication. |
| Hard disk space | An installation of a new server only, with no management tools, requires 65 MB. A Typical installation requires 170 MB, and a full installation requires 180 MB. An installation of only management tools requires 90 MB. |
| File system | NTFS or FAT NTFS is recommended for server installation because of the security and recovery advantages that this file system offers. |
| Operating system | Windows NT Server 4 or Windows NT Workstation 4 with Service Pack 4, or a later service pack, Microsoft Windows 95, or Windows 98. |
| Other software | All operating systems require Microsoft Internet Explorer 4.01 with SP1 or later. |
Note
You must install Microsoft Internet Explorer 4.01 with SP1 or later in order to use SQL Server Enterprise Manager.
Caution
In general, if the hard disk of the computer has a write-caching disk controller, disable it. Unless a write-caching disk controller is designed specifically for a database, it can seriously threaten SQL Server data integrity.
SQL Server Installation Options
When installing SQL Server 7, the following options are available. Many of these options are bypassed if you perform a Typical installation, but you can set all of them if you perform a Custom installation.
Licensing Mode
During installation, the Setup program requires you to select a licensing mode and then to accept the terms of the license agreement. You have a choice of two licensing modes Per Server and Per Seat:
- With Per Server licensing, the Client Access Licenses are assigned to a particular server. The maximum number of simultaneous workstation connections that are allowed must equal the number of Client Access Licenses that are assigned to that server.
- With Per Seat licensing, a Client Access License is assigned to a particular workstation. Each workstation that connects to SQL Server with either Microsoft-provided client software or third-party software requires a Client Access License. This mode allows an unlimited number of simultaneous workstation connections to multiple servers, without requiring the client to purchase additional licenses for each server.
If you are unsure of which licensing mode to choose, select the Per Server option. The licensing agreement provides a one-time, one-way option to change from Per Server licensing mode to Per Seat licensing mode. For example, if you begin with one server, 30 users, and 30 Client Access Licenses and later install a second server, you can configure the second server with Per Server licensing (and then purchase 30 additional Client Access Licenses). Or you can convert the first server to Per Seat and configure the second server as Per Seat (and purchase no additional Client Access Licenses).
In order for SQL Server 7 Desktop Edition to connect to SQL Server 7 Standard Edition to perform replication or use Data Transformation Services, SQL Server 7 Standard Edition must be installed with Per Seat licensing.
Note
Workstation connections and user connections are not the same. A workstation can run several different clients, each using multiple user connections, with one Client Access License.
Installation Path
You can install SQL Server on any local hard drive and in any folder.
Default Installation Path
The default installation path for the program files and data files is C:\Mssql7
Note
Some SQL commands (such as BACKUP DATABASE TO DISK) cannot recognize embedded spaces in folder names. For this reason, use "8.3" style folder names to ensure compatibility.
You can either accept the default installation path for SQL Server or specify another drive or folder for the program files, the data files, or both. The Setup program also installs files in the system folder, the location of which cannot be changed.
Caution
Make sure that you have sufficient disk space available on the drive that you specify. If you change the program or data file locations, do not include a space in the name of the new folder. Also note that a Typical installation requires approximately 72 MB of space on the system drive, regardless of the location of the program or data files.
Program Files
The program files folder is the root folder where the Setup program creates folders that contain all of the program files. Program files include the relational database engine, core tools (such as the osql and bcp utilities), upgrade tools, replication objects, and the Microsoft full-text search engine. The size of these files will not increase over time.
Data Files
The data files folder is the root folder in which folders for databases, transaction log files, system log, backup files, and replication data files are created. The Setup program creates database and transaction log files for the master, model, msdb, pubs, Northwind, and tempdb databases in this location. You can specify other locations for data files that are created after installation. These files should be located on a drive that has a sufficient amount of extra space because, unlike program files, data files increase in size over time.
Character Set
During installation, you choose the code page containing the character set that supports the language you use. A code page stores the codes that map to the character set you choose. You choose one character set for all databases on a server. A character set is a set of 256 uppercase and lowercase letters, digits, and symbols that SQL Server recognizes in your databases. The first 128 values are the same for all character set choices. The last 128 characters (sometimes called extended characters) differ from set to set and contain language-specific letters and symbols.
Default Character Set
The Setup program selects code page 1252 (ISO Character Set) as the default character set. Every database attached to a specific SQL Server uses the same code page. The following table describes frequently selected character sets.
| Character set | Description |
|---|---|
| Code page 1252 (ISO 8859-1,Latin 1, or ANSI) | This is the default character set. It is compatible with the ANSI characters used by the Windows NT and Windows operating systems. Use this character set if you intend to use Windows NT- and Windows-based clients exclusively or if you must maintain exact compatibility with a SQL Server environment for UNIX or VMS. |
| Code page 850 (multilingual) | This is a multilingual character set that includes all of the characters used by most languages of European, North American, and South American countries. This character set is a good choice for use in international companies or for use with MS-DOS-based clients that use extended characters. |
| Code page 437 States,(U.S. English) | This is the most commonly used character set in the United and it includes many characters for graphics that are not usually stored in databases. Unless you have a specific reason to select this character set, choose code page 1252, which provides more compatibility with languages other than U.S. English. |
Selecting a Character Set
The code page that you select for SQL Server does not have to match the code page that the Windows NT operating system uses. However, if the data in a server running SQL Server contains extended characters, you must carefully determine the code pages that are used in the database and on the clients. If SQL Server and a client use different code pages and a client also uses extended characters, the potential for translation conflicts exists.
SQL Server recognizes only one character set at a time. When you select a character set, consider the following facts and guidelines:
- Choose a code page that includes the language characters of your database.
- If you want to store data written in multiple languages on the same server, choose the default character set and use Unicode data types when you build your databases.
- When you use SQL Server Unicode data types, a column can store any character that the Unicode standard defines, which includes all of the characters that are defined in the various character sets. Unicode data types can support 65,536 different uppercase and lowercase letters, digits, and symbols.
- Use the same character set on both the client and the server unless your databases use only the first 128 characters of a character set.
Caution
To change the character set after you have installed SQL Server, you must rebuild all databases.
Sort Order
A sort order determines how character data is compared, in what sequence character data is returned from a query, and what characters are considered equal when they are compared. The selection of available sort orders depends on the character set that you choose.
Note
Sort order applies only to non-Unicode data. The next topic discusses Unicode collation.
Selecting a Sort Order
The Setup program selects dictionary sort order, case-insensitive as the default. Most users choose the default sort order because selecting a nondefault sort order may affect query result sets, the performance, and the development of clients, as illustrated in the following examples.
- Effects on query result sets If, for example, you choose a case-sensitive sort order and a user performs a query in which lastname='MacDonald', the result set includes only those names that match the case, whereas if you choose a case-insensitive sort order, the result set includes all variations of MacDonald, including Macdonald and macdonald.
- Effects on performance Choosing a sort order that is case-insensitive can improve performance because uppercase and lowercase characters of the same kind do not have to be compared and sorted beyond alphabetization. For example, an uppercase letter A is treated the same as a lowercase letter a.
- Effects on development of clients Choosing a case-sensitive sort order requires the client to convert characters to the proper case and to reference objects by using the proper case.
You cannot have databases with different sort orders on the same server, and you cannot back up and restore databases among servers that are configured for different sort orders. If you are considering having several SQL Server installations, each with different character sets and sort orders, and you intend to move data among these servers, first consider the consequences. For example, results may sort differently, result sets may differ, and characters may be lost.
Note
If you change the sort order after you install SQL Server, you must rebuild all databases. Run sp_helpsort to verify the current sort order.
Unicode Collation
SQL Server 7 supports Unicode and non-Unicode data types for columns in database tables. A Unicode collation acts as a sort order for Unicode data. This is separate from the sort order for non-Unicode data.
A Unicode collation consists of one locale and several comparison styles. Locales, usually named after countries or cultural regions, sort characters following the standard in that area. The Unicode collation still provides a sort order for all characters in the Unicode standard, but it gives precedence to the specified locale.
Default Unicode Collation
The Setup program selects general, case-insensitive, width-insensitive, and Kana-insensitive as the default set of options. This default set is based on the character set and sort order that you choose.
Selecting a Unicode Collation
Selecting the same sort order for Unicode and non-Unicode data is generally recommended. You can choose a collation other than the default, but exercise this option with caution. When you choose a different value, migrating data from non-Unicode to Unicode is more difficult, and Unicode and non-Unicode data may sort differently.
Note
If you change the Unicode collation after you install SQL Server, you must rebuild all databases.
Network Support
SQL Server uses network libraries to communicate with a specific network protocol and to pass network packets between a client and SQL Server. The server simultaneously listens on multiple network libraries; a client communicates with the server by using a specific network library. This allows different types of clients to use the same server.
Note
Selecting the appropriate network support for SQL Server requires a thorough understanding of your Windows NT network topography.
Default Network Libraries
The Setup program selects the Named Pipes, TCP/IP Sockets, and Multiprotocol network libraries as the default for SQL Server when installed on Windows NT. The TCP/IP Sockets and Multiprotocol network libraries are selected as the default for SQL Server when installed on Windows 95 or Windows 98 because these operating systems do not support Named Pipes. The Setup program makes Named Pipes on Windows NT and TCP/IP Sockets on Windows 95 and Windows 98 the default network library for client management tools. The client network library must match one of the network libraries on the SQL Server.
Selecting a Network Library
When you select a network library, consider the following facts:
- Your choice of network libraries requires that the appropriate network protocols be installed on your Windows NT, Windows 95 or Windows 98 computer.
- Windows NT authentication requires either the Named Pipes or the Multiprotocol network library.
- Windows NT encryption requires the Multiprotocol network library.
The following table describes the network libraries supported by SQL Server.
| Network library | Description |
|---|---|
| Named Pipes | Installed by default — allows clients to connect with named pipes over any Microsoft-supplied transport protocol. Not supported by Windows 95 or Windows 98. |
| TCP/IP Sockets | Installed by default — allows clients to communicate by using standard Windows Sockets across TCP/IP. |
| Multiprotocol | Installed by default — takes advantage of the Windows NT remote procedure call (RPC) facility. Communicates over Named Pipes, TCP/IP Sockets, NWLink IPX/SPX, and other IPC mechanisms. |
| NWLink IPX/SPX | Allows Novell SPX clients to connect to SQL Server by using native Novell IPX/SPX. |
| AppleTalk ADSP | Allows Apple Macintosh-based clients to connect to SQL Server by using native AppleTalk (as opposed to TCP/IP). |
| Banyan VINES | Supports Banyan VINES Sequenced Packet Protocol (SPP) as the IPC method across the Banyan VINES IP network protocol. |
Note
To change a network library to SQL Server after installation, use the SQL Server Network Utility.
SQL Server Services Account
Each SQL Server service runs in the security context of an assigned service account. The assigned service account can be a domain user account or the local System account.
The Default SQL Server Services Account
The Setup program selects a domain user account as the default. In general, use the same service account for all SQL Server services.
Using a Domain User Account
If you use a domain user account, the SQL Server services can communicate with remote servers by using trusted connections.
When you select a domain user account, the Setup program grants the right to the domain user to log on as a service on the SQL Server computer.
When you use a domain user account, consider the following facts and guidelines:
- The domain user account that you select must be a member of the Administrators local group on the SQL Server computer.
- Replicating data or multiserver administrative activities assumes that a domain user account is the security context for the SQL Server services.
- If you use SQL Server to send notifications through e-mail, you must use a domain user account.
- If multiple Microsoft BackOffice products, such as Microsoft Exchange Server and SQL Server, are installed on a single computer and the services must communicate with one another, you must use a Windows NT domain user account.
- Use a dedicated domain user account for the SQL Server services account, de-select User Must Change Password At Next Logon, select the Password Never Expires attribute, and allow all logon hours.
- SQL Server often is installed in environments that are composed of one or more Windows NT domains. In these environments, install SQL Server in a domain that has access to all user accounts for all domains.
- Installing SQL Server on a either a Primary Domain Controller (PDC) or Backup Domain Controller (BDC) is generally not recommended, because these computers perform the resource-intensive tasks of maintaining and replicating the network accounts database and performing network logon authentication.
Note
If you change the service account after installation, use SQL Server Enterprise Manager to set the service account because it sets the required rights automatically.
Using the Local System Account
The local System account is a built-in account that has the same authority as the Windows NT operating system, but only for local resources. If you choose the local System account, SQL Server services cannot communicate with remote servers using a trusted connection. If you install SQL Server on a computer running Windows NT that is not part of a domain, you can use the local System account. This is not generally recommended; however, it means that you do not need to create a Windows NT account for SQL Server services.
Autostart Services
By default, the Setup program installs the MSSQLServer service (the database engine) to run as an automatically started Windows NT service. The benefit of having this service start automatically is that a system administrator does not have to log on to start it each time Windows NT starts. If you want, you can have the other SQL Server-related services (SQL Server Agent, Microsoft Distributed Transaction Coordinator, and Full Text Search) start automatically too.
Exercise: Granting Administrator Rights to User Accounts
In this exercise, you will log on to your local computer with the local administrator account and add the SQLService domain user account to the Administrators local group. The SQLService domain user account will be used for the SQL Server services account.
Adding the SQLService domain user account to the Administrators local group gives the SQLService domain user account administrator rights on the local computer. This is required in order to install SQL Server on the local computer with the SQLService domain user account for the SQL Server services account.
- To grant local administrator rights to the SQLService domain user account
- Log on to your local computer as Administrator or by using another account that is a member of the local Administrators group.
- Start User Manager for Domains.
- Add a new user named SQLService.
- Set the new user password to password.
- Ensure that the User Cannot Change Password and Password Never Expires check boxes are checked and that the User Must Change Password At Next Logon and Account Disabled check boxes are not checked.
- Click the Groups button.
- Add SQLService to the Administrators local group. (Administrators must appear in the Member Of list box.) If your computer is part of a Windows NT domain, also add SQLService to the Domain Admins global group.
- Click OK to close the Group Memberships dialog box, and then click Add to add the SQL Service user. Click Close to close the New User dialog box.
Lesson Summary
Familiarize yourself with the hardware and software requirements of SQL Server 7 before you begin installation. The following options are configured during installation: licensing mode, file locations, character set, sort order, Unicode collation, network support, and SQL Server services accounts.