Step by step process of setting up geographically distributed SQL HA cluster on Azure

Prerequisites:

2 VMs setup with SQL 2016 in different geo regions and 2 different VNETs.

Instructions:

Create connection between 2 VNETs. There is 2 ways to do that. VNET peering and VNET gateway. Example below is using VNET peering

  • Go to CanadaCentral VNET and click on peering and click “Add”

  • Create VNET peering between CanadaCentral and US West Central

  • Do the same from US West Central VNET

  • Verify connectivity between subnets by logging to SQLCanada and testing connection to port 1433 of SQLWestCentral
PS C:\Users\cloudadmin> Test-NetConnection 10.1.0.6 -port 1433

ComputerName : 10.1.0.6
RemoteAddress : 10.1.0.6
RemotePort : 1433
InterfaceAlias : Ethernet
SourceAddress : 10.0.4.4
TcpTestSucceeded : True

Enable SQL Always On High Availability Group

Create failover cluster

  • Install Failover cluster tools and features on Windows on both SQL servers
PS C:\Users\cloudadmin> Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools

Success Restart Needed Exit Code Feature Result
------- -------------- --------- --------------
True No Success {Failover Clustering, Remote Server Admini...
  • Install DNS service on both nodes (external DNS service can be used as well)
PS C:\Users\cloudadmin> install-windowsfeature -Name dns -IncludeManagementTools
WARNING: The following recommended condition is not met for DNS: No static IP addresses were found on this computer. If
the IP address changes, clients might not be able to contact this server. Please configure a static IP address on this
computer before installing DNS Server.

Success Restart Needed Exit Code Feature Result
------- -------------- --------- --------------
True No Success {DNS Server, DNS Server Tools, Role Admini...
  • Open DNS management tool on SQLCanada and create a new primary zone

Name it anything you want, in my case it’s “cluster.local”

Enable “non secure dynamic updates”

Right click on properties of newly created zone and switch to “Zone Transfers Tab” and click to allow transfer “to any server”

Go to Virtual Network of CanadaCentral and click on DNS servers tab/Choose custom and enter internal IP addresses of SQLCanada server and SQLWestCentral

Do the same for USWestCentral VNET

Reboot both SQL servers for new DNS servers to take effect

Create DNS zone on SQLWestCentral, right click on “Forward Look up zone/New Zone”, choose “Secondary as Type”

Enter zone name

Put IP address of SQLCanada

Finish wizard and you will see zone with records in it

  • Open failover cluster manager on SQLCanada

  • Choose “Create Cluster”

  • Enter server name in wizard when prompted

  • Run through verify cluster configuration wizard
  • If everything is successful, choose a cluster name

  • Wizard shall be completed now

Configure cloud witness

  • Create storage account in any region in Azure

  • Go to “Access Keys” and copy storage account name as well as Key

  • Go back Failover Cluster Manager, right click on cluster and choose “Configure Cluster Forum Settings”

  • Choose “Select Quorum Witness” in wizard

  • Choose “configure a cloud witness”

  • Paste in values you copied from storage account settings page earlier. Finish wizard

Configure SQL HAG

  • On both SQLWestCentral and SQLCanada server add the same DNS suffix in properties for computer

  • Configure static IP addresses on both computers and in Azure portal

Example below from SQLWestCentral

  • Add SQLWestCentral server to Failover cluster. Go to Failover Cluster Manager and choose “Add node”

  • Run the validation steps and make sure there are no errors reported (ignore Active Directory errors)
  • Add node to cluster. End result shall show both nodes online and healthy

Create certificates for mirroring endpoint

Since servers are not domain joined you would need to use certificate based authentication between instances.

  • On SQLCanada server executed following TSQL
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
  • Create Encrypted certificate in master database
USE master;
CREATE CERTIFICATE SQLCanada
WITH SUBJECT = 'HOST_A certificate for database mirroring',
EXPIRY_DATE = '11/30/2113';
GO
  • Delete existing endpoint if it exists
SELECT name
FROM sys.database_mirroring_endpoints;
DROP endpoint Hadr_endpoint
  • Create new endpoint based off certificate
CREATE ENDPOINT Endpoint_Mirroring
 STATE = STARTED
 AS TCP (
 LISTENER_PORT=7024
 , LISTENER_IP = ALL
 )
 FOR DATABASE_MIRRORING (
 AUTHENTICATION = CERTIFICATE SQLCanada
 , ENCRYPTION = REQUIRED ALGORITHM AES
 , ROLE = ALL
 );
 GO
  • Export certificate and copy to server SQLWestCentral
BACKUP CERTIFICATE SQLcanada TO FILE = 'C:\sqlcanada.cer';
  • Perform similar steps above on SQLWestCentral (drop existing endpoint on it first with `DROP endpoint hadr_endpoint`)
USE master;
--Create the database Master Key, if needed.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''; 
GO
-- Make a certifcate on sqlwestcentral server instance.
CREATE CERTIFICATE sqlwestcentral
WITH SUBJECT = 'HOST_B certificate for database mirroring',
EXPIRY_DATE = '11/30/2113';
GO
--Create a mirroring endpoint for the server instance on sqlwestcentral .
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE sqlwestcentral
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
--Backup sqlwestcentral certificate.
BACKUP CERTIFICATE sqlwestcentral TO FILE = 'C:\sqlwestcentral.cer';
GO
--Using any secure copy method, copy C:\sqlwestcentral.cer to SQLcanada.
  • On SQLCanada create login for SQLWestCentral
USE master;
CREATE LOGIN SQLWestCentral_login
WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
  • Create user for that login
USE master;
 CREATE USER SQLWestCentralUser FOR LOGIN SQLWestCentral_login;
 GO
  • Associate certificate you exported from SQLWestCentral login with this user
USE master;
CREATE CERTIFICATE SQLWestcentral_cert
AUTHORIZATION sqlwestcentraluser
FROM FILE = 'C:\sqlwestcentral.cer'
GO
  • Grant CONNECT permission to login to remote mirroring endpoint
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SQLWestCentral_login];
GO
  • Perfom similar steps on SQLWestCentral
USE master;
--On SQLWestCentral, create a login for SQLCanada.
CREATE LOGIN SQLCanada_login WITH PASSWORD = 'AStrongPassword!@#';
GO
--Create a user, SQLCanada_user, for that login.
CREATE USER SQLCanada_user FOR LOGIN SQLCanada_login
GO
--Asscociate this SQLCanada with the user, SQLCanada_user.
CREATE CERTIFICATE SQLCanada_cert
AUTHORIZATION SQLCanada_user
FROM FILE = 'C:\sqlcanada.cer';
GO
--Grant CONNECT permission for the server instance on SQLcanada.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO SQLCanada_login
  • Run following TSQL on both servers to allow local system account access to create High Availabilty group
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO
  • Go to SQL Server configuration manager and restart SQL server, then right click on server and go to properties/AlwaysOn High Availability on both servers

  • Configure SQL Server to run as “Local System” on both nodes

  • Open SSMS and restore your DB as usual

  • Go to Always On High Availability setting and choose “New Availability Group Wizard”

  • Select DB

  • Click “Add Replica”

  • Accept defaults on next screen

  • If everything is correct your new HAG as well Database SQLHA shall appear under Always On High Availability on both servers

  • You can monitor health of high availability group by right clicking on group and choosing “Show Dashboard”

  • You can use script below to verify connectivity and health of replication. It periodically checks same query executed against sql cluster

import-module sqlserver
function QueryDB{
$mirrorConnString = "Data Source=sqlcanada;Failover Partner=sqlwestcentral;Initial Catalog=sqlha;user id=contained_user;password=A1234567890!;"
(Invoke-Sqlcmd -Query "SELECT @@SERVERNAME" -ConnectionString $mirrorConnString -AbortOnError:$false).Column1 + " " + (Invoke-Sqlcmd -Query "SELECT COUNT(*) AS Count FROM ID" -ConnectionString $mirrorConnString).Count
}
while ($true)
{
QueryDB
start-sleep 3
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s