Kaloyan Kosev

Availability Groups WITHOUT an Active Directory Domain in Google Cloud Platform

March 20, 2017 by

Starting with SQL Server 2016 and Windows Server 2016 there have been numerous cloud related enhancements, most of them are tightly related to Microsoft’s Azure like the ‘Cloud Witness’ but there are others that can be implemented within other public or private clouds.

One of this enhancements is the ‘Active Directory-Detached Cluster’ on top of which we will build our Availability Group. The ‘Active Directory-Detached Cluster’ works without depending on an Active Directory Domain Services (AD DS) for network names, instead it relies on Domain Name Service (DNS). No computer objects are created as well. If your project is not utilizing the Microsoft stack and you don’t require an Active Directory but you love (who doesn’t?) SQL Server this is a great approach to consider.

Let us cover all required steps from creating the virtual networks and the new VMs to configuring a fully functional Availability Group along with a listener.

You would need at least a trial account in Google Cloud Platform where you should simply create a new project and then start the Cloud Shell console.

Let us start with creating a new network named ‘suntorynw’.

gcloud compute networks create suntorynw – – mode custom

And then add two subnets, the ‘sunsubnet0’ will be used for the first node, the ‘sunsubnet02’ will be used for the second one.

gcloud compute networks subnets create sunsubnet0 – -network
suntorynw – -region europe-west1 – -range
gcloud compute networks subnets create sunsubnet1 – -network
suntorynw – -region europe-west1 – -range

Configure the firewall rules to allow all communication between the networks.

gcloud compute firewall-rules create allow-internal-ports – -network
suntorynw –allow tcp:1-65535,udp:1-65535,icmp – -source-ranges,

Allow RDP and PowerShell access to the machines from outside (from everywhere in our case, you should review the firewall and the security configurations if you are deploying this in Production).

gcloud compute firewall-rules create allow-rdp – -network suntorynw –
-allow tcp:3389 – -source-ranges
gcloud compute firewall-rules create allow-pshell – -network
suntorynw –allow tcp:5986 – -source-ranges

We will use relatively small virtual machines in our scenario with only a single disk, consider changing and optimizing the VMs for your needs.

Create the virtual machine using Windows Server 2016 and SQL Server 2016.

gcloud compute instances create suntorysql01 –machine-type n1-
standard-1 \
– -boot-disk-type pd-ssd –boot-disk-size 200GB \
– -image-project windows-sql-cloud –image-family sql-ent-2016-win-
2016 \
– -zone europe-west1-d –subnet sunsubnet0 –private-network-
ip= \
– -can-ip-forward –metadata sysprep-specialize-script-ps1=”Install-
WindowsFeature Failover-Clustering -IncludeManagementTools;”

Create the second virtual machine.

gcloud compute instances create suntorysql02 – -machine-type n1-
standard-1 \
– -boot-disk-type pd-ssd – -boot-disk-size 200GB \
– -image-project windows-sql-cloud – -image-family sql-ent-2016-win-
2016 \
– -zone europe-west1-d –subnet sunsubnet1 – -private-network-
ip= \
– -can-ip-forward –metadata sysprep-specialize-script-ps1=”Install-
WindowsFeature Failover-Clustering -IncludeManagementTools;”

Both of the virtual machines are now created and running, both of them have dynamic external IP we can use to connect to.

gcloud compute instances list

However both machines are created without any credentials being provided to us, we should use the ‘reset-windows-password’ utility provided from Google.

Reset the local administrators password for the first virtual machine suntorysql01.

gcloud compute reset-windows-password suntorysql01 – -zone europe-

Next, reset the password for suntorysql02.

gcloud compute reset-windows-password suntorysql02 – -zone europe-

Now that we have account and randomly generated passwords we can connect to the virtual machines using PowerShell initiating the connection from our machine (my laptop in this scenario).

Start PowerShell and declare the $credentials variable, it will immediately ask you for username and password, use the one you have generated from the ‘reset-windows-password’ utility.

$credentials = Get-Credential

Then initiate a new PowerShell session towards the first virtual machine.

Enter-PSSession -ComputerName -UseSSL -SessionOption
(New-PSSessionOption -SkipCACheck -SkipCNCheck) -Credential

When connection is established, change the password to something more suitable, repeat the same for the second virtual machine.

