Configuring and Deploying Windows SQL Server on Google Cloud
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:
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).
- Time to complete the lab---remember, once you start, you cannot pause a lab.
Wait for your lab resources to be instantiated
While you wait, you can read the documentation on configuring SQL Server availability groups. You can also download the Microsoft SQL Server Always On Availability Group on Compute Engine white paper to understand the technical background for this lab. 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.
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:
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.
RDP into virtual machine
Find cluster-sql1 in the list of VM instances, and click on the RDP button. This instance will be used to configure the cluster.
When you are prompted for credentials, use these:
As displayed under DomainAccountPassword on the lab page.
In the RDP session, use Search to find Powershell.
In the search results, right-click on PowerShell and choose Run as administrator.
Inside PowerShell, set variables that reflect your cluster environment.
Create the failover cluster using this PowerShell
Enable AlwaysOn High Availability for both nodes in the cluster using these
The Windows Server Failover Cluster is ready. Next, you'll create the availability group.
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.
In the RDP session for cluster-sql1, use Search to find SQL Server Management. In the search results, right-click on SQL Server Management Studio 17 and choose Run as administrator.
In the Connect to Server dialog, browse for more databases, choose the SQL Server engine running on cluster-sql1 and click Connect.
In the left-side Object Explorer panel, right-click on CLUSTER-SQL1 and choose New Query... .
Enter the following SQL:
Click the Execute button to launch the action:
Right-click on AlwaysOn High Availability and select New Availability Group Wizard:
You may need to expand the window to see the entire screen. Click Next.
On the Specify Name page, name the availability group
cluster-ag. Leave the Database Level Health Detection and Per Database DTC Support box unchecked.
On the Select Databases page, click the box for the TestDB database to specify that you want to replicate it:
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. Click Connect.
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:
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
The Validation page generates a warning because there is no listener. Ignore this warning:
Review the choices in the Summary page, then click Finish.
When the wizard completes successfully, click Close.
Then, in the left hand Object Explorer panel, expand the folder AlwaysOn High Availability, then expand the Availability Groups folder beneath it.
Right-click on the new availability group
cluster-ag and select Add Listener.
Specify the parameters for this listener:
Listener DNS Name
Note: If you leave the Network Mode at the default setting, DHCP, you will not be able to proceed to the next step.
Use the Add button to add each of these IP address fields to the two subnets:
For this Subnet
Add this Listener IPv4 address
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.
Test The Failover
In SQL Server Management Studio go to File > Connect Object Explorer and type in "ag-listener" to connect to
In Object Explorer, navigate to AlwaysOn High Availability > Availability Groups > cluster-ag.
Right-click on cluster-ag and choose Show Dashboard.
Notice how CLUSTER-SQL1 is currently the Primary node. 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.
Next you will switch to the other node as the primary node.
Start the Failover Wizard
Click on the Start Failover Wizard link in the upper right-hand corner.
You may need to expand your window. In the wizard, click Next then Next. You will arrive at the Connect to Replica screen.
Click on Connect... to connect to CLUSTER-SQL2 and follow the wizard. Use the default Windows authentication.
Click Next, then Finish. 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.
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 in this quest and get immediate completion credit if you've taken this lab. See other available quests.
Take Your Next Lab
Continue your quest with Deploying a Fault-Tolerant Microsoft Active Directory Environment, or check out these suggestions:
- Learn more about SQL Server availability groups in Google Cloud.
- Read the full Microsoft SQL Server Always On Availability Group on Compute Engine white paper.
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 May 20, 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.