Wednesday 30 July 2014

SQL Always On - things to get set up before you get going with the Wizard....

This article is key. At this point, I'm assuming that all the nodes involved are in the Windows Failover Cluster

  • Make sure that all nodes in the cluster are patched with this. Otherwise, you cannot set quorum votes on this node. Do this before the server is a live server. Regardless of what the article says, you need to restart each node. This means failing over. This means downtime. This is a pain in the butt if you have seven instances of SQL on a three node cluster.

  • Set the Node Votes. Easy. Use Powershell. Use the Windows Powershell Modules found in admin tools in the start menu. If it's not there, make sure it's installed. This doesn't result in downtime
    • Get-clusternode | fl NodeName, NodeWeight
      Should be ‘1’ for each node because that’s the default.
      This needs to be changed to ‘0’ for DRSQLALWAYSON by running
      (Get-ClusterNode “DRSQLALWAYSON”).NodeWeight = 0 
       
  • Make sure that HADR Always On is enabled for each instance of SQL involved. The easiest way is via SQL Configuration Manager. You will have to restart SQL. Again, downtime.

  • Make sure that the DR node is not a node onto which the primary instance of SQL could failover. This doesn't result in downtime.
    • Get-ClusterOwnerNode -resource “SQL Server (InstanceName)”
      Set-ClusterOwnerNode -resource “SQL Server (InstanceName)” –owners Node1,Node2

       It is worth running get-clustergroup in order to establish the names of the SQL Server instances. For the default instance on the cluster, I found that the command was 
    • Get-ClusterOwnerNode -resource "SQL Server")

  •  Work out where your listener will be.  If you've got primary and secondary instances on different subnets, this will be a whole bunch of fun. You need to us TSQL rather than the GUI:
    • http://blogs.msdn.com/b/saponsqlserver/archive/2012/04/24/sql-server-2012-alwayson-part-7-details-behind-an-alwayson-availability-group.aspx
USE [master]
           GO
           ALTER AVAILABILITY GROUP MyAvailabityGroup
           ADD LISTENER N'MyListener' (
           WITH IP
           ((N'local.ip.add.dress', N'255.255.0.0'),
           (N'dr.ip.add.dress', N'255.255.0.0') ) , PORT=60173);
           GO