Configuring SQL Server Always On Availability Groups in Azure - Part 3

Posted in Azure, AlwaysOn, High Availability, WSFC, Failover Cluster, Windows Server

This is the last of the 3 part blog series where I will explain how to setup Always On using primarily PowerShell and a little bit of GUI. In this section we discuss how to setup and test the Always On SQL Server functionality

Prerequisites###

You have already completed Part 1 and Part 2 of the series.

Part 3 - Prepare the cluster nodes for Always On###

You can simply copy paste the code below and execute it step by step in order to implement Part 3

##########################################################
#Step: 1
#Description: Start an Azure PowerShell session
#             Or start an elevated PowerShell session and run the following to import the Azure library:
#Run On: Local Machine
##########################################################
Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"

##########################################################
#Step: 2
#Description: Set the appropriate variables.
#             Note: these need to be exactly the same as in Part 1 if you run it
#Run On: Local Machine
##########################################################
$affinityGroupName = "MyAffinityGroup"            
$availabilityGroupName = "MyAvailabilityGroup"  # The name used by the AG
$availabilitySetName = "MYSQLHADR"              # The name of the AG set
$clusterName = "TestCluster"				      # The windows cluster name that will support the AG
$dcServerName = "ad-server"                      # The Domain Controller/Active Directory server name
$dcServiceName = "my-ad"                        # The Azure Service for the DC/AD server
$domainName= "test"                         # The Active Directory Domain name
$FQDN = "test.net"                          # The fully qualified Domain name
$location = "West EU"                             # The datacenter location
$quorumServerName = "server0"              # The name of the Quorum Cluster node - used for backups and filesharing
$sqlServiceName = "my-service-name"                   # The service name for the Cluster and SQL AG
$sql1ServerName = "server1"                # The name of the master SQL Server node
$sql2ServerName = "server2"                # The name of the replica SQL Server node
$storageAccountName = "my-storage-account"                 # Location for storing vhds - needs to be all lowercase and < 20 chars
$storageAccountContainer = "https://" + $storageAccountName + ".blob.core.windows.net/vhds/"
$subnetName = "Cluster"                           # The name of the Subnet that will be used by the Cluster/AG
$virtualNetworkName = "myVn"               # 
$vmAdminUser = "ClusterAdmin"                 # The domain admin user running the Cluster
$vmAdminPassword = "AVerySecurePasswordHere"                 # The domain admin password
$vmSQLServiceAccount = "SQLSvcAdmin"               # The SQL Service domain account
$workingDir = "c:\Temp"                          # Location where all scripts and temp files will be placed. Can be any location

##########################################################
#Step: 3
#Description: Get your Azure settings file (credentials, permissions etc)
#Details: This will open a browser window to allow you to log in to your Azure account. 
#         Then, it will prompt you to save the settings file locally to your machine. 
#         Make sure you use an easy-to-find location
#Run On: Local Machine
##########################################################
Get-AzurePublishSettingsFile

##########################################################
#Step: 4
#Description: Run the following command. Ensure you replace <publishsettingsfilepath> 
#             with the location that you used on step 3
#Run On: Local Machine
##########################################################
Import-AzurePublishSettingsFile <publishsettingsfilepath>

##########################################################
#Step: 5
#Description: Select the subscription you will work with. 
#             Use the command below to enumerate all subscriptions available to your account:
#Run On: Local Machine
##########################################################
Get-AzureSubscription | Select SubscriptionName

##########################################################
#Step: 6
#Description: Set your Powershell session to the right subscription. Change the subscription name as necessary
#Run On: Local Machine
##########################################################
$subscriptionName = "My Subscription"
Select-AzureSubscription -SubscriptionName $subscriptionName

##########################################################
#Step: 7
#Description: Configure the default storage account to use with the subscription
#Run On: Local Machine
##########################################################
Set-AzureSubscription `
	-SubscriptionName $subscriptionName `
    -CurrentStorageAccountName $storageAccountName
	
##########################################################
#Step: 8
#Description: Create the working directory
#Run On: Local Machine
##########################################################
if (!(Test-Path -path $workingDir)) `
 { New-Item -ItemType directory -Path $workingDir  }
 
##########################################################
#Step: 9
#Description: Attach a data disk to the master server to be used for the SQL data and log files
#Run On: Local Machine
##########################################################
Get-AzureVM $sqlServiceName -Name $sql1ServerName `
| Add-AzureDataDisk -CreateNew -DiskSizeInGB 256 -DiskLabel "Data" -LUN 0 `
| Update-AzureVM

##########################################################
#Step: 10
#Description: Attach a data disk to the replica server to be used for the SQL data and log files
#             Note: Remember to run this for all other replicas!
#Run On: Local Machine
##########################################################
Get-AzureVM $sqlServiceName -Name $sql2ServerName `
| Add-AzureDataDisk -CreateNew -DiskSizeInGB 256 -DiskLabel "Data" -LUN 0 `
| Update-AzureVM

##########################################################
#Step: 11
#Description: Attach a data disk to the Quorum Server to be used for storing the SQL backups
#Run On: Local Machine
##########################################################
Get-AzureVM $sqlServiceName -Name $quorumServerName `
| Add-AzureDataDisk -CreateNew -DiskSizeInGB 512 -DiskLabel "Data" -LUN 0 `
| Update-AzureVM

##########################################################
#Step: 12
#Description: Log on to the Quorum Server to initialize and format the new disk
#Run On: Quorum
##########################################################

##########################################################
#Step: 13
#Description: Start an elevated Powershell session to initialize and format the new disk
#             Note: Close any windows opened by this operation and enter ‘Y’ when prompted by PowerShell
#Details: the following command will initialize the disk to make it visible to the operating system
#         It will then format the disk with the following settings
#         - File System: NTFS
#         - Sector Size: 64K (recommended for SQL)
#         - Label: Data
#         - Drive Letter: F (assign automatically)
#         Finally, it will create a new directory F:\backup
#Run On: Quorum
##########################################################
Initialize-disk -Number 2
new-partition -DiskNumber 2 -UseMaximumSize –AssignDriveLetter | format-volume -FileSystem NTFS -NewFileSystemLabel Data -AllocationUnitSize 65536 -Force
 
New-Item F:\backup -ItemType directory

##########################################################
#Step: 14
#Description: Share the folder created on step 12 and add the necessary permissions
#Run On: Quorum
##########################################################
#Open Windows Explorer and navigate to the new folder (F:\backup)
#Right-click on the newly created folder
#Select Properties --> Sharing --> Click on ‘Share’
#Add the test.net\SQLSvcAdmin accounts with Read/Write permissions
#Click on ‘Share’
#Go to the Security Tab
#Check that both the SQLSvcAdmin & ClusterAdmin accounts appear in the ‘Groups or user names’ box
#Ensure they have Full Control in the ‘Permissions’ box

##########################################################
#Step: 15
#Description: Log on to the Master Server to initialize and format the new disk
#Run On: Master
##########################################################

##########################################################
#Step: 16
#Description: Start an elevated Powershell session to initialize and format the new disk
#             Note: Close any windows opened by this operation and enter ‘Y’ when prompted by PowerShell
#Details: the following command will initialize the disk to make it visible to the operating system
#         It will then format the disk with the following settings
#         - File System: NTFS
#         - Sector Size: 64K (recommended for SQL)
#         - Label: Data
#         - Drive Letter: F (assign automatically)
#         Finally, it will create a new directory F:\backup
#Run On: Master
##########################################################
Initialize-disk -Number 2
new-partition -DiskNumber 2 -UseMaximumSize –AssignDriveLetter | format-volume -FileSystem NTFS -NewFileSystemLabel Data -AllocationUnitSize 65536 -Force

##########################################################
#Step: 17
#Description: Create the data/log directory on master
#Run On: Master
##########################################################
$data = "F:\data"
New-Item $data -ItemType directory

##########################################################
#Step: 18
#Description: Configure SQL Server to use the newly created drive for data/logs and backups 
#Run On: Master
##########################################################
#Open SSMS
#Connect to the local instance
#Right click on the Server
#Select Database Settings
#Change the Database default locations as per below:
#Data: F:\Data
#Log: F:\Data
#Backup: \\<QuorumServerName>\Backup

##########################################################
#Step: 19
#Description: Log on to the Replica Server to initialize and format the new disk
#Run On: Replica
##########################################################

##########################################################
#Step: 20
#Description: Start an elevated Powershell session to initialize and format the new disk
#             Note: Close any windows opened by this operation and enter ‘Y’ when prompted by PowerShell
#Details: the following command will initialize the disk to make it visible to the operating system
#         It will then format the disk with the following settings
#         - File System: NTFS
#         - Sector Size: 64K (recommended for SQL)
#         - Label: Data
#         - Drive Letter: F (assign automatically)
#         Finally, it will create a new directory F:\backup
#Run On: Replica
##########################################################
Initialize-disk -Number 2
new-partition -DiskNumber 2 -UseMaximumSize –AssignDriveLetter | format-volume -FileSystem NTFS -NewFileSystemLabel Data -AllocationUnitSize 65536 -Force

##########################################################
#Step: 21
#Description: Create the data/log directory on master
#Run On: Replica
##########################################################
$data = "F:\data"
New-Item $data -ItemType directory

##########################################################
#Step: 22
#Description: Configure SQL Server to use the newly created drive for data/logs and backups 
#Run On: Replica
##########################################################
#Open SSMS
#Connect to the local instance
#Right click on the Server
#Select Database Settings
#Change the Database default locations as per below:
#Data: F:\Data
#Log: F:\Data
#Backup: \\<QuorumServerName>\Backup

##########################################################
#Step: 23
#Description: Repeat steps 17-20 for other replicas
#Run On: n/a
##########################################################

Enable Always On on all SQL cluster nodes###

##########################################################
#Step: 1
#Description: On the Master Server continue with the elevated powershell session
#             Set the session variables. Uncomment if you need more replicas in the cluster
#Run On: Master
##########################################################
$quorumServerName = "server0"
$sql1ServerName = "server1"
$sql2ServerName = "server2"
#$sql3ServerName = "a2-qa02-sql-03"
$acct1 = "TEST\SQLSvcAdmin"          # The domain SQL Service account
$domainName = "test.net"             # Fully qualified domain name i.e test.net or test.north
$clusterName = "TestCluster"         # The cluster name as configured in Part 2
$timeout = New-Object System.TimeSpan -ArgumentList 0, 0, 30
$dbs = "MyDB"                        # All database names that will be part of the Availability Group
$availabilityGroupName = "MyAvailabilityGroup"    # The name of the availability group
$password = "AVerySecurePasswordHere"             # The password for the domain SQL Service account

##########################################################
#Step: 2
#Description: Enable the Always On feature on SQL Server.
#             This should be run for all SQL server nodes so if there is another
#			  replica, ensure that you edit and uncomment the necessary sections below
#Run On: Master
##########################################################
Enable-SqlAlwaysOn `
	-Path SQLSERVER:\SQL\$sql1ServerName\Default `
	-Force
Enable-SqlAlwaysOn `
	-Path SQLSERVER:\SQL\$sql2ServerName\Default `
	-NoServiceRestart
$svc2 = Get-Service -ComputerName $sql2ServerName -Name 'MSSQLSERVER'
$svc2.Stop()
$svc2.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
$svc2.Start();
$svc2.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)

#Enable-SqlAlwaysOn `
	#-Path SQLSERVER:\SQL\$sql3ServerName\Default `
	#-NoServiceRestart
