arrow_back

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

GSP034

Google Cloud self-paced labs logo

Overview

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

Prerequisites

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.

    Setup

    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.

    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:

    Role

    Instance name

    Active Directory domain controller

    dc

    SQL Server instance (primary)

    cluster-sql1

    SQL Server instance

    cluster-sql2

    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:

    Domain

    gcpnext

    Username

    bootcampadmin

    Password

    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.

    $node1 = "cluster-sql1" $node2 = "cluster-sql2" $nameWSFC = "cluster-dbclus" #Name of cluster $ipWSFC1 = "10.1.1.4" #IP address of cluster in subnet 1 $ipWSFC2 = "10.2.1.4" #IP address of cluster in subnet 2

    Create the failover cluster using this PowerShell cmdlet:

    New-Cluster -Name $nameWSFC -Node $node1, $node2 -NoStorage -StaticAddress $ipWSFC1, $ipWSFC2

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

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

    (Output)

    ed70e3faf4b6c5ed.png

    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:

    BACKUP DATABASE TestDB to disk = '\\cluster-sql2\SQLBackup\TestDB01.bak'

    Click the Execute button to launch the action:

    52aefa9a0e0e0dbd.png

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

    65537e47cf824bad.png

    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.

    availability_check.png

    Click Next.

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

    9f7e07e15e3f4392.png

    Click Next.

    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:

    25672b0f996c88c5.png

    Click Next.

    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:

    initial_data_sync.png

    Click Next.

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

    e3e81454cdc690e1.png

    Click Next.

    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.

    f5db74722a54ca3b.png

    Specify the parameters for this listener:

    Listener DNS Name

    ag-listener

    Port

    1433

    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.

    Use the Add button to add each of these IP address fields to the two subnets:

    For this Subnet

    Add this Listener IPv4 address

    10.1.0.0/16

    10.1.1.5

    10.2.0.0/16

    10.2.1.5

    4dc2b74449446c60.png

    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.

    Test The Failover

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

    79a7bd08de759689.png

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

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

    2745f4af61802f26.png

    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.

    a5ab3e0d5ce663f8.png

    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.

    2bed349c8db6224a.png

    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.

    ecb97c4a89494185.png

    Congratulations!

    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:

    Next Steps

    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.