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.
Assuming the following network configuration on the local nodes, there are 3 networks on this cluster:
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.
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:
The key to this is actually that the SQL Server resource in the cluster is now dependent on the 2 VIPs:
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:
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:
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.