#$svc3 = Get-Service -ComputerName $sql3ServerName -Name 'MSSQLSERVER'
#$svc3.Stop()
#$svc3.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
#$svc3.Start();
#$svc3.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)

##########################################################
#Step: 3
#Description: Sync the databases to all the replicas. Run the following script in one go
#Details: The databases need to be in full sync before setting up the availability group
#Run On: Master
##########################################################
$dbs = "db1","db2"                     #add necessary databases comma-separated
$masterServer = ""                     #add the master server
$replicaServers = "server1", "server2" #add the replica server(s) comma-separated
$backupShare = "C:\temp"               #the location where the backup files will end up

foreach($db in $dbs)
{
    write-host("Backing up: $db")
    Backup-SqlDatabase -Database $db -BackupFile "$backupShare\$db.bak" -ServerInstance $masterServer 
    Backup-SqlDatabase -Database $db -BackupFile "$backupShare\$db.log" -ServerInstance $masterServer -BackupAction Log
    write-host("Backup of $db is complete")
}

foreach($replicaServer in $replicaServers)
{
    foreach($db in $dbs)
    {
        write-host("Restoring up: $db")
        Restore-SqlDatabase -Database $db -BackupFile "$backupShare\$db.bak" -ServerInstance $replicaServer -NoRecovery
        Restore-SqlDatabase -Database $db -BackupFile "$backupShare\$db.log" -ServerInstance $replicaServer -RestoreAction Log -NoRecovery 
        write-host("Restore of $db is complete")
    }
}

