Prepare your project and network

To prepare your Google Cloud project and Virtual Private Cloud (VPC) for the deployment of SQL ServerAlways On availability groups, do the following:

1. In the Google Cloud console, openCloud Shell by clicking theActivate Cloud Shellbutton.

   Go to the Google Cloud console

2. Initialize the following variables:

   VPC_NAME=VPC_NAMESUBNET_NAME=SUBNET_NAME

   Replace the following:

   • VPC_NAME: name of your VPC
   • SUBNET_NAME: name of your subnet

3. Set your defaultproject ID:

   gcloud config set projectPROJECT_ID

   ReplacePROJECT_ID with the ID of your Google Cloud project.

4. Set your default region:

   gcloud config set compute/regionREGION

   ReplaceREGION with the ID of the region you want to deploy in.

5. Set your default zone:

   gcloud config set compute/zoneZONE

   ReplaceZONE with the ID of the zone you want to deploy in.

Create SMB on NetApp Volumes for SQL Server

To create a NetApp Volumes, you must create an Active Directory policy, private services access, storage pool, and storage volume.

1. Create a newPrivate Service Access for NetApp Volumes using the instructions provided inConfigure private services access.

2. To create a newActive Directory policy, do the following:

   1. In the Google Cloud console, go to theNetApp Volumes page.

      Go to NetApp Volumes

   2. In the Navigation pane, clickActive Directory policies under thePolicies section.

   3. ClickCreate button.

   4. Specify a name for the active directory policy.

   5. UnderRegion, select the available region closest to where the SQL Server instances will be hosted.

   6. In theActive Directory connection details section, provide the following information:

      1. Name of the domain
      2. Active Directory DNS server addresses
      3. Active Directory site name
      4. Name of the organizational unit
      5. NetBIOS name prefix

   7. In theActive Directory credentials section, provide the credentials for a user who is allowed to join computers to the specified domain.

   8. In theSMB settings section, provide theBackup Operators andSecurity privilege users, if applicable.

   9. ClickCreate to complete the process of creating a new Active Directory policy.

3. To create a new storage pool, do the following:

   1. In the Navigation pane, clickStorage pools under theStorage section.
   2. ClickCreate button.
   3. Enter a name for the storage pool.
   4. UnderLocation, select the available region closest to where the SQL Server instances will be hosted.

   5. Select aService Level that would satisfy the expected storage performance.

      Warning: Avoid using the Flex option as it disables support for CA shares for SQL Server.

   6. Enter theCapacity for the storage pool. Storage pool capacity defines the total storage capacity that can be allocated for all the volumes within the pool.

   7. In theNetwork list, select the VPC that the SQL Server instances will be hosted in.

   8. In theActive Directory policy section, selectAssign an Active Directory policy to the storage pool and select the Active Directory policy created in the previous step.

   9. ClickCreate button to complete the process of creating a new storage pool.

4. To test the connection between the storage pool and the active directory, do the following:

   1. In the Google Cloud console, go to theActive Directory policy page.

      Go to Active Directory policies

   2. Click the Active Directory policy you assigned to the storage pool in the previous step.

   3. LocateAssociated storage pools and clickTest Active Directory connection.

      Note: If the connection fails, seeFirewall rules for volumes accessto create any missing firewall rules.

5. To create a newstorage volume, do the following:

   1. In the Navigation pane, clickVolumes under theStorage section.
   2. ClickCreate button.
   3. Specify a name for the storage volume.
   4. UnderStorage pool details, clickSelect storage pool to select the storage pool created in the previous step.
   5. In theVolume details section, provide the following information:
      1. Share name
      2. Volume capacity
   6. In theProtocol(s) configuration section, selectSMB protocol andEnable CA share support for SQL Server, FSLogix.
   7. ClickCreate button to complete the process of creating a new storage volume.

Create firewall rules

To allow clients to connect to SQL Server, allow communication between the Windows Server Failover Cluster (WSFC)nodes, and to enable the load balancer toperform health checks,you need to create several firewall rules. To simplify the creation of thesefirewall rules, you can usenetwork tags:

• The WSFC you use are annotated with thewsfc-node tag.
• All servers, including the witness, are annotated with thewsfc tag.

To create firewall rules that use these network tags, use the following steps:

1. Return to your existing Cloud Shell session.

2. 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 10000gcloud 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

3. Create a firewall rule that allows health checks from theIP ranges of the Google Cloud probers:

   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

Create VM instances

You will now deploy three VM instances for the failover cluster.

Node-1 will host the primary replica of the SQL Server databaseandnode-2 will host the secondary replica. The two VM instances must:

• be located in the same region so that they can be accessed by aninternal passthrough Network Load Balancer

• have WSFC and SQL Server 2022 installed usingSQL Server premium image

  Note: If you plan to bring your own licenses for SQL Server by using thelicense mobility program,select Windows Server base images for these nodes and install SQL Serverusing your own product keys.

• haveCompute Engine WSFC support enabled

To provide a tie-breaking vote and achieve a quorum for the failover scenario,you deploy a third VM instancenode-3 that serves as afile share witness.

1. Return to your existing Cloud Shell session.

2. Create ascriptfor the WSFC nodes. The script installs the necessary Windows feature and createsfirewall rules for WSFC and SQL Server:

   cat << "EOF" > specialize-node.ps1$ErrorActionPreference = "stop"# Install required Windows featuresInstall-WindowsFeature Failover-Clustering -IncludeManagementToolsInstall-WindowsFeature RSAT-AD-PowerShell# Open firewall for WSFCnetsh advfirewall firewall add rule name="Allow SQL Server health check" dir=in action=allow protocol=TCP localport=59997# Open firewall for SQL Servernetsh advfirewall firewall add rule name="Allow SQL Server" dir=in action=allow protocol=TCP localport=1433# Open firewall for SQL Server replicationnetsh advfirewall firewall add rule name="Allow SQL Server replication" dir=in action=allow protocol=TCP localport=5022EOF

3. Create the VM instances. On the two VMs that serve as WSFC nodes,attach an additional data disk and enable the Windows Server Failover Clusteringby setting the metadata keyenable-wsfc totrue:

   REGION=$(gcloud config get-value compute/region)PD_SIZE=200MACHINE_TYPE=n2-standard-8gcloud 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" \  --metadata enable-wsfc=true \  --metadata-from-file=sysprep-specialize-script-ps1=specialize-node.ps1gcloud 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" \  --metadata enable-wsfc=true \  --metadata-from-file=sysprep-specialize-script-ps1=specialize-node.ps1gcloud 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"

   Note: Depending on yourperformance requirements,consider using a machine type larger thann2-standard-8 for the WSFCnodes.

4. To join the 3 VM instances to Active Directory, do the following for each ofthe 3 VM instances:

   1. Monitor the initialization process of the VM by viewing its serial port output:

      gcloud compute instances tail-serial-port-outputNAME

      ReplaceNAME with the name of the VM instance.

      Wait for a few minutes until you see the outputInstance setup finished,then press Ctrl+C. At this point, the VM instance is ready to be used.

   2. Create a username and passwordfor the VM instance

   3. Connect to the VM using Remote Desktopand sign in using the username and password created in the previous step.

   4. Right-clickStart button (or pressWin+X) and clickWindows PowerShell (Admin).

   5. Confirm the elevation prompt by clickingYes.

   6. Join the VM instance to your Active Directory domain and restart:

      Add-Computer -DomainDOMAIN -Restart

      ReplaceDOMAIN with the DNS name of your Active Directory domain.

      Wait for the VM instance to restart.

Assign storage permissions

To assign permissions to the cloud volume storage, complete the following steps:

1. From the Google Cloud console, select Cloud Volumes and select the volume that was just created.

   

2. Connect tonode-1by using Remote Desktop.Sign in with your domain user account.

3. Open Windows Explorer and right-clickThis PC.

4. SelectMap network drive.

5. Paste the copied SMB path in the folder.

   

6. At sign-in, disable the Reconnect option.

7. ClickFinish.

8. Right-click the mapped network drive and selectProperties.

   

9. From theSecurity tab, clickEdit.

10. Remove Everyone and add the SQL Server installer and SQL Server service accounts with full control permissions.

    

11. Confirm the elevation prompt by clickingYes.

Reserve cluster IP addresses

Reserve two static IP addresses in your VPC, one for the WSFC cluster IP address and another for the internal load balancer.

