October 27, 2017

SQL Server Availability Groups in GCP – IP Alias & PowerShell

In my last post I talked about how to create a SQL Server Availability Group using Alias IP Ranges. In this post I want to share a set of PowerShell scripts that I created to automate that process. You can find the scripts in my GitHub repository.

Pre-requisites

Before you create the Availability Group there are few requirements:

  1. If you are doing a two node Availability Group you will need two subnets. In our example we create them with a /20 netmask. Notice that Alias IP Ranges will use a /24 to add 254 additional IPs to every SQL Server node. Then the node by itself needs its primary IP. The /20 netmask is big enough to meet that requirement.
  2. We need to have a Windows AD Domain. This is a requirement if using SQL Server 2012 or 2014. It is not a requirement for SQL Server 2016, but it would require some extra set of commands that are beyond this demo.
  3. We need to create Firewall rules to allow traffic between the subnets.

To help you configure those requirements using PowerShell you can take a look at the script: create-sql-instance-availability-group-prerequisites.ps1.

Configure parameters specific to your deployment

To configure the parameters specific to your deployment you just need to modify the script: parameters-config.ps1. Some of the values that you can specify are the following:

  1. Information about your GCP network and subnets
  2. Type of disk for your instances (standard or ssd)
  3. Information about your Windows Server Failover Cluster (WSFC) and Availability Group
  4. Information about your SQL Server instances including which Alias IP range to use
  5. Information about the initial login account needed to access the server
  6. Information about the the domain account used to add the servers to the Windows AD domain
  7. Information about the database to be created and added into the Availability Group
  8. Other GCP options regarding how to configure the instances

Create the Availability Group

To create the Availability Group you just need to run the script: create-sql-instance-availability-group.ps1. It will create the two nodes, do all the configuration necessary and then create the Availability Group.
Note: If you run the scripts without making any changes, they will create an Availability Group that meets the limits imposed on a GCP Free Tier account. For production environments you may want to change some parameters like using SSD for disk storage and creating the domain controller in the same subnet as the SQL Server nodes.