Migrating Microsoft SQL Server from AWS to Google Cloud

Last reviewed 2023-05-05 UTC

This document shows you how to migrate a Microsoft SQL Server instance installed on Amazon Elastic Compute Cloud (Amazon EC2) to a Microsoft SQL Server instance on Compute Engine in Google Cloud. This migration is solely based on built-in database technology provided by Microsoft SQL Server. This method is effectively a zero downtime method that uses an Always On Availability Group. The Always On Availability Group spans AWS and Google Cloud over VPN, and it enables the replication of the Microsoft SQL Server database. This document assumes that you're familiar with network setup, Google Cloud, Compute Engine, AWS, and Microsoft SQL Server.

If you want to perform replication only, you can follow the steps in this tutorial, but stop after you add test data and omit the cutover steps.

Objectives

  • Deploy a multi-cloud Microsoft SQL Server Always On Availability Group that spans a Microsoft SQL Server in Amazon EC2 and a Microsoft SQL Server in Google Cloud on Compute Engine.
  • Set up a primary Microsoft SQL instance in Amazon EC2.
  • Set up the Microsoft SQL Server instance in Google Cloud as secondary to the primary Microsoft SQL Server in AWS (target of data replication).
  • Complete the data migration by making the secondary Microsoft SQL Server in Google Cloud the primary Microsoft SQL Server in Google Cloud.

Costs

In this document, you use the following billable components of Google Cloud:

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

This tutorial also requires resources on AWS that might incur cost.

Before you begin

  1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  2. Make sure that billing is enabled for your Google Cloud project.

  3. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

Understanding database migration

Database migration moves the data from a source database to a target database. In general, you can migrate a subset of the data or have a different schema in the source and target database. However, this tutorial addresses homogeneous database migration that requires the migration of the complete database without changes—the target database is a copy of the source database.

Zero downtime database migration

The term zero downtime refers to the fact that during the migration, the clients that access the source database remain fully operational and aren't interrupted. The only downtime occurs when the clients have to reconnect to the target database after the migration is complete. Although this method isn't truly zero downtime, the term refers to this scenario of minimal downtime.

For a general discussion of database migration, see Database Migration - Concepts and Principles (Part 1) and Database Migration - Concepts and Principles (Part 2). These articles provide an overview of the possible complexity of database migration in different scenarios.

Database migration using Microsoft SQL Server technology

Some database migration technologies provide separate components and services. When database migration requires a copy of the source database, you can use built-in Microsoft SQL Server technology.

This tutorial uses Microsoft SQL Server Always On Availability Group technology to connect the source database (primary) to a target database (secondary). This technology provides asynchronous replication from the primary to the secondary database. Because the primary database is in Amazon EC2 and the secondary database is in Google Cloud on Compute Engine, the replication accomplishes the database migration. After all data is migrated through asynchronous replication, the secondary is promoted to primary and the clients can reconnect to the new primary for continued processing.

This approach supports explicit testing by a trial replication into a test target database: you can start the replication, keep it running for a while, and then stop the replication. The test target database is in a consistent state, and you can use it to test the application. After testing is complete, you can delete the test target database, and initiate replication for a live database.

Multi-cloud database migration architecture

The following diagram shows the overall deployment architecture for a multi-cloud database migration:

An Always On Availability Group connects an AWS database to a Google Cloud database.

The preceding diagram shows the source (primary) SQL Server Database in AWS as an Amazon EC2 instance. The diagram also shows the target database (secondary) in Google Cloud. The databases are connected by an Always On Availability Group. The network connection between AWS and Google Cloud is assumed to be a secured HA VPN connection.

Setting up a multi-cloud Microsoft SQL Server availability group

In the following sections, you set up a two node Always On Availability Group where the primary node resides in AWS, and the secondary node resides in Google Cloud. This configuration is described earlier in this document in Multi-cloud database migration architecture.

The following tables provide a summary of the nodes and IP addresses that you set up in this tutorial. For every database VM, you allocate two IP addresses in addition to the primary IP address: one IP address for the Windows Server Failover Cluster (WSFC) and one IP address for the Availability Group Listener.

Provider Instance Primary IP WSFC and Availability Group Listener IPs WSFC Availability Group
AWS cluster-sql1 192.168.1.4 192.168.1.5
192.168.1.6
Name: cluster-dbclus Name: cluster-ag
Listener: ag-listener
Google Cloud cluster-sql2 10.1.1.4 10.1.1.5
10.1.1.6
Provider Instance Primary IP
AWS dc-windows 192.168.1.100 Domain controller

