sdirectfullpac

Creating a Windows Server 2008 R2 Failover Cluster I hear youyou want your SQL, DHCP, Hyper-V or other services to be highly available for your clients or your internal users. They can be if you create a Windows Failover Cluster and configure those services in the cluster.

• Server 2016 Failover Cluster Requirements
• How To Install Windows Server 2008 On Virtualbox

Oracle has announced support for running MySQL on Windows Server Failover Clustering (WSFC); with so many people developing and deploying MySQL on Windows, this offers a great option to add High Availability to MySQL deployments if you don’t want to go as far as deploying MySQL Cluster.

This post will give a brief overview of how to set things up but for all of the gory details a new white paper MySQL with Windows Server 2008 R2 Failover Clustering is available – please give me any feedback. I will also be presenting on this at a free webinar on Thursday 15th September (please register in advance) as well at an Oracle OpenWorld session in San Francisco on Tuesday 4th October (Tuesday, 01:15 PM, Marriott Marquis – Golden Gate C2) – a good opportunity to get more details and get your questions answered.

It sometimes surprises people just how much MySQL is used on Windows, here are a few of the reasons:

• Lower TCO
  • 90% savings over Microsoft SQL Server
  • If your a little skeptical about this then try it out for yourself with the MySQL TCO Savings Calculator
• Broad platform support
  • No lock-in
  • Windows, Linux, MacOS, Solaris
• Ease of use and administration
  • < 5 mins to download, install & configure
  • MySQL Enterprise Monitor & MySQL WorkBench
• Reliability
  • 24×7 Uptime
  • Field Proven
• Performance and scalability
  • MySQL 5.5 delivered over 500% performance boost on Windows.
• Integration into Windows environment
  • ADO.NET, ODBC & Microsoft Access Integration
  • And now, support for Windows Server Failover Clustering!

Solution Overview

MySQL with Windows Failover Clustering requires at least 2 servers within the cluster together with some shared storage (for example FCAL SAN or iSCSI disks). For redundancy, 2 LANs should be used for the cluster to avoid a single point of failure and typically one would be reserved for the heartbeats between the cluster nodes.

The MySQL binaries and data files are stored in the shared storage and Windows Failover Clustering ensures that at most one of the cluster nodes will access those files at any point in time (hence avoiding file corruptions).

Clients connect to the MySQL service through a Virtual IP Address (VIP) and so in the event of failover they experience a brief loss of connection but otherwise do not need to be aware that the failover has happened other than to handle the failure of any in-flight transactions.

Target Configuration

This post will briefly step through how to set up and use a cluster and this diagrams shows how this is mapped onto physical hardware and network addresses for the lab used later in this post. In this case, iSCSI is used for the shared storage. Note that ideally there would be an extra subnet for the heartbeat connection between ws1 and ws3.

This is only intended to be an overview and the steps have been simplified refer to the white paper for more details on the steps.

Prerequisites

• MySQL 5.5 & InnoDB must be used for the database (note that MyISAM is not crash-safe and so failover may result in a corrupt database)
• Windows Server 2008 R2
• Redundant network connections between nodes and storage
• WSFC cluster validation must pass
• iSCSI or FCAL SAN should be used for the shared storage

Step 1 Configure iSCSI in software (optional)

This post does not attempt to describe how to configure a highly available, secure and performant SAN but in order to implement the subsequent steps shared storage is required and so in this step we look at one way of using software to provide iSCSI targets without any iSCSI/SAN hardware (just using the server’s internal disk). This is a reasonable option to experiment with but probably not what you’d want to deploy with for a HA application. If you already have shared storage set up then you can skip this step and use that instead.

As part of this process you’ll create at least two virtual disks within the iSCSI target; one for the quorum file and one for the MySQL binaries and data files. The quorum file is used by Windows Failover Clustering to avoid “split-brain” behaviour.

Step 2. Ensure Windows Failover Clustering is enabled

To confirm that Windows Failover Clustering is installed on ws1 and ws3, open the “Features” branch in the Server Manager tool and check if “Failover Cluster Manager” is present.

If Failover Clustering is not installed then it is very simple to add it. Select “Features” within the Service Manager and then click on “Add Features” and then select “Failover Clustering” and then “Next”.

Step 3. Install MySQL as a service on both servers

If MySQL is already installed as a service on both ws1 and ws3 then this step can be skipped.

