Configuring a Multi-Subnet SQL Server Test Environment

Have you ever wanted to play with a multi-subnet SQL Server configuration for Availability Groups or Failover Clustering with SQL Server 2012 or higher? Getting an environment setup to support multiple subnets is not trivial, and a lot of times if you look online for information about how to configure routing for multiple subnets you will find a recommendation to use a Linux based VM separately as a router/appliance to handle the routing.  In this blog post I’m going to show you how to setup a virtual environment with a single Windows Server 2012 R2 server that performs the role of Active Directory Domain Controller, DNS server, DHCP host, and as a router for multiple subnets as the basis for a multi-subnet virtual environment for testing and playing with multi-subnet configurations for SQL Server 2012 and higher.  I am building this environment in VMware Workstation 10, but the same configuration can be performed under any hypervisor that supports multiple virtual network configurations, once the host configuration of the virtual networks has been appropriately configured.  VMware Workstation makes this configuration very easy through the Virtual Network editor that is included as a part of the VMware Workstation implementation.

Initial VM Configuration

For the initial VM setup, I created a VM with 2 vCPUs and 2GB RAM and then edited the settings to change the virtual network adapter settings to add two new network adapters to the VM.  The default setup includes one virtual network adapter for NAT, so I am going to add one network adapter on VMNet2, then add another adapter on VMNet3 as shown below.

imageimage

The final hardware configuration for the VM should then look like this:

image

Now we can install Windows Server 2012 R2 and setup our basic server configuration.  In this case I’ve changed the name of the server to be DC, and set static IP’s to each of the network adapters, 192.168.52.1 for VMNet2, and 192.168.53.1 to VMNet3.

imageimage

To properly identify each adapter, you can edit the VM settings and disconnect them one at a time so it shows Network cable unplugged for the network connection.  I usually set the NAT adapter to disconnected unless there is a need to have the VM connected to the internet through the host internet connection for some reason.  Once this has been done we can proceed to setup the server as our Active Directory Domain Controller, DNS server, DHCP host for the networks, and as a Router between the two subnets.

Configuring Active Directory Domain Services

To setup Active Directory Domain Services, open the Add Roles and Features Wizard, and then check Active Directory Domain Services as a role for the server.  It will open a popup window shown below to add additional features required by Active Directory Domain Services.  Click the Add Features button then click Next twice followed by the Install button.

image

When the installation finishes, don’t click the Close button.  Instead click on the link that says Promote this server to a domain controller to launch the Active Directory Domain Services Configuration Wizard.

image

Click Close

image

Click the Add a new forest radio button, then add the Domain Name and click Next

image

Set the domain functional level and then specify the recovery password and click next

image

Click Next

image

Click Next

image

Click Next and wait for the prerequisites checks to complete

image

Click Install and it will finalize the configuration of the Active Directory Domain Services on the server making it a new Domain Controller in the environment.  When it finishes Windows will prompt to sign you out as shown below, and then the VM will reboot.

image

Configuring DHCP

Configuring DHCP is not required, but it simplifies creating all the remaining VMs by automatically configuring their network settings based on the VMNet that they have been assigned on the virtual network adapters. Open the Add Roles and Features Wizard and add the DHCP Server role to the machine.

image

A popup will open with the additional features required and you can click Add Features.

image

Then click Next, followed by Install and when the installation finishes you can click the Complete DHCP Configuration.  If you mistakenly click Close, you can still complete the configuration by opening Server Manager and then clicking on Manageability under the DHCP box.

image

This will open up the Manageability Detail View shown below.

image

Click Go To DHCP.

image

Click the More link at the top in the yellow bar to the far right

image

Click the Complete DHCP configuration link.

image

Click Next

image

Click Commit

image

This authorizes the server to be a DHCP host, so now we need to customize the configuration of DHCP. Click the Start button then type in DHCP and open the DHCP console.

image

Right click on the IPv4 and choose New Scope then click Next.

image

image

I name the scopes by the Subnet they apply to so in this case 192.168.52.x and once we complete this scope a second will have to be made for the 192.168.53.x subnet by simply repeating these same steps and using that subnet for the gateway and DNS server addresses.

