SQL Server Availability Groups in GCP using IP Alias Ranges

On a previous post I talked about how to create an Availability Group in GCP. One key requirement to make it work is to change the netmask of the guest OS to be broader than the GCP netmask (for example /16 in guest OS vs. /24 in GCP.) Then you need create routes for the IPs used by the WSFC and listener. Another simpler option is to use Alias IP Ranges which allows to assign a range of extra IPs to your instance. We only need two extra IPs (one for the WSFC and another for the Listener.) Right now Alias IP Ranges only supports /32 (1 IP) or /24 (254 IPs) for the secondary netmask. Because we need 2 IPs we will have to use /24. If you don’t mind the fact that you only use 2 IPs and the other 252 you will not use, then you can use this technique. Alias IP Ranges was intended to be used by containers, but it can also be used to create a SQL Server Availability Group.
Below is a diagram on how the 2-node Availability Group is configured:

These are the steps to configure the Availability Group.

1. Create the VPC Network

We only need 2 subnets, but we create 3 to put our domain controller in a different subnet. Notice how the subnets have a /20 netmask (4,094 IPs). They need to be have enough IPs for the SQL node (1 IP in our example) plus the Alias IP range which has 254 IPs (/24) .
NOTE: We use region=us-east1 and zone=us-east1-b for the nodes, but you can change it to your preferred location. We also put the the domain controller (wsfcsubnet3) in us-east4. We are running these commands in a free account that has a limit of 8 CPUs per region and the two nodes need 4 CPUs each. But, for production loads you will probably want the domain controller in the same region as the SQL Server nodes.


2. Create the firewall rules

We want to allow all traffic between the subnets and we want to allow RDP connections. In this example we open RDP to the world ( but you may want to change it to only allow your IP address.


3. Create a domain controller

Create a new instance to be our domain controller. We like to keep the domain controller in a different subnet (wsfcsubnet3), but it is not absolutely necessary. Notice that boot-disk-type is set to pd-standard to save money during our demo, but pd-ssd is the recommended value.

Wait until the instance is configured by taking a look at the serial port output. You should press Ctrl-C once you see the text “Instance setup finished” to stop this command.

Create a username/password for a user called “config-user.” This command will provide the information that you need to do an RDP connection.


4. Create a Windows AD Domain

Do an RDP connection to the new domain controller. Before running the command below, please assign a password to the local Administrator account or otherwise you will get an error. Then, open a PowerShell console is elevated mode and run the following command. It will prompt you to type the username and password used to logon to the instance (“config.user”). The name of the Windows domain that will be created is “dbeng.com.”

After the computer restarts, logon again to verify that the Domain administrator password works which should be the password previously assigned to the local Administrator.

5. Create two SQL Server instances

When creating the two instances we set the boot-disk-type to pd-standard to save money in our test, but pd-ssd is the recommended value. We also use an image with SQL Server 2016 Standard on Windows 2016 . You can run the command gcloud compute images list to see a list of all the SQL Server images under the project windows-sql-cloud. We also use n1-standard-4 for machine-type but for production you may want to use a “highmem” machine type (run gcloud compute machine-types list). Pay attention to the parameter network-interface as it is critical to type the correct subnet, private network and alias IP.

Wait until the instances are configured. Press Ctrl-C once you see the text “Instance setup finished” to stop this command.

Create a username/password for a user called “config-user.”


6. Configure Windows firewall and add servers to the domain

In both nodes: Do an RDP connection using the login config.user. Open a PowerShell console in elevated mode to run these commands. They will change the DNS server to be the domain controller, open the firewall ports for SQL Server and Availability Groups, and add the servers to the domain. When prompted type the password for the domain administrator.

The servers will restart. Do an RDP connection again but this time log on as domain administrator.

7. Create a Windows Server Failover Cluster (WSFC)

Only in Node 1:
Open a PowerShell console in elevated mode to run these commands. Make sure you provide the correct IP adddress for the WSFC. It has to be an address within the IP Alias range in each node.


8. Create a folder for the database backups

Only in Node 2:
Open a PowerShell console in elevated mode to run these commands. We create a folder in Node 2 to store the backups for the initial synchronization. This could also be a network share.


9. Create folders for the database data and log files

Both nodes:
Run these commands in PowerShell. They create the folders for the data and transaction log for the database.


10. Create the Test database

Only in Node 1:
Run this command in PowerShell. It will create a TestDB database in Node 1.


11. Create Endpoint in Node 1

Only in Node 1:
Create an endpoint in Node 1 and grant connect permission to the remote node (Node 2).


12. Create Endpoint in Node 2

Only in Node 2:
Create an endpoint in Node 2 and grant connect permission to the remote node (Node 1).


13. Create the Availability Group

Only in Node 1:
The following PowerShell commands will create the Availability Group. It is very important to provide the correct IP for the listener in both nodes. It has to be an IP address within the range of the IP Alias. You should run these commands one at a time in case you get any errors.


14. Test your Availability Group

You should now be ready to test the Availability Group. If you followed these instructions, you can use “ag-listener” as the name of the SQL Server when testing your Availability Group.