The instructions use these names and IP addresses as examples. If you want to use your own names and IP addresses, replace the example values in the instructions.

Prerequisites for AWS

On AWS you should have two virtual machines: one that runs the domain controller, and one that runs a SQL Server. The domain controller used as an example in this tutorial has the following configuration:

Domain                    : dbeng.com
Domain controller         : Name: dc-windows
                            Private IP: 192.168.1.100
VPC Subnet                : 192.168.1.0/24
SQL Server service account: dbeng\sql_service

The SQL Server VM used as an example in this tutorial is part of a Windows Active Directory domain in Amazon EC2. The server has two secondary IP addresses to be used by the WSFC and Availability Group Listener. The SQL Server VM has the following configuration:

VM Instance : Name: cluster-sql1
              Private IP: 192.168.1.4
              Secondary Private IPs: 192.168.1.5, 192.168.1.6
VPC Subnet  : 192.168.1.0/24

You can use the service account NT SERVICE\MSSQLSERVER as the SQL Server service account. During the Always On Availability Group setup, you grant access to the machine accounts (dbeng\cluster-sql1$, dbeng\cluster-sql2$) instead of the domain account. The following section provides the commands to configure the availability group.

Prerequisites for connectivity between AWS and Google Cloud

To connect your AWS project with your Google Cloud project, set up the following network connectivity:

  1. Set up a Google Virtual Private Cloud and an AWS VPC in your respective projects, and configure a VPN between the VPCs. For information about how to set up a VPN between Google Cloud and AWS, see Multi-cloud VPN and multi-zone subnetworks — network setup for multi-cloud database deployments.
  2. In Cloud Shell, create a subnet in the Google Cloud project where you're creating the SQL Server instance. If you already have a subnet, you can use it, but make sure to set up the firewall rules in the next step.

    gcloud compute networks create demo-vpc --subnet-mode custom
    
    gcloud compute networks subnets create demo-subnet1 \
      --network demo-vpc --region us-east4 --range 10.1.1.0/24
    

    This tutorial uses the following values:

    • VPC: demo-vpc
    • Subnet: demo-subnet1; 10.1.1.0/24

    The subnet appears on the Google Cloud console VPC networks page.

  3. In your Google Cloud project, create a firewall rule to open all traffic between your Google Cloud subnet and your AWS subnet:

    gcloud compute firewall-rules create allow-vpn-ports \
      --network demo-vpc --allow tcp:1-65535,udp:1-65535,icmp \
      --source-ranges 10.1.1.0/24,192.168.1.0/24
    

    The firewall rule appears on the Google Cloud console Firewall policies page.

  4. In your AWS project, create a firewall rule in the Security Group to open all traffic between your Google Cloud subnet and your AWS subnet, as shown in the following screenshot:

    AWS Inbound rules are set to allow all traffic, all protocols, and all port ranges.

    In a production environment, you might consider opening only the required TCP/UDP ports. Opening only the required ports limits potentially harmful traffic and follows a least necessary principle.

Creating an instance in Google Cloud for the Always On Availability Group

This tutorial works with the following Microsoft SQL Server editions and features:

  • Edition:
    • Microsoft SQL Server 2016 Enterprise Edition, or
    • Microsoft SQL Server 2017 Enterprise Edition, or
    • Microsoft SQL Server 2019 Enterprise Edition, or
    • Microsoft SQL Server 2022 Enterprise Edition, or
    • Microsoft SQL Server 2016 Standard Edition, or
    • Microsoft SQL Server 2017 Standard Edition, or
    • Microsoft SQL Server 2019 Standard Edition, or
    • Microsoft SQL Server 2022 Standard Edition
  • Feature: Always On Availability Groups

The following instructions use the image for Microsoft SQL Server 2019 Enterprise Edition: sql-ent-2019-win-2019. If you want to install Microsoft SQL Server 2017, 2016 or 2022 Enterprise Editions, use sql-ent-2017-win-2019, sql-ent-2016-win-2019, respectively sql-ent-2022-win-2019, instead. For a list of all images, see the Compute Engine Operating system details page.

In the following steps, you create a SQL Server instance in Google Cloud for the availability group. The instance uses the following IP address configuration with Alias IP addresses:

VM Instance: Name: cluster-sql2
             Private IP: 10.1.1.4
             Secondary Private IPs: 10.1.1.5, 10.1.1.6