##########################################################
#Step: 4
#Description: Create the SQL Availability Group endpoint
#Run On: Master
##########################################################
$endpoint =
    New-SqlHadrEndpoint MyMirroringEndpoint `
	-Port 5022 `
	-Path "SQLSERVER:\SQL\$sql1ServerName\Default"
	
Set-SqlHadrEndpoint `
	-InputObject $endpoint `
	-State "Started"
	
##########################################################
#Step: 5
#Description: Create the SQL Availability Group endpoint on the replicas
#             Note: you need to replace the $sql2ServerName with the names of the other replicas
#Run On: Master for all replicas - replace variables as necessary
##########################################################
$endpoint =
    New-SqlHadrEndpoint MyMirroringEndpoint `
	-Port 5022 `
	-Path "SQLSERVER:\SQL\$sql2ServerName\Default"
	
Set-SqlHadrEndpoint `
	-InputObject $endpoint `
	-State "Started"

##########################################################
#Step: 6
#Description: Configure SQL Server to allow connections on the new endpoint
#             Note: Uncomment and replace the $sql2ServerName with the names of the other replicas
#Run On: Master
##########################################################
Set-ExecutionPolicy RemoteSigned -Force
Import-Module "sqlps" –DisableNameChecking
 
Invoke-SqlCmd -Query "CREATE LOGIN [$acct1] FROM WINDOWS" -ServerInstance $sql1ServerName
Invoke-SqlCmd -Query "GRANT CONNECT ON ENDPOINT::[MyMirroringEndpoint] TO [$acct1]" -ServerInstance $sql1ServerName
Invoke-SqlCmd -Query "CREATE LOGIN [$acct1] FROM WINDOWS" -ServerInstance $sql2ServerName
Invoke-SqlCmd -Query "GRANT CONNECT ON ENDPOINT::[MyMirroringEndpoint] TO [$acct1]" -ServerInstance $sql2ServerName
#Invoke-SqlCmd -Query "CREATE LOGIN [$acct1] FROM WINDOWS" -ServerInstance $sql3ServerName
#Invoke-SqlCmd -Query "GRANT CONNECT ON ENDPOINT::[MyMirroringEndpoint] TO [$acct1]" -ServerInstance $sql3ServerName

##########################################################
#Step: 7
#Description: Open the firewall to accept connections on the Always On replication port
#Run On: Master
##########################################################
netsh advfirewall firewall add rule name='Always On Replication (TCP-In)' localport=5022 dir=in action=allow protocol=TCP | Out-Null	

##########################################################
#Step: 8
#Description: Open the firewall to accept connections on the Always On replication port
#Run On: All Replicas
##########################################################
netsh advfirewall firewall add rule name='Always On Replication (TCP-In)' localport=5022 dir=in action=allow protocol=TCP | Out-Null

Create the Availability Group and add the nodes

Run the following scripts to create the availability replicas

##########################################################
#Step: 1
#Description: Create the availability group node objects (set in memory)
#             Note: uncomment and edit for more replicas
#Run On: Master
##########################################################
$primaryReplica =
    New-SqlAvailabilityReplica `
	-Name $sql1ServerName `
	-EndpointURL "TCP://$sql1ServerName.test.net:5022" `
	-AvailabilityMode "SynchronousCommit" `
	-FailoverMode "Automatic" `
    -ConnectionModeInSecondaryRole "AllowAllConnections" `
	-Version 11 `
	-AsTemplate
$secondaryReplica =
    New-SqlAvailabilityReplica `
	-Name $sql2ServerName `
	-EndpointURL "TCP://$sql2ServerName.north.net.prod:5022" `
	-AvailabilityMode "SynchronousCommit" `
	-FailoverMode "Automatic" `
    -ConnectionModeInSecondaryRole "AllowAllConnections" `
	-Version 11 `
	-AsTemplate
#$thirdReplica =
    #New-SqlAvailabilityReplica `
	#-Name $sql3ServerName `
	#-EndpointURL "TCP://$sql3ServerName.test.net:5022" `
	#-AvailabilityMode "SynchronousCommit" `
	#-FailoverMode "Automatic" `
    #-ConnectionModeInSecondaryRole "AllowAllConnections" `
	#-Version 11 `
	#-AsTemplate