image

Set the starting and ending IP address range for the scope and click Next. Then click Next on the Add Exclusions and Delay and Lease Duration windows.

image

On the Configure DHCP Options page, make sure it says Yes, I want to configure these options now and click Next.

image

Add the local IP address for this server as the Router address, in this case 192.168.52.1 and click Add, then click Next. (For the second scope you will use the other IP address, in this case the 192.168.53.1 address)

image

If the IP Address for the DNS Server is not the local address for the subnet, remove any existing IP addresses, and add the local IP address for the subnet, in this case 192.168.52.1 as the DNS server and then click Next. (Again, for the second scope you will use the other IP address, in this case the 192.168.53.1 address)

image

Remove any WINS server IP Addresses and click Next.

image

Then click Next to activate the scope.  Now repeat these same steps for the 192.168.53.x subnet specifying the 192.168.53.1 address for the router and DNS server and activate that scope.

Configuring Routing Between Subnets

To setup routing between the subnets, open the Add Roles and Features Wizard, and then check Remote Access as a role for the server.

image

Click Next three times and then check the Routing box.  It will open a popup window shown below to add additional features required for Routing.  Click the Add Features button.

image

This will also cause the DirectAccess and VPN (RAS) option to become checked as shown below.

image

Click Next twice and it will have the addition IIS role services window shown below with the required features checked.  Nothing needs to be changed here, just click Next again, followed by Install to install the Remote Access Role to the server.

image

When the installation completes, click Close.  Now click on Start and type in Routing and Remote Access and open the Routing and Remote Access management console.

image

Right click on the server and select the Configure and Enable Routing and Remote Access option.

image

Click Next on the first window then select the Custom Configuration option and click Next.

image

Check the box for LAN routing and click Next.  Then click Finish.

image

Now click the Start service button to start the Routing and Remote Access services.

image

 

EDIT: After initially publishing this blog post, Thomas Stringer (Blog | Twitter) pointed out that static routes don’t have to be configured and RIP can be used to simplify the configuration of routing.  So I am updating this post with the RIP configuration as an additional section. Either option will work but I have to admit that RIP is easier to configure. You only need to configure RIP or the Static Routes, don’t configure both options. 

Routing with Routing Information Protocol (RIP)

  1. Expand the IPv4 folder and right click on General and choose New Routing Protocol.
  2. Select the RIP Version 2 for Internet Protocol option and click OK.
  3. Now right click on RIP under IPv4 and choose New Interface.
  4. Select the 192.168.52.1 interface and click OK.
  5. Then click OK again.
  6. Now right click on RIP under IPv4 and choose New Interface.
  7. Select the 192.168.53.1 interface and click OK.
  8. Then click OK again.
  9. Now you are finished.

Routing with Static Routes

Expand the IPv4 folder and right click on Static Routes and choose the New Static Route option.

image

Select the 192.168.52.1 interface and then add a Destination of 192.168.53.0, subnet mask of 255.255.255.0 and gateway of 192.168.53.1 to create a static route between the subnets.

image

Now create a New Static route again.

image

This time, change to the 192.168.53.1 interface and set the Destination to 192.168.52.0, network mask to 255.255.255.0, and the Gateway to 192.168.52.1 and click OK to add the static route between the subnets.

image

Restart the Routing and Remote Access Services and you’re all set.

Testing

To test the configuration, I created two new VMs named SQL1 and SQL2 with a single network adapter each.  SQL1 was configured on VMNet2 and SQL2 was configured on VMNet3, then joined both of the servers to the SQLskillsDomain.com domain.  The Windows Firewall needs to be configured to allow ICMP echo packets in the Inbound Rules on each of the servers by enabling the File and Printer Sharing (Echo Request –ICMPv4-In) rule that already exists in the Inbound Rules of the firewall. Once this is enabled on each of the servers, we can test with ping between the VMs as shown below:

SQL1 to SQL2

image

SQL2 to SQL1

image

