Step-by-step instructions for setting up SQL Server 2000 and SQL Server 7.0

If you're a Windows 2000 or Windows NT 4.0 systems administrator who has been assigned the task of managing a Microsoft SQL Server system, you might be concerned about how you're going to carry out your new responsibilities. Fortunately, administering SQL Server 2000 and SQL Server 7.0 is much simpler than managing earlier versions.

The first step in managing SQL Server is to install it. Let's look at the decisions you need to make before you install SQL Server, then go through the process of installing SQL Server 2000 on Win2K and NT 4.0. The SQL Server 2000 installation has some neat new features, and I point those out. I strongly recommend that if your company is running SQL Server 6.5 or earlier, you immediately upgrade to SQL Server 2000 or at least to SQL Server 7.0. The sidebar, "SQL Server 7.0 Installation Procedures," walks you through the steps for SQL Server 7.0 installation.

Before You Start
You need to perform some tasks and make some decisions before you begin to install SQL Server, whether you've chosen SQL Server 2000 or SQL Server 7.0. Microsoft recommends dedicating a user account for the OS to use to start the SQL Server service (the core engine for the database) rather than using the Administrator account, so you need to set up a domain user account. You could use the Local System account, but this account has no network connectivity in NT 4.0 (it does in Win2K), so you wouldn't be able to administer an NT 4.0 server remotely. If you've set up a dedicated service logon account before or are at least familiar with the concept from NT 4.0 replication, you know that the account needs administrator privileges and that you should set its password to never expire. Clear the User must change password at next logon check box, or the SQL Server service will fail to start. The account, which I usually call something like SQLExec, should have a hard-to-guess password. The only time you need to type the password is when you're installing SQL Server, and the account is a potential target for a break-in because it has administrative privileges.

During the installation, you'll have to specify the character set and sort order for SQL Server to use. The character set specifies how SQL Server should interpret the extended ASCII characters (i.e., those from 128 to 255). Different versions of SQL Server use different character sets that reflect the underlying OS version. Code page 437 is the old DOS character set, in which many of the extended ASCII characters are graphical symbols. Code page 850, which international applications use, includes various accented characters. The newest character set is code page 1252, also known as the International Organization for Standardization (ISO) 8859-1 code page in SQL Server 7.0.

The sort order determines how SQL Server will present data when users ask for result sets from the database. For example, in a binary sort order, characters are sorted by their position in the ASCII table. Uppercase characters (i.e., A to Z) come first, followed by lowercase (i.e., a to z). Thus, "Zahn" comes before "de Lucia" in a result set that's sorted in binary order. But database users usually expect a dictionary-like, case-insensitive sort order, so recent versions of SQL Server have adopted such an order as the default. If you're installing SQL Server for new applications and new databases, go with the default code page and sort order.

If you have older databases that use an older character set or sort order, you might need to retain that code page or sort order on your new SQL Server installation. If you intend to transfer the data by dumping it to an ASCII file and importing the file, you can import it using the new default sort order.

Unfortunately, in SQL Server 7.0 and earlier, you can have only one sort order per server, and a few major software packages insist on the older binary sort order. If you have one of these packages, your only choices are to install it on its own server or make all your new databases use the binary sort order. Keep in mind that to change the sort order after you install SQL Server 7.0 and earlier versions, you must unload any data to ASCII files, reinstall SQL Server with the new sort order and character set, and reload the data. You can see that getting the sort order right the first time is crucial.

SQL Server 2000 is much more flexible about sort orders and character sets, replacing them with what Microsoft refers to as collations (i.e., combinations of sort orders and character sets) and allowing different collations for different databases on the same SQL Server system. So with SQL Server 2000, you can install with the default collation to accommodate any new databases, and you'll still be able to work with third-party software packages with no problem. Sometimes your internal programmers want one sort order but a third-party program such as PeopleSoft demands a different sort order. If sort order and character sets are concerns in your organization, insist on installing SQL Server 2000 so that everyone can have their own sort order for their particular set of databases.

Folder Locations
You also need to decide on which drive to install the SQL Server software, although in typical fashion, Microsoft gives you only limited say in the matter. SQL Server 2000 dumps a whopping 183MB of files on the system drive (i.e., the drive on which you installed the OS). SQL Server 7.0 puts 33MB on the system drive. So, make sure that you have plenty of space on this drive.

Microsoft's excuse for installing so many SQL Server 2000 files on the system drive is that the new option for multiple instances of SQL Server requires a fixed location for the files that are common to all instances. However, I don't see any reason for Microsoft not to let the administrator specify that location.

