Recently a colleague asked me if there were any changes specific to Windows Server 2008 which allow SQL Server (clustered instance) to listen on multiple subnets. There was only one change in Windows 2008 to my knowledge (which I have detailed in this post later on) but that had nothing specific to do with SQL listening on different subnets. I did some testing and have posted the results below.

Network configuration

Assuming the following network configuration on the local nodes, there are 3 networks on this cluster:

  • Cluster_Public: subnet is 255.255.255.0 (used only for mixed communications in the cluster)
  • Second_Public: subnet is 255.255.0.0 (used only for public communications in the cluster)
  • Cluster_Private: subnet is 255.0.0.0 (used only for private communications in the cluster)

clip_image001

SQL Server 2000

This version had the capability to bind to multiple virtual IP addresses. When setting up SQL Server, on the following page, you can specify both the IP addresses. The same page can also be subsequently accessed by re-running the setup program and selecting the Advanced option to Maintain Failover Clustering.

clip_image002

Assuming that setup succeeds, you can check the SQL Server error log (shown below) and verify that the SQL instance is successfully listening on both virtual IPs:

clip_image003

The key to this is actually that the SQL Server resource in the cluster is now dependent on the 2 VIPs:

clip_image004

However, due to a limitation in SQL 2000, we cannot directly do this in the cluster administrator, you always have to use the setup program to do the same. As we will see later on the limitation no longer exists in SQL 2005 or 2008.

SQL Server 2005 / 2008

Things are different and easier in these versions. One option is at setup time, if you want to configure SQL Server to listen on multiple virtual IPs, you need to specify them in the setup program:

clip_image005

However, later on if you want to maintain the cluster and ADD a new virtual IP, all you need to do to have SQL listen on multiple Virtual IP addresses is to follow the steps below:

  • Take the SQL Server network name offline
  • Add an additional  IP resource with the new (additional) VIP for SQL to listen on
  • Add the new IP resource as an additional dependency for the sql network name resource
  • Bring the SQL Server resource online, the errorlog should show sql listening on the 2 IPs:

clip_image006

Windows 2008 cluster, multiple subnets, 'OR' dependencies and SQL Server

So, with all this background, what has changed in Windows 2008? Well, it is a question of dependencies and how they affect the dependent resource (in this case SQL Server.) By default, in Windows 2000 and 2003, the failure of any one of multiple virtual IP address resource dependencies for SQL Server, will cause the SQL Server resource to restart. This is because the dependencies by default work in a 'AND' mode.

In Windows 2008, there is now support for each node to be on a different subnet (see this MVP article for a nice explanation) due to which support for an OR dependency has been introduced. However, for SQL Server to co-exist in this environment, both the subnets must be up for SQL Server to function correctly. I will cover this topic (SQL 2005 / 2008 on Windows Server 2008 with nodes in different subnets) in a later post.