Windows 2000 and SQL Server

Windows 2000 (Win2K)—the fifth major release of the Windows NT OS—includes many sophisticated features that enable it to compete head-to-head with UNIX systems, which are well established as file, application, and database servers in the enterprise market. The networking and usability improvements in Win2K make the new OS a better database platform for SQL Server than NT 4.0. See also, "SQL Server Is Tops."

SQL Server and Windows Version Compatibility

Win2K Server supports SQL Server 2000, SQL Server 7.0, and SQL Server 6.5. However, SQL Server 6.5 requires Service Pack 5a (SP5a) before you upgrade to Win2K. Neither SQL Server 2000 nor SQL Server 7.0 requires additional service packs before an upgrade to Win2K. If you're running a version earlier than SQL Server 6.5, you'll need to upgrade to SQL Server 6.5 or later before you move to Win2K.

SQL Server 2000 Enterprise Edition runs on Windows 2000 Advanced Server (Win2K AS), Windows 2000 Datacenter Server (Datacenter), and NT Server 4.0. The standard version of SQL Server runs on Win2K Server, Win2K AS, Datacenter, and NT Server 4.0. The SQL Server 2000 Desktop Edition runs on Windows 2000 Professional (Win2K Pro), NT Workstation 4.0, and Windows 98.

Active Directory and Kerberos Integration

The most significant integration feature between SQL Server 2000 and Win2K is SQL Server 2000's support for Active Directory (AD). When you install SQL Server 2000 on a Win2K Server system, SQL Server can immediately take advantage of available directory services by storing information within AD about SQL Server systems and databases. AD will track SQL Server information, such as the names and locations of registered SQL Server systems and their database names, descriptions, aliases, version information, sizes, and last backup dates. The integration of SQL Server database information within AD lets SQL Server database administrators see and connect to remote SQL Server systems across the enterprise without first needing to register the remote servers in Enterprise Manager. In addition, this integration facilitates centralized administration by letting a local DBA immediately view and manage new networked servers, databases, and OLAP cubes when they come online.

SQL Server 2000 can also take advantage of Win2K Kerberos security. Kerberos provides much stronger and better-performing user authentication capabilities than the NT LAN Manager (NTLM) security implementation that NT 4.0 provides. To authenticate a client with NTLM authentication, you need to connect an application server to a domain controller. With Kerberos authentication, you don't need to connect the server to a domain controller. Instead, the server directly authenticates the client by examining credentials the client presents. In addition to improvements in single-server authentication, SQL Server 2000 can use the delegation capabilities in Kerberos to pass a client's security credentials to remote servers when SQL Server processes distributed queries. As you might expect, the remote system also needs to support Kerberos authentication to take advantage of the delegation capabilities.

Support for smart card authentication is another Win2K enhancement that SQL Server 2000 can benefit from. Win2K's support for smart card logon lets you use certificates and private keys stored on smart cards to authenticate local and remote users. When you use SQL Server's integrated security, Win2K can then pass the smart card user's credentials to SQL Server.

Windows 2000 Performance Improvements

Improvements to the core of Win2K make it a faster database platform than NT. SAP benchmark tests conducted with SQL Server 7.0 Enterprise Edition on Win2K AS showed that Win2K AS processed more than 3.17 million line items per hour, whereas NT Server 4.0 clocked in at 3.05 million line items per hour. Win2K AS processed more than 100,000 additional line items per hour running on the same hardware.

Two OS improvements that have the biggest impact on overall database performance are processing power and available memory. Win2K AS and Datacenter both support more SMP processors and more maximum available memory. Standard Win2K Server provides support for up to 4 processors and 4GB of RAM. Win2K AS supports up to 8 processors in an SMP system and, with Intel's Physical Address Extension (PAE), can support up to 8GB of memory. Datacenter boosts this limit even higher with support for 32-processor SMP systems and a maximum physical memory of 64GB.

PAE lets Advanced Windowing Extensions (AWE)-enabled applications such as SQL Server Enterprise Edition use addresses above 4GB to cache data in memory. Holding additional data in RAM gives memory-hungry applications such as large SQL Server database applications significant performance improvements by avoiding the slow process of accessing the disk subsystem. PAE maps up to 64GB of physical memory into a 32-bit (4GB) virtual address space in 4KB pages. PAE extends the Win2K page tables from 20 bits to 24 bits. The extra four bits enable the use of 36-bit physical addresses.

In addition to increased memory support, Win2K includes several other SMP-related improvements such as reduced lock contention, reduced process serialization, reduced code page lengths, improved affinity control, increased kernel pool size, and reduced data copy operations.

Win2K also supports the latest generation of Intelligent Input/Output (I2O) hardware, which uses a dedicated processor with its own storage to offload I/O processing from the system CPU. The primary benefit of I20 hardware is its ability to provide improved system throughput and reduce the CPU requirements. Win2K also uses a new scatter and gather I/O algorithm, which increases I/O throughput when application data is in discontinuous memory locations, as is typical of most applications.

