Building a SQL Server Failover Cluster on Windows Server ’08 Pt 1

A few weeks ago I did a demonstration on Installing a SQL Server 2008 R2 Failover Cluster on Windows Server 2008 R2 at TechInsights 2010 in Kuala Lumpur, Malaysia and have had questions about what needs to be done as part of the installation. While I’ve done several of these demonstrations in as short as 45 minutes to an hour, creating a SQL Server Failover Cluster is not something that you do in that timeframe. Planning the server infrastructure, disk allocations, network configuration and bunch of other technologies that relate to Windows Clustering need to be considered.

As I’ve already gone thru building a Windows Server 2008 cluster in this blog post, let’s have a look at installing SQL Server 2008. Whether you’re looking at R2 or non-R2 versions of both Windows Server 2008 and SQL Server 2008, the steps are almost the same except for some PowerShell support for Windows Server 2008 on the Clustering side and whether or not you’re dealing with Hyper-V LiveMigration as part of your cluster (this, however is beyond the scope of this blog post). If you will be installing SQL Server 2008 on an R2 version of Windows Server 2008 – whether on a failover cluster or not – you would have to do a slipstreamed version of the installation. Microsoft KB article 955725 specifically mentions that you need to have at least SQL Server 2008 with Service Pack 1 or later to successfully install it on Windows Server 2008 R2. For more information on creating a slipstreamed version of the SQL Server 2008 media, check out this article.

If you have followed thru the series, I would assume that you have a working Windows Server 2008 Failover Cluster and that you have no issues whatsoever with your existing Windows Cluster. It is strongly recommended to fix issues that get reported in the Windows Failover Cluster Validation Wizard even before proceeding with the creation of the Windows Failover Cluster. This is because SQL Server Failover Clustering is very much dependent on Windows.

Before proceeding with installing SQL Server 2008 Failover Cluster, you may need to install and configure MSDTC on the cluster. If you are using distributed transactions on your applications, this is definitely a must. SQL Server uses the MS DTC service for distributed queries and two-phase commit transactions, as well as for some replication functionality.

To install and configure the MSDTC on the Failover Cluster,

1.Open the Failover Cluster Management console on any of the cluster node.
2.Under the cluster name, right-click on Server and Applications and select Configure a Service or Application. This will run the High Availability Wizard
3.In the Service or Application dialog box, select Distributed Transaction Coordinator (DTC) and click Next.
4.In the Client Access Point dialog box, enter the name and IP address of the clustered MSDTC. This should be a different IP address and host name from the one that the Windows Server 2008 cluster is already using. Click Next.
5.In the Select Storage dialog box, select the disk subsystem that will be used by MSDTC. These disk subsystems have to be predefined as available storage in your cluster. Click Next
6.In the Confirmation dialog box, validate the configuration you have selected for MSDTC and click Next
7.In the Summary dialog box, click Close. This completes the installation of MSDTC on the cluster.

You can validate your installation of MSDTC by expanding the Services and Applications node and check the cluster name of MSDTC. Make sure that all of the dependency resources are online.

Note that Steps # 4 and #5 assume that you already have these components as part of your planning. I see a lot of issues posted on the public Microsoft forums mentioning failure of creating cluster applications mainly because they are using an existing virtual server name or IP address. If you intend to use existing virtual server name and IP address, check with your network engineers for proper IP address allocation as well as your system administrators to check for server names in Active Directory and entries in your DNS server. Another thing is that the cluster disk resources that is assigned for MSDTC (or any cluster-aware applications like SQL Server) need to be pre-allocated even before proceeding with the installation. This is because once the cluster-aware application is up and running, you would need to bring it offline in order for you to add disk resources as dependencies. That, in itself, would require downtime and simply render your high availability implementation useless.

In the second part of this series, we will proceed with installing SQL Server 2008 Failover Cluster