Stay organized with collections Save and categorize content based on your preferences.
Microsoft SQL Server Always On availability groups let you replicate databases across multiple SQL Server Enterprise instances.
Similar to SQL Server Failover Cluster Instances, Always On availability groups use Windows Server Failover Clustering (WSFC) to implement high availability. However, the two features differ in the following ways:
Always On availability groups Failover cluster instances Scope of fail-over Group of databases Instance Storage Not shared SharedFor a more detailed comparison, see Comparison of failover cluster instances and availability groups.
Always On availability groups support multiple availability modes. This tutorial shows how you can deploy Always On availability groups in synchronous commit mode to implement high availability for one or more databases.
In the setup, you will create three VM instances. Two VM instances, node-1 and node-2 serve as cluster nodes and run SQL Server. A third VM instance, witness, is used to achieve a quorum in a failover scenario. The three VM instances are distributed over three zones and share a common subnet.
Using a SQL Server Always On availability group, an example database, bookshelf, is synchronously replicated across the two SQL Server instances.
In an on-premises Windows cluster environment, Address Resolution Protocol (ARP) announcements trigger IP address failover. Google Cloud, however, disregards ARP announcements. Consequently, you must implement one of the following two options: using an internal load balancer and a distributed network name (DNN).
The article assumes that you have already deployed Active Directory on Google Cloud and that you have basic knowledge of SQL Server, Active Directory, and Compute Engine. For more information about Active Directory on Google Cloud, see section Before you begin.
Using a SQL Server Always On availability group, an example database, bookshelf, is synchronously replicated across the two SQL Server instances. An internal load balancer ensures that traffic is directed to the active node.
For more information about Windows Server Failover Clustering with an internal load balancer, see failover clustering.
This diagram includes the following:
node-1 and node-2. One hosts the primary replica of the SQL Server database while the other node hosts the secondary replica.witness serves as a file share witness to provide a tie-breaking vote and achieve a quorum for failover.This tutorial aims to achieve the following objectives:
This tutorial uses billable components of Google Cloud, including:
Use the pricing calculator to generate a cost estimate based on your projected usage.
Before you beginTo complete the tasks in this tutorial, ensure the following:
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.Verify that billing is enabled for your Google Cloud project.
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.Verify that billing is enabled for your Google Cloud project.
setupadmin user. For more information about Active Directory user account provisioning, see Active Directory user account provisioningTo deploy your SQL Server Always On availability groups, you must prepare your Google Cloud project and VPC for the deployment. The following sections discuss how you can do this in detail.
Configure your project and regionTo prepare your Google Cloud project for the deployment of SQL Server Always On availability groups, do the following:
In the Google Cloud console, open Cloud Shell by clicking the Activate Cloud Shell button.
Initialize the following variables.
VPC_NAME=VPC_NAMESUBNET_NAME=SUBNET_NAME
Replace the following:
VPC_NAME: name of your VPCSUBNET_NAME: name of your subnetSet your default project ID.
gcloud config set project PROJECT_ID
Replace PROJECT_ID with the ID of your Google Cloud project.
Set your default region.
gcloud config set compute/region REGION
Replace REGION with the ID of the region you want to deploy in.
To allow clients to connect to the SQL Server and the communication between the cluster nodes you need to create several firewall rules. You can use network tags to simplify the creation of these firewall rules, as follows:
wsfc-node tag.witness) are annotated with the wsfc tag.To create firewall rules that use these network tags, use the following steps:
Create firewall rules to allow traffic between cluster nodes.
SUBNET_CIDR=$(gcloud compute networks subnets describe $SUBNET_NAME --format=value\('ipCidrRange'\))
gcloud compute firewall-rules create allow-all-between-wsfc-nodes \
--direction=INGRESS \
--action=allow \
--rules=tcp,udp,icmp \
--enable-logging \
--source-tags=wsfc \
--target-tags=wsfc \
--network=$VPC_NAME \
--priority 10000
gcloud compute firewall-rules create allow-sql-to-wsfc-nodes \
--direction=INGRESS \
--action=allow \
--rules=tcp:1433 \
--enable-logging \
--source-ranges=$SUBNET_CIDR \
--target-tags=wsfc-node \
--network=$VPC_NAME \
--priority 10000
Create and deploy two VM instances for the failover cluster. At any point in time, one of these VMs hosts the primary replica of the SQL Server database while the other node hosts the secondary replica. The two VM instances must:
You use a SQL Server premium image which has SQL Server 2022 preinstalled.
Note: If you plan to bring your own licenses for SQL Server by using the License Mobility program, select Windows Server base images for these nodes and install SQL Server using your own product keys.To provide a tie-breaking vote and achieve a quorum for the failover scenario, deploy a third VM that serves as a file share witness using the following steps:
Create a specialized script for the WSFC nodes. This script installs the necessary Windows features and creates firewall rules for WSFC and SQL Server.
cat << "EOF" > specialize-node.ps1 $ErrorActionPreference = "stop" # Install required Windows features Install-WindowsFeature Failover-Clustering -IncludeManagementTools Install-WindowsFeature RSAT-AD-PowerShell # Open firewall for WSFC netsh advfirewall firewall add rule name="Allow WSFC health check" dir=in action=allow protocol=TCP localport=59998 # Open firewall for SQL Server netsh advfirewall firewall add rule name="Allow SQL Server" dir=in action=allow protocol=TCP localport=1433 # Open firewall for SQL Server replication netsh advfirewall firewall add rule name="Allow SQL Server replication" dir=in action=allow protocol=TCP localport=5022 # Format data disk Get-Disk | Where partitionstyle -eq 'RAW' | Initialize-Disk -PartitionStyle MBR -PassThru | New-Partition -AssignDriveLetter -UseMaximumSize | Format-Volume -FileSystem NTFS -NewFileSystemLabel 'Data' -Confirm:$false # Create data and log folders for SQL Server md d:\Data md d:\Logs EOF
Create the VM instances. On the two VMs that serve as cluster nodes, attach an additional data disk and enable the Windows Server Failover Clustering by setting the metadata key enable-wsfc to true:
REGION=$(gcloud config get-value compute/region) ZONE1=ZONE1ZONE2=ZONE2ZONE3=ZONE3PD_SIZE=200 MACHINE_TYPE=n2-standard-8 gcloud compute instances create node-1 \ --zone $ZONE1 \ --machine-type $MACHINE_TYPE \ --subnet $SUBNET_NAME \ --image-family sql-ent-2022-win-2022 \ --image-project windows-sql-cloud \ --tags wsfc,wsfc-node \ --boot-disk-size 50 \ --boot-disk-type pd-ssd \ --boot-disk-device-name "node-1" \ --create-disk=name=node-1-datadisk,size=$PD_SIZE,type=pd-ssd,auto-delete=no \ --metadata enable-wsfc=true \ --metadata-from-file=sysprep-specialize-script-ps1=specialize-node.ps1 gcloud compute instances create node-2 \ --zone $ZONE2 \ --machine-type $MACHINE_TYPE \ --subnet $SUBNET_NAME \ --image-family sql-ent-2022-win-2022 \ --image-project windows-sql-cloud \ --tags wsfc,wsfc-node \ --boot-disk-size 50 \ --boot-disk-type pd-ssd \ --boot-disk-device-name "node-2" \ --create-disk=name=node-2-datadisk,size=$PD_SIZE,type=pd-ssd,auto-delete=no \ --metadata enable-wsfc=true \ --metadata-from-file=sysprep-specialize-script-ps1=specialize-node.ps1 gcloud compute instances create "witness" \ --zone $ZONE3 \ --machine-type e2-medium \ --subnet $SUBNET_NAME \ --image-family=windows-2022 \ --image-project=windows-cloud \ --tags wsfc \ --boot-disk-size 50 \ --boot-disk-type pd-ssd \ --metadata sysprep-specialize-script-ps1="add-windowsfeature FS-FileServer"
Replace ZONE1, ZONE2, ZONE3 based on the zones you are using.
Note: Depending on your performance requirements, consider using a machine type larger thann2-standard-8 for the WSFC nodes. Consider disabling Simultaneous multithreading (SMT) for potential savings on licensing costs. Note: For the purpose of this tutorial, and to fit within the default regional SSD Persistent Disk quota, the size of the disks attached to each VM is smaller than it would be in a production environment. For better performance and to accommodate a larger database, increase the size of each disk.To join the three VM instances to Active Directory, do the following for each of the three VM instances:
Monitor the initialization process of the VM by viewing its serial port output.
gcloud compute instances tail-serial-port-output NAME
Replace NAME with the name of the VM instance.
Wait for a few minutes until you see the output Instance setup finished, then press Ctrl+C. At this point, the VM instance is ready to be used.
Create a username and password for the VM instance.
Connect to the VM by using Remote Desktop and sign in using the username and password created in the previous step.
Right-click the Start button (or press Win+X) and click Windows PowerShell (Admin).
Note: In this guide, we used Powershell 5.1.Confirm the elevation prompt by clicking Yes.
Join the computer to your Active Directory domain and restart.
Add-Computer -Domain DOMAIN -Restart
Replace DOMAIN with the DNS name of your Active Directory domain.
Enter the credentials of an account that has permissions to join a VM to the domain
Wait for the VM to restart. You have now joined the VM instance to the Active Directory.
You now reserve two static IP addresses in your VPC. One IP address is used as the default WSFC cluster IP address, the other serves as the static IP for the SQL Server availability group listener.
In a WSFC cluster, the cluster IP address primarily serves administrative purposes and accessing cluster resources. This virtual IP address is assigned to the cluster itself, enabling administrators to manage the cluster and perform tasks such as configuring cluster settings, monitoring the health of nodes, and managing failover processes.
In the context of SQL Server availability group, a listener is a virtual network name (VNN) and IP address that allows clients to connect to the availability group without needing to know which specific server is the primary node.
An internal load balancer needs an internal IP address to efficiently route internal traffic and support high availability and load balancing in the context of a WSFC cluster. The internal load balancer ensures that requests are always directed to the current primary replica of the cluster. During failover events, the load balancer detects changes in the primary replica and redirects client connections to the new primary without requiring manual intervention, minimizing downtime and ensuring continuous availability of the database services.
In the context of a WSFC with SQL Server Always On availability groups, both reserved internal static IP addresses for the default WSFC cluster IP address and the availability group listener are also used by associated internal load balancers.
To reserve two static IP addresses in your VPC, use the following steps.
gcloud compute addresses create wsfc-cluster-ip \
--subnet $SUBNET_NAME \
--region $(gcloud config get-value compute/region) && \
CLUSTER_IP=$(gcloud compute addresses describe wsfc-cluster-ip \
--region $(gcloud config get-value compute/region) \
--format=value\(address\)) && \
echo "cluster IP: $CLUSTER_IP"
Replace the cluster IP address in the CLUSTER_IP variable, you need it later to specify it as the cluster IP:
CLUSTER_IP=CLUSTER_IP
Reserve another static IP for the availability group listener and capture the address in a new environment variable named LISTENER_IP.
gcloud compute addresses create wsfc-listener-ip \ --subnet $SUBNET_NAME \ --region $(gcloud config get-value compute/region) LISTENER_IP=$(gcloud compute addresses describe wsfc-listener-ip \ --region $(gcloud config get-value compute/region) \ --format=value\(address\)) && \ echo "Listener IP: $LISTENER_IP"Note: Make sure that the IP address is in the same region as your SQL Server cluster.
Replace the load balancer's reserved IP address as the LISTENER_IP variable, you need it later to configure your availability group.
LISTENER_IP=LISTENER_IP
Your project and VPC are now ready for the deployment of the Windows Server Failover Cluster and SQL Server.
Deploying the failover clusterYou can now use the VM instances to deploy a Windows Server Failover Cluster and SQL Server. The following sections discuss how you can do this in detail.
Preparing SQL ServerCreate a new user account in Active Directory for SQL Server using the following steps.
node-1 by using Remote Desktop. Sign in with your domain user account.Create a domain user account for SQL server and the SQL agent and assign a password:
$Credential = Get-Credential -UserName sql_server -Message 'Enter password' New-ADUser ` -Name "sql_server" ` -Description "SQL Admin account." ` -AccountPassword $Credential.Password ` -Enabled $true -PasswordNeverExpires $trueNote: If you use Managed AD, append
-Path "OU=Cloud,DC=example,DC=org" to the command to create the user in the Cloud organizational units (OU). Ensure that the value of the path is the output of the following command: Get-ADOrganizationalUnit -Filter "Name -eq 'Cloud'" | Select-Object -ExpandProperty DistinguishedNameTo configure SQL Server, perform the following steps on both node-1 and node-2, use the following steps:
Under Log on as, change the account as follows:
DOMAIN\sql_server where DOMAIN is the NetBIOS name of your Active Directory domain.Click OK.
When prompted to restart SQL Server, select Yes.
SQL Server now runs under a domain user account.
Warning: Make sure you've completed the previous configuration steps on both VM instances, otherwise setting up the Always On availability group will fail.Create two file shares on the VM instance witness so that it can store SQL Server backups and act as a file share witness:
witness by using Remote Desktop. Sign in with your domain user account.Create a witness file share and grant yourself and the two cluster nodes access to the file share.
New-Item "C:\QWitness" –type directory icacls C:\QWitness\ /grant 'node-1$:(OI)(CI)(M)' icacls C:\QWitness\ /grant 'node-2$:(OI)(CI)(M)' New-SmbShare ` -Name QWitness ` -Path "C:\QWitness" ` -Description "SQL File Share Witness" ` -FullAccess $env:username,node-1$,node-2$
Create another file share to store backups and grant SQL Server full access:
New-Item "C:\Backup" –type directory New-SmbShare ` -Name Backup ` -Path "C:\Backup" ` -Description "SQL Backup" ` -FullAccess $env:USERDOMAIN\sql_server
To create the failover cluster, use the following steps:
node-1.Create a new cluster.
New-Cluster `
-Name sql-cluster `
-Node node-1,node-2 `
-NoStorage `
-StaticAddress CLUSTER_IP
Replace CLUSTER_IP with the cluster IP address that you created earlier.
Computers (in an OU), the permission Create Computer objects must be delegated to the cluster's computer account in the OU.Return to the PowerShell session on witness and grant the virtual computer object of the cluster permission to access the file share.
icacls C:\QWitness\ /grant 'sql-cluster$:(OI)(CI)(M)' Grant-SmbShareAccess ` -Name QWitness ` -AccountName 'sql-cluster$' ` -AccessRight Full ` -Force
Return to the PowerShell session on node-1 and configure the cluster to use the file share on witness as a cluster quorum.
Set-ClusterQuorum -FileShareWitness \\witness\QWitness
Verify that the cluster was created successfully.
Test-Cluster
You might see the following warnings that you can safely ignore.
WARNING: System Configuration - Validate All Drivers Signed: The test reported some warnings.. WARNING: Network - Validate Network Communication: The test reported some warnings.. WARNING: Test Result: HadUnselectedTests, ClusterConditionallyApproved Testing has completed for the tests you selected. You should review the warnings in the Report. A cluster solution is supported by Microsoft only if you run all cluster validation tests, and all tests succeed (with or without warnings).
You can also launch the Failover Cluster Manager MMC snap-in to review the cluster's health by running cluadmin.msc.
If you're using Managed AD, add the computer account used by the Windows cluster to the Cloud Service Domain Join Accounts group so that it can join computers to the domain.
Add-ADGroupMember ` -Identity "Cloud Service Domain Join Accounts" ` -Members sql-cluster$
Enable Always On availability groups on both nodes.
Enable-SqlAlwaysOn -ServerInstance node-1 -Force Enable-SqlAlwaysOn -ServerInstance node-2 -Force
You now create a sample database bookshelf, include it in a new availability group named bookshelf-ag and configure high availability.
Create a new database. For the purpose of this tutorial, the database doesn't need to contain any data.
node-1.node-1 and select Connect.Paste the following SQL script into the editor:
-- Create a sample database
CREATE DATABASE bookshelf ON PRIMARY (
NAME = 'bookshelf',
FILENAME='d:\Data\bookshelf.mdf',
SIZE = 256MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 256MB)
LOG ON (
NAME = 'bookshelf_log',
FILENAME='d:\Logs\bookshelf.ldf',
SIZE = 256MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 256MB)
GO
USE [bookshelf]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- Create sample table
CREATE TABLE [dbo].[Books] (
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](max) NOT NULL,
[Author] [nvarchar](max) NULL,
[PublishedDate] [datetime] NULL,
[ImageUrl] [nvarchar](max) NULL,
[Description] [nvarchar](max) NULL,
[CreatedById] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Books] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-- Create a backup
EXEC dbo.sp_changedbowner @loginame = 'sa', @map = false;
ALTER DATABASE [bookshelf] SET RECOVERY FULL;
GO
BACKUP DATABASE bookshelf to disk = '\\witness\Backup\bookshelf.bak' WITH INIT
GO
The script creates a new database with a single table and performs an initial backup to witness.
Select Execute to run the SQL script.
You can now configure high availability for the availability group using either T-SQL or SQL Server Management Studio.
Using T-SQLTo configure high availability for the availability group using T-SQL, use the following steps:
Connect to node-1 and then execute the following script to create the bookshelf-ag availability group.
CREATE LOGIN [Note: In theNET_DOMAIN\sql_server] FROM WINDOWS; GO USE [bookshelf]; CREATE USER [NET_DOMAIN\sql_server] FOR LOGIN [NET_DOMAIN\sql_server]; GO USE [master]; CREATE ENDPOINT bookshelf_endpoint STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=ALL); GO GRANT CONNECT ON ENDPOINT::[bookshelf_endpoint] TO [NET_DOMAIN\sql_server] GO
NET_DOMAIN\sql_server login and user, NET_DOMAIN is the NetBIOS name of your Active Directory domain.Connect to node-2 and execute the following script.
CREATE LOGIN [Note: In theNET_DOMAIN\sql_server] FROM WINDOWS; GO CREATE ENDPOINT bookshelf_endpoint STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=ALL); GO GRANT CONNECT ON ENDPOINT::[bookshelf_endpoint] TO [NET_DOMAIN\sql_server] GO
NET_DOMAIN\sql_server login and user, NET_DOMAIN is the NetBIOS name of your Active Directory domain.On node-1 and then execute the following script to create the bookshelf-ag availability group.
USE master;
GO
CREATE AVAILABILITY GROUP [bookshelf-ag]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
CLUSTER_TYPE = WSFC,
DB_FAILOVER = ON
)
FOR DATABASE [bookshelf]
REPLICA ON
N'node-1' WITH (
ENDPOINT_URL = 'TCP://node-1:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC,
BACKUP_PRIORITY = 50,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
N'node-2' WITH (
ENDPOINT_URL = 'TCP://node-2:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC,
BACKUP_PRIORITY = 50,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
);
GO
In the following script, replace LISTENER_IP with the IP address that you reserved earlier for the internal load balancer and execute it.
USE master;
GO
ALTER AVAILABILITY GROUP [bookshelf-ag]
ADD LISTENER N'bookshelf' (
WITH IP (
(N'LISTENER_IPConnect to node-2 and then execute the following script to join the secondary replica to the availability group and enable automatic seeding.
USE master; GO ALTER AVAILABILITY GROUP [bookshelf-ag] JOIN; ALTER AVAILABILITY GROUP [bookshelf-ag] GRANT CREATE ANY DATABASE;
Check the status of the availability group.
SELECT * FROM sys.dm_hadr_availability_group_states; GO
You should see synchronization_health_desc as HEALTHY
To configure high availability for the availability group using SQL Server Management Studio, use the following steps:
bookshelf-ag, then select Next.bookshelf database, then select Next.On the Specify Replicas page, select the Replicas tab.
In the Connect to server dialog, enter the server name node-2 and select Connect.
The list of availability replicas now contains SQL Server instances, node-1 and node-2.
Set the Availability mode to Synchronous commit for both instances.
Set Automatic failover to Enabled for both instances.
Select the Listener tab
Enter the following settings.
bookshelf1433Select Add and enter the listener IP address (LISTENER_IP
Select Next.
On the Select Data Synchronization page, select Automatic Seeding.
On the Validation page, verify that all checks are successful.
On the Summary page, select Finish.
On the Results page, select Close.
The cluster IP represents a single endpoint for the Windows failover cluster. You use it for administrative purposes and managing cluster resources. The cluster IP always points to the host (or primary) node of the cluster. You deploy an internal load balancer that uses a health check that ensures that traffic is directed to the host node of the cluster. As WSFC tooling requires multiple protocols to be available for forwarding (ICMP, UDP, and TCP), we recommend deploying an internal load balancer with multiple protocols that support all ports.
To deploy an internal load balancer, use the following steps:
Create two unmanaged instance groups, one per zone, and add the two nodes to the groups.
REGION=$(gcloud config get-value compute/region) gcloud compute instance-groups unmanaged create wsfc-group-1 --zone $ZONE1 gcloud compute instance-groups unmanaged add-instances wsfc-group-1 --zone $ZONE1 \ --instances node-1 gcloud compute instance-groups unmanaged create wsfc-group-2 --zone $ZONE2 gcloud compute instance-groups unmanaged add-instances wsfc-group-2 --zone $ZONE2 \ --instances node-2
Create health check for the cluster IP that the load balancer can use to determine which is the active node from the Windows cluster perspective. The default port which the Compute Engine guest agent responds to health checks is 59998. The health check provides the cluster IP address in the request and it expects 1 as a response returned from the active node.
gcloud compute health-checks create tcp wsfc-healthcheck \ --request=$CLUSTER_IP \ --response=1 \ --check-interval="2s" \ --healthy-threshold=2 \ --unhealthy-threshold=2 \ --port=59998 \ --timeout="1s"
Create a backend service and add the two existing instance groups.
gcloud compute backend-services create wsfc-backend \ --load-balancing-scheme internal \ --region $REGION \ --health-checks wsfc-healthcheck \ --protocol UNSPECIFIED gcloud compute backend-services add-backend wsfc-backend \ --instance-group wsfc-group-1 \ --instance-group-zone $ZONE1 \ --region $REGION gcloud compute backend-services add-backend wsfc-backend \ --instance-group wsfc-group-2 \ --instance-group-zone $ZONE2 \ --region $REGION
Create the internal load balancer associated with the cluster IP.
gcloud compute forwarding-rules create wsfc \ --load-balancing-scheme internal \ --address $CLUSTER_IP \ --ports ALL \ --network $VPC_NAME \ --subnet $SUBNET_NAME \ --region $REGION \ --ip-protocol L3_DEFAULT \ --backend-service wsfc-backend
To provide a single endpoint for SQL Server clients that want to connect to any database in your bookshelf availability group, deploy a new internal load balancer dedicated to that availability group, use the following steps:
Create a health check for the availability group listener that the load balancer can use to determine which is the primary node in the bookshelf SQL Server availability group.
gcloud compute health-checks create tcp wsfc-bookshelf-healthcheck \ --request=$LISTENER_IP \ --response=1 \ --check-interval="2s" \ --healthy-threshold=1 \ --unhealthy-threshold=2 \ --port=59998 \ --timeout="1s"
The health check uses the same Compute Engine guest agent port, but it provides in its request the listener IP address of the bookshelf availability group.
Create a new backend service and add the two instance groups.
gcloud compute backend-services create wsfc-bookshelf-backend \ --load-balancing-scheme internal \ --region $REGION \ --health-checks wsfc-bookshelf-healthcheck \ --protocol UNSPECIFIED gcloud compute backend-services add-backend wsfc-bookshelf-backend \ --instance-group wsfc-group-1 \ --instance-group-zone $ZONE1 \ --region $REGION gcloud compute backend-services add-backend wsfc-bookshelf-backend \ --instance-group wsfc-group-2 \ --instance-group-zone $ZONE2 \ --region $REGION
Create the internal load balancer associated with the SQL Server bookshelf-ag availability group listener.
gcloud compute forwarding-rules create wsfc-bookshelf \ --load-balancing-scheme internal \ --address $LISTENER_IP \ --ports ALL \ --network $VPC_NAME \ --subnet $SUBNET_NAME \ --region $REGION \ --ip-protocol L3_DEFAULT \ --backend-service wsfc-bookshelf-backend
You can now connect to SQL Server availability group listener by using the DNS name bookshelf and its port defined in the bookshelf availability group listener. The internal load balancer directs traffic to the primary node of the bookshelf availability group.
To create multiple availability groups on a single failover cluster, you must use separate backend services and a separate load balancer with its own healthcheck per availability group.
Each availability group might have different nodes designated as the primary, and they might be different than the host node of the Windows cluster. For multiple availability groups, you need the following:
A reserved static IP address for the availability group listener that the internal load balancer uses. Reserve one address for each availability group.
A separate health check rule for each availability group. The request of the health check provides the static IP address of the availability group listener ( which is also the reserved IP address at the previous step). The health check probes for response 1 returned by the GCE agent. All health checks use port 59998.
A separate backend service for each availability group to which you add the existing two compute instance groups. The backend service uses the health check defined at the previous step.
An internal load balancer for each availability group for the backend service created at the previous step. The load balancer is associated with the availability group listener static IP address.
You are now ready to test if the failover works as expected:
witness.Run the following script.
while ($True){
$Conn = New-Object System.Data.SqlClient.SqlConnection
$Conn.ConnectionString = "Server=tcp:bookshelf,1433;Integrated Security=true;Initial Catalog=master"
$Conn.Open()
$Cmd = New-Object System.Data.SqlClient.SqlCommand
$Cmd.Connection = $Conn
$Cmd.CommandText = "SELECT SERVERPROPERTY('ServerName')"
$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter $Cmd
$Data = New-Object System.Data.DataSet
$Adapter.Fill($Data) | Out-Null
$Data.Tables[0] + (Get-Date -Format "MM/dd/yyyy HH:mm:ss")
Start-Sleep -Seconds 2
}
In this guide, we used the DNS name bookshelf and the port value 1433 for the availability group listener in the server definition tcp:bookshelf,1433.
Every 2 seconds, the script connects to SQL Server by using the availability group listener, and queries the server name.
Leave the script running.
Return to the Remote Desktop session on node-1 to trigger a failover.
node-2 is selected as new primary replica and that the Failover readiness column indicates No data loss. Then select Next.node-2 and click Connect.Return to the PowerShell session on witness.
Observe the output of the running script and notice that the server name changes from node-1 to node-2 as a result of the failover.
Stop the script by pressing Ctrl+C.
After you finish the tutorial, you can clean up the resources that you created so that they stop using quota and incurring charges. The following sections describe how to delete or turn off these resources.
Deleting the projectThe easiest way to eliminate billing is to delete the project that you created for the tutorial.
To delete the project:
appspot.com URL, delete selected resources inside the project instead of deleting the whole project.If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.
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 the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-08-07 UTC.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["SQL Server Always On availability groups replicate databases across multiple SQL Server Enterprise instances, using Windows Server Failover Clustering (WSFC) for high availability, but with a failover scope of a group of databases rather than an entire instance like Failover Cluster Instances."],["This guide demonstrates deploying Always On availability groups in synchronous commit mode for high availability, utilizing three VM instances: two for cluster nodes (`node-1`, `node-2`) and one as a file share witness (`witness`) for quorum, all within a common subnet across three zones."],["Google Cloud disregards ARP announcements, so implementing IP address failover requires using either an internal load balancer or a distributed network name (DNN), and this tutorial focuses on the internal load balancer method."],["The tutorial outlines the steps to deploy a WSFC, create an availability group with synchronous commit, configure an internal load balancer to direct traffic to the active node, and verify failover functionality, including considerations for multiple availability groups."],["The tutorial utilizes multiple Google Cloud resources that will incur costs for use, including Compute Engine and Cloud Load Balancing, which can be estimated using the pricing calculator."]]],[]]
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4