Migrate SQL Server database from AWS EC2 to Compute Engine

Stay organized with collections Save and categorize content based on your preferences.

Prepare the project and network

To prepare your Google Cloud project and Virtual Private Cloud (VPC) for the deployment of SQL Server for migration, do the following:

1. In the Google Cloud console, click Activate Cloud Shell to open Cloud Shell.

   Go to the Google Cloud console

2. Set your default project ID:

   gcloud config set project PROJECT_ID
   

   Replace PROJECT_ID with the ID of your Google Cloud project.

3. Set your default region:

   gcloud config set compute/region REGION
   

   Replace REGION with the ID of the region you want to deploy in.

4. Set your default zone:

   gcloud config set compute/zone ZONE
   

   Replace ZONE with the ID of the zone you want to deploy in. Ensure that the zone is valid in the region you specified in the previous step.

Create a SQL Server instance on Compute Engine

Before you migrate your SQL Server database to Compute Engine, you must create a virtual machine (VM) on Compute Engine to host it.

Use the following command to create a SQL Server instance on Compute Engine:

gcloud compute instances create sql-server-std-migrate-vm \
--project=PROJECT_ID \
--zone ZONE \
--machine-type n4-standard-8 \
--subnet SUBNET_NAME \
--create-disk=auto-delete=yes,boot=yes,device-name=node-1,image=projects/windows-sql-cloud/global/images/sql-2022-standard-windows-2022-dc-v20250213,mode=rw,size=50,type=projects/PROJECT_ID/zones/ZONE/diskTypes/pd-balanced \
--scopes=https://www.googleapis.com/auth/compute,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly,https://www.googleapis.com/auth/logging.write,https://www.googleapis.com/auth/monitoring.write,https://www.googleapis.com/auth/trace.append,https://www.googleapis.com/auth/devstorage.read_write

gcloud compute instances create sql-server-ent-migrate-vm \
--project=PROJECT_ID \
--zone ZONE \
--machine-type n4-standard-8 \
--subnet SUBNET_NAME \
--create-disk=auto-delete=yes,boot=yes,device-name=node-1,image=projects/windows-sql-cloud/global/images/sql-2022-enterprise-windows-2022-dc-v20250213,mode=rw,size=50,type=projects/PROJECT_ID/zones/ZONE/diskTypes/pd-balanced \
--scopes=https://www.googleapis.com/auth/compute,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly,https://www.googleapis.com/auth/logging.write,https://www.googleapis.com/auth/monitoring.write,https://www.googleapis.com/auth/trace.append,https://www.googleapis.com/auth/devstorage.read_write

For more information about creating SQL Server instances on Compute Engine, see Create a SQL Server instance.

Configure and connect to your SQL Server VM

To configure your SQL Server VM and connect to it, use the following steps:

1. Set the initial Windows password for your account:

   1. In the Google Cloud console, go to the VM Instances page.

      Go to VM instances

   2. Click the name of the SQL server VM.

   3. Click the Set Windows password button.

   4. Enter a password and click Set once prompted to set the new Windows password.

   5. Save the username and password.

2. Connect to the SQL Server VM:

   1. Use the public IP address of the SQL Server VM from the VM Instances page and credentials saved from the previous step to connect to your SQL Server VM using Microsoft Remote Desktop (RDP).

   2. Run SQL Server Management Studio (SSMS) as an administrator.

   3. Verify that the Trust server certificate checkbox is selected and click Connect.

Your SQL Server VM is now ready to be used for database migration. To create new user logins to connect and manage your SQL Server VM, see Create a login.

Full database backup and restore

A full database backup and restore is the most common and straightforward method of database migration. With this approach, a full backup of the SQL Server database is taken from the source environment and is then restored on the destination Google Cloud environment. While this method is relatively straightforward, it can be time-consuming for large databases due to the time it takes to create and restore the backup.

This section discusses how you can use SSMS to export your SQL Server database using a sample AdventureWorks2022 database.

Create a full database backup

