#sql failover cluster

Setting up clusters in Windows Server 2016 has become so easy. However; When integrating it with other environments, like VMWare, and AO SQL Clustering. It can get quite Tricky. First we want to setup our environment in VSphere. Next we will setup Windows Server 2016 with Failover Clustering. Then we’ll make sure to make some adjustments to DNS. Finally, We will setup AlwaysOn SQL Clustering. 

Please make sure you have .NET Framework 3.5.1 or greater on the servers. Then We will need to create 2 VMs with 3 drives each. Then we will need to make sure that the drives are online, and available from other locations. However one main aspect that I had overlooked was that the Virtual Disks have to be created Eager Zero Thick not Lazy Zero Thick. I made the heinous mistake of using Lazy Zero Thick, and then could not understand why I was having so many problems. 

Note: When creating virtual disks with Eager Zero Thick, it does take longer than using the faster Lazy Zero Thick option. Eager Zero Thick Disks allocates the space for the virtual disk, then zeros it all out unlike the Lazy Zero Thick which only allocates the space. 

You also generally wouldn’t use Eager Zero Thick except for Microsoft clustering and Oracle programs. Once the disks are created we are reading to install Windows Server 2016.

Install either Datacenter edition or Standard edition. For this example we’ll use the standard edition. Install all the Microsoft Windows feature updates and verify that you have already allocated all the resources needed. Check that the additional Virtual Disks are available, and make sure you install the Failover Cluster feature. You may want to reboot after the feature is installed, if you have not done so. Once you installed the feature go to the Failover Cluster Manager and prepare to create the cluster. If this is a two node cluster be sure to add a Witness Server or Desktop. Once this is created and validated, go to your computer manager and verify that the virtual disks are online and initialized. Next, you will want to configure the cluster quorum settings. I created a separate server for this quorum and configured a File share witness. 

Now, make sure you can access these from another computer on the secured network. You will have to setup a Host A record round robin DNS situation, where you give a specified name the 2 failover cluster nodes IP adresses in the Host address. Example: if the nodes had up address of 192.168.1.43 and 192.168.1.44. Then the two Host records you would need to create are AOSqlServer -> 192.168.1.43 and AOSqlServer -> 192.169.1.44

Finally, We will enable AlwaysOn Availability Groups on SQL Server 2016. 

After Installing SQL Server 2012 or 2014 Enterprise edition on all the replicas. Please install it on as a stand-alone instance, and then we will configure SQL Server. Once you expand SQL Server Network Configuration node, and click on Protocols for MSSQLServer, you will see the TCP/IP dialog box on the right panel. Right click on the TCP/IP entry and select Enable. 

In SQL Server Configuration Manager, right click on SQL Server Services to open the Properties dialog box. Navigate to the AlwaysOn High Availability tab to select the “Enable AlwaysOn Availability Groups.

Now we must configure the login accounts and the replicas that will need read write privileges. 

First verify that your SQL Service Account is there and is a domain account, not a local machine account. Now login through SQL Management Studio (SSMS). Add you SQL Service account to the Administrators group on each replica (via computer management). Then allow connect permissions to the SQL Service account through SSMS: Right click on the SQL Service login to open the Properties dialog box. On each replica navigate to the Securables page and make sure Connect SQL Grant box is checked and allow remote connections. You can do this by using SSMS in the instance properties or by using sp_configure.

EXEC sp_configure ‘remote access’, 1;

GO

RECONFIGURE;

GO

Now we will create the file share through the Server Manager that the SQL Service account, and the replicas can access. The File is for the initial backup/restore process that happens to the databases when you join the AlwaysOn group during setup.

Last thing is to install the AlwaysOn Availability group. Once you’s ensured that full backups have been created, and all databases are in Full recovery mode, you will have to remove these databases from the tlog backup maintenece during the installation of Always on (you can always add them back). It could cause errors with both tlogs backing up while AlwaysOn is being created.

On you primary, open SSMS and expand the AlwaysOn High Availablity folder. Right click on the Availability Groups and select New Availability Group Wizard.

Select only the databases you want to include in the AlwaysOn group.

Next to the databases you will see the status with a blue link. If you see "Meets Prerequisites” it will signify that these databases are included in your group. If it does not say "Meets Prerequisites”, then click on the link to see more details on what needs to be corrected.

