Configuring and Deploying Windows SQL Server on Google Cloud

Join Sign in

Configuring and Deploying Windows SQL Server on Google Cloud

1 hour 30 minutes 5 Credits


Google Cloud self-paced labs logo


Compute Engine lets you use SQL Server images to start instances that have Windows Server with SQL Server pre-installed. In your own environment, because of per-minute billing, you would pay for both Windows Server and SQL Server licenses only when you use them.

Always On Availability Groups (AGs) makes sense in the cloud for a few reasons:

  • Automatic failover of multiple databases.​ This is a common high-availability requirement, and on-premises\DBAs usually solve this with failover cluster instances (FCIs). Unfortunately, that requires shared storage, which isn't usually available in today's infrastructure-as-a-service (IaaS) cloud. There are some workarounds involving third-party software or UNC paths, but neither of those are great options in IaaS. AGs can pull this off without shared storage.
  • Automatic repair of corrupt data pages.​ Each replica has its own copy of the database's data pages. Only logged transactions are sent from one replica to another, not data pages. When a replica encounters a corrupt data page, it can request a clean copy of the page from another replica, and heal itself live. This doesn't provide protection from every kind of corruption, but it can be a real lifesaver in many situations.
  • Potentially lower downtime for patching.​ For minimal downtime, patch the secondary replica, make sure it patched successfully, then take a brief outage to fail over from the primary replica to the newly patched secondary. After that, you can patch the former primary instance. If something goes wrong when patching either replica, simply build a new one to replace it and join it into the AG.
  • Possibly easier scalability with lower downtime​. When you want to switch to a bigger instance type, you can provision a new one, add it into the AG, fail over to it, and then remove the previous instance. You can scale up and down to handle load with this approach, although this isn't commonly used on a scripted basis to handle daily peaks and valleys. It's more of a seasonal approach.

In this lab you will configure SQL Server in a highly available configuration in Compute Engine. This lab provides a running environment for you which allows you to jump right in to the interesting learning. Launching Windows Server systems in the background can take ~20 minutes to fully configure. Watch the green progress bar at the top of the page to see the progress.

What you'll learn

  • How to configure the Failover Cluster Manager

  • How to use Powershell to configure and create a failover cluster

  • How to enable AlwaysOn High Availability mode


This lab assumes you have:

  • A working RDP Client and experience using it (no instructions on RDP are given)
  • Comfort at the command line
  • Familiarity with Powershell

If you are using a Chromebook, two finger tap is how to right click.


Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

  • Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.
  • Time to complete the lab---remember, once you start, you cannot pause a lab.
Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

Task 1. Wait for your lab resources to be instantiated

While you wait, you can read the documentation on configuring SQL Server availability groups. To understand the technical background for this lab, you can also download the Microsoft SQL Server Always On Availability Group on Compute Engine white paper. This reading is optional. The documentation is more general than these lab instructions; it will help you configure your own environment.

Your lab environment is ready when the green progress bar disappears and you see Lab Running on the lab page.

Task 2. Configuring the failover cluster manager

Go see the existing VMs using the Cloud Console.

  • In the console, click on Compute Engine > VM instances.

These are the instances you should see:


Instance name

Active Directory domain controller


SQL Server instance (primary)


SQL Server instance


In the next steps you will:

  • Enable failover clustering on the instances in your availability group
  • Configure one instance to act as the Failover Cluster Manager.
  • Enable AlwaysOn High Availability on all instances in the group.

Copy and paste with the RDP client

Once you are securely logged in to your instance, you may find yourself copying and pasting commands from the lab manual.

  • To paste, hold the CTRL-V keys (if you are a Mac user, using CMND-V will not work.)
  • If you are in a Powershell window, be sure that you have clicked in to the window or else the paste shortcut won't work.
  • If you are pasting into putty, right click.

Task 3. RDP into virtual machine

  1. Find cluster-sql1 in the list of VM instances, and click on the RDP button.

    This instance will be used to configure the cluster.

  2. When you are prompted for credentials, use these:






As displayed under DomainAccountPassword on the lab page.

  1. In the RDP session, use Search to find Powershell.

  2. In the search results, right-click on PowerShell and choose Run as administrator.

Note: If you launch PowerShell without having chosen Run as administrator, the lab will not run correctly.
  1. Inside PowerShell, set variables that reflect your cluster environment:

$node1 = "cluster-sql1" $node2 = "cluster-sql2" $nameWSFC = "cluster-dbclus" #Name of cluster $ipWSFC1 = "" #IP address of cluster in subnet 1 $ipWSFC2 = "" #IP address of cluster in subnet 2
  1. Create the failover cluster using this PowerShell cmdlet:

New-Cluster -Name $nameWSFC -Node $node1, $node2 -NoStorage -StaticAddress $ipWSFC1, $ipWSFC2 Note: If you see an error message like the one in the below screenshot ("There were issues while creating the clustered role..."), disregard it. The system is warning you that this cluster will not have a witness disk.

The error message 'There were issues while creating the clustered role that may prevent it from starting.'

  1. Enable AlwaysOn High Availability for both nodes in the cluster using these cmdlets:

Enable-SqlAlwaysOn -ServerInstance $node1 -Force Enable-SqlAlwaysOn -ServerInstance $node2 -Force


PS C:\windows\system32> Enable-SqlAlwaysOn -ServerInstance $node1 -Force PS C:\windows\system32> Enable-SqlAlwaysOn -ServerInstance $node2 -Force PS C:\windows\system32> _ Note: On cluster-sql2 the folder C:\SQLBackup and the Windows share \\cluster-sql2\SQLBackup was already created for you. This share will be used for the initial backup to synchronize the servers. You can choose another location for a shared folder as long as both instances have access to the shared folder.