The SQL Server installation program also puts files in a program-file directory and a data-file directory. In SQL Server 7.0, the default directories are C:\mssql and C:\mssql\data, respectively. You can specify different locations for the directories. If you do so, the SQL Server 7.0 program-file directory will have the exact name and path that you indicate, but the installation program will add a \data subdirectory to the location you specify for the data files. SQL Server 7.0 puts a hefty 83MB of files on the drive you specify for program files.

SQL Server 2000 builds subdirectories below what you specify for both program files and data files. Therefore, if you say that you want the program files in D:\mssql2000, SQL Server 2000 actually puts them in D:\mssql2000\mssql. If you say that you want the data files in D:\mssql2000\data, SQL Server 2000 puts them in D:\mssql2000\data\mssql\data. To avoid these multilevel directories in SQL Server 2000, specify only a drive (e.g., D), and the installation process will build the \mssql and \mssql\data directories on that drive.

The data files I refer to here are the files in SQL Server system databases (i.e., Master, Model, Msdb, and Tempdb). If you perform a Custom installation, you can specify a location other than \mssql\data for the system databases, but accepting where SQL Server offers to put the system databases is probably best. The databases don't take up much disk space (a little more than 30MB on both SQL Server 2000 and SQL Server 7.0), and anyone who knows about SQL Server will look for them in the default location first.

SQL Server Editions and Full Text Search
Before you start the SQL Server 2000 or SQL Server 7.0 installation routine, you should also determine which version and edition of SQL Server you want to use and whether you want to install Full Text Search. SQL Server 2000 Standard Edition or SQL Server 7.0 Standard Edition is adequate for most production servers. SQL Server 2000 Enterprise Edition and SQL Server 7.0 Enterprise Edition offer some advanced features such as failover clustering support. SQL Server 2000 Enterprise Edition adds the ability to spread huge tables across multiple servers.

SQL Server 7.0 Standard Edition runs on Win2K Server, Win2K Advanced Server, and NT Server 4.0, Standard Edition with Service Pack 4 (SP4) or later. SQL Server 7.0 Enterprise Edition runs on Win2K AS or NT Server 4.0, Enterprise Edition (NTS/E) with SP4 or later. Both editions of SQL Server 2000 run on Win2K Server; Win2K AS; Win2K Datacenter Server; NT Server 4.0, Standard Edition with SP5 or later; and NTS/E with SP5 or later. Microsoft claims that indexed views run only on SQL Server 2000 Enterprise Edition, but I run them on SQL Server 2000 Standard Edition.

Microsoft intended SQL Server 2000 Personal Edition and SQL Server 7.0 Desktop Edition for developers and others who need a local copy of SQL Server, perhaps on a laptop that they use outside the office. These editions lack some of the functionality of their bigger siblings—for example, they don't support Full Text Search. If you're installing SQL Server on Win2K Professional, NT Workstation, Windows Me, or Windows 9x, only the Personal Edition or Desktop Edition will work. Choosing the Standard Edition on any of these platforms will generate an error message.

The Full Text Search service uses many of the same components as Microsoft Index Server. The service lets developers index text data and do searches for matching words or phrases, including multiple forms of verbs and nouns. Not surprisingly, Full Text Search requires a significant amount of storage capacity to index text data.

To install Full Text Search with SQL Server 7.0, you must do a Custom installation. SQL Server 2000 installs Full Text Search by default in the Typical installation and even in the Minimum configuration. If you don't install this option initially, you can rerun the setup program later to install it.

Installing SQL Server 2000
Installing SQL Server 2000 or SQL Server 7.0 is quite easy and takes only about 10 minutes on a 400MHz Pentium II or Pentium III machine. Installation might take a little longer on a 166MHz machine, which is the minimum requirement for both SQL Server 2000 and SQL Server 7.0. You must also have at least 64MB of memory for SQL Server Standard Edition and SQL Server Enterprise Edition.

The instructions that follow are for SQL Server 2000 Standard Edition installation and configuration on Win2K. However, installing SQL Server 2000 on NT 4.0 is very similar. Installing SQL Server 7.0 Standard Edition on Win2K or NT 4.0 is also similar but different enough that I present a detailed account in the Web-exclusive sidebar "SQL Server 7.0 Installation Procedures" at http://www.win2000mag.com. If you plan to install SQL Server 2000, you can ignore the sidebar. But if you plan to install SQL Server 7.0, you might benefit from reading the rest of this article in addition to the SQL Server 7.0 sidebar—the SQL Server 2000 installation and configuration information details installation improvements and SQL Server 2000—only features that might convince you to switch to SQL Server 2000.

Before you install SQL Server 2000, you should have installed Win2K Server, Win2K AS, or NT Server 4.0 with SP5. You also should have installed Microsoft Internet Explorer (IE) 5.0 or later.

Insert the SQL Server 2000 CD-ROM to begin the SQL Server installation. If you haven't disabled the Autorun feature on your computer, you'll see the initial CD-ROM dialog box. If you have disabled Autorun, open Windows Explorer and click the autorun.exe file in the root directory of the CD-ROM. You already have the prerequisite software installed, so click SQL Server 2000 Components.

The second dialog box lets you choose to install the Database Server, Analysis Services (formerly known as OLAP Services), or English Query. Click the Install Database Server option. Analysis Services manages and analyzes data in data-warehouse and data-mart installations. English Query, used mainly with data-mart applications, lets users perform English language—style queries. As English Query evolves and improves, you might see it being used in many other ways, such as with interactive Web applications. If you want to install Analysis Services or English Query, you'll have to return later to this list of installation choices and run the appropriate setup procedure.

Your next choice is whether to install SQL Server locally or on a remote server. The instructions in this article assume that you're installing SQL Server on a local server, so choose the local option. If you choose the remote option, later in the installation process you'll have to supply the name, password, and domain of an account with sufficient privileges to install the software on the remote server. You'll also have to specify the target path for the installation on the server and the location of the source files. You use a Universal Naming Convention (UNC) path for both the target and source paths, so the source files don't have to be on your computer. You can browse for the remote computer, but if you type in the name, type just the name, without slashes.

At the next dialog box, which Figure 1 shows, choose the Create a new instance of SQL Server, or install Client Tools option. The multiple instances feature, new in SQL Server 2000, lets you install multiple SQL Server 2000 instances on the same computer and run them simultaneously. The separate instances show up as distinct processes in Task Manager, each with its own area of memory and its own resources. Obviously, multiple instances of SQL Server 2000 require a multiple-CPU server with a lot of memory.

You don't need multiple SQL Server instances to run multiple databases: One SQL Server instance can and typically does support multiple databases. But one reason to run multiple instances is to provide different security levels for different databases. You might want to let network administrators manage databases that require low-to-moderate security. But if you have one or two databases that require tighter security, you might want to build these in a separate instance of SQL Server that only a few people given the role of database administrator can access.

To create a second instance of SQL Server 2000, you would just run the installation process again and specify that you're installing another new instance, rather than upgrading or modifying the existing instance. However, the use of multiple instances is an advanced feature, so you might want to put off installing multiple instances until you're comfortable with SQL Server and have a good reason for using this feature.

The dialog box in Figure 1 also offers Advanced options, which is a new feature of the SQL Server 2000 setup. One of the advanced options lets you rebuild the SQL Server registry hive if it has become corrupted. Another lets you record your installation choices in a file without installing SQL Server 2000. You can then modify the file with a text editor as needed and use it for unattended SQL Server 2000 installations.

When you install SQL Server 2000, the installation process automatically creates the setup.iss file in the \%systemroot% directory (e.g., C:\winnt). If you specify during the installation process that you just want to create this file (rather than installing SQL Server 2000), the installation process will let you create the file without installing the software. Microsoft also supplies at the root level of the SQL Server 2000 CD-ROM some unattended-installation files and the batch files to run them. You can use these files, or you can run setupsql.exe and tell it the name of the unattended-installation file to use. The CD-ROM also contains support files for installing SQL Server 2000 with Microsoft Systems Management Server (SMS).

The next installation dialog box requests a name and company name (the company name is optional). You must also enter the CD-ROM key unless you're installing the 120-day evaluation version of SQL Server. The license agreement follows, and as usual, you must accept it before you can continue with the installation.

Next, you can choose whether to install both SQL Server and the Client Tools or just the Client Tools. Select the SQL Server and Client Tools option to install SQL Server with the administration tools. You would select the other option if you wanted to install the Client Tools on this computer to connect to and administer another server.

Your response at the next dialog box tells the installation procedure whether you want to use the default name for this instance of SQL Server 2000 or specify a name for the instance. Leave the Default check box selected.

SQL Server 2000 offers three installation choices—Typical, Minimum, and Custom—as the Setup Type dialog box in Figure 2 shows. You can go with the Typical installation unless you need to install development tools, specify a character set and sort order (i.e., a collation) other than the default, omit Full Text Search, or put the system databases in a location other than an \mssql\data directory.

The Minimum option installs the basics needed to run SQL Server and Full Text Search; it doesn't install the documentation, client tools, or code samples that the Typical and default Custom options install. The Custom option starts with the same set of choices as the Typical option, but the Custom option lets you modify the defaults. If you want a detailed account of what each of the options installs, click Help.

