In my previous blogpost I had the opportunity to share my thoughts about setting up a Microsoft SQL 2012 Server AlwaysOn Availability group. I will continue with explaining the steps that you need to perform in order to have your first AlwaysOn Availability group up and running in a matter of 45 minutes. It took myself 2 hours of reading and troubleshooting, before I had a working AlwaysOn Availability group.
Our ultimate goal is to see the wizard complete successfully.
I assume that you have a working Active Directory infrastructure, before proceeding. Without an Active Directory in place, it will be almost impossible to setup the Windows Server Failover Cluster environment.
Part One – Networking
All the Microsoft SQL 2012 Enterprise servers that are going to be participating in the AlwaysOn group, must reside in the same subnet as by Microsoft recommendations. As long as every server can ping each other , you will be fine and the AlwaysOn group will work.
You don’t need a special “clustering network or vlan” in your networking infrastructure.
Part two – Install Windows Server Failover Cluster role
I will provide a step-by-step procedure on how to setup the Windows Server Failover Cluster Role.
1) On the central Server Manager Dashboard, you click the Manage button and select “Add Roles and Features’’
2) You will be presented with this screen
Select: Role-based or feature-based installation and press “Next”
3) You have to select the destination server that will host the the Windows Server Failover Cluster Role
After selecting the first server, continue to the next screen by pressing “Next”
4) Select the Application Server role and press “Next”
5) Put a checkmark on the Failover Clustering Feature. Extra Roles and Features will be installed (click on Add Features). After that you can continue by clicking on the next button.
Windows 2012 will proceed to install the required binaries on the first server. It will take around 5 minutes to complete.
Remember: Each server that will be part of the Microsoft SQL Server AlwaysOn group must have the Windows Server Failover Cluster Role installed.
E.g: If you are planning to have two Microsoft SQL 2012 Servers with the AlwaysOn feature, you have to repeat the procedure above twice.
There you go. You have installed the Windows Server Failover Cluster role successfully.
Part three – Configuring the Windows Server Failover Cluster
To configure the Windows Server Failover Cluster, I want to redirect you to this amazing blogpost, that helped me a lot during my own installation of the Microsoft SQL Server 2012 AlwaysOn group.
The blogpost above describes vividly how to setup an AlwaysOn Availability group.
What I will do in this part is explain, some pitfalls that I encountered myself. It will save you, the reader, a lot of troubleshooting and headaches. I will also
I begin with the most important technet article that you need to read thoroughly:
My personal tip for you: Make sure that the Cluster computer object, is allowed to create computer objects. Without this simple checkmark
You will not succeed in creating your first AlwaysOn Availability group. The validation of the AlwaysOn Availability group will fail.
I will provide some screenshots on how to configure the computer object that represents the Windows Server Failover Cluster.
First I start with my Windows Server Failover Cluster Setup. The screenshots are in their correct order of execution (left to right, each row).
I have shown in 7 steps how the setup process is executed. The following computer objects can be seen in the Active Directory User and Computers management console:
Take notice of the computer objects. I will explain them now for your better understanding:
– WSFC1 is the first virtual machine that is hosting the Windows Server Failover Cluster role. In my previous blogpost, I have installed Microsoft SQL 2012 Server enterprise edition on this virtual machine.
– WSFC2 is the second virtual machine that is hosting the Windows Server Failover Cluster role. In my previous blogpost, I have installed Microsoft SQL 2012 Server enterprise edition on this virtual machine.
– SPCLUSTER is a virtual network name for our AlwaysOn Availability group and this computer object is a special one that needs some security editing.
If you forget to give the Failover Cluster computer object (e.g.: SPCLUSTER in my case) the needed security rights, you will not be able to successfully complete the AlwaysOn Availability group wizard. The wizard will abort all operations. I am saving you the headache and troubleshooting despair
Part four – Setting up Active Directory computer object rights
Let’s continue with the most important change that you have to do in your Active Directory environment. You will be giving the Windows Server Failover computer object, one special permission (Create computer objects).
1) Open the Active Directory User and Computers management console
Right click on the “Computers’’ container and select “Properties”. The following window will appear:
Go to the “Security” tab and select “Advanced”
2) Click on “Add’’ and the following window will appear
You will be selecting and providing the following information:
Principal: The failover cluster computer object. In my case: SPCluster
Applies to: This object and all descendant object
In my case it happens to be this
Take notice of the high lightened Permission. I have selected “Create Computer objects”. This is the only permission that you need to give the failover cluster computer object.
When you are done, you can click on “OK” and then on “Apply” to close everything.
This concludes applying the correct permissions for the failover cluster computer object. I will now proceed activating the AlwaysOn Availability option in Microsoft SQL 2012 Server Enterprise Edition.
Part five – Enabling the AlwaysOn Availability option
I will be brief in this part. I want to redirect you to this blogpost:
Scroll down to the section titled:
“Enable SQL Server 2012 AlwaysOn Availability Groups Feature”
and read the small instruction on how to activate the AlwaysOn Availability.
In my case:
Please, do not forget to restart the SQL Server service as stated in the blogpost above.
Part six – Creating your first AlwaysOn Availability group
You have reached the end of this blogpost. If you have followed my steps correctly, you will be ready to create your first AlwaysOn Availabity group in SQL Server Management Studio. Let’s proceed.
1) Open SQL Server Management Studio and connect to the SQL Server instance that you created earlier.
2) In Object Exporer, expand the AlwaysOn High Availability folder.
3) Right-click on the Availability Groups folder and select the New Availability Group Wizard… option. This will launch the New Availability Group Wizard.
4) Read or skip the introduction page and click “Next”
5) Specify an Availability Group name. In my case I have used this convention: <Short application description><application version>AG is short for Availability Group. e.g. SP2013AG. SP2013AG stands for: <Sharepoint><2013><Availability group>.
6) Select the databases that needs to reside in the Availability group. The databases have to be in Full recovery model prior to joining them in the Availability group. In my case I have created a temporary_seed database, that I will be using to create the Availability group. You will do this only when you are going to use Sharepoint 2013. Sharepoint 2013 (as with Sharepoint 2010) will provision all databases during the installation of Sharepoint.
Click “Next” and you will proceed to specify replica partners.
I will add my second Microsoft SQL 2012 Server as the replica partner. I do this by clicking on the “Add Replica” button. A connect window will appear. Here you connect to the second Microsoft SQL 2012 Enterprise Edition server.
7) Once you’ve selected all the correct options, you will be presented with this screen:
Pay attention on how I have configured the options:
SPSQL01 is the primary AlwaysOn server
SPSQL02 is the secondary AlwaysOn server.
Automatic failover is selected for both servers. I have checked this option, because the business requirements dictates that a failover must occur automatically without an administrator’s intervention. If this is not your case, then you can uncheck both options. But this defeats the purpose of AlwaysOn .
Synchronous Commit has been selected, because the business requirements dictates that no data must be lost in case of a disaster.
Readable Secondary has been activated. I have done this, to keep our Sharepoint 2013 farm online and serve content, in case of a disaster.
8) Proceed to the “Listener” tab and configure the Listener name and ip.
I have used this convention:
<Short application description><application version>_<LISTENER>. e.g. SP2013_LISTENER. SP2013_LISTENER stands for: <Sharepoint><2013>_<LISTENER>.
Remember to create the AlwaysOn Group listener DNS name in your internal DNS infrastructure. An A-record will suffice. The A-record must contain the following information:
– hostname e.g. SP2013_LISTENER
– IP Address it must resolve to: e.g 10.0.0.187
9) When you are happy with the settings in the “Replica” and “Listener” tab, click on “Next”.
10) Select the Data Synchronization method and select the “FULL” option. Point the wizard to a shared folder on your network accessible by all SQL 2012 Enterprise Edition servers. The SQL Server service account used by both replicas must have write permissions to this shared folder.
11) Proceed to the validation page, by clicking on “Next”. The Availability group wizard will run some checks on your whole setup and infrastructure.
If you have followed my steps correctly, you shouldn’t have any error and you will be able to click on the “Next” button to start the process of creating your first Availability group.
12) In the summary page, verify that all configuration settings are ok and click “Finish”. Your first Availability group will be setup right away.
13) In the results page, verify that all tasks have been completed successfully.
Congrats! You have just created your first SQL Server 2012 AlwaysOn Availability group. Sharepoint 2013 or any other application that needs a database server, can use the Availability group listener’s name as the database connection string.
You can clearly see that my Availability group is working and that I can manipulate it using SQL Management Studio. SPSQL01\SP2013 is the primary node of the SP2013 Availability group. SP2013_AG is Listener DNS name for my Sharepoint Availability group.
I have enjoyed writing the second part of the Sharepoint 2013 HA series. Tomorrow I will finish the series, by sharing my knowledge on how to complete a Microsoft Sharepoint 2013 Setup that will run on a Microsoft SQL 2012 Server AlwaysOn Availability group. After all, your first AlwaysOn Availability group is up and running
I want to wish my readers/followers/fellow Sharepoint Administrators a Merry Christmas, filled with joy and happiness.
I want to share the original blogpost that helped me in the quest to setup my first AlwaysOn Availability group.