The Windows Server Failover Cluster is ready.

Next, you'll create the availability group.

Task 4. Create the Availability group

The SQL instance cluster-sql1 already has a test database created for you. You will add that database into an availability group. Before doing that, you first need to backup the database.

  1. In the RDP session for cluster-sql1, use Search to find SQL Server Management.

  2. In the search results, right-click on SQL Server Management Studio 17 and choose Run as administrator.

Note: If you launch SQL Server Management Studio 17 without having chosen Run as administrator, the following steps might fail.
  1. In the Connect to Server dialog, browse for more databases, choose the SQL Server engine running on cluster-sql1 and click Connect.

  2. In the left-side Object Explorer panel, right-click on CLUSTER-SQL1 and choose New Query... .

  3. Enter the following SQL:

BACKUP DATABASE TestDB to disk = '\\cluster-sql2\SQLBackup\TestDB01.bak'
  1. Click the Execute button to launch the action.

  2. Right-click on AlwaysOn High Availability and select New Availability Group Wizard.

    You may need to expand the window to see the entire screen.

  3. Click Next.

  4. On the Specify Availability Group Options page, name the availability group cluster-ag, and leave the Database Level Health Detection and Per Database DTC Support box unchecked.

  5. Click Next.

  6. On the Select Databases page, click the box for the TestDB database to specify that you want to replicate it.

  7. Click Next.

  8. On the Specify Replicas page, use the Add Replica... button to add cluster-sql2 as a replica by typing it in. You can connect to it with the default Windows authentication.

  9. Click Connect.

  10. Then configure both replicas for automatic failover and synchronous commit by checking the boxes for each.

When you complete this action, the page will look like this:

The Specify Replicas page, wherein the primary and secondary version of CLUSTER-SQL are listed.

  1. Click Next.

  2. On the Select Data Synchronization page, add the network share to keep the backup of the database for initial synchronization. For this example, you'll specify \\cluster-sql2\SQLBackup:

The Select Initial Data Synchronization page, wherein \cluster-sql2\SQLBackup has been entered into the text field.

  1. Click Next.

    The Validation page generates a warning because there is no listener. Ignore this warning:

    The Validation page, with the warning 'Checking the listened configuration'.

  2. Click Next.

  3. Review the choices in the Summary page, then click Finish.

  4. When the wizard completes successfully, click Close.

  5. Then, in the left hand Object Explorer panel, expand the folder AlwaysOn High Availability, then expand the Availability Groups folder beneath it.

  6. Right-click on the new availability group cluster-ag and select Add Listener.

  7. Specify the parameters for this listener:

Listener DNS Name




Network Mode

Static IP

Note: If you leave the Network Mode at the default setting, DHCP, you will not be able to proceed to the next step.
  1. Use the Add button to add each of these IP address fields to the two subnets:

For this Subnet

Add this Listener IPv4 address

The New Availability Group Listener page, wherein the IP addresses are listed along with their subnets.

  1. Click OK.

In the next activity, you will be able to connect to SQL Server by means of ag-listener rather than by means of an instance. The connection by means of ag-listener points to the instance that is currently active.

Task 5. Test the failover

  1. In SQL Server Management Studio go to File > Connect Object Explorer and type in "ag-listener" to connect to ag-listener.

    The Connect to Server pop-up, wherein ag-listener is added to the Server name field, along with buttons such as Connect and Help.

  2. In Object Explorer, navigate to AlwaysOn High Availability > Availability Groups > cluster-ag.

  3. Right-click on cluster-ag and choose Show Dashboard.

    The Object Explorer pane, whereing Show Dashboard is highlighted within the cluster-ag (Primary) sub-menu.

    Notice how CLUSTER-SQL1 is currently the Primary node.

  4. Confirm that the Synchronization State is shown as Synchronized for both nodes.

    If a node is shown as Synchronizing, wait for its state to change to Synchronized before you proceed.

    A list of available replicas, whereint the Synchronization State column displays both replicas as synchronized.

Next you will switch to the other node as the primary node.

Task 6. Start the Failover wizard

  1. Click on the Start Failover Wizard link in the upper right-hand corner.

    You may need to expand your window.

  2. In the wizard, click Next then Next.

    You will arrive at the Connect to Replica screen.

  3. Click on Connect... to connect to CLUSTER-SQL2 and follow the wizard.

  4. Use the default Windows authentication.

  5. Click Next, then Finish.

  6. Then click Close to close the wizard.

After a few seconds (up to a possible maximum of 30 seconds), you will notice that CLUSTER-SQL2 is now the Primary node.

The Available replicas pop-up, wherein the Role column displays 'Primary' within the CLUSTER-SQL2 details.


In this lab you configured SQL Server in a highly available configuration and how to manage Compute Engine instances and Cloud Storage buckets from PowerShell. You can also manage Cloud SQL and Cloud CDN resources using PowerShell.

Finish your quest

This self-paced lab is part of the Windows on Google Cloud quest. A quest is a series of related labs that form a learning path. Completing this quest earns you a badge to recognize your achievement. You can make your badge or badges public and link to them in your online resume or social media account. Enroll or any quest that contains this lab and get immediate completion credit. See the Google Cloud Skills Boost catalog to see all available quests.

Take your next lab

Continue your quest with Deploying a Fault-Tolerant Microsoft Active Directory Environment, or check out these suggestions:

Next steps/ learn more

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual last updated August 17, 2022

Lab last tested May 20, 2022

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.