The Setup Type dialog box also lets you choose the directories for SQL Server's program files and data files. To avoid the multilevel directories I mentioned earlier, select just a drive letter. For example, specify D for both the program files and data files, and SQL Server will build D:\mssql for the program files and D:\mssql\data for the data files.

If you choose the Custom option at the Setup Type dialog box, the next dialog box lists the components you can install. The Typical and Minimum installations skip this dialog box.

Regardless of the type of installation you choose, you must supply at the next dialog box—the Services Accounts dialog box—a user account name and password for the SQL Server service. Use the name and password of the dedicated account that I advised you to establish at the beginning of this article. You can separately configure the SQL Server service and the SQL Server Agent service (which handles administrative tasks such as job scheduling for SQL Server), but typically you should use the same account for both.

SQL Server 2000 lets you choose Windows-only authentication or mixed-mode (Windows and SQL Server) authentication during the setup process. If you want only Win2K- and NT-authenticated users to connect to the SQL Server database, select the check box for Windows only. (For a discussion of SQL Server security, see "Introducing SQL Server Security," October 2000, InstantDoc ID 15479.) If you choose mixed mode, you can supply a password for the system administrator (sa) account at the next dialog box. This method of specifying authentication is an improvement over that of earlier versions, in which the password defaulted to blank during installation and you had to set the password after installation.

If you choose a Custom installation, you'll see the Collation Settings dialog box, which Figure 3 shows. You shouldn't have to change from the default unless you must match a different collation on an application or on another server. This dialog box and the following dialog boxes for Network Libraries don't appear during a Typical or Minimum installation.

All that remains now is to select the licensing mode (per server or per seat), then click Finish to let the SQL Server installation begin copying files. After the installation is complete, reboot the computer to make sure the installation is successful and to start the SQL Server service. Then, use Service Manager to manually start the SQL Server Agent and other necessary services.

To start Service Manager, click Start, click Programs, select Microsoft SQL Server 2000, and click Service Manager. The Auto-start service when OS starts check box should already be selected for the SQL Server service, as Figure 4 shows. Start the SQL Server Agent service and select its autostart check box.

The Microsoft Distributed Transaction Coordinator (MS DTC) service needs to run only if your applications update more than one server at a time (e.g., if an application updates personnel and payroll databases that are on different servers). Your programmers should be able to tell you whether you need the MS DTC service. If in doubt, start it and set its autostart option.

If you installed Full Text Search, you'll have a fourth service, imaginatively named the Microsoft Search service. Presumably, if you installed Full Text Search, you want it to run, so start it, and set its autostart option if you want. If you need to remove SQL Server 2000, you can do so with the Add/Remove Programs utility.

SQL Server 2000 Configuration
You don't have much to configure in SQL Server 2000 because you set many parameters during installation. But you might want to make one change.

SQL Server 7.0 uses the directory you established for the SQL Server system databases as the default directory for the databases you build. However, SQL Server 2000 lets you set a separate default data directory for your databases and a default log directory for transaction logs that record changes to your databases. When you create a database, the files and logs go in these directories unless you say otherwise. The directories can go anywhere you want, so you might decide to move them off the disk on which you installed the SQL Server software. Even if you do move them to a different disk, that disk can quickly fill up if all your databases' log files end up on it. To avoid this problem, I use the default data directory mostly for development, creating and deleting test databases as needed. I take a little more care placing production databases.

When planning your database storage, make sure that your data and transaction logs are on separate physical disks. If you lose the disk that stores the data, you can restore your most recent backup, then reapply from the transaction log the changes made since the backup to recover the data. If you lose the disk that contains the transaction log, all processing stops, but at least the data is intact. If you have to call in a consultant to help with a complex recovery after a disaster, you'll have all the information he or she needs.

To set the default directory locations, click Start, click Programs, select Microsoft SQL Server 2000, and click Enterprise Manager. If you're logged on to your domain as an administrator, you'll be able to connect to the SQL Server system because all Win2K and NT 4.0 administrators are by default SQL Server administrators. In Enterprise Manager, right-click the SQL Server 2000 system in the hierarchy and select Properties. Click the Database Settings tab, which Figure 5 shows, and enter the desired folder paths. You can now exit Enterprise Manager, unless you need to add SQL Server users. For instructions about how to add users, see "Introducing SQL Server Security."

As you can see, installing SQL Server isn't difficult. But as with most software installations, it's easier when you're prepared for the choices that you'll have to make during the installation process.