70-457 Reviewer #08, Implementing Clustered Instance in SQL Server 2012

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.

Shared Storage

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.

Failover Cluster Manager

Add Features Wizard

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.

Create Cluster Wizard

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.

Create Cluster Wizard Summary

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;

New Failover Cluster Install

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.

Cluster Resource Group

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.

Cluster Disk Selection

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 🙂

Cluster Network Configuration

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.

Server Services Configuration

After all verification and installation is completed, viola! Then your SQL Server failover cluster is done in your first node.

Failover Cluster Node Complete

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 Manager Completed

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.


4 responses to “70-457 Reviewer #08, Implementing Clustered Instance in SQL Server 2012

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: