Brents IT Blog

Random thoughts by an IT GOAT

NAVIGATION - SEARCH

Microsoft AlwaysOn Clustering and Availability Groups for MSSQL

Below is a guide of how i implemented AlwaysOn clustering to take advantage of Availability groups in MSSQL 2012 R2 running on Windows Server 2012 R2.  There are a few things before i drop all the details though.  First, MS best practices say to use a domain service account to run (each) MSSQL service(s).  This is really evident when you try to setup a AlwaysOn AG.  When you start having to provide exclusive permission settings and copy them manually across hosts, it become very evident the the necessity of such a recommendation.  Doing so will save you headaches when trying to complete this task.  They also recommend having separate interfaces for cluster heartbeat, do not take this lightly because if one server doesnt see the other(s), then it will dissolve the cluster and the SQL live database ownership will transfer to the failover.  This doesnt sound like a bad thing, but when your server does this randomly throughout the day, a few times a day, your users will begin to make your job as problematic as their user experience.  You can adjust settings to get around the problem or set another host as the primary service holder in the clustering settings, but the better fix is to set it up properly when its created.

 

So with that said, here are my setup notes on this:

Prerequisites:

  1.              Windows domain, preferably 2012 ( or 2012 R2 ) functional, but 2008 will work.

    a.       You must use your domain DNS server(s).

    2.       Two or more SQL servers running server 2012 R2 on windows server 2012 R2.

    a.       I believe these steps also work on just plain 2012 as well, my lab was built on 2012 R2 windows servers with SQL 2012(not R2) servers.

    b.      Make sure both SQL servers have the SAME features installed!!!

    c.       NOTE: MSSQL can be installed prior to or after clustering is setup.

    3.       A windows file share on a third server ( for witness to ensure correct votes )

    a.       This is not needed when you have three or more nodes

                                                                   i.      This guide is for 2 nodes

     

    Setup Windows Failover Clustering

    1.       On both SQL servers

    a.       Click on Add Roles and Features, next all the way to features

    b.      Select Failover Clustering from the list, click add features in the pop up window, then hit next

    c.       Click install and let it run and complete it

    d.      Go into your firewall advanced settings

                                                                   i.      Create a rule that allows for the two servers to talk to one another.

    1.       There are a few ways to do this, but I typically create a custom rule that is set to allow any connection from a range of IPs or a single IP ( in this case ).

                                                                 ii.      Next you need a rule that allows connection to port 1433 or whatever port you plan to use for your AG.  Be safe and easy, use 1433.

    2.       On the Primary SQL Server / Primary Cluster Node

    a.       Open the Failover Cluster Manager

                                                                   i.      Notice everything is blank

    b.      Click Create cluster

    c.       Add the servers

    d.      Run through the tests!!

                                                                   i.      You will get some warnings about storage controllers and network stuff which you can safely ignore assuming everything else checks out and they most likely do not apply to your setup.

    e.      Name the Cluster and set the IP Address the cluster will respond to

                                                                   i.      This will be the netbios name as well, but SQL will use a different one

                                                                 ii.      The IP for the cluster only, not the SQL AG.

    f.        Uncheck the Add local storage and finish.

    3.       Go over to your DC

    a.       Make sure the cluster name appears in DNS

    b.      Next open up ADUC, right click the domain and select Delegate Control

    c.       Add the cluster name ( you will have to add computers to the object types )

                                                                   i.      IE: ClusterName$ ( dollar sign specifies computer account )

    d.      Create a custom task to delegate, hit next

    e.      Leave it ticked for “this folder” and hit next

    f.        Uncheck General and check Creation/deletion of specific child objects

    g.       In the box, check “Create Computer Objects”, Next and Finish

                                                                   i.      This is done because by default computers can only create 10 accounts which means you would be limited to 10 services for that cluster

    h.      At this point I normally create the file share for the witness you will need in a 2 node cluster.  Simply find a separate server from your SQL cluster nodes, in this case the DC is what I am using, then create a share.

                                                                   i.      When creating the share, I normally create a folder on the C drive and name it something like ClusterWitnessShare, but name it whatever you want just don’t let it get deleted.

                                                                 ii.      Make sure to give the cluster server object, the one you just delegated, read/write to the share folder.

    1.       Simplest way, right click the folder, select share with, then specific people, add the new cluster server object you delegated to the permissions and change the permission level from read to read/write.

     

    4.       Back on your Primary SQL Server / Primary Cluster Node

    a.       Open the Failover Cluster Manager

    b.      Right click the cluster name at the top left, select More Actions, then Configure Cluster Quorum Settings…

    c.       Click Select the Quorum Witness

    d.      Click Configure a file share witness

    e.      Type the path and next until finished.

                                                                   i.      Any errors indicate you did not provide write permissions to the cluster object name on the witness share.

    f.        If you click on Nodes, you will see the Current Vote change from 0 on the secondary to 1, this indicates the share is online and there is a sudo 3 node cluster.  It also means faster and automatic failover will work.

                                                                   i.      You can also verify this on the main page at the bottom where it shows everything setup as online.  If its offline, just click it and try to bring it online.

     

    Setup of AlwaysOn Availability Groups

    1.       On the Primary SQL Server / Primary Cluster Node

    a.       Open up the SQL Server Configuration Manager

    b.      Expand SQL Server Services

    c.       Right click the instance of MSSQLSERVER and click properties

    d.      Click on the AlwaysOn High Availability Tab

    e.      Check the Enable AlwaysOn Availability Groups check box and hit ok.

    f.        Restart the instance

    g.       You will need to create another share somewhere on your network for the AG backup/restore process.  You can use one of the SQL servers or another third party server.  I used the DC.

                                                                   i.      You will want to create a domain account for the SQLAgent service.  This account will need write permissions to the share and all MSSQL servers will need to use this account for the SQLAgent service.  Otherwise backups to the folder and AG creation will fail.

    1.       Make sure the Everyone group has write permission on the share otherwise the cluster service will fail it on the backup task.  This covers the cluster service write. 

    2.       I also suggest creating a group with all the cluster server computers and then giving it full control in the NTFS permissions.

                                                                 ii.      You will also need to add this user to the SQL servers login list as a sysadmin.

    h.      Connect to the SQL instance using management studio

    i.         Right click AlwaysOn High Availability and select New Availability Group Wizard…

                                                                   i.      Before you do this:

    1.       Ensure you have your database mounted and functional.

    2.       Ensure you have the database recovery model set to Full

    3.       Ensure you have performed a backup of the database after setting its recovery model to Full.

    j.        Name the group

                                                                   i.      This is a group name, not the computer object name or the share name

    k.       Check the database(s) you want to add

                                                                   i.      Notice the status to the right if you cannot select it, it will tell you why not.

    l.         Click on add replica to add server(s), connect to remote server

    m.    Check Automatic Failover for both servers and set readable secondary to yes for both servers

    n.      Click on the Backup Preferences Tab, leave it as prefer secondary and increase the priority of the server at the bottom to 51 or more.

    o.      Click on the listener tab, select create an availability group listener now

                                                                   i.      Type the Netbios name you wish to access the cluster with

                                                                 ii.      Type the port number, use default 1433 unless you need some other config

                                                                iii.      Select static IP, click add at the bottom, type the static IP for the listener

    p.      Click next

    q.      Leave it set to Full for synchronization, type the share path you created for the SQL backups in step G, hit next

    r.        It will validate, make sure to fix any errors, then finish and it will start the creation.

    s.       Give it a few minutes to create and synchronize the replicas, time will depend on the DB size.

    t.        Now would be a good time to repeat the delegation for the AlwaysOn AG Computer Object

                                                                   i.      Go over to your DC

    1.       Make sure the AG cluster name appears in DNS

    2.       Next open up ADUC, right click the domain and select Delegate Control

    3.       Add the cluster name ( you will have to add computers to the object types )

    a.       IE: ClusterName$ ( dollar sign specifies computer account )

    4.       Create a custom task to delegate, hit next

    5.       Leave it ticked for “this folder” and hit next

    6.       Uncheck General and check Creation/deletion of specific child objects

    7.       In the box, check “Create Computer Objects”, Next and Finish

    a.       This is done because by default computers can only create 10 accounts which means you would be limited to 10 services for that cluster

    2.       Copy Users

    a.       Once the database is replicated and the AG is greened up, you must then copy the user logins from one SQL server to the other.  If you used contained databases, this step may not be necessary. 

    b.      Script can be found here:

                                                                   i.      https://support.microsoft.com/en-us/kb/918992

                                                                 ii.      Used the stored procedure method and sp_help_revlogin to export users.

    1.       This creates a script that you can simply run on the secondary server and you are done.

    2.       This needs to be done for any user added to the database.  If it’s a windows domain user, then you can simply add the user manually in the secondary server with the same permissions.  SQL users must be transferred by this script as they have unit IDs.

    3.       IE:  EXEC sp_help_revlogin

    Adding a database after AG Creation

    1.       Back on the primary SQL / Cluster server

    a.       Right click on the Availability group and select add database

    b.      The wizard will appear, click next, check the appropriate database and click next.

    c.       The share should already be defined( unless you want to change it ), click next

    d.      Click connect next to the server instance ( should show the secondary instance name ), I use windows auth, but you can also enter SA and click connect again.  It will now show as connected as the user you connected with.

    e.      Click next

    f.        It will run through its check list, fix any issues and re-run validation before continuing ( VERY important there are no issues )

    g.       Next and Finish

    h.      The time it takes will depend on the size of the DB.  Keep in mind that it dumps a backup, then creates the database on the secondary with the backup file, so the size of the DB transfer is compressed.  Yay, not wasting our time.

    i.         Click close

    j.        Refresh the server and you will see the database listed as “synchronized”

    k.       Connect to the secondary server, you will see the same thing.

                                                                   i.      I should note that on the secondary, when expanding the always on folder, group, then replicas, the primary will not be listed as primary, it will just have a question mark on it.  Not sure why MS did that, but if you go to the primary server, it lists them correctly under the same replica folder….

    2.       Adding a database without using the AG wizard to perform the initial sync ( manual mode )

    a.       First perform a manual backup of the database.

    b.      Perform a manual backup of the transactions logs for the database in question

    c.        

     

     

    AG Database Restore

    1.       Expand Availability Groups on the primary server

    2.       Right click the Availability Group with the database you need to restore and select properties.

    3.       Select the desired database and click remove

    4.       Click ok.

    5.       At this point, refresh the database list, if you get a white status, just close and reopen studio

    6.       The database should show up as just a plain DB, no synchronizing or restoring next to it.

    7.       At this point you can restore the database as you normally would ( including transaction logs ).

    a.       While the restore is going on, login to the secondary replica server

                                                                   i.      You will see the database there noted as restoring..

                                                                 ii.      Right click the database and select delete

                                                                iii.      The database will go away and the files on the server will as well.

    8.       Back on the primary server, assuming you completed all the steps in 7, right click on the Availability group and select add database

    9.       The wizard will appear, click next, check the appropriate database and click next.

    10.   The share should already be defined( unless you want to change it ), click next

    a.       If you change the share location, the share MUST have the appropriate permissions assigned to the share and NTFS

                                                                   i.      Easiest way to set the permissions is to add the authenticated users group and give them full control

    1.       Not the most secure thing to do, but in the data center, it’s not like there are other users on that domain.

    11.   Click connect next to the server instance ( should show the secondary instance name ), I use windows auth, but you can also enter SA and click connect again.  It will now show as connected as the user you connected with.

    12.   Click next

    13.   It will run through its check list, fix any issues and re-run validation before continuing ( VERY important there are no issues )

    14.   Next and Finish

    15.   The time it takes will depend on the size of the DB.  Keep in mind that it dumps a backup, then creates the database on the secondary with the backup file, so the size of the DB transfer is compressed.  Yay, not wasting our time.

    16.   Click close

    17.   Refresh the server and you will see the database listed as “synchronized”

    18.   Connect to the secondary server, you will see the same thing.

    a.       I should note that on the secondary, when expanding the always on folder, group, then replicas, the primary will not be listed as primary, it will just have a question mark on it.  Not sure why MS did that, but if you go to the primary server, it lists them correctly under the same replica folder….