This part of the review is still a part of the topic Maintain Instances and Databases which accounts for 17% of the examination.
The objective here may include, installing a cluster, manage multiple instances on a cluster, setup a subnet clustering and recover from a failed over cluster node.
AlwaysOn Availability Groups are new technology introduced in SQL Server 2012 that also rely on failover clustering technologies. AlwaysOn Availability Groups are replacement for the database mirroring feature and have the benefit of allowing clients read-only access to the secondary replica. In failover clustering technology, if the server that host the database engine instance fails, another database engine within the failover cluster takes control of the database files and seamlessly continues to service client requests.
You can deploy failover cluster instances only on specific editions of the host operating system and SQL Server edition. During the planning stage, keep in mind that SQL Server 2012 Enterprise edition supports up to 16 cluster nodes. This is the only edition of SQL Server 2012 that you can deploy in production that can support multi-subnet failover clustering. You must have a functional Windows Server failover cluster prior to deploying SQL Server as a failover cluster instance. Because SQL Server failover cluster is built and will run on top of Windows Server failover cluster.
To setup the failover cluster instance we need to setup our data files and log files in a shared storage or a file server within the subnet system. If you don’t have access to a SAN setup and you want to setup a training VM for you to practice, you can use the Windows Storage Server 2008 R2 as an iSCSI target. You can download the Windows iSCSI target software here. You can use this software to simulate an iSCSI storage device on a SAN when running virtual machines within Hyper-V environment.
I have a similar setup in my VM and it’s very easy setup and mount actually. Then you can setup all your cluster nodes to use this as shared storage for your data files and log files. This part is not actually covered in the certification exam but is important to set this up when we want to play around with setting up a failover cluster.
To launch iSCSI Initiator properties, go to your Administrative Tools and click on iSCSI Initiator, if your iSCSI service is not yet running, it will prompt you that it will start the service. On the Target tab, enter the IP address or the DNS name of your setup. Then for the Volumes and Devices tab, click on Auto Configure button to automatically add the drives in your previous setup for iSCSI Targets.
Starting to Create a Cluster in Windows Server 2008 R2
First, we need to setup a Failover Cluster feature in Windows Server 2008 R2. From the server manager, click on Features > Add Features to launch the Add Features Wizard. You will need to do this for every node that you want to add to the cluster.
After the Failover Cluster Feature is successful, open the Failover Cluster Manager from the Server Manager console and click on Create a Cluster link. This will launch the Create a Cluster Wizard that will guide you through all the required steps. See my screenshot below.
On the Select Servers screen, enter all the node names that you want included in the cluster. Also you need to prepare a cluster name and cluster IP for your cluster. You will enter this information in the Access Point for Administering Cluster page in the wizard. If the Create Cluster wizard is successful, this will display all the nodes you have entered in the wizard.
Install SQL Server Failover Cluster
After we have completed setting the Failover Cluster feature in the host operating system (you need to this in all your nodes participating in the cluster), we can now proceed with installing the SQL Failover Cluster instance.
To launch the installation wizard, run the setup.exe from your installer media. Go to Installation side menu on the left and click on link ‘New SQL Server failover cluster installation’. See screenshot below;
Enter the information needed to complete the installation wizard and finally click Install to complete the installation process. What is important here during your failover cluster installation is the Cluster Resource Group name. The failover cluster node will use this name as the name for your cluster group. The Name column is just telling you that you cannot use these names for your cluster group name as this are reserved words.
For the Cluster Disk Selection, you should be able to see here the disks that you brought online during our setup of iSCSI resources. Just tick the checkboxes for all the disks you initially setup.
Another important thing during the installation is the Cluster Network Configuration. See screen shot below. You need to enter the IP address that your cluster group will use. I say this is the time to be most friendly with your sysadmin to ask for free IP that you can use 🙂
For the server configuration, see screenshot below, notice that most of the services startup type are already greyed out. All you need to do here is enter the service accounts and the passwords. For best practice, i suggest that you create a domain service accounts for sole use of this instance.
After all verification and installation is completed, viola! Then your SQL Server failover cluster is done in your first node.
On the succeeding nodes that you want to add to the cluster, from the installation screenshot, click on link ‘Add node to a SQL Server failover cluster’ instead.
Failover Cluster Manager
You can verify all your SQL Failover Cluster nodes in the Failover Cluster Manager. You can test or simulate a failover by going to your SQL Server under the Services and Applications menu, right click, go to Move this service to another node, Move to node <AnotherNodeNameHere>. Then you will see that the cluster owner is changed to another node name within your cluster. At this point when you open you SQL Server, you should still be able to do some database actions because in reality, what is happening in the failover cluster is not visible to you or your users.
Failover Cluster is a lot of work during setup and installation but it is really cool once you get it going and you get to be the rockstar in your organization. I had difficulty to get this going at first when I encountered problems in SQL Agent in one our nodes. But I did a lot of research, and a lot of test and after a lot of coffee and I was able to get it running.
If you need to work on one of your nodes, like server updates or additional hardware resources, you need to kick them out of the cluster, still via the Failover Cluster Manager. Just go to your node that’s giving your problems or you need to get fixed, right click, go to More Actions and select Evict. This will evict that node from the cluster. If you need to add the node again, after getting it fixed, you need to go thru the SQL Server installation wizard again and select link ‘Add node to a SQL failover cluster’.
You can also practice setting up failover clusters and SQL Server failover cluster in TechNet Virtual Labs https://cmg.vlabcenter.com/lab/6f5ac2a3-33cc-4431-b43f-e6cce61d1f8e.
Featured image courtesy of grover_net.