sqlserver-alwayson

SQL Server Always On Groups blueprint

This is an blueprint of building SQL Server Always On Availability Groups using Fabric modules. It builds a two node cluster with a fileshare witness instance in an existing VPC and adds the necessary firewalling.

The actual setup process (apart from Active Directory operations) has been scripted, so that least amount of manual works needs to performed:

• Joining the domain using appropriate credentials
• Running an automatically generated initialization script (C:\InitializeCluster.ps1)
• Creating the Availability Groups using the wizard (please note that healthchecks are automatically configured when the appropriate AGs are created)

To monitor the installation process, the startup scripts log output to Application Log (visible under Windows Logs in Event Viewer) and to C:\GcpSetupLog.txt file.

Files

name	description	modules
instances.tf	Creates SQL Server instances and witness.	compute-vm
main.tf	Module-level locals and resources.	project
outputs.tf	Module outputs.
secrets.tf	Creates SQL admin user password secret.	secret-manager
service-accounts.tf	Creates service accounts for the instances.	iam-service-account
variables.tf	Module variables.
vpc.tf	Creates the VPC and manages the firewall rules and LB.	net-address · net-lb-int · net-vpc · net-vpc-firewall

Variables

name	description	type	required	default
ad_domain_fqdn	Active Directory domain (FQDN).	string	✓
ad_domain_netbios	Active Directory domain (NetBIOS).	string	✓
network	Network to use in the project.	string	✓
prefix	Prefix used for resource names.	string	✓
project_id	Google Cloud project ID.	string	✓
sql_admin_password	Password for the SQL admin user to be created.	string	✓
subnetwork	Subnetwork to use in the project.	string	✓
always_on_groups	List of Always On Groups.	list(string)		["bookshelf"]
boot_disk_size	Boot disk size in GB.	number		50
cluster_name	Cluster name (prepended with prefix).	string		"cluster"
data_disk_size	Database disk size in GB.	number		200
health_check_config	Health check configuration.	…		{…}
health_check_port	Health check port.	number		59997
health_check_ranges	Health check ranges.	list(string)		["35.191.0.0/16", "209.85.152.0/22", "209.85.204.0/22"]
managed_ad_dn	Managed Active Directory domain (eg. OU=Cloud,DC=example,DC=com).	string		""
node_image	SQL Server node machine image.	string		"projects/windows-sql-cloud/global/images/family/sql-ent-2019-win-2019"
node_instance_type	SQL Server database node instance type.	string		"n2-standard-8"
node_name	Node base name.	string		"node"
project_create	Provide values if project creation is needed, uses existing project if null. Parent is in 'folders/nnn' or 'organizations/nnn' format.	object({…})		null
region	Region for resources.	string		"europe-west4"
shared_vpc_project_id	Shared VPC project ID for firewall rules.	string		null
sql_client_cidrs	CIDR ranges that are allowed to connect to SQL Server.	list(string)		["0.0.0.0/0"]
vpc_ip_cidr_range	Ip range used in the subnet deployef in the Service Project.	string		"10.0.0.0/20"
witness_image	SQL Server witness machine image.	string		"projects/windows-cloud/global/images/family/windows-2019"
witness_instance_type	SQL Server witness node instance type.	string		"n2-standard-2"
witness_name	Witness base name.	string		"witness"

Outputs

name	description	sensitive
instructions	List of steps to follow after applying.

Test

module "test" {
  source = "./fabric/blueprints/data-solutions/sqlserver-alwayson/"
  project_create = {
    billing_account_id = "123456-123456-123456"
    parent             = "folders/12345678"
  }
  project_id         = "project-1"
  prefix             = "test"
  network            = "example-network"
  subnetwork         = "example-subnetwork"
  sql_admin_password = "password"
  ad_domain_fqdn     = "ad.example.com"
  ad_domain_netbios  = "ad"
}
# tftest modules=12 resources=40