System Components and Options | Microsoft SQL Server 2000 Administrators Companion

3 4

Now that you have had a brief introduction to the types of applications available and service level requirements, you are ready to decide which software to install on your system. You can choose from four versions of Windows 2000 and three versions of SQL Server 2000. In this section, you will learn the differences between these versions and why you might select one over another.

Windows 2000 Versions

The four versions of Windows 2000 are designed to provide the right software for the right application. The capabilities of Windows 2000 grow as you move from Windows 2000 Professional to Windows 2000 Server to Windows 2000 Advanced Server and finally to Windows 2000 Datacenter. The following sections describe the capabilities of each version. You should choose the version of Windows 2000 that provides the capabilities that you need, rather than just purchase the most expensive version with the most capabilities.

Windows 2000 Professional

Windows 2000 Professional is essentially the desktop version of Windows 2000. Typically, a system running Windows 2000 Professional will take advantage of only the SQL Server 2000 client components. However, if you need to run SQL Server on your computer, you can install the Personal Edition of SQL Server 2000. The Personal Edition permits only local access to the database. Access from other systems is not permitted.

NOTE
Only the Personal Edition of SQL Server (and the client components) can be installed on Windows 2000 Professional.

Windows 2000 Server

Windows 2000 Server is designed as a server operating system, which means that installing Windows 2000 Server on a computer allows other systems to access resources on that computer. Windows 2000 Server supports SQL Server 2000 Standard Edition. Windows 2000 Server doesn't support systems with more than four CPUs and more than 4 gigabytes (GB) of memory. SQL Server 2000 allows remote clients to access the database as well.

NOTE
Only SQL Server Standard Edition, SQL Server Personal Edition, and the SQL Server client components can be installed on computers that are running Windows 2000 Server.

Windows 2000 Advanced Server

Windows 2000 Advanced Server is also a server operating system. As with systems that run Windows 2000 Server, systems that run Windows 2000 Advanced Server allow other systems to access their system resources as well as SQL Server. In addition to having the capabilities of Windows 2000 Server, Windows 2000 Advanced Server supports up to eight CPUs and 8 GB of memory. In order to use Microsoft Cluster Services (MSCS) for failover support, you must be using Windows 2000 Advanced Server. In addition to supporting MSCS, Windows 2000 Advanced Server with SQL Server 2000 supports the new SQL Server clustering technology, updatable distributed views.

NOTE
To utilize eight CPUs and 8 GB of memory within SQL Server 2000, you must run the Enterprise Edition of SQL Server. In addition, SQL Server Standard Edition, SQL Server Personal Edition, and SQL Server client components can be installed on Windows 2000 Advanced Server.

Windows 2000 Datacenter

The flagship version of Windows 2000 is the Datacenter Edition. This version supports all of the components that the other editions of Windows 2000 do, as well as up to 64 CPUs and 64 GB of memory. Windows 2000 Datacenter is available only from hardware vendors. In addition to integrating Windows 2000 Datacenter with their hardware, these vendors offer the highest level of support available for Windows 2000. This integration provides a single point of contact for Windows 2000 support and hardware support.

NOTE
To utilize 64 CPUs and 64 GB of memory within SQL Server 2000, you must run the Enterprise Edition of SQL Server. In addition, SQL Server Standard Edition, SQL Server Personal Edition, and SQL Server client components can be installed on Windows 2000 Datacenter.

SQL Server Versions

In addition to the versions of Windows 2000 that you have to choose from, there are several editions of SQL Server. It is fairly easy to choose, based on the amount of memory and number of CPUs that you need to use. The editions of SQL Server are described here.

Client Software

The SQL Server 2000 client components consist of the network libraries and utilities needed to access a remote or local SQL Server system. These components are necessary for any system to access SQL Server, and they are identical regardless of which edition of SQL Server is installed.

Personal Edition

The Personal Edition of SQL Server is designed for small databases that are accessed locally on a client system. SQL Server 2000 Personal Edition does not allow other computers to gain access to the database.

Standard Edition

SQL Server 2000 Standard Edition is one of the two server editions of SQL Server 2000. The Standard Edition functions the same way the Enterprise Edition does except that a maximum of four CPUs and 4 GB of memory can be accessed from the Standard Edition.