In addition, Microsoft enhanced the performance of the basic TCP/IP stack in Win2K Server so the stack can take better advantage of high-bandwidth networks. Win2K Server's TCP/IP stack includes large window support. The TCP/IP window essentially defines the number of packets that can be sent in a streamed sequence before the system requires acknowledgment. Win2K's large TCP/IP window support lets the system dynamically calculate and increase the TCP/IP window size as more data packets are transmitted. This feature speeds data movement between SQL Server and the client systems.

Windows 2000 Clustering and High-Availability Enhancements

Improved reliability was one of the key goals guiding Win2K development. During the Win2K development process, Microsoft conducted a survey of 5000 NT Server administrators to identify the main causes of system downtime. Microsoft used the survey results to make several core system improvements to reduce the number of reboots and make Win2K a more reliable OS. Microsoft identified and eliminated 45 of the top hardware and software configuration and maintenance functions that required system reboots. Common tasks that no longer require a system restart include configuring new hardware (which Win2K Plug and Play—PnP—handles), changing the pagefile size and location, adding new networking protocols, changing the system's IP address, installing File and Print Services for NetWare (FPNW), and adding disk space to NTFS volumes.

In addition, Microsoft designed Win2K's Windows File Protection (WFP) feature to make Win2K a more reliable platform. WFP can save you from DLL hell by preventing applications from replacing essential system files. WFP protects system files by checking the version of all essential system files when the system executes the Winlogon procedure. If a user modifies or deletes an essential system file, WFP will replace the system file with the cached version from the dllcache directory. If the dllcache directory doesn't contain the file, the system will prompt the user to insert the Windows installation or service pack media.

Another feature that makes Win2K a more reliable platform is its new kernel mode write protection. Kernel mode write protection helps prevent blue screen of death system crashes: The Win2K memory manager provides write protection for code and sets sections of the kernel memory to read-only. This feature prevents misbehaved applications and device drivers from inadvertently writing over crucial system memory locations. A similar kernel mode improvement in Win2K called pool tagging lets device drivers allocate memory from a special memory pool rather than use a shared system memory pool. Pool tagging prevents an error in one device driver from affecting other currently loaded device drivers.

Beyond base system improvements, SQL Server also receives availability benefits from the improved clustering support. Datacenter increases the total number of failover nodes from two to four, giving clustered database implementations even greater availability. Win2K AS and Datacenter clusters further reduce required downtime by providing support for rolling upgrades. Rolling upgrades let you take offline one server node in a cluster for upgrades to application and system software. The remaining cluster nodes continue to provide system availability while you perform the upgrade. When the upgrade is finished, Win2K restores the cluster configuration. You don't need to manually recreate the cluster configuration, and users don't experience unnecessary downtime.

Administrative Tools

Win2K uses the Microsoft Management Console (MMC) as its management interface, so you can use one management console for SQL Server and Win2K. You can add SQL Server Enterprise Manager and OLAP Manager as snap-ins to the MMC for Win2K systems management. Although Microsoft included the MMC in SQL Server 2000 and 7.0, the MMC's use as the Windows management tool is new to Win2K. In addition to using the MMC to run Enterprise Manager and OLAP Manager, you can use the Win2K MMC to run Win2K and SQL Server management tools such as Event Viewer and Performance Monitor.

Windows Management Instrumentation (WMI) support is another Win2K feature that benefits SQL Server administrators. WMI supports the Web-Based Enterprise Management (WBEM) remote management standard. WMI provides a scriptable interface so that administrators can manage devices, drivers, and services for local and remote systems. You can use WMI-based management applications to manage Win2K and any SQL Server services on the system. You can use WMI by employing Windows Script Host (WSH) scripts and Active Server Pages (ASP), ODBC, OLE DB, or Active Directory Service Interfaces (ADSI) program interfaces.

Application Support Improvements

SQL Server database applications will benefit from the improved application support that Win2K provides. Win2K AS and Datacenter include support for Microsoft's COM+ specification, which is the Microsoft runtime environment for supporting transaction-processing applications. COM+ extends the standard Microsoft COM architecture by enabling COM objects to run seamlessly across distributed systems. The distributed system support in COM+ provides a framework for implementing scalable n-tiered applications. COM+ provides the ability to execute queued components for asynchronous processing and enables dynamic load balancing among multiple middle-tier application servers. You can also configure COM+ component services for clustered servers. Support for clustering enables the backup node in a cluster to take over any transaction processing a failed cluster node previously handled.

The improved performance of Microsoft IIS and ASP with Win2K also boosts the performance of Web-based database applications. IIS 5.0 is a component of Win2K and supports scriptless ASP. When pages have .asp extensions but no executable code, scriptless ASP lets the system process them as if they contained static HTML, bypassing standard ASP parsing. In addition, IIS 5.0 supports server-side request redirection. Server-side request redirection lets the system directly transfer new page requests to another .asp file on the server without requiring a round-trip to the Web client. IIS 5.0 also provides improved ASP error handling by giving you the option to capture ASP errors in an .asp file, which you can use to give Web users meaningful error information.

Worth the Wait

Win2K offers significant improvements over NT 4.0 as a database platform for SQL Server. Organizations that adopt Win2K will pay a price in increased processor and memory requirements, but they'll reap commensurate rewards in system reliability, performance, and manageability.

Learn more from "Finding Tools in Windows 2000" and "Tapping In to Windows 2000."