To create a full database backup, use the following steps:

1. Log into your AWS EC2 VM using Microsoft RDP.

2. Connect to SQL Server using SSMS.

3. Expand the databases folder in the Object Explorer.

4. Right-click on the database name, click Tasks from the menu.

5. Click Back Up to open the database backup wizard.

   1. Verify the database name to backup and the backup type is set to Full.

   2. Click Add under the destination for the full backup.

   3. Click ellipses icon (...) to select the folder and name of the backup file.

   4. Click OK to set the filename and OK again to set the destination.

      

   5. Click OK to start the database backup and wait for the backup to complete.

      After the backup process is complete a backup file is created. You can now use this backup file to migrate the database contents to a Compute Engine VM.

   6. Click OK to exit the database backup wizard.

Transfer the backup file to a Compute Engine VM

To migrate the your SQL Server database content, you must transfer the backup file created in the previous step to the Compute Engine VM that you created. For information on the various transfer options, see Transfer files to Windows VMs.

Restore your SQL Server database from the backup file

To restore the database from the backup file, use the following steps:

1. Log into your Compute Engine VM using RDP.

2. Connect to SQL Server using SSMS.

3. In the Object Explorer, right-click the Databases folder and click Restore Database.

4. For the Source, click Device and the ellipses icon (...) to open the Select backup device page.

5. Verify that the backup media type is set to File and click Add to select the backup file.

   

6. Click OK to set the backup file as the restoration device.

7. Click OK to restore the database.

   When the process is complete, your database is migrated to the destination SQL Server on Compute Engine.

8. To verify if the process has completed successfully, you can expand the databases folder in the Object Explorer and verify if you can see the migrated database.

   

Migrate using a BACPAC file

A backup package (BACPAC) file is a logical representation of a SQL Server database. It can be exported from the source AWS environment and then imported into the destination Google Cloud environment. This method is typically faster than a full backup and restore for smaller databases but may not be suitable for very large databases or those with complex dependencies.

The following section discuss how you can migrate your SQL Server database using a BACPAC file.

Create a BACPAC export

To create a BACPAC export, use the following steps:

1. Log into the AWS EC2 VM using Microsoft RDP.

2. Connect to SQL Server using SSMS.

3. Expand the databases folder in the Object Explorer.

4. Right-click on the database name and click Tasks.

5. Click Export Data-tier Application to open the export wizard.

   1. Click Next.

   2. Click Browse in the Save to local disk option and select the BACPAC file.

   3. Click the Advanced tab and select the schema(s) that you want to export.

   4. Click Next to advance to the summary.

   5. Click Finish to export the BACPAC file and wait for the export to complete.

   6. Click Close to exit the wizard.

6. Transfer the BACPAC file created in the previous steps to your destination VM on Compute Engine. For information on the transfer options, see Transfer files to Windows VMs.

Restore your SQL Server database from a BACPAC file

To restore the database from the BACPAC file, follow these steps:

1. Log into the Compute Engine VM using RDP.

2. Connect to SQL Server using SSMS.

3. In the Object Explorer, right-click the Databases folder and click Import Data-tier Application.

4. Click Next.

5. Click Browse and select the BACPAC file that you want to restore, and then click Next.

6. Verify the New database name and click Next.

7. Click Finish and wait for the import to complete.

8. Click Close to exit the wizard.

9. To verify if the process has completed successfully, you can expand the databases folder in the Object Explorer and verify if you can see the migrated database.

Migrate using Always-on availability groups

An AOAG is a high availability and disaster recovery feature of SQL Server. You can use an AOAG to migrate existing AOAG clusters, standalone SQL Servers, and Windows Server failover clusters (WSFC). With this method, a replica of the database is created in the destination Google Cloud environment and the data is synchronized between the source and target. Once synchronization is complete, the replica on the destination Google Cloud environment can be made primary. This method minimizes downtime but requires additional configuration and setup. For straight forward migrations with significant downtime tolerance, other methods may be simpler and more cost-effective.

