Installing SQL Server

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.