Now, you will specify and Add the Replicas. You will need to specify if you want Automatic or Manual Failover, Synchronous or Asynchronous Data Replication, and the type of Connections you are allowing to the end users.

Be sure to view the troubleshooting page if you have any issues:

http://blogs.msdn.com/b/alwaysonpro/archive/2013/12/09/trouble-shoot-error.aspx

The backup preferences tab will assist in choosing the type of backup and to prioritize the replica backups.

In the Listener tab, you will create an availability group listener button, Enter the string DNS name, enter port 1433 and enter the IP address for your listener, which should be an unused IP address on the network. 

Next, you will Select Initial Data Synchronization page, join the databases to the Always on group, then verify the Full option is selected for using File Shares. For large databases select Join or Skip to restore the databases to the secondary replica. We will use Full for now. Last thing to do here is remember the SQL Service accounts and set that all replicas have read/write permissions to the file Share or it will not work. 

Run the Validation checks, and make sure it the results are successful. 

We're moving to Windows 2008 and there's more and more opportunities to use PowerShell to help with my work. This is very cool. Next up is a 2008 R2 SQL cluster. I've always wondered how long it actually takes for a failover to happen and SQL be functional, we don't have a test cluster to find this information out so whilst it's not in production I'm doing some testing and it turns out it's around 14 seconds! A lot quick than I expected.

This isn't under load, that's the next step but just firing a simple query at the DB. This is possibly a flaw in this test - I'm not a DBA and don't know if this SQL query could return before the databases a fully attached. The load testing should highlight that. What I've found interesting is using jobs to test connections to the databases in parallel. It 's the first time I've used jobs to do something useful. 

The names are commented out to protect the innocent. 

# #### ## Run this in a local PowerShell session #### $sql = "select convert(varchar,SYSDATETIME()) + ' - ' + DB_NAME()" #$sql = "select convert(VARCHAR(19), GETDATE(), 120) + ' - ' + DB_NAME()" $databases = @("XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX") foreach ($database in $databases) { $Job = Start-Job -ScriptBlock { Add-PSSnapin SqlServerCmdletSnapin100; do { try { $output = Invoke-Sqlcmd -Query $args[1] -Database $args[0] -ServerInstance "Cluster"; $time = "[" + (Get-Date) + "]" Write-Output "$time $($output.Column1)"; } catch { $time = "[" + (Get-Date) + "]" Write-Output "$time Unable to run the SQL - $_" }# $output = "" Start-Sleep -Seconds 5 } while ( 1 -eq 1 ) } -ArgumentList $database, $sql }

This is a foreach loop that will create a PowerShell Job for each database in the array of databases. The code run for each job is in the script block - it's important to remember that this code is going to be run in it's own session. There are no profiles loaded, no modules\snapins\functions\variables from your current session so they need to be loaded in the script or passed via the -ArgumentList parameter. 

In my case I've needed to load the SQL Snapin and pass two variables to the script. These are referenced by the $args variable. Each job created by this script will perform the $sql query against the database and log the time that it was done or log when it errored. This is done in a loop so I can get an idea of when SQL is unavilable and when it comes back.

You may be wondering how to get the information from the jobs!?  as expected with PS that's easy.

Get-Job | Receive-Job -Keep

that'll return all the information on the Jobs running and keep the information (if you just want to get the information and not keep it don't pass the keep switch, of course you can assign this command to a variable).

If you've got some other jobs running\hanging around you can use the Id of the Jobs your're interested in to get the information (as an advancement the above script could return the Ids to an array and that be used): 

Get-Job -Id 17,15,13,11 | Receive-Job -Keep

Well that's how to set up a collections of jobs - the next bit is failing over the cluster - it's been recommended that the groups failover in a particular order and we have 3 of them so here's the command used to failover: 

# #### ## Run this in a PowerShell session on Node1 or Node2 #### $t = measure-command{ Get-ClusterGroup "Group1" | Move-ClusterGroup -Verbose ; Get-ClusterGroup "Group2" | Move-ClusterGroup -Verbose ; Get-ClusterGroup "Group3" | Move-ClusterGroup -Verbose ; }