Windows Server 2008 Changes Simplify SQL Server Clustering

I frequently find that SQL Server DBAs pay close attention to the features in each new SQL Server version to determine which features will make their lives easier, but they overlook OS features that can make SQL Server easier to work with. Windows Server 2008 offers some wonderful new functionality for working with SQL Server in a clustered environment and distributed queries or transactional replication with queued updating.

In Windows Server 2003 and earlier there was only one copy of the Distributed Transaction Coordinator (DTC) per server or cluster. The DTC lets SQL Server manage distributed transactions and queued updating subscribers. With only one copy of DTC, multiple applications on a Windows 2003 server or cluster all share that single instance of DTC.

In a cluster, resources belong to a resource group. A group is owned by only one node (server) at any point in time. That node services all requests for that group, such as CPU cycles and memory allocations. Each SQL Server instance on a cluster belongs to its own group. Within that group are things like the SQL Server service, SQL Agent, one or more disks, the virtual SQL Server name, and the SQL Server system's IP address. If there are multiple instances of SQL Server, each must be in its own group with its own set of resources.

In Windows 2003, many people thought they had to put DTC into the same group as a SQL Server instance for SQL Server to be able to use it. Other people thought that DTC had to be owned by the same node as a SQL Server instance. Neither of those things is true, and with multiple instances of SQL Server it's not even possible. Ideally on a Windows 2003 cluster you would put the DTC resource in its own group with its own IP address and disk. However, many companies find it to be impractical to have an entire resource group just for the DTC. The second best choice is to put DTC in the Windows group with the Windows quorum drive. Either way, DTC should be set so that the failure of DTC doesn't cause the whole group to be seen as failed and therefore incur downtime as it's moved to another node. This configuration lets any clustered application in the cluster use that single DTC service without letting the DTC service interfere with any SQL Server instance. By keeping DTC out of any single SQL Server resource group you can move SQL Server groups around without bringing DTC down, which increases your overall uptime.

In Server 2008, you can configure multiple DTC resources in a cluster. Therefore, for SQL Server 2008 or SQL Server 2005 running on Server 2008, Microsoft has a new recommendation for how to configure DTC. To spread out the load of any DTC activity and to allow for maximum flexibility and uptime, you configure one DTC resource per SQL Server instance, with each DTC resource belonging to the same resource group as one instance of SQL Server. You still have to make sure DTC doesn't become a point of failure for SQL Server, so you shouldn't make SQL Server dependent on DTC unless that’s absolutely essential for your environment. You should also make sure that DTC won’t affect the whole group if it fails.

The ability to have multiple DTC resources in Server 2008 can enhance your SQL Server experience, and it's available even if you aren’t using SQL Server 2008 yet. There are other Windows changes, some of which have received a little bit more press, so don’t overlook what your OS can offer you in support of your database system and applications.