You create an instance named cluster-sql2 from public SQL Server images, with a 200-GB boot disk size and an n1-highmem-4 machine type. SQL Server instances usually require more compute resources than the domain controller instance. If you need additional compute resources later, you can change the machine type for these instances. If you need additional storage space, add a disk or resize the persistent boot disk. In larger availability groups, you can create several instances.

The following steps also include the --metadata sysprep-specialize-script-ps1 flag that runs a Microsoft PowerShell command during instance creation to install the Failover-Clustering feature.

  1. In Cloud Shell, create a SQL Server instance in Google Cloud that uses the same operating system version as in AWS:

    gcloud compute instances create cluster-sql2 --machine-type n1-highmem-4 \
      --boot-disk-type pd-ssd --boot-disk-size 200GB \
      --image-project windows-sql-cloud --image-family sql-ent-2019-win-2019 \
      --zone us-east4-a \
      --network-interface "subnet=demo-subnet1,private-network-ip=10.1.1.4,aliases=10.1.1.5;10.1.1.6" \
      --can-ip-forward \
      --metadata sysprep-specialize-script-ps1="Install-WindowsFeature Failover-Clustering -IncludeManagementTools;"
    
  2. Set a Windows username and password before you connect to the instance.

  3. When you use Remote Desktop Protocol (RDP) from your laptop, create a firewall rule that allows access to the instance.

  4. Connect to the Google Cloud instance using RDP and open an elevated PowerShell (run as administrator).

  5. In this tutorial, you configure a local DNS to use the domain controller in AWS (192.168.1.100) to avoid creating another VM in Google Cloud. For production workloads, we recommend that you use a domain controller (primary or secondary) in Google Cloud to avoid authenticating over the VPN tunnel.

    In the elevated PowerShell, you should be able to ping the domain controller 192.168.1.100:

    ping 192.168.1.100
    

    If the ping fails, make sure that the firewall and VPN tunnel are configured correctly between AWS and Google Cloud, as described in Prerequisites for connectivity earlier in this document.

  6. Because the server was originally set up with DHCP, change the instance to use static IP addresses:

    netsh interface ip set address name=Ethernet static 10.1.1.4 255.255.255.0 10.1.1.1 1
    

    After you run the preceding command, you lose your connection. Reconnect in RDP.

  7. Configure local DNS to use the domain controller in AWS and open the local firewall ports for SQL Server. Opening the firewall ports lets the SQL Server connect to remote SQL Servers.

    netsh interface ip set dns Ethernet static 192.168.1.100
    
    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
    
  8. Add the instance to the Windows domain:

    Add-Computer -DomainName "dbeng.com" -Credential "dbeng.com\Administrator" -Restart -Force
    

    The command prompts you for the domain administrator credentials. When the command finishes running, the instance restarts.

    If the command doesn't run, make sure that you're running it as administrator.

  9. Use the dbeng\Administrator account to reconnect to your instance using RDP.

  10. Set the SQL Server service account:

    1. Open SQL Server 2019 Configuration Manager.
    2. On the SQL Server Services tab, right-click SQL Server (MSSQLSERVER), and then click Properties.
    3. Set the account and password for dbeng\sql_service.
    4. Restart SQL Server.
  11. Rename the SQL Server instance to match the computer name and restart SQL Server:

    Invoke-Sqlcmd -Query "EXEC sp_dropserver @@SERVERNAME, @droplogins='droplogins'"
    
    Invoke-Sqlcmd -Query "EXEC sp_addserver '$env:COMPUTERNAME', local"
    
    Stop-Service -Name "MSSQLServer" -Force
    
    Start-Service -Name "MSSQLServer"
    

Next, you configure the instance in AWS.

Configure the instance in AWS

This tutorial assumes that you have already configured the following in AWS:

  • The SQL Server instance is part of the Active Directory domain.
  • Local DNS is working properly and the name of the remote server in Google Cloud (cluster-sql2.dbeng.com) can be translated into an IP address.
  • Firewall rules are opened between the subnets on AWS and Google Cloud.