These same steps can be performed in Windows Server 2008R2 and Windows Server 2012, with only minor differences in the configuration of the roles and features.  The Routing and Remote Access configuration to provide the routing between the subnets is nearly identical on all three versions of Windows.  Once the server is configured, the environment can be used to create a multi-subnet Windows Server Failover Cluster for a multi-subnet Availability Group or even a multi-subnet Failover Clustered instance.

Clustering SQL Server on Virtual Machines (Round 2)

Recently there was lengthy discussion on the #sqlhelp hash tag on Twitter about clustering SQL Server on VMs and whether or not that was a good idea or not. Two years ago I first blogged about this same topic on my blog post, Some Thoughts on Clustering SQL Server Virtual Machines. If you haven’t read that post, I recommend reading it before continuing with this one because it gives a lot of background that I won’t be rehashing as a part of this post. However, a lot has changed in VMware and Hyper-V since I wrote that original post and those changes really affect the recommendations that I would make today.

As I stated in the twitter discussion, we have a number of clients at SQLskills that run WSFC clusters across VMs for SQL Server HA and few have problems with the clusters related to them being VMs. There are some additional considerations that have to be made when working with VMs for the cluster nodes, a big one is that you should plan for those nodes to be on different hosts so that a hardware failure of the host doesn’t take out both of your cluster nodes, which defeats the purpose of having a SQL cluster entirely. There is also the additional layer of the hypervisor and resource management that plays into having a cluster on VMs but with proper planning and management of the VM infrastructure this won’t be a problem, it’s just another layer you have to consider if you do happen to have a problem.

In response to the discussion, Chuck Boyce Jr (Blog|Twitter) wrote a blog post that provided his opinion, which was not to do it, that started a separate discussion later on Twitter. The biggest problem Chuck points out is rooted in problems with inter-team communications within an IT shop. To be honest, Chuck’s point is not an incorrect one, I see this issue all the time, but it’s not specific to VMs. If you work in a shop that has problems with communication between DBAs, Windows administrators, VM administrators, the networking team, and any other IT resource in the business, the simple fact is those problems can be just as bad for a physical implementation of a SQL cluster as they might be for a VM implementation. The solution is to fix the communication problem and find ways to make the “team” cooperate better when problems arise, not avoid merging technologies in the hopes of preventing problems that will still occur in a physical implementation as well.

Am I saying that clustering VMs for SQL Server is for every shop? No, certainly not. There are plenty of places where clustering isn’t the best solution overall. However, with virtualization, depending on the infrastructure, the other SQL HA options that exist might not be a better decision as they would in a physical world either. One of the biggest things to think about is where are the VMs ultimately going to be stored? If the answer is a shared SAN then options like Database Mirroring and Log Shipping don’t really provide you with the same advantages that they do in physical implementation, the big one being that you have a second copy of the database on completely different storage generally. Yes I know that you could have two SQL Servers connected to the same SAN physically that use Database Mirroring, and my response to that would be that a cluster probably would make more sense because the SAN is your single point of failure in either configuration.

If you are new to clustering SQL Server, I wouldn’t recommend that you start out with VMs for your failover cluster. The odds are that you also don’t have a lot of VM experience and if there is a problem, you aren’t going to be able to troubleshoot it as effectively because you have two new technologies that you have to try and dig through. If you are comfortable with clustering SQL Server, adding virtualization as a new technology to the mix is really not that big of a deal, you just need to read the configuration guides and whitepapers for how to setup the VMs, usually your VM administrator is going to have to do this so it’s a good area to break the ice with them and work together to start the open lines of communication, to allow for a supported WSFC implementation and then finally install SQL Server and manage it like you would any other SQL Server failover cluster.

Where else would I recommend not implementing a cluster on VMs? iSCSI SANs that only offer 1Gb/s connectivity over Ethernet, simply because you are likely to run into I/O limitations quickly, and to build the cluster you have to use the software initiator for iSCSI so there is a CPU overhead associated with the configuration. Generally the host has limited ports so you end up sharing the networking between normal traffic and the iSCSI traffic which can be problematic as well. Does that mean it’s not possible? No – I have a number of clients that have these setups and they work fine for their workloads, but it’s not a configuration I would recommend if we were planning a new setup from the ground up.