##########################################################
#Step: 2
#Description: Create the availability group by running the following. 
#             Note: Ensure you add all the nodes AvailabilityReplica parameter
#Run On: Master
##########################################################
New-SqlAvailabilityGroup `
    -Name $availabilityGroupName `
    -Path "SQLSERVER:\SQL\$sql1ServerName\Default" `
    -AvailabilityReplica @($primaryReplica,$secondaryReplica) `
    -Database $dbs
	
##########################################################
#Step: 3
#Description: Run this to join each replica to the availability group
#             Note: ensure you replace $sql2ServerName with the name of the other replicas
#Run On: Master
##########################################################
Join-SqlAvailabilityGroup `
    -Path "SQLSERVER:\SQL\$sql2ServerName\Default" `
    -Name $availabilityGroupName
Add-SqlAvailabilityDatabase `
    -Path  "SQLSERVER:\SQL\$sql2ServerName\Default\AvailabilityGroups\$availabilityGroupName" `
-Database $dbs

At this stage, your Always On configuration is complete! You should be able to connect to each server and perform manual failovers throught the AlwaysOn. However we have a few more steps to complete before fully automating the failover and adding the listener.

Fix the mismatching user SIDs across all nodes

When backing up and restoring databases from one server to another, you end up with database users taht

--########################################################
--Step: 1
--Description: Extract the user account(s) from master. Open SSMS and run the following query to 
--install the stored procedure
--Run On: Master
--########################################################

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE  [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
		SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin 
		FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) 	
		WHERE p.type = 'S'
		AND p.is_disabled <> 1
		AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
		SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin 
		FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) 
		WHERE p.type = 'S'
		AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    SELECT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

--########################################################
--Step: 2
--Description: Execute the stored procedure on master and copy the sql statement for the appropriate user(s)
--Run On: Master
--########################################################
exec sp_help_revlogin;

--########################################################
--Step: 3
--Description: Open SSMS and run the command you copied on the previous step for all appropriate users
--Run On: All replicas
--########################################################

--########################################################
--Step: 4
--Description: Check that everything is correct.
-- All of these SIDs should match, Login on Master, 
-- Login on Secondary, User on the database.
--Run On: All replicas
--########################################################
SELECT * from master..syslogins 
SELECT * FROM sys.database_principals where type = 'S' and principal_id not in (0,1,2,3,4)

Configure the Availability Group Listener

And now, the important part. The listener is necessary to ensure that your applications only use one connection point and are agnostic of the underlying SQL setup.

Copy and paste and run the following code:

##########################################################
#Step: 1
#Description: Check that KB2854082 is installed (it should be if you picked up the most up-to-date server image)
#Details: This is a Windows Server 2012 patch necessary for the listener.
#Run On: All Availability Group nodes
##########################################################

##########################################################
#Step: 2
#Description: Download and install Azure PowerShell on the server
#             http://www.windowsazure.com/en-us/downloads/
#Run On: Master
##########################################################

##########################################################
#Step: 3
#Description: Start an elevated Azure Powershell session
#             and Import your Azure Account Settings
#             Note: this will open a browser. Authenticate and download the file
#Run On: Master
##########################################################
Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"
Get-AzurePublishSettingsFile

##########################################################
#Step: 4
#Description: Import your Azure settings file and set the default subscription
#             Note: change the value of $subscriptionName as necessary. The 
#                   <publishSettingsFilePath> is the fully qualified path to the file
#                   you downloaded on step 3
#Run On: Master
##########################################################
Import-AzurePublishSettingsFile c:\temp\azure.publishsettings
$subscriptionName = " My subscription"
Select-AzureSubscription -SubscriptionName $subscriptionName

##########################################################
#Step: 5
#Description: Define the variables
#Run On: Master
##########################################################
$AGNodes = "server1", "server2"  # Add more AG nodes here
$AGName = "MyAvailabilityGroup"
$EndpointName = "MyEndpoint"
$EndpointPort = "60001"                        # Public port to use for the endpoint
$ServiceName = "my-service-name"
$ListenerName = "MyAGListener"               # The name of the AG Listener

##########################################################
#Step: 6
#Description: Open the firewall to allow connection over the probe port (59999)
#Run On: Master
##########################################################
netsh advfirewall firewall add rule name='Load Balance Probe (TCP-In)' localport=59999 dir=in action=allow protocol=TCP

##########################################################
#Step: 7
#Description: Open the firewall to allow connection over the probe port (59999)
#Run On: All Replicas
##########################################################
netsh advfirewall firewall add rule name='Load Balance Probe (TCP-In)' localport=59999 dir=in action=allow protocol=TCP

##########################################################
#Step: 8
#Description: Add a new endpoint to the AG node to allow connectiong on the probe port
#Run On: Master
##########################################################
Get-AzureVM -ServiceName $ServiceName -Name $AGNodes[0] | Add-AzureEndpoint -Name $EndpointName `
   -Protocol "TCP" -PublicPort $EndpointPort -LocalPort 1433 -LBSetName "$EndpointName-LB" `
   -ProbePort 59999 -ProbeProtocol "TCP" -DirectServerReturn $true | Update-AzureVM
   
##########################################################
#Step: 9
#Description: Add a new endpoint to the AG node to allow connectiong on the probe port
#Run On: Master
##########################################################
Get-AzureVM -ServiceName $ServiceName -Name $AGNodes[1] | Add-AzureEndpoint -Name $EndpointName `
   -Protocol "TCP" -PublicPort $EndpointPort -LocalPort 1433 -LBSetName "$EndpointName-LB" `
   -ProbePort 59999 -ProbeProtocol "TCP" -DirectServerReturn $true | Update-AzureVM
   
#Edit the $AGNodes[] variable and run for all other nodes then exit the Azure Powershell issueing the command below
#Get-AzureVM -ServiceName $ServiceName -Name $AGNodes[2] | Add-AzureEndpoint -Name $EndpointName `
#  -Protocol "TCP" -PublicPort $EndpointPort -LocalPort 1433 -LBSetName "$EndpointName-LB" `
# -ProbePort 59999 -ProbeProtocol "TCP" -DirectServerReturn $true | Update-AzureVM
exit

##########################################################
#Step: 10
#Description: Start a new elevated PowerShell session and import the Cluster cmdlets
#Run On: Master
##########################################################
Add-WindowsFeature "RSAT-Clustering-PowerShell" | Out-Null

##########################################################
#Step: 11
#Description: Set the variables
#Run On: Master
##########################################################
$AvailabilityGroupName = "MyAvailabilityGroup"
$EndpointPort = "60001"
$ServiceName = "my-service-name"
$ListenerName = "MyAGListener"
$AGService = Get-AzureService -ServiceName $ServiceName
$IPAddress = (Resolve-DnsName -Name "$ServiceName.cloudapp.net").IPAddress
$ClusterNetworkName = "Cluster Network 1" # the cluster network name
$affinityGroupName = "MyAffinityGroup"            
$availabilityGroupName = "MyAvailabilityGroup"  # The name used by the AG
$availabilitySetName = "MYSQLHADR"              # The name of the AG set
$clusterName = "TestCluster"				      # The windows cluster name that will support the AG
$dcServerName = "ad-server"                      # The Domain Controller/Active Directory server name
$dcServiceName = "my-ad"                        # The Azure Service for the DC/AD server
$domainName= "test"                         # The Active Directory Domain name
$FQDN = "test.net"                          # The fully qualified Domain name
$location = "West EU"                             # The datacenter location
$quorumServerName = "server0"              # The name of the Quorum Cluster node - used for backups and filesharing
$sqlServiceName = "my-service-name"                   # The service name for the Cluster and SQL AG
$sql1ServerName = "server1"                # The name of the master SQL Server node
$sql2ServerName = "server2"                # The name of the replica SQL Server node
$storageAccountName = "my-storage-account"                 # Location for storing vhds - needs to be all lowercase and < 20 chars
$subnetName = "Cluster"                           # The name of the Subnet that will be used by the Cluster/AG
$virtualNetworkName = "myVn"               # 
$vmAdminUser = "ClusterAdmin"                 # The domain admin user running the Cluster
$vmAdminPassword = "AVerySecurePasswordHere"                 # The domain admin password
$vmSQLServiceAccount = "SQLSvcAdmin"               # The SQL Service domain account

##########################################################
#Step: 12
#Description: Get your Azure settings file (credentials, permissions etc)
#Details: This will open a browser window to allow you to log in to your Azure account. 
#         Then, it will prompt you to save the settings file locally to your machine. 
#         Make sure you use an easy-to-find location
#Run On: Local Machine
##########################################################
Get-AzurePublishSettingsFile

##########################################################
#Step: 13
#Description: Run the following command. Ensure you replace <publishsettingsfilepath> 
#             with the location that you used on step 3
#Run On: Local Machine
##########################################################
Import-AzurePublishSettingsFile <publishsettingsfilepath>

##########################################################
#Step: 14
#Description: Select the subscription you will work with. 
#             Use the command below to enumerate all subscriptions available to your account:
#Run On: Local Machine
##########################################################
Get-AzureSubscription | Select SubscriptionName

##########################################################
#Step: 15
#Description: Set your Powershell session to the right subscription. Change the subscription name as necessary
#Run On: Local Machine
##########################################################
$subscriptionName = "My Subscription"
Select-AzureSubscription -SubscriptionName $subscriptionName

##########################################################
#Step: 16
#Description: Configure the default storage account to use with the subscription
#Run On: Local Machine
##########################################################
Set-AzureSubscription `
	-SubscriptionName $subscriptionName `
    -CurrentStorageAccountName $storageAccountName
	
##########################################################

##########################################################
#Step: 17
#Description: Add the listener as a cluster IP Address resource
#Run On: Master
##########################################################
Add-ClusterResource -Name "IP Address $IPAddress" -ResourceType "IP Address" `
   -Group $AvailabilityGroupName | Set-ClusterParameter `
   -Multiple @{"Address"="$IPAddress";"ProbePort"="59999";"SubnetMask"="255.255.255.255"; `
   "Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}
   
##########################################################
#Step: 18
#Description: Add the listener as a cluster Network Name resource 
#Run On: Master
##########################################################
Add-ClusterResource -Name $ListenerName -ResourceType "Network Name" `
   -Group $AvailabilityGroupName | Set-ClusterParameter `
   -Multiple @{"Name"=$ListenerName;"DnsName"=$ListenerName}
   
##########################################################
#Step: 19
#Description: Start up the listener
#Run On: Master
##########################################################
Get-ClusterResource -Name $ListenerName | Set-ClusterResourceDependency "[IP Address $IPAddress]"
Start-ClusterResource -Name $ListenerName

##########################################################
#Step: 20
#Description: Set the cluster's dependency to the new listener
#Run On: Master
##########################################################
Get-ClusterResource -Name $AvailabilityGroupName | Set-ClusterResourceDependency "[$ListenerName]"

##########################################################
#Step: 21
#Description: Configure the SQL Server Availability Group to use the new listener
#Run On: Master
##########################################################
Set-SqlAvailabilityGroupListener -Path SQLSERVER:\SQL\$env:COMPUTERNAME\DEFAULT\AvailabilityGroups\$AvailabilityGroupName\AvailabilityGroupListeners\$ListenerName -Port $EndpointPort -ErrorAction Stop | Out-Null

Finally you need to add the custom port number to the Listener in SQL. To achieve this, follow the steps below:

  • Log in to your master server
  • Start SSMS and connect to Master.
  • Navigate to Master => AlwaysOn High Availability => Availability Groups => MyAvailabilityGroup => Availability Group Listeners.
  • You should now see the listener name that you created in Failover Cluster Manager.
  • Right-click the listener name and click Properties.
    -In the Port box, specify the endpoint's port number (should be 60001 unless you used a different port) for the availability group listener in the Port box, then click OK.

Testing the Availability Group##

Perform a manual failover###

  • Log in to the master server
  • In SSMS expand AlwaysOn High Availability => Availability Groups => [Your availability Group Name]
  • Right click on [Your availability Group Name] and select ‘Failover’
  • Go through the wizard and ensure that the failover works

Perform an automatic failover###

  • Log in to the master server
  • Stop the SQL Service, either using the Services snap-in or the SQL Server Configuration console
  • The replica with the highest priority should become the new Primary
  • Restart SQL Service on the old primary server
    -The databases which are part of the availability group should now display Synchronized

Test the listener connectivity from a server outside the cluster###

NOTE: he Endpoint name is either the DNS name of the cluster service or the Public IP address of the service i.e my-service.cloudapp.net or 192.168.1.234

On any server with SSMS installed use the following steps:

  • Open SSMS
  • Connect to the endpoint using “EndpoingName”,”EndpointPort” as the server name e.g. 192.169.1.1,60001
  • For authentication select “SQL Server Authentication”
  • For username enter the SQL Server login name
  • For password enter the SQL Server login password

BIG CAVEAT: in my effort to anonymize the scripts, I may have inadvertedly introduced errors. Ensure you double and triple-check your variables throughout the script execution!

Happy coding...

  • Share this post on