Before you begin

Ensure the following before you begin the migration:

• To ensure secure and seamless transition of data, establish a peering connection between AWS and Google Cloud. For more information, see Create HA VPN connections between Google Cloud and AWS.

• Ensure that the source database is running in standalone mode, and both the source and destination servers are joined to an Active Directory (AD). If the source database is already part of a WSFC cluster using an AOAG, see Migrate using distributed availability groups.

• Ensure that all encryption keys on the source SQL Server database are installed on all SQL Server instances that will join the AOAG.

Prepare your SQL Server to be part of an AOAG

To be able to add SQL Servers to an AOAG, you must enable the AOAG feature on all SQL Server instances that you want to add to the group.

To enable the AOAG feature on all SQL Server VMs that you want to add to an AOAG, use the following steps:

1. Enable AOAG on your SQL Server.

   1. Log into your SQL Server VM using RDP.

   2. Open Powershell in administrator mode.

   3. Run the following command to enabled AOAG on your SQL Server.

      Enable-SqlAlwaysOn -ServerInstance $env:COMPUTERNAME -Force
      

   4. Run the following command to open a firewall port for data replication.

      netsh advfirewall firewall add rule name="Allow SQL Server replication" dir=in action=allow protocol=TCP localport=5022
      

   5. Repeat step 1 for all the SQL Server VMs that you want to add to the AOAG.

2. Create a new user for your SQL Server on your AD.

   $Credential = Get-Credential -UserName sql_server -Message 'Enter password'
   New-ADUser `
   -Name "sql_server" `
   -Description "SQL Admin account." `
   -AccountPassword $Credential.Password `
   -Enabled $true -PasswordNeverExpires $true
   

3. Perform the following steps on all SQL Server instances that is part of AOAG:

   1. Open SQL Server Configuration Manager.
   2. In the navigation pane, select SQL Server Services.
   3. In the list of services, right-click SQL Server (MSSQLSERVER) and select Properties.
   4. Under Log on as, change the account as follows:
      • Account name: DOMAIN\sql_server where DOMAIN is the NetBIOS name of your AD domain.
      • Password: Enter the password you chose in the previous step 2 of this section.

   5. Click OK.

   6. When prompted to restart SQL Server, select Yes.

Your SQL Server is now running under a domain user account.

Set up the mirroring endpoint for your SQL Server database

To create the endpoint for your AOAG, use the following steps:

1. If the source SQL Server database is encrypted with Transparent data encryption (TDE), take this step to backup, transfer, and install the certificates and keys to the destination SQL Server.

2. Log into the source database on AWS using SSMS.

3. Run the following T-SQL command to create the endpoint for the availability group.

   USE [master]
   GO
   CREATE LOGIN [NET_DOMAIN\sql_server] FROM WINDOWS
   GO
   
   USE [DATABASE_NAME]
   GO
   CREATE USER [NET_DOMAIN\sql_server] FOR LOGIN [NET_DOMAIN\sql_server]
   GO
   
   USE [master]
   GO
   CREATE ENDPOINT migration_endpoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=5022)
       FOR DATABASE_MIRRORING (ROLE=ALL);
   GO
   
   GRANT CONNECT ON ENDPOINT::[migration_endpoint] TO [NET_DOMAIN\sql_server]
   GO
   

   Replace NET_DOMAIN with the NetBIOS name of your AD domain, and DATABASE_NAME with the name of the database to migrate.

4. Connect to the destination SQL Server on Google Cloud using SSMS and execute the following T-SQL command to create the database mirroring endpoint.

   CREATE LOGIN [NET_DOMAIN\sql_server] FROM WINDOWS
   GO
   
   CREATE ENDPOINT migration_endpoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=5022)
       FOR DATABASE_MIRRORING (ROLE=ALL);
   GO
   
   GRANT CONNECT ON ENDPOINT::[migration_endpoint] TO [NET_DOMAIN\sql_server]
   GO
   

   Replace NET_DOMAIN with the NetBIOS name of your AD domain.