Enterprise Edition

The Enterprise Edition of SQL Server supports all the features and functionality of all versions of Windows 2000. SQL Server 2000 Enterprise Edition requires Windows 2000 Advanced Server or Windows 2000 Datacenter. In addition, SQL Server 2000 Enterprise Edition supports 2-node failover clustering and updatable distributed views.

Version Comparison

This table shows the versions and capabilities of Windows 2000 and SQL Server 2000.

Table 4-1. Version comparison

	SQL Server 2000 Personal Edition	SQL Server 2000 Standard Edition	SQL Server 2000 Enterprise Edition
Windows 2000 Professional	Limited capabilities No client access	N/A	N/A
Windows 2000 Server	Limited capabilities No client access	Server capabilities Up to 4 CPUs and 2 GB of memory	Server capabilities Up to 4 CPUs and 4 GB of memory
Windows 2000 Advanced Server	Limited capabilities No client access	Server capabilities Up to 4 CPUs and 2 GB of memory	Server capabilities MSCS Up to 8 CPUs and 8 GB of memory
Windows 2000 Datacenter	Limited capabilities No client access	Server capabilities Up to 4 CPUs and 2 GB of memory	Server capabilities MSCS Up to 64 CPUs and 64 GB of memory

As you can see, you have several choices. You should carefully consider your decision on what to purchase. Once you have installed the system, you will have to start from scratch if change your mind.

System Options

In addition to selecting an edition of SQL Server 2000 and a version of Windows 2000, you can take advantage of several other options. These options are described in the following section, and they include MSCS, SQL Server 2000 replication options, and updatable distributed views (new in SQL Server 2000 Enterprise Edition). Each of these options has specific capabilities and requirements and therefore may or may not be useful with your configuration, as you will see in the next few paragraphs.

MSCS

MSCS stands for Microsoft Cluster Services and is a Windows 2000 option that works in conjunction with SQL Server 2000. MSCS provides the ability for one computer to act as a standby, or failover, server for another computer. This capability allows the recovery process to begin almost immediately in the event of a hardware or even a software failure.

MSCS requires a shared disk subsystem to be connected to both systems in the cluster. The SQL Server transaction log and data files, as well as executable files, must reside on this shared disk subsystem. In the event of a failure, indicated by the loss of a heartbeat signal, the standby system takes over the SQL Server functions. Because the standby system captures the IP address and system name, it appears to the outside world as though the primary database server has simply rebooted.

NOTE
A cluster failover requires the same database recovery as any system that has rebooted suddenly. MSCS does not provide fault tolerance—merely quick recovery.

SQL Server Replication

SQL Server replication allows data from one SQL Server database to be replicated to another database system. There are several varieties of replication—snapshot, transactional, and merge—which are described in the following paragraphs. Which one would work best for you depends on your preferences and needs. SQL Server replication works on a publish-and-subscribe model, in which the publisher publishes the data and one or more subscribers receive copies of that data.

Snapshot Replication Snapshot replication periodically takes a picture, or snapshot, of the data and provides that data for other systems to use. Snapshot replication operates only when the snapshot is being created and applied; thus, no overhead is incurred during normal operations. The downside to snapshot replication is that the data is only as current as the last snapshot, which could be quite old.

Transactional Replication Transactional replication starts with a snapshot, but from that point on, the transaction log on the publisher is continually read, and transactions that have been applied to the publisher are then applied to the subscriber or subscribers. This allows the subscriber or subscribers to be kept somewhat up-to-date. Of course, there is some delay between when a transaction is committed on the publisher and when it is applied to the subscriber or subscribers.

Merge Replication Merge replication is different from snapshot and transactional varieties in that updates take place on both the publisher and subscriber systems. SQL Server uses triggers and timestamps to coordinate the changes between the various systems involved in the replication. Merge is useful if multidirectional replication is required, but it has much higher overhead than snapshot or transactional replication.

Updatable Distributed Views

SQL Server 2000 introduces updatable distributed views. This option allows SQL Server systems to share a logical database, thus increasing scalability. The logical database can become large, and you can spread it across many computers to increase its capacity. Updatable distributed views are described in detail in Chapter 18.