$Password = ‘str0ng_p@ssw0rd’
([ADSI] “WinNT://suntorysql02/kaloyan_kosev”).SetPassword($Password)

Now we can proceed with the network configuration of the first virtual machine – suntorysql01.

We are setting up a static IP address, mask and gateway, without DNS – we will configure this later.

netsh interface ip set address name=Ethernet static 1
netsh advfirewall firewall add rule name=”Open Port 5022 for
Availability Groups” dir=in action=allow protocol=TCP localport=5022
netsh advfirewall firewall add rule name=”Open Port 1433 for SQL
Server” dir=in action=allow protocol=TCP localport=1433

Configure the network settings for the second virtual machine – suntorysql02.

netsh interface ip set address name=Ethernet static 1
netsh advfirewall firewall add rule name=”Open Port 5022 for
Availability Groups” dir=in action=allow protocol=TCP localport=5022
netsh advfirewall firewall add rule name=”Open Port 1433 for SQL
Server” dir=in action=allow protocol=TCP localport=1433

We will name the cluster ‘sunclx’ and give it IPs and;

The listener will be named ‘sunlistener’ and the IPs it will have are and

From the Cloud Shell console, we need to add routes for the Windows cluster and availability groups based on the IPs we will use for the cluster and the listener in the different subnets.

gcloud compute routes create suntorysql01-route – -network suntorynw \
– -destination-range –next-hop-instance suntorysql01 \
– -next-hop-instance-zone europe-west1-d – -priority 1
gcloud compute routes create suntorysql01-route-listener – -network suntorynw \
– -destination-range – -next-hop-instance suntorysql01 \
– -next-hop-instance-zone europe-west1-d – -priority 1
gcloud compute routes create suntorysql02-route – -network suntorynw \
– -destination-range – -next-hop-instance suntorysql02 \
– -next-hop-instance-zone europe-west1-d –priority 1
gcloud compute routes create suntorysql02-route-listener – -network suntorynw \
– -destination-range – -next-hop-instance suntorysql02 \
– -next-hop-instance-zone europe-west1-d – -priority 1

Leaving the command line utilities aside for the moment we would need to RDP to the machines in order to proceed with the required configurations.

At the moment the virtual machines have the following settings, we need to add primary DNS suffix. An FQDN is a prerequisite for the Windows Cluster.

Do add the primary DNS suffix reach the System page from Control Panel > System and Security > System.
Open the System Properties window by clicking ‘Change settings’ > ‘Change’ > ‘More’.
Then add the desired DNS suffix of your chose – in our case it will be ‘un.com’.

After performing this on both virtual machines and restarting them you will need to add a DNS service on one of the machines.

We will add it on suntorysql01.un.com from the ‘Add roles and features’ wizard. The installation does not require restart.

Once the DNS server is installed we would need to configure it and add the required records.

Create a new Forward Lookup Zone named after the DNS suffix.

Within the new zone add the records for both nodes, the two IPs for the cluster and the two IPs for the listener.

Note: If you are creating a Windows Cluster using the local administrative account you need to alter the following registry key. Within the Google Cloud Platform, the Windows images have it already enabled.

New-ItemProperty -Path
HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System –
Name LocalAccountTokenFilterPolicy -Value 1

Finish the network configuration for suntorysql02 by setting DNS server (execute locally in a PowerShell console):

netsh interface ip set dns Ethernet static

We are now ready to create the Windows Cluster.

In the PowerShell console on suntorysql01 we will use the New-Cluster cmdlet providing name for the cluster – ‘sunclx’, listing both nodes – ‘suntorysql01’ and ‘suntorysql02’, listing the two IP addresses. The most important parameter is the ‘–AdministrativeAccessPoint DNS’ which states we are creating a ‘Active Directory-Detached Cluster’.

New-Cluster -Name sunclx -Node suntorysql01, suntorysql02 -NoStorage
-StaticAddress, –AdministrativeAccessPoint DNS

The cluster have been created, we have been prompt to review the report file for warnings. The following warning is listed as we haven’t configured a witness, so I advise to consider using File Share Witness.

An appropriate disk was not found for configuring a disk witness. The cluster is not configured with a witness. As a best practice, configure a witness to help achieve the highest availability of the cluster. If this cluster does not have shared storage, configure a File Share Witness or a Cloud Witness.

The next step is to enable the AlwaysOn feature on both SQL Server instances by executing the following PowerShell cmdlet locally on each virtual machine, you can use the GUI instead if you prefer.

Enable-SqlAlwaysOn -ServerInstance suntorysql01 -Force

Enable-SqlAlwaysOn -ServerInstance suntorysql02 -Force

Having the network, the virtual machines and the Windows Cluster prepared we can now move towards the SQL Server instances.

Let us start by creating a new user database in FULL recovery model.

Then we will setup a certificate based security as we restraining ourselves from the Active Directory and the domain accounts.

We will need to
Create a Database Master Key;
Create new security certificate;
Create an endpoint to be used by the Availability Group

We can now see the endpoint created:

Create new accounts to be used for the communication between the two SQL Servers.

The account suntorysql02acc will be created on SQL Server suntorysql01 to provide access to SQL Server Suntorysql02.

The account suntorysql01acc will be created on SQL Server suntorysql02 to provide access to SQL Server Suntorysql01.

We will use the certificates we backed up on the filesystem and import them on the opposite nodes authorizing the created accounts.

We will now create a standard Availability Group named ‘SuntoryAOAG’ using the created endpoints:

Create backups of the user database in order to restore it on the secondary instance:

Add the second SQL instance suntorysql02 to the Availability Group:

Restore the user database leaving it in restoring state:

And let’s add it to the availability group:

Create a new listener using the name we provided within the DNS, add the specified IPs from both subnets:

Testing a new connection true the listener and we are ready with our Always On Availability Group using certificate based authentication running on a Active Directory-Detached Cluster.


Kaloyan Kosev

Kaloyan Kosev

Kaloyan Kosev is a SQL Server DBA working for Hewlett-Packard in the past 5 years. His daily work routine is a mixture of troubleshooting bugs and issues, consulting and advising clients and presenting to students.

View all posts by Kaloyan Kosev
Kaloyan Kosev