5. Verify the endpoints by navigating to Server Objects > Endpoints > Database Mirroring in the Object Explorer in SSMS.

   

Create the AOAG

To create an AOAG, use the following steps:

1. Log into the source database on AWS using SSMS.

2. Run the following T-SQL command to set the database recovery mode to full and take a full backup.

   USE [master]
   GO
   
   ALTER DATABASE [DATABASE_NAME]
   SET RECOVERY FULL;
   BACKUP DATABASE [DATABASE_NAME]
   TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\DATABASE_NAME.bak';
   

   Replace DATABASE_NAME with the name of the database to migrate.

3. Run the following T-SQL command to create the AOAG.

   USE [master]
   GO
   
   CREATE AVAILABILITY GROUP [migration-ag]
   WITH (
       AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
       DB_FAILOVER = OFF,
       DTC_SUPPORT = NONE,
       REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0
   )
   FOR DATABASE [DATABASE_NAME]
   REPLICA ON
   N'SOURCE_SERVERNAME' WITH (
       ENDPOINT_URL = 'TCP://SOURCE_HOSTNAME:5022',
       AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
       FAILOVER_MODE = MANUAL,
       BACKUP_PRIORITY = 50,
       SEEDING_MODE = AUTOMATIC,
       SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)
   ),
   N'DEST_SERVERNAME' WITH (
       ENDPOINT_URL = 'TCP://DEST_HOSTNAME:5022',
       AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
       FAILOVER_MODE = MANUAL,
       BACKUP_PRIORITY = 50,
       SEEDING_MODE = AUTOMATIC,
       SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)
   );
   GO
   

   Replace the following:

   • DATABASE_NAME: with the name of the database to migrate.
   • SOURCE_SERVERNAME: with the servername of the source database.
   • DEST_SERVERNAME: with the servername of the destination database.
   • SOURCE_HOSTNAME: with the Fully Qualified Domain Name (FQDN) of the source.
   • DEST_HOSTNAME: with the FQDN of the target.

4. Run the following T-SQL command on the destination database to add it to the AOAG.

   USE [master]
   GO
   
   ALTER AVAILABILITY GROUP [migration-ag] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
   ALTER AVAILABILITY GROUP [migration-ag] GRANT CREATE ANY DATABASE;
   GO
   

5. Verify the newly created AOAG and database state in the Object Explorer or by running the following T-SQL command.

   SELECT * FROM sys.dm_hadr_availability_group_states
   GO
   

   

The SQL Server AOAG is now configured and keeps synchronizing between AWS and Google Cloud. As the next step, you must configure a WSFC and a listener for high availability and disaster recovery. For more information, see Windows Server Failover Clustering with SQL Server and What is an availability group listener.

Migrate using distributed availability groups

A distributed availability group is a special type of availability group that spans two separate availability groups. It is designed to provide high availability and disaster recovery capabilities across geographically dispersed locations. This architecture enables seamless data replication and failover between the primary and secondary availability groups, ideal for data migration. For more detailed information see Distributed availability groups.

The following sections discuss how you can migrate your SQL Server database using distributed availability groups.

Before you begin

Ensure that you have a WSFC with SQL Server using an availability group with a Virtual Network Name (VNN) listener, running on AWS.

Prepare the destination environment

To prepare the destination environment, use the following steps:

1. To configure a WSFC with SQL Server using an availability group using an internal load balancer on Google Cloud, see Configure SQL Server Always-on availability groups with synchronous commit using an internal load balancer.

2. In Object Explorer, verify that bookshelf-ag has been created and is replicating the bookshelf database. Once verified, use the next steps to remove both the availability group and the database from both nodes in your failover cluster.

   

3. Connect to node-1 in SSMS and save the IP address of the bookshelf listener.

   SELECT * FROM sys.availability_group_listeners
   