The big thing that I work through with clients when they are considering whether to cluster VMs for SQL Server is the business requirements for availability and whether or not those can be met without having to leverage one of the SQL HA options or not. With the changes in VMware ESX 5 and Hyper-V 2012, you can scale VMs considerably, and both platforms allow for virtualized NUMA configurations inside of the guest VM for scalability, so the performance and sizing considerations I had two years ago are no longer primary concerns for the implementation to me. If we need 16 vCPUs and 64GB RAM for the nodes, with the correct host configuration, we can easily do that, and we can do it without performance problems while using Standard Edition licensing if we plan the infrastructure correctly.

In my previous post on this topic I linked to a number of VMware papers, and in the post prior to that one I linked to even more papers that include best practice considerations for configuration and sizing of the VMs, how to configure the VMs for clustering, and many other topics. Newer versions of these documents exist for ESX 5 and a number also exist for Hyper-V as well. I recommend that anyone looking at running SQL in a VM, whether as a clustered instance or not, spend some time reading through the papers about the hypervisor you want to run the VM on so you understand how it works, the best practices for running SQL Server on that hypervisor, and what to look for while troubleshooting problems should they occur.

In the end, Microsoft supports SQL Server failover clustering on SVVP certified hypervisors, so there isn’t a hard reason to not consider using VMs objectively to evaluate whether they might be an appropriate fit your business requirements.  When I teach about virtualization in our IE3: High Availability & Disaster Recovery class, most of the perceptions at the start of the virtualization module are negative towards SQL Server on VMs, often from past experiences of failed implementations.  By the end of the demos for the module, most of the opinions have changed, and in a lot of cases attendees have found and been able to communicate correctly with their VM administrator to get the problem fixed while I am performing demos of specific problems and their causes.  In the last year I’ve setup a number of SQL Server clusters on VMs for clients where it was the best fit for their needs.  If you would like assistance with reviewing the infrastructure, business requirements, and determining the best configuration for your needs, I’d be happy to work with you as well. 

New features of ALTER SERVER CONFIGURATION in SQL Server 2012

While working on a chapter I am writing, I was going through the ALTER SERVER CONFIGURATION topic in the Books Online and noticed some really useful changes in the syntax that I hadn’t seen before.  Starting in SQL Server 2008 R2, ALTER SERVER CONFIGURATION was introduced to allow for process affinity configuration and to replace the affinity mask sp_configure options for SQL Server, which were marked as deprecated at the same time.  SQL Server 2012 extends the usage to also control diagnostic logging and failover clustering properties as well.

Diagnostic logging in SQL Server 2012 is accomplished through the execution of sp_server_diagnostics, which Joe blogged about last year here and again here.  It can be turned ON or OFF using ALTER SERVER CONFIGURATION, and you can also specify the PATH, MAX_SIZE, and MAX_FILES options to control the logging location, file size, and maximum number of rollover logs to be created and maintained by SQL Server.

— Disable Diagnostics logging
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;

— Enable Diagnostics logging
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;

— Configure logging path
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG PATH=N'C:\SQLskills\Logs';

— Configure max file sizes
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG MAX_SIZE=100 MB;

— Configure max number of files to keep
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG MAX_FILES=5;

The flexible failover policy in SQL Server 2012 allows for more fine grained control of when a SQL Server Failover Cluster actually performs a failover of the instance.  This is accomplished through the execution of sp_server_diagnostics and is based on the output of the health checks being performed by the procedure.  The FailureConditionLevel can be set by ALTER SERVER CONFIGURATION, along with the HealthCheckTimeout value to control when a instance performs a failover or restart associated with an unhealthy condition.  You can also set the logging level for the Failover Cluster, and a number of SQLDumper options as well.

— Set failover on any qualified failure condition
ALTER SERVER CONFIGURATION SET FAILOVER CLUSTER PROPERTY FailureConditionLevel = 5;

— Set the health check timeout to 120000 milliseconds (2 minutes)
ALTER SERVER CONFIGURATION SET FAILOVER CLUSTER PROPERTY HealthCheckTimeout = 120000;