Preparing the project and network
To prepare your Google Cloud project and VPC for the deployment of SQL Server
AlwaysOn availability groups, do the following:
In the Google Cloud console, open
Cloud Shell
by clicking the
Activate Cloud Shell
button.
Go to the Google Cloud console
Initialize the following variables:
VPC_NAME=
VPC_NAME
SUBNET_NAME=
SUBNET_NAME
Where:
VPC_NAME
: name of your VPC
SUBNET_NAME
: name of your subnet
Set 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.
Create firewall rules
To allow clients to connect to SQL Server, allow communication between the WSFC
nodes, and to enable the load balancer to
perform health checks
,
you need to create several firewall rules. To simplify the creation of these
firewall rules, you use
network tags
:
- The 2 WSFC nodes are annotated with the
wsfc-node
tag.
- All servers (including the witness) are annotated with the
wsfc
tag.
Create firewall rules that use these network tags:
- Return to your existing Cloud Shell session.
Create firewall rules for the WSFC 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 a firewall rule that allows health checks from the
IP ranges of the Google Cloud probers
.
<pre class="devsite-click-to-copy">
gcloud compute firewall-rules create allow-health-check-to-wsfc-nodes \
--direction=INGRESS \
--action=allow \
--rules=tcp \
--source-ranges=130.211.0.0/22,35.191.0.0/16 \
--target-tags=wsfc-node \
--network=$VPC_NAME \
--priority 10000
</pre>
Create VM instances
You now 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:
- be located in the same region so that they can be accessed by an
internal passthrough Network Load Balancer
(not applicable for DNN)
.
- have WSFC and SQL Server installed.
- have
Compute Engine WSFC support
enabled.
You use a
SQL Server premium image
which has SQL Server 2022 preinstalled.
To provide a tie-breaking vote and achieve a quorum for the failover scenario,
you deploy a third VM that serves as a
file share witness
.
- Return to your existing Cloud Shell session.
Create a
specialize script
for the WSFC nodes. The script installs the necessary Windows feature 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 SQL Server health check" dir=in action=allow protocol=TCP localport=59997
# 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 WSFC 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)
PD_SIZE=200
MACHINE_TYPE=n2-standard-8
gcloud compute instances create node-1 \
--zone $REGION-a \
--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 $REGION-b \
--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 $REGION-c \
--machine-type n2-standard-2 \
--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"
To join the 3 VM instances to Active Directory, do the following for each of
the 3 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 about 3 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 log 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)
.
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.
Wait for approximately 1 minute for the restart to complete.
Reserve cluster IP addresses
You now reserve two static IP addresses in your VPC. One IP address is used as the
WSFC cluster IP address, the other is used by the internal load balancer.
Reserve a static IP address that you use as cluster IP:
gcloud compute addresses create wsfc-cluster \
--subnet $SUBNET_NAME \
--region $(gcloud config get-value compute/region) && \
CLUSTER_ADDRESS=$(gcloud compute addresses describe wsfc-cluster \
--region $(gcloud config get-value compute/region) \
--format=value\(address\)) && \
echo "Cluster IP: $CLUSTER_ADDRESS"
Note the IP address, you need it later.
Reserve another static IP for the internal load balancer and capture the address in
a new environment variable named
LOADBALANCER_ADDRESS
:
gcloud compute addresses create wsfc \
--subnet $SUBNET_NAME \
--region $(gcloud config get-value compute/region)
LOADBALANCER_ADDRESS=$(gcloud compute addresses describe wsfc \
--region $(gcloud config get-value compute/region) \
--format=value\(address\)) && \
echo "Load Balancer IP: $LOADBALANCER_ADDRESS"
Note the IP address, you need it later.
Your project and VPC are now ready for the deployment of the WSFC and SQL Server.
Deploying the failover cluster
You now use the VM instances to deploy a WSFC and SQL Server.
Preparing SQL Server
Create a new user account in Active Directory for SQL Server:
- Connect to
node-1
by using Remote Desktop
.
Log in with your domain user account.
- Right-click the
Start
button (or press
Win+X
) and click
Windows PowerShell (Admin)
.
- Confirm the elevation prompt by clicking
Yes
.
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 $true
To configure SQL Server, perform the following steps on both
node-1
and
node-2
:
Right-click the
Start
button (or press
Win+X
) and click
Windows PowerShell (Admin)
.
Rename the SQL server instance so that its name matches the hostname:
$OLD_NAME = Invoke-Sqlcmd -Query "
select @@SERVERNAME;
GO" | ConvertTo-Csv | SELECT -Skip 2
$OLD_NAME = $OLD_NAME.Replace('"', '')
Invoke-Sqlcmd -Query "
sp_dropserver '$OLD_NAME';
GO
sp_addserver '$env:computername', local;
GO"
Restart-Service -Name MSSQLSERVER
Open
SQL Server Configuration Manager
.
In the navigation pane, select
SQL Server Services
In the list of services, right-click
SQL Server (MSSQLSERVER)
and select
Properties
.
Under
Log on as
, change the account:
- Account name
:
DOMAIN
\sql_server
where
DOMAIN
is the NetBIOS name of your Active Directory domain.
- Password
: Enter the password you chose previously.
Click
OK
.
When prompted to restart SQL Server, select
Yes
.
SQL Server now runs under a domain user account.
Creating file shares
Create two file shares on
witness
so that the VM instance can
store SQL Server backups and act as a file share witness:
- Connect to
witness
by using Remote Desktop
.
Log in with your domain user account.
- Right-click the
Start
button (or press
Win+X
) and click
Windows PowerShell (Admin)
.
- Confirm the elevation prompt by clicking
Yes
.
Create a witness file share and grant yourself and the two WSFC 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
Deploy WSFC
You are now ready to create the failover cluster:
- Return to the Remote Desktop session on
node-1
.
- Right-click the
Start
button (or press
Win+X
) and click
Windows PowerShell (Admin)
.
- Confirm the elevation prompt by clicking
Yes
.
Create a new cluster:
- For load balancer configuration
New-Cluster `
-Name sql-cluster `
-Node node-1,node-2 `
-NoStorage `
-StaticAddress
CLUSTER_ADDRESS
Replace
CLUSTER_ADDRESS
with the cluster IP address
that you created earlier.
New-Cluster `
-Name sql-cluster `
-Node node-1,node-2 `
-NoStorage `
-ManagementPointNetworkType Distributed
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 some warnings that can be safely ignored:
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 WSFC 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 AlwaysOn availability groups on both nodes:
Enable-SqlAlwaysOn -ServerInstance node-1 -Force
Enable-SqlAlwaysOn -ServerInstance node-2 -Force
Creating an availability group
You now create a sample database
bookshelf
, include it in a new availability
group named
bookshelf-ag
and configure high availability.
Creating a database
Create a new database. For the purpose of this tutorial, the database doesn't
need to contain any data.
- Return to the Remote Desktop session on
node-1
.
- Open the
SQL Server Management Studio
.
- In the
Connect to server
dialog, verify the server name is set to
NODE-1
and select
Connect
.
- In the menu, select
File > New > Query with current connection
.
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.
Configuring high availability
- In the
Object Explorer
window, right-click
AlwaysOn High Availability
and then select
New Availability Group Wizard
.
- On the
Specify Options
page, set the availability group name to
bookshelf-ag
, then select
Next
.
- On the
Select Databases
page, select the
bookshelf
database,
then select
Next
.
On the
Specify Replicas
page, select the
Replicas
tab:
- Select
Add replica
.
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 **
- Select
Create an availability group listener
Enter the following settings:
- Listener DNS name
:
bookshelf
- Port
:
1433
- Network mode
:
Static IP
Select
Add
and enter the load balancer IP address that you created
earlier. Then select
OK
.
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
.
As a final step, configure the cluster to expose a health check endpoint that
can be used by an internal load balancer:
- Return to the PowerShell session on
node-1
.
Initialize a variable with the IP address of the load balancer.
$LoadBalancerIP = '
IP_ADDRESS
'
Replace
IP_ADDRESS
with the IP address of the
wsfc
address that you reserved earlier.
Configure the Failover Cluster to respond to the health check service:
$SqlIpAddress = Get-ClusterResource |
Where-Object {$_.ResourceType -eq "IP Address"} |
Where-Object {$_.Name.StartsWith("bookshelf")}
$SqlIpAddress | Set-ClusterParameter -Multiple @{
'Address'= $LoadBalancerIP;
'ProbePort'= 59997;
'SubnetMask'='255.255.255.255';
'Network'= (Get-ClusterNetwork).Name;
'EnableDhcp'=0; }
Restart the cluster resource:
$SqlIpAddress | Stop-ClusterResource
$SqlIpAddress | Start-ClusterResource
Create an internal load balancer
To provide a single endpoint for SQL Server clients, you now deploy an
internal load balancer
.
The load balancer uses a health check which ensures that traffic is directed to
the active node of the WSFC.
- Return to your existing Cloud Shell session.
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 $REGION-a
gcloud compute instance-groups unmanaged add-instances wsfc-group-1 --zone $REGION-a \
--instances node-1
gcloud compute instance-groups unmanaged create wsfc-group-2 --zone $REGION-b
gcloud compute instance-groups unmanaged add-instances wsfc-group-2 --zone $REGION-b \
--instances node-2
Create a health check that the load balancer can use to determine which is
the active node.
gcloud compute health-checks create tcp wsfc-healthcheck \
--check-interval="2s" \
--healthy-threshold=1 \
--unhealthy-threshold=2 \
--port=59997 \
--timeout="1s"
The health check probes port
59997
, which is the port you previously
configured as
ProbePort
for the availability group listener.
Create a backend service and add the two instance groups:
gcloud compute backend-services create wsfc-backend \
--load-balancing-scheme internal \
--region $(gcloud config get-value compute/region) \
--health-checks wsfc-healthcheck \
--protocol tcp
gcloud compute backend-services add-backend wsfc-backend \
--instance-group wsfc-group-1 \
--instance-group-zone $REGION-a \
--region $REGION
gcloud compute backend-services add-backend wsfc-backend \
--instance-group wsfc-group-2 \
--instance-group-zone $REGION-b \
--region $REGION
Create the internal load balancer:
gcloud compute forwarding-rules create wsfc-sql \
--load-balancing-scheme internal \
--address $LOADBALANCER_ADDRESS \
--ports 1433 \
--network $VPC_NAME \
--subnet $SUBNET_NAME \
--region $REGION \
--backend-service wsfc-backend
You can now connect to SQL Server availability group listener by using
the internal load balancer and the DNS name
bookshelf
.
To create multiple availability groups on a single failover cluster,
you must use a separate load balancer with its own healthcheck per availability
group.
This means that for each availability group, you need:
A reserved internal IP address (one for each availability group) to be used for
the listener and the internal load balancer.
A separate load balancer (one for each availability group) with its own IP address
reserved in the previous step.
A separate probe port (59997, 59998, and so on for each availability group)
and firewall rule to allow traffic on those probe ports.
A separate health check rule for each availability group using the respective probe
ports.
Similar to the internal load balancing configuration, DNN listener serves as a single endpoint for SQL Server clients.
- Return to the PowerShell session on
node-1
.
Execute the following script to create a DNN listener.
$Ag='bookshelf-ag'
$Port='
DNN_PORT
'
$Dns='
DNN_NAME
'
# create the DNN resource with the port as the resource name
Add-ClusterResource -Name $Port -ResourceType "Distributed Network Name" -Group $Ag
# set the DNS name of the DNN resource
Get-ClusterResource -Name $Port | Set-ClusterParameter -Name DnsName -Value $Dns
# start the DNN resource
Start-ClusterResource -Name $Port
# add the Dependency from availability group resource to the DNN resource
Set-ClusterResourceDependency -Resource $Ag -Dependency "[$Port]"
#bounce the AG resource
Stop-ClusterResource -Name $Ag
Start-ClusterResource -Name $Ag
Replace
DNN_PORT
with the DNN listener port. The DNN listener port must be configured with a unique port. For more information, see
Port considerations
.
Replace
DNN_NAME
with the DNN listener name.
Create firewall rules for DNN listener port on both
node-1
and
node-2
.
netsh advfirewall firewall add rule name="Allow DNN listener" dir=in action=allow protocol=TCP localport=
DNN_PORT
Testing failover
You are now ready to test if the failover works as expected:
- Return to the PowerShell session on
witness
.
Run the following script:
while ($True){
$Conn = New-Object System.Data.SqlClient.SqlConnection
$Conn.ConnectionString = "Server=
LISTENER
;Integrated Security=true;Initial Catalog=master"
$Conn.Open()
$Cmd = New-Object System.Data.SqlClient.SqlCommand
$Cmd.Connection = $Conn
$Cmd.CommandText = "SELECT @@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
}
Replace
LISTENER
with the listener DNS name or the DNN listener, and port of the availability group.
Every 2 seconds, the script connects to SQL Server by using the availability
group listener or DNN listener, and queries the server name.
Leave the script running.
Return to the Remote Desktop session on
node-1
to trigger a failover:
- In
SQL Server Management Studio
, navigate to
AlwaysOn
High Availability > Availability Groups > bookshelf-ag (Primary)
and
right-click the node.
- Select
Failover
.
- On the
Select new primary replica
page, verify that
node-2
is selected
as new primary replica and that the
Failover readiness
column
indicates
No data loss
. Then select
Next
.
- On the
Connect to replica
page, select
Connect
.
- In the
Connect to server
dialog, verify that the server name is
node-2
and
click
Connect
.
- Select
Next
and then
Finish
.
- On the
Results
page, verify that the failover was successful.
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
.