4. Run the following T-SQL command to remove the bookshelf-ag availability group and the bookshelf database.

   USE master
   GO
   
   DROP AVAILABILITY GROUP [bookshelf-ag]
   GO
   ALTER DATABASE [bookshelf] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   GO
   DROP DATABASE [bookshelf]
   GO
   

5. Run the following T-SQL on node-2 in SSMS to remove the replicated database.

   USE master
   GO
   
   DROP DATABASE [bookshelf]
   GO
   

Create a distributed availability group

To create a new availability group to use for the distributed availability group, use the following steps:

1. Run the following T-SQL command on node-1.

   USE master
   GO
   
   CREATE AVAILABILITY GROUP [gcp-dest-ag]
   FOR
   REPLICA ON
       N'NODE-1' WITH
       (
           ENDPOINT_URL = N'TCP://NODE-1:5022',
           FAILOVER_MODE = MANUAL,
           AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
           BACKUP_PRIORITY = 50,
           SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
           SEEDING_MODE = AUTOMATIC
       ),
       N'NODE-2' WITH
       (
           ENDPOINT_URL = N'TCP://NODE-2:5022',
           FAILOVER_MODE = MANUAL,
           AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
           BACKUP_PRIORITY = 50,
           SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
           SEEDING_MODE = AUTOMATIC
       );
   GO
   

2. Create a listener.

   USE master;
   GO
   
   ALTER AVAILABILITY GROUP [gcp-dest-ag]
   ADD LISTENER N'gcp-dest-lsnr' (
   WITH IP (
   (N'LISTENER_IP', N'255.255.255.0')
   ),
   PORT = 1433);
   GO
   

   Replace LISTENER_IP with the IP address of the listener.

3. Connect to node-2 using SSMS and run the following T-SQL command to add it to the gcp-dest-ag availability group.

   USE master
   GO
   
   ALTER AVAILABILITY GROUP [gcp-dest-ag] JOIN;
   ALTER AVAILABILITY GROUP [gcp-dest-ag] GRANT CREATE ANY DATABASE;
   

4. Connect to the primary replica of the source SQL Server on AWS using SSMS and run the following T-SQL command to create a distributed availability group.

   USE [master]
   GO
   
   CREATE AVAILABILITY GROUP [distributed-ag]
   WITH (DISTRIBUTED)
   AVAILABILITY GROUP ON
   'AWS_AG' WITH
   (
       LISTENER_URL = 'tcp://AWS_LISTENER:5022',
       AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
       FAILOVER_MODE = MANUAL,
       SEEDING_MODE = AUTOMATIC
   ),
   'gcp-dest-ag' WITH
   (
       LISTENER_URL = 'tcp://gcp-dest-lsnr:5022',
       AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
       FAILOVER_MODE = MANUAL,
       SEEDING_MODE = AUTOMATIC
   )
   GO
   

   Replace AWS_AG with the name of the availabity group in AWS and AWS_LISTENER with the listener of the AWS availability group.

5. Run the following T-SQL command in SSMS on node-1 to add it to the distributed availability group.

   USE [master]
   GO
   
   ALTER AVAILABILITY GROUP [distributed-ag]
   JOIN
   AVAILABILITY GROUP ON
   'AWS_AG' WITH
   (
       LISTENER_URL = 'tcp://AWS_LISTENER:5022',
       AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
       FAILOVER_MODE = MANUAL,
       SEEDING_MODE = AUTOMATIC
   ),
   'gcp-dest-ag' WITH
   (
       LISTENER_URL = 'tcp://gcp-dest-lsnr:5022',
       AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
       FAILOVER_MODE = MANUAL,
       SEEDING_MODE = AUTOMATIC
   )
   GO
   

   Replace AWS_AG with the name of the availabity group in AWS and AWS_LISTENER with the listener of the AWS availability group.

6. Run the following T-SQL command on `node-1' to verify that all the availability groups are healthy and replicating across the distributed availability group to the new SQL Server cluster on Google Cloud

   SELECT * FROM sys.dm_hadr_availability_group_states
   GO