The installation is very straight-forward using the MySQL Windows Installer and selecting the default options is fine.

Within the MySQL installation wizard, sticking with the defaults is fine for this exercise. When you reach the configuration step, check “Create Windows Service”.

The installation and configuration must be performed on both ws1 and ws2, if necessary.

Step 4. Migrate MySQL binaries & data to shared storage

If the MySQL Service is running on either ws1 or ws3 then stop it – open the Task Manager using ctrl-shift-escape, select the “Services” tab and then right-click on the MySQL service and choose “Stop Service”.

As the iSCSI disks were enabled on ws1 you can safely access them in order to copy across the MySQL binaries and data files to the shared disk.

Step 5. Create Windows Failover Cluster

From the Server Manager on either ws1 or ws3 navigate to “Features -> Failover Cluster Manager” and then select “Validate a Configuration”. When prompted enter ws1 as one name and then ws3 as the other.

In the “Testing Options” select “Run all tests” and continue. If the tests report any errors then these should be fixed before continuing.

Now that the system has been verified, select “Create a Cluster” and provide the same server names as used in the validation step. In this example, “MySQL” is provided as the “Cluster Name” and then the wizard goes on to create the cluster.

Step 6. Create Cluster of MySQL Servers within Windows Cluster

Adding the MySQL service to the new Cluster is very straight-forward. Right-click on “Services and applications” in the Server Manager tree and select “Configure a Service or Application…”. When requested by the subsequent wizard, select “Generic Service” from the list and then “MySQL” from the offered list of services. Our example name was “ClusteredMySQL”. Please choose an appropriate name for your cluster. The wizard will then offer the shared disk that has not already been established as the quorum disk for use with the Clustered service – make sure that it is selected.

Once the wizard finishes, it starts up the MySQL Service. Click on the “ClusteredMySQL” service branch to observe that the service is up and running. You should also make a note of the Virtual IP (VIP) assigned, in this case 192.168.2.18.

Step 7. Test the cluster

As described in Step 6, the VIP should be used to connect to the clustered MySQL service:

From there create a database and populate some data.

The MySQL service was initially created on ws1 but it can be forced to migrate to ws3 by right-clicking on the service and selecting “Move this service or application to another node”.

As the MySQL data is held in the shared storage (which has also been migrated to ws3), it is still available and can still be accessed through the existing mysql client which is connected to the VIP: Eminem album torrent.

Note the error shown above – the mysql client loses the connection to the MySQL service as part of the migration and so it automatically reconnects and complete the query. Any application using MySQL with Windows Failover Cluster should also expect to have to cope with these “glitches” in the connection.

Conclusion

More users develop and deploy and MySQL on Windows than any other single platform. Enhancements in MySQL 5.5 increased performance by over 5x compared to previous MySQL releases. With certification for Windows Server Failover Clustering, MySQL can now be deployed to support business critical workloads demanding high availability, enabling organizations to better meet demanding service levels while also reducing TCO and eliminating single vendor lock-in.
Please let me know how you get on by leaving comments on this post.

3,486 total views, 10 views today

There are many ways to implement Windows Server Failover Clustering with Hyper-V. I could actually find five unique methods to do it. Some of them will actually not give you a fully fault-tolerant solution, but most of them actually make sense in specific scenarios (even if only for demonstrations). In any case, just trying to understand and differentiate them will probably be a good exercise.

1 – Parent-based Failover Clustering with two physical servers

In this first scenario, probably the most common one, you implement Windows Server 2008 Failover Clustering at the Hyper-V Parent (Host) level. You will need some shared storage, like a Fibre-Channel or iSCSI SAN.

Here is a diagram describing the scenario before a failure:

Here is a diagram describing the scenario after a failure:

As you can see, this can survive the failure of one of the physical servers. In fact, if you have a redundant network and storage infrastructure (not shown above), you can have a truly highly available solution.

Additional details about this solution (including screenshots on how to configure it) are available at http://blogs.technet.com/josebda/archive/2008/04/14/snw-demo-windows-server-2008-core-hyper-v-and-failover-clustering-with-screenshots.aspx

2 – Child-based Failover Clustering with two physical servers

In this second scenario, you implement Windows Server 2008 Failover Clustering at the Hyper-V Child (Guest) level. In this case, your shared storage must be an iSCSI SAN.

Here is a diagram describing the scenario before a failure:

Here is a diagram describing the scenario after a failure:

This one can also survive the failure of one of the physical servers. Given redundant network and storage infrastructure (not shown above), you can again have a truly highly available solution.

3 – Mixed Physical/Virtual Failover Clustering

This third scenario is probably is one of the more unusual ones, but I have been asked about it at least a couple of times. Here you have a physical server clustered with a virtual one. If the physical server fails, the virtual sibling will take over the workload. This scenario uses dissimilar hardware with Failover Clustering, but if this is running Windows Server 2008, you can likely make it work. Just make sure you run the Failover Clustering Validation Wizard to confirm this is supported in your specific configuration. In this case, because you need to expose the LUNs directly to the child partition, your shared storage must again be an iSCSI SAN.

Here is a diagram describing the scenario before a failure:

Here is a diagram describing the scenario after a failure:

This can also survive the failure of one of the physical servers. If you configure the network and storage infrastructure to be fault tolerant (not shown above), you can have yet another truly highly available solution.

4 – Failover Clustering with two child partitions on one physical server

This scenario is also common. Here you have a single physical server running Hyper-V and two child partitions where you run Failover Clustering. If the physical server fails, both (virtual) cluster nodes will fail. Obviously, this is not useful for true high availability, but could be interesting for testing, training or demonstrations. In this case, your shared storage must be an iSCSI SAN.

Here is a diagram describing the scenario before a failure:

Here is a diagram describing the scenario after a failure:

This scenario cannot be made truly highly available even if your network and iSCSI SAN are redundant, since you have the physical server running Hyper-V as a single point of failure. The simulated failure can be achieved by turning off one of the child partitions in Hyper-V.

5 – Standalone demo laptop with Virtual iSCSI SAN

This last scenario is something I also get asked a lot. The goal here is to have a single laptop hosting an entire Failover Clustering demo with Hyper-V. In order to accomplish this, you need a virtual iSCSI SAN plus two child partitions to play the role of cluster nodes. To be the virtual iSCSI SAN, you can use an evaluation version of the Microsoft iSCSI Software Target described at http://blogs.technet.com/josebda/archive/2008/01/07/installing-the-evaluation-version-of-wudss-2003-refresh-and-the-microsoft-iscsi-software-target-version-3-1-on-a-vm.aspx. This is certainly not a true highly available solution, but it can be an interesting demo machine with no external network dependencies.

Here is a diagram describing the scenario before a failure:

Here is a diagram describing the scenario after a failure:

As with the previous scenario, this one cannot be made truly highly available, for obvious reasons. In fact, this one only really makes sense for demonstrations or training. The simulated failure can once again be achieved by turning off one of the child partitions in Hyper-V.

Conclusion

I hope this helped you understand the different options for using Failover Clustering with Hyper-V. Note also that you can combine some of these solutions, like the first and the second (some VMs using parent-based and some using child-based failover clustering).

For production use, it’s probably wise to restrict yourself to the first two scenarios. However, if you have a Hyper-V capable laptop and some free time, I would encourage you to try out the last one. Although not a supported production solution, it will certainly teach you a lot about all the technologies involved…

Links

Additional information about Windows Server 2008 Failover Clustering support
http://support.microsoft.com?id=943984

Failover Clustering support in previous versions of Windows Server
http://support.microsoft.com/?id=309395.

Server 2016 Failover Cluster Requirements

Step-by-Step Guide to Getting Started with Hyper-V
http://www.microsoft.com/downloads/details.aspx?FamilyID=bcaa9707-0228-4860-b088-dd261ca0c80d&DisplayLang=en

Step-by-Step Guide for Testing Hyper-V and Failover Clustering
http://www.microsoft.com/downloads/details.aspx?FamilyId=CD828712-8D1E-45D1-A290-7EDADF1E4E9C&displaylang=en

Failover Cluster Step-by-Step Guide: Configuring a Two-Node File Server Failover Cluster
http://technet2.microsoft.com/windowsserver2008/en/library/adbf1eb3-a225-4344-9086-115a9389a2691033.mspx

How To Install Windows Server 2008 On Virtualbox

Additional details on Storage Options for Hyper-V
http://blogs.technet.com/josebda/archive/2008/02/14/storage-options-for-windows-server-2008-s-hyper-v.aspx
http://blogs.technet.com/josebda/archive/2008/03/06/more-on-storage-options-for-windows-server-2008-s-hyper-v.aspx.