1. Reserve a static IP for the internal load balancer and capture the address ina new environment variable namedLOADBALANCER_ADDRESS:

   gcloud compute addresses create wsfc \  --region $(gcloud config get-value compute/zone | rev | cut -c 3- | rev) \  --subnet $SUBNET_NAMELOADBALANCER_ADDRESS=$(gcloud compute addresses describe wsfc \  --region $(gcloud config get-value compute/zone | rev | cut -c 3- | rev) \  --format=value\(address\))echo "Load Balancer IP: $LOADBALANCER_ADDRESS"

   Note the IP address, you'll need it later.

2. Reserve another static IP address that you use as cluster IP:

   gcloud compute addresses create wsfc-cluster \  --subnet $SUBNET_NAME \  --region $(gcloud config get-value compute/zone | rev | cut -c 3- | rev)CLUSTER_ADDRESS=$(gcloud compute addresses describe wsfc-cluster \  --region $(gcloud config get-value compute/zone | rev | cut -c 3- | rev) \  --format=value\(address\))echo "Cluster IP: $CLUSTER_ADDRESS"

   Note the IP address, you'll need it later.

Your project and VPC are now ready for the deployment of the WSFC and SQL Server.

Create a witness file share

To preparewitness to serve as file share witness, create a file share and grantyourself and the two WSFC nodes access to the file share:

1. Connect towitnessby using Remote Desktop.Sign in with your domain user account.
2. Right-clickStart button (or pressWin+X) and clickWindows PowerShell (Admin).
3. Confirm the elevation prompt by clickingYes.

4. Create the witness folder and share the folder:

   New-Item "C:\QWitness" –type directoryicacls 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$

Deploying the failover cluster

You will now use the VM instances to deploy a WSFC and SQL Server.

Deploy WSFC

You are now ready to create the failover cluster:

1. Connect tonode-1by using Remote Desktop.Sign in with your domain user account.
2. Right-clickStart button (or pressWin+X) and clickWindows PowerShell (Admin).
3. Confirm the elevation prompt by clickingYes.

4. Create a new cluster:

   New-Cluster `  -Name windows-fci `  -Node node-1,node-2 `  -NoStorage `  -StaticAddressCLUSTER_ADDRESS

   ReplaceCLUSTER_ADDRESS with the cluster IP addressthat you created earlier.

5. Return to the PowerShell session onwitness and grant the virtualcomputer object of the cluster permission to access the file share:

   icacls C:\QWitness\ /grant 'windows-fci$:(OI)(CI)(M)'Grant-SmbShareAccess `  -Name QWitness `  -AccountName 'windows-fci$' `  -AccessRight Full `  -Force

6. Return to the PowerShell session onnode-1 and configure the clusterto use the file share onwitness as a cluster quorum:

   Set-ClusterQuorum -FileShareWitness \\witness\QWitness

7. 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, ClusterConditionallyApprovedTesting has completed for the tests you selected. You should review the warnings in the Report.  A cluster solution issupported 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 thecluster's health by runningcluadmin.msc.

8. If you're using Managed AD, add the computer account used by WSFC to theCloud Service Domain Join Accounts group so that it can join VM instancesto the domain:

   Add-ADGroupMember `  -Identity "Cloud Service Domain Join Accounts" `  -Members windows-fci$

Remove the default SQL Server installation

You will now remove the default SQL Server installation from the two nodes and replaceit with a new FCI configuration.

For each of the two WSFC nodes,node-1 andnode-2, perform the following steps:

1. Right-clickStart button (or pressWin+X) and clickWindows PowerShell (Admin).
2. Confirm the elevation prompt by clickingYes.

3. Remove the default SQL Server instance:

   C:\sql_server_install\Setup.exe /Action=Uninstall /FEATURES=SQL,AS,IS,RS /INSTANCENAME=MSSQLSERVER /Q

4. Remove Microsoft OLE Driver:

   Get-Package -Name "Microsoft OLE*" | Uninstall-Package -Force

5. Remove Microsoft ODBC Driver:

   Get-Package -Name "Microsoft ODBC*" | Uninstall-Package -Force

6. Restart the computer:

   Restart-Computer

7. Wait for the VM instance to restart.

Install SQL Server FCI

Before you install the new FCI configuration, verify that thenode-1 is theactive node in the cluster:

1. Reconnect tonode-1by using Remote Desktopand sign in using your domain user.
2. Right-clickStart button (or pressWin+X) and selectRun
3. Entercluadmin.msc and selectOK.

4. In the left window pane, navigate toFailover Cluster Manager > windows-fci, and do the following:

   1. Verify that thecurrent host server is set tonode-1.

   2. If thecurrent host server is set tonode-2, right-clickwindows-fciin the left window pane and selectMore actions > Move core cluster resources > Select node… > node-1and clickOK.

You will create a new SQL Server failover cluster installation onnode-1:

1. Right-clickStart button (or pressWin+X) and clickWindows PowerShell (Admin).

2. Confirm the elevation prompt by clickingYes.

3. Start the SQL Server setup:

   & c:\sql_server_install\setup.exe

4. In the menu on the left, selectInstallation.

5. SelectNew SQL Server failover cluster installation.

6. On theEdition page, enter the product key, select the software license type, and clickNext.

7. On theLicense Terms page, review the terms and, if you accept, clickNext.

8. On theMicrosoft Update page, clickNext to start the installation.

9. On theInstall Failover Cluster Rules page, you see a WarningMSCS cluster verification warnings andWindows firewall.You can ignore these warnings and clickNext.

10. On theFeature Selection page, selectDatabase Engine Services and clickNext.

11. On theInstance Configuration page, entersql as network name and clickNext.

12. On theCluster Resource Group page, you can ignore the qualification warnings and clickNext.

13. On theCluster Disk Selection page, clickNext.

14. On theCluster Network Configuration page, configure the following settings,then clickNext:

    • DHCP: clear
    • IP address: enter the IP address of the internal load balancer

15. On theServer configuration page, configure the following settings forbothSQL Server Agent andSQL Server Database Engine:

    • Account name:DOMAIN\sql_server whereDOMAIN is the NetBIOS name of yourActive Directory domain
    • Password: entersql_server user's password

16. Select theCollation tab and select the collation that you want to use.Then clickNext.

17. On theDatabase Engine Configuration page, selectAdd current user to designate the current user as SQL Server administrator. In theData Directories tab, enter the SMB path in theData root directory field and selectNext. A message window will appear informing you to verify that the SQL Server service account has full control share permission. SelectYes to proceed.

    

18. On theReady to Install page, review the settings, then selectInstall.

19. After the installation completes, selectClose.

Now addnode-2 to the SQL Server failover cluster:

1. Connect tonode-2by using Remote Desktopand sign in using your domain user.
2. Right-clickStart button (or pressWin+X) and clickWindows PowerShell (Admin).
3. Confirm the elevation prompt by clickingYes.

4. Start the SQL Server setup:

   & c:\sql_server_install\setup.exe

5. In the menu on the left, selectInstallation.

6. SelectAdd node to a SQL Server failover cluster.

7. Follow the instructions of the installation wizard and accept the default settingsuntil you reach the pageService Accounts.

8. On theService Accounts page, enter the password that you created earlierfor bothSQL Server Agent andSQL Server Database Engine. Then selectNext.

9. On theReady to Install page, review the settings, then selectInstall.

10. After the installation completes, selectClose.

Configure health checks

As a final step, configure the cluster to expose a health check endpoint thatcan be used by an internal load balancer:

1. Return to the PowerShell session onnode-2

2. Initialize a variable with the IP address of the load balancer.

   $LoadBalancerIP = 'IP_ADDRESS'

   ReplaceIP_ADDRESS with the IP address of thewsfcaddress that you reserved earlier.

3. Configure the Failover Cluster to respond to the health check service:

   $SqlGroup = Get-ClusterGroup |  Where-Object {$_.Name.StartsWith("SQL Server")}$SqlIpAddress = Get-ClusterResource |  Where-Object {$_.Name.StartsWith("SQL IP Address")}$SqlIpAddress | Set-ClusterParameter -Multiple @{ 'Address'=$LoadBalancerIP; 'ProbePort'= 59997; 'SubnetMask'='255.255.255.255'; 'Network'= (Get-ClusterNetwork).Name; 'EnableDhcp'=0; }

4. Restart the cluster resource:

   $SqlIpAddress | Stop-ClusterResource$SqlIpAddress | Start-ClusterResource

