SQL Server Availability Groups in GCP using PowerShell
In the previous post SQL Server Availability Groups in GCP we showed some tips on how to create a SQL Server Availability Group in GCP. To automate that process we created PowerShell scripts that can be found in the GCP GitHub repository. They are also a good reference on how to use PowerShell to manage GCP projects.
Below is a guide on how to use the PowerShell scripts. If you don’t make any changes to the scripts they will create an Availability Group as described in this article.
1.) Prerequisites – Before trying to create the Availability Group make sure you have all the prerequisites:
a.) A GCP network with at least 2 subnets. We need to create the two nodes in two different subnets. We also want to create a firewall rule in GCP so all traffic is allowed between the subnets.
b.) A Windows Active Directory domain with at least one domain controller.
c.) A firewall rule in GCP to open the ports for WinRM (5986) and RDP (3389). Only the WinRM port is needed to run PowerShell, but you may want to open RDP to verify the installation.
If you need help creating the prerequisites you can check the script create-sql-instance-availability-group-prerequisites.ps1.
2.) Modify your configuration parameters – You will need to modify the file parameters-config.ps1 with the values specific to your deployment. Need to identify values like: subnets, name and IP address of the nodes, name and IP address of the cluster and Availability Group, AD account used to add nodes to the domain and information about the database. You also provide information about the version of SQL Server to use and the type of GCE instance to create. It is very important to choose the correct IP addresses for the Windows Failover Cluster and Availability Group Listener. See my previous article that talks about how to choose the correct IP.
3.) Create the Availability Group – Run the script create-sql-instance-availability-group.ps1. The script will do the following:
a.) Creates two SQL Server instances
b.) Create GCP routes for the cluster and listener IPs to send traffic to the corresponding node
c.) Change the network netmask in the Windows guest OS to be broader than the GCP subnet netmask
d.) Adds the two instances to a Windows AD domain
e.) Creates a Windows Server Failover Cluster (WSFC)
f.) Creates a SQL Server Availability Group
If you want to see a demo of the scripts you can see my presentation during Google Cloud Next’17. I went over every section of the script and then showed how to test the failover.
4.) Cleanup – If you are just testing the scripts and want to delete all the instances and the network after done testing, you can run the script create-sql-instance-availability-group-cleanup.ps1.
I have a new version of the PowerShell scripts that will create the Availability Group using a new feature in GCP called Alias IP Ranges. While Alias IP Ranges is primarily intended to be used by containers, it also simplifies the creation of an Availability Group in GCP. It allows to “alias” the IP addresses for the Windows Cluster and Availability Group to the corresponding node. Currently we create GCP routes to send traffic intended for the cluster or listener back to the node. That step can be eliminated by using Alias IP. I will be creating another post on how to run that other set of scripts.