Installing and Configuring SQL Server 2000

SQL Server 2000 can be installed on Windows 2000 Server, Windows 2000 Advanced Server, and Windows 2000 DataCenter Server. To support failover clustering, SQL Server 2000 Enterprise Edition is required and the operating system needs to be either Windows 2000 Advanced Server or Windows 2000 DataCenter Server. SQL Server 2000 Enterprise Edition supports the creation of server clusters that link up to four SQL server systems together as a virtual system.

Security on SQL Server 2000 fully supports Windows domain security allowing authentication based on Windows user and group accounts as well as SQL Server user accounts. When SQL Server is installed, you must choose the method to use for authentication:

Windows domain accounts only
A combination of Windows domain accounts and SQL Server user accounts (default)

Using only Windows domain accounts can simplify the management required when multiple SQL Servers are operated in a domain. Groups of users can be created in the Windows domain that are given different abilities to use and manage the instances of SQL Server.

The SQL Server (MSSQLServer) process that operates the database server and the SQL Server Agent (SQLServerAgent) used for scheduling tasks are configured to run as services on Windows 2000. An important decision that must be made during the installation process is the service account under which these services are run. The options are as follows:

Local System account
Specific Windows Domain user account

A disadvantage of using the Local System account is that while this account has administrative rights on the local Windows 2000 Server, the Local System has no privileges on other network computers in the Windows domain. This is particularly a problem if the SQL Server instance needs to operate tasks (run DTS packages or send e-mail on errors, for example) that depend on having privileges on other systems in the domain. So a domain account is a better choice for the proposed scenario. When using a Windows domain account, it is important that the user have administrative privileges on the local Windows 2000 Server where SQL Server is installed.

SQL Server 2000 supports the ability to integrate with Active Directory, the directory service in Windows 2000. Custom applications can use the OLE DB Provider for Microsoft Directory Services to access Active Directory information. This OLE DB Provider exposes both an LDAP interface and an SQL interface that can be used for queries.

SQL Server 2000 supports the option of running multiple instances of the SQL Server database engine on a single computer system. One instance of the database engine can be installed on a computer system as the default SQL Server instance, while additional named instances may also be installed. Each instance of the SQL Server database engine will include its own set of system and user databases and separate SQL Server and Server agent services. Additional instances can be created and added later to a system. This feature can be very useful, allowing a single computer to support several environments such as a production system in parallel with a separate test and development system.

The default collation sequence and character set to be used is determined when SQL Server 2000 is installed. While the collation sequence and character set can be specified on a per column basis in a data table, the defaults when these attributes are not specified are based on the selections made during installation.

SQL Server configuration information is stored in the local Windows registry as well as within various system tables maintained by the database engine. While many of these settings can be modified on a running system, some configuration settings such as the default collation sequence and the service account to use require a complete rebuild of the SQL system databases. A rebuild of the system database also deletes any data tables, so care should be taken to backup data before rebuilding SQL Server system tables.

A number of configurable parameters affect the performance of SQL Server 2000 including memory usage, CPU usage on multiprocessor systems, number of connections supported, threading, priorities, and fibers. If database performance is an issue, careful attention to these parameters can be used to tune the performance of SQL Server.

Previous Next