5. Restart the cluster group:

   $SqlGroup | Stop-ClusterGroup$SqlGroup | Start-ClusterGroup

Create an internal load balancer

To provide a single endpoint for SQL Server clients, you will now deploy aninternal load balancer.The load balancer uses a health check which ensures that traffic is directed tothe active node of the WSFC.

1. Return to your existing Cloud Shell session.

2. Create anunmanaged instance group,and add the two nodes to the group:

   REGION=$(gcloud config get-value compute/region)gcloud compute instance-groups unmanaged create wsfc-group-a \  --zone $REGION-agcloud compute instance-groups unmanaged add-instances wsfc-group-a \   --zone $REGION-a --instances node-1gcloud compute instance-groups unmanaged create wsfc-group-b \  --zone $REGION-bgcloud compute instance-groups unmanaged add-instances wsfc-group-b \ --zone $REGION-b --instances node-2

3. Create a health check that the load balancer can use to determine which isthe 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 port59997, which is the port you previouslyconfigured asProbePort for the WSFC cluster IP address.

4. Create a backend service and add the instance group:

   gcloud compute backend-services create wsfc-backend \  --load-balancing-scheme internal \  --region $REGION \  --health-checks wsfc-healthcheck \  --protocol tcpgcloud compute backend-services add-backend wsfc-backend \  --instance-group wsfc-group-a \  --instance-group-zone $REGION-a \  --region $REGIONgcloud compute backend-services add-backend wsfc-backend \  --instance-group wsfc-group-b \  --instance-group-zone $REGION-b \  --region $REGION

5. 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

Testing the failover cluster

You've completed the installation of the failover cluster, but you still have totest whether the cluster works correctly.

Prepare a client

Create a new VM instance which you can use to connect to the failover cluster:

1. Return to your existing Cloud Shell session.

2. Create a new VM instance:

   gcloud compute instances create sqlclient \  --machine-type n2-standard-2 \  --subnet $SUBNET_NAME \  --image-family sql-ent-2022-win-2022 \  --image-project windows-sql-cloud \  --boot-disk-size 50 \  --boot-disk-type pd-ssd

3. Monitor the initialization process of the VM by viewing its serial port output:

   gcloud compute instances tail-serial-port-output sqlclient

   Wait for a few minutes until you see the output Instance setup finished, thenpress Ctrl+C. At this point, the VM instance is ready to be used.

4. Create a username and passwordfor the VM instance

5. Connect to the VM by using Remote Desktopand sign in using the username and password created in the previous step.

6. Right-clickStart button (or pressWin+X) and clickWindows PowerShell (Admin).

7. Confirm the elevation prompt by clickingYes.

8. Join the VM instance to your Active Directory domain:

   Add-Computer -DomainDOMAIN

   ReplaceDOMAIN with the DNS name of your Active Directory domain.

9. Restart the VM instance:

   Restart-Computer

   Wait for the VM instance to restart.

Run the test

Use thesqlclient VM to test that you can connect to the failover cluster andto verify that the failover works correctly:

1. Connect tosqlclientby using Remote Desktopand sign in using your domain user.
2. Right-clickStart button (or pressWin+X) and clickWindows PowerShell.

3. Connect to SQL Server cluster by using its network namesql and query thedm_os_cluster_nodes table:

   & "$env:ProgramFiles\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE" `   -S sql -E -Q "SELECT * FROM sys.dm_os_cluster_nodes"

   The output should look like this:

   NodeName                       status      status_description is_current_owner------------------------------ ----------- ------------------ ----------------NODE-1                                   0 up                                1NODE-2                                   0 up                                0(2 rows affected)

   Notice thatnode-1 is the current owner of the SQL Server failover cluster resource.

4. Return to Cloud Shell and bring down the node-1 VM to test the failover scenario:

   gcloud compute instances stop node-1

5. Repeat the query:

   & "$env:ProgramFiles\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE" `   -S sql -E -Q "SELECT * FROM sys.dm_os_cluster_nodes"

   The output should now look like this:

   NodeName                       status      status_description is_current_owner------------------------------ ----------- ------------------ ----------------NODE-1                                   1 down                              0NODE-2                                   0 up                                1(2 rows affected)

   Notice that despite the loss ofnode-1, the query succeeds, and shows thatnode-2 is now the current owner of the failover cluster.