To configure cluster-sql1 in AWS, do the following:

  1. Connect to the AWS instance using RDP (cluster-sql1).
  2. Open an elevated PowerShell (run as administrator).
  3. Install Windows Failover Clustering if it isn't already installed.

    Install-WindowsFeature Failover-Clustering -IncludeManagementTools
    

    This command requires a restart if the feature wasn't already installed. After the restart, continue with the next step.

  4. Open the local firewall ports for the SQL Server instance in AWS:

    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
    
    netsh advfirewall firewall add rule name="ICMP Allow incoming V4 echo request" protocol="icmpv4:8,any" dir=in action=allow
    
  5. Rename the SQL Server instance to match the computer name and restart SQL Server:

    Invoke-Sqlcmd -Query "EXEC sp_dropserver @@SERVERNAME, @droplogins='droplogins'"
    
    Invoke-Sqlcmd -Query "EXEC sp_addserver '$env:COMPUTERNAME', local"
    
    Stop-Service -Name "MSSQLServer" -Force
    
    Start-Service -Name "MSSQLServer"
    
  6. Validate that the instance in AWS can connect to the instance in Google Cloud when using the remote instance name. To test the connection, run the following commands from a domain account that has granted connect access to the SQL Server.

    1. Test the network connection:

      ping -4 cluster-sql2.dbeng.com
      

      The output looks like the following:

      RESULTS:
      Pinging cluster-sql2.dbeng.com [10.1.1.4] with 32 bytes of data:
      Reply from 10.1.1.4: bytes=32 time=3ms TTL=127
      Reply from 10.1.1.4: bytes=32 time=2ms TTL=127
      Reply from 10.1.1.4: bytes=32 time=2ms TTL=127
      Reply from 10.1.1.4: bytes=32 time=2ms TTL=127
      
    2. Test the Windows Authentication to the remote server:

      sqlcmd -E -S cluster-sql2.dbeng.com -Q "SELECT 'CONNECTED'"
      

      The output looks like the following:

      RESULTS:
      --------------------------------------------------------------------------
      CONNECTED
      
      (1 rows affected)
      

    If you're unable to connect, make sure that the DNS is working properly and that firewall rules are open between the AWS and Google Cloud subnets.

Verify Google Cloud instance is ready to join availability group

  1. Use the dbeng\Administrator account to connect to the Google Cloud instance using RDP (cluster-sql2).
  2. Open an elevated PowerShell (run as administrator).
  3. Validate that the instance in Google Cloud can connect to the instance in AWS when using the instance name. To test the connection, run the following commands from a domain account that has granted connect access to the SQL Server.

    1. Test the network connection:

      ping -4 cluster-sql1.dbeng.com
      

      The output looks like the following:

      RESULTS:
      Pinging CLUSTER-SQL1.dbeng.com [192.168.1.4] with 32 bytes of data:
      Reply from 192.168.1.4: bytes=32 time=3ms TTL=127
      Reply from 192.168.1.4: bytes=32 time=2ms TTL=127
      Reply from 192.168.1.4: bytes=32 time=3ms TTL=127
      Reply from 192.168.1.4: bytes=32 time=2ms TTL=127
      
    2. Test the Windows Authentication to the remote server:

      sqlcmd -E -S cluster-sql1 -Q "SELECT 'CONNECTED'"
      

      The output looks like the following:

      RESULTS:
      ------------------------------------------------------------
      CONNECTED
      
      (1 rows affected)
      

      If you're unable to connect, make sure that the DNS is working properly and that firewall rules are open between the AWS and Google Cloud subnets.

  4. Create folders at C:\SQLData and C:\SQLLog. The database data and log files use these folders.

    New-Item "C:\SQLData" –type directory
    New-Item "C:\SQLLog" –type directory
    
  5. Create a folder at C:\SQLBackup and a Windows share at \\cluster-sql2\SQLBackup to transfer the backup from the AWS instance. You can use any other network share that is available to both servers.

    New-Item "C:\SQLBackup" –type directory
    New-SmbShare -Name "SQLBackup" -Path "C:\SQLBackup" -FullAccess
    "dbeng.com\cluster-sql1$","dbeng.com\cluster-sql2$","NT
    SERVICE\MSSQLSERVER","authenticated users","dbeng.com\sql_service"
    

The instances are now ready for the availability group. Because you only have two instances, in the next section you configure a file share witness to provide a tie-breaking vote and achieve a quorum.

Creating a file share witness

To provide a tie-breaking vote and achieve a quorum for the failover scenario, create a file share that acts as a witness. For the purposes of this tutorial, you create the file share witness on the domain controller VM. In a production environment, you would create the file share witness on any server within your Active Directory domain.

  1. Use the dbeng\Administrator account to connect to the domain controller VM, dc-windows, using RDP.
  2. Open an elevated PowerShell (run as administrator).
  3. Create the witness folder: