Deploy a Microsoft SQL Server Always On availability group in a single subnet
Contributed by Google employees.
In this tutorial, you learn how to deploy the Microsoft SQL Server database engine in an Always On availability group configuration in a single subnet.
Objectives
- Install Microsoft SQL Server in Always On availability group configuration using a single subnet.
- Set up a VPC network with a Windows domain controller.
- Create two Windows SQL Server VM instances to act as cluster nodes.
- Set up an internal load balancer to direct traffic to the active node.
- Test the failover operation to verify that the cluster is working.
Costs
This tutorial uses billable components of Google Cloud, including the following:
- Compute Engine
- SQL Server licensing through a premium image
Use the pricing calculator to generate a cost estimate based on your projected usage.
Before you begin
For this tutorial, you need a Google Cloud project. You can create a new one, or you can select a project that you already created.
When you finish this tutorial, you can avoid continued billing by deleting the resources that you created. For details, see the "Cleaning up" section at the end of this tutorial.
Overview
SQL Server Always On availability groups allow users to deploy highly available SQL Server databases with automated failover. Always On availability groups are commonly deployed using multiple network subnets in Google Cloud. However, sometimes you need to deploy in a single subnet configuration. For example, your network design may have been planned to only have one subnet per region, and adding new subnets might be difficult.
This tutorial is based in part on SQL Server failover cluster instance setup and SQL Server multi-subnet Always On availability groups.
Create and configure a Windows domain controller
In this tutorial, you use an exisiting default VPC network.
An Active Directory domain is used for domain name services and Windows Failover Clustering, which is used by Always On availabilty groups.
Having the AD domain controller in the same VPC network is not a requirement, but is a simplification for the purpose of this tutorial.
It is possible to use Managed Service for Microsoft Active Directory, but it takes an hour to initialize, so this tutorial uses a virtual machine (VM) for AD.
In this tutorial, the domain is gontoso.com
. The domain controller VM name is dc-windows
. By default, the Windows computer name matches the VM name,
dc-windows
. The VM is created in the us-central1
default subnet, with the IP address 10.128.0.3
.
In Cloud Shell, create a VM to use as the domain controller:
gcloud compute instances create "dc-windows" \ --zone "us-central1-a" \ --machine-type "n1-standard-2" \ --private-network-ip "10.128.0.3" \ --can-ip-forward \ --image-family "windows-2016" \ --image-project "windows-cloud" \ --boot-disk-size "200" \ --boot-disk-type "pd-standard" \ --boot-disk-device-name "dc-windows" \ --metadata sysprep-specialize-script-ps1="Install-WindowsFeature -Name AD-Domain-Services -IncludeManagementTools;"
Generate a password so that you can connect to the domain controller VM using a local account. Note the username and password for future use.
Using RDP, connect to the domain controller VM with your local account username and password.
In PowerShell with administrator privileges, set the following variables, which are used to configure the Active Directory forest:
$DomainName = "gontoso.com"; $DomainMode = "Win2012R2"; $ForestMode = "Win2012R2"; $DatabasePath = "C:\Windows\NTDS"; $LogPath = "C:\Windows\NTDS"; $SysvolPath = "C:\Windows\SYSVOL";
Set the local
Administrator
account password:net user Administrator *
Install and configure the Active Directory forest:
net user Administrator /active:yes Install-ADDSForest -CreateDnsDelegation:$false -DatabasePath $DatabasePath -LogPath $LogPath -SysvolPath $SysvolPath -DomainName $DomainName -DomainMode $DomainMode -ForestMode $ForestMode -InstallDNS:$true -NoRebootOnCompletion:$false -SafeModeAdministratorPassword ((Get-Credential Administrator).Password) -Force:$true;
When this command prompts you for credentials, use the
Administrator
username and password that you created in a previous step.
Create the Always On cluster's VMs
In Cloud Shell, create two SQL Server VMs:
gcloud compute instances create "node-1" \ --zone "us-central1-f" \ --machine-type "n1-standard-2" \ --subnet "default" \ --private-network-ip "10.128.0.4" \ --image-family "sql-ent-2016-win-2016" \ --image-project "windows-sql-cloud" \ --boot-disk-size "200" \ --boot-disk-type "pd-ssd" \ --boot-disk-device-name "node-1" \ --metadata enable-wsfc=true,sysprep-specialize-script-ps1="Install-WindowsFeature Failover-Clustering -IncludeManagementTools;" gcloud compute instances create "node-2" \ --zone "us-central1-a" \ --machine-type "n1-standard-2" \ --subnet "default" \ --private-network-ip "10.128.0.5" \ --image-family "sql-ent-2016-win-2016" \ --image-project "windows-sql-cloud" \ --boot-disk-size "200" \ --boot-disk-type "pd-ssd" \ --boot-disk-device-name "node-2" \ --metadata enable-wsfc=true,sysprep-specialize-script-ps1="Install-WindowsFeature Failover-Clustering -IncludeManagementTools;"
Create a file share witness
To provide a tie-breaking vote and achieve a quorum for the failover scenario, create a file share that will act as a witness. For the purposes of this tutorial, you create the file share witness on the domain controller VM.
In a production environment, you should create the witness file share on one of the following:
- Separate single node in the third zone (
us-central-b
in this scenario) - Highly available file share, if the SQL Server node's zone is used
Using RDP, connect to the domain controller VM,
dc-windows
, with thegontoso.com\Administrator
account.If you are using Chrome RDP for Google Cloud, in the Options menu, under the Certificates list, delete the existing RDP certificates for these addresses.
Open PowerShell as administrator.
Create the witness folder:
New-Item "C:\QWitness" –type directory
Share the folder:
New-SmbShare -Name "QWitness" -Path "C:\QWitness" -Description "SQL File Share Witness" -FullAccess "Authenticated Users"
Configuring the cluster VMs
Configure the first node
- Generate a password for
node-1
. Note the username and password for future use. - Connect to
node-1
using RDP. - Open PowerShell as administrator.
Add a firewall rule to open a port for the health check service:
netsh advfirewall firewall add rule name="Open port for Health Check" dir=in action=allow protocol=TCP localport=59997
This tutorial uses
tcp:59997
. You can change this to a different port, but it must match the health check port that you define later. The health check process periodically pings the agent on each cluster node to determine its availability.Set the DNS to the domain controller:
netsh interface ip set dns Ethernet static 10.128.0.3
Open Windows firewall ports for the SQL Server availability group:
netsh advfirewall firewall add rule name="5022 for Avail Groups" dir=in action=allow protocol=TCP localport=5022 netsh advfirewall firewall add rule name="1433 for SQL Server" dir=in action=allow protocol=TCP localport=1433
Add the node to the Active Directory domain that you created earlier:
Add-Computer -DomainName gontoso.com -Restart -Force -Credential gontoso.com\Administrator
When you are prompted for credentials, use the administrator username and password that you set when you configured the domain controller VM in a previous step.
The machine will restart.
Use RDP to connect to the SQL Server instance by using the credentials for the
gontoso.com\Administrator
account.Create a new folder for database backups and share it:
New-Item -ItemType directory -Path C:\SQLBackup New-SMBShare -Name SQLBackup -Path C:\SQLBackup -FullAccess "Authenticated Users"
Configure the second node
- Generate a password for
node-2
. Note the username and password for future use. - Connect to
node-2
using RDP. - Open PowerShell as administrator.
Add a firewall rule to open a port for the health check service:
netsh advfirewall firewall add rule name="Open port for Health Check" dir=in action=allow protocol=TCP localport=59997
Set the DNS to the domain controller:
netsh interface ip set dns Ethernet static 10.128.0.3
Open Windows firewall ports for the SQL Server availability group:
netsh advfirewall firewall add rule name="5022 for Avail Groups" dir=in action=allow protocol=TCP localport=5022 netsh advfirewall firewall add rule name="1433 for SQL Server" dir=in action=allow protocol=TCP localport=1433
Add the node to the Active Directory domain that you created earlier:
Add-Computer -DomainName gontoso.com -Restart -Force -Credential gontoso.com\Administrator
When you are prompted for credentials, use the Administrator username and password that you set when you configured the domain controller VM in a previous step.
The machine will restart.
Configure the Failover Cluster Manager
In this section, you enable failover clustering on the instances in your availability group, configure one instance to act as the Failover Cluster Manager, and enable Always On high availability on all instances in the group.
Reconnect to
node-1
using RDP usinggontoso.com\Administrator
.Open PowerShell as an administrator and create a Windows Server Failover Cluster (WSFC):
$node1 = "node-1" $node2 = "node-2" $nameWSFC = "cluster-dbclus" #Name of cluster $ipWSFC = "10.128.0.10" #This should un-utilized / free ip New-Cluster -Name $nameWSFC -Node $node1, $node2 -NoStorage -StaticAddress $ipWSFC
Enable Always On high availability for both nodes in the cluster:
Enable-SqlAlwaysOn -ServerInstance $node1 -Force Enable-SqlAlwaysOn -ServerInstance $node2 -Force
Add the file share witness:
Set-ClusterQuorum -FileShareWitness \\dc-windows\QWitness
Create the availability group
Create a test database and configure it to work with a new availability group. Alternatively, you can specify an existing database for the availability group.
Reconnect to
node-1
using RDP usinggontoso.com\Administrator
.If you do not have a database configured already, create a test database:
osql -E -Q "CREATE DATABASE TestDB" osql -E -Q "ALTER DATABASE [TestDB] SET RECOVERY FULL" osql -E -Q "BACKUP DATABASE [TestDB] to disk = 'C:\SQLBackup\TestDB.bak' with INIT"
Create a database mirrioring endpoint (required for Always On availability groups) in each SQL Server node:
osql -S node-1 -E -Q "CREATE ENDPOINT [aodns-hadr] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING ( ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES );” osql -S node-2 -E -Q "CREATE ENDPOINT [aodns-hadr] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING ( ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES );”
Create the availability group:
osql -S node-1 -E -Q "CREATE AVAILABILITY GROUP [sql-ag] FOR DATABASE TestDB REPLICA ON N'node-1' WITH (ENDPOINT_URL = N'TCP://node-1.gontoso.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO), SEEDING_MODE = AUTOMATIC), N'node-2' WITH (ENDPOINT_URL = N'TCP://node-2.gontoso.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO), SEEDING_MODE = AUTOMATIC);”
Join secondary node
node-2
to the availability group:osql -S node-2 -E -Q "ALTER AVAILABILITY GROUP [sql-ag] JOIN" osql -S node-2 -E -Q "ALTER AVAILABILITY GROUP [sql-ag] GRANT CREATE ANY DATABASE”
Create the availability group listener:
osql -S node-1 -E -Q “USE [master] ALTER AVAILABILITY GROUP [sql-ag] ADD LISTENER N'sql-listner' (WITH IP ((N'10.128.0.20', N'255.255.252.0')) , PORT=1433);”
The listener must be created with an unused IP address before creating the internal load balancer. Later, the same IP address is allocated to the internal load balancer. If SQL Server detects that the IP address is already in use, then this command to create the listener fails.
Create the Windows Server Failover Cluster health check:
$cluster_network_name = 'Cluster Network 1' $ip_resource_name = 'sql-ag_10.128.0.20' $load_balancer_ip = '10.128.0.20' [int]$health_check_port = 59997 Get-ClusterResource $ip_resource_name | Set-ClusterParameter -Multiple @{ 'Address'=$load_balancer_ip; 'ProbePort'=$health_check_port; 'SubnetMask'='255.255.240.0'; 'Network'=$cluster_network_name; 'EnableDhcp'=0; } #restart cluster resource group Stop-ClusterGroup 'sql-ag' Start-ClusterGroup 'sql-ag'
This makes the primary node accept connections on port
59997
, which is used by the internal load balancer for health checks.
Create an internal load balancer
An internal load balancer provides a single IP address for SQL Server. The load balancer listens for requests and routes network traffic to the active cluster node. It knows which node is the active node because a health check runs for each node. Only the active node responds as healthy. If the active node goes down, then the other Always On availability group node activates. The health checker receives the signal, and traffic is redirected to the other node.
Create two instance groups, and add one SQL Server node to each group:
gcloud compute instance-groups unmanaged create wsfc-group-f --zone us-central1-f gcloud compute instance-groups unmanaged add-instances wsfc-group-f --instances node-1 --zone us-central1-f gcloud compute instance-groups unmanaged create wsfc-group-a --zone us-central1-a gcloud compute instance-groups unmanaged add-instances wsfc-group-a --instances node-2 --zone us-central1-a
These instance groups act as backends that the load balancer can direct traffic to.
Create a health check that the load balancer can use to determine which is the active node:
gcloud compute health-checks create tcp sql-healthcheck \ --check-interval="2s" \ --healthy-threshold=1 \ --unhealthy-threshold=2 \ --port=59997 \ --timeout="1s"
Add a firewall rule to allow the health check:
gcloud compute firewall-rules create allow-health-check \ --source-ranges 130.211.0.0/22,35.191.0.0/16 \ --allow tcp
Create one backend service and add the two backend instance groups:
gcloud compute backend-services create wsfcbackend \ --load-balancing-scheme internal \ --region us-central1 \ --health-checks sql-healthcheck \ --protocol tcp gcloud compute backend-services add-backend wsfcbackend \ --instance-group wsfc-group-a \ --instance-group-zone us-central1-a \ --region us-central1 gcloud compute backend-services add-backend wsfcbackend \ --instance-group wsfc-group-f \ --instance-group-zone us-central1-f \ --region us-central1
Create an internal load balancer to forward requests to the IP address of the listener for the active node in the Always On availability group:
gcloud compute forwarding-rules create wsfc-forwarding-rule \ --load-balancing-scheme internal \ --ports 1433 \ --region us-central1 \ --backend-service wsfcbackend \ --address 10.128.0.20
By design, the internal load balancer will show only one instance group healthy at any time.
Simulate failure to test failover
To simulate failure, execute this SQL query on the secondary node to make it primary:
osql -S node-2 -E -Q "ALTER AVAILABILITY GROUP [sql-ag] FAILOVER;"
The internal load balancer starts pointing to this node automatically.
Another way to test the failover mechanism is to shut down or reset the primary node.
Cleaning up
The easiest way to avoid incurring charges to your Google Cloud account for the resources used in this tutorial is to delete the project that you created for the tutorial.
You can delete a project with the following command, where [PROJECT_ID]
is your Google Cloud project ID:
gcloud projects delete [PROJECT_ID]
What's next
- Deploying Microsoft SQL Server for multi-regional disaster recovery
- Try out other Google Cloud features for yourself. Have a look at our tutorials.
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.