Prepare the project and network

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

1. In the Google Cloud console, clickActivate Cloud Shellto openCloud Shell.

   Go to the Google Cloud console

2. Set your default project ID:

   gcloud config set projectPROJECT_ID

   ReplacePROJECT_ID with the ID of your Google Cloud project.

3. Set your default region:

   gcloud config set compute/regionREGION

   ReplaceREGION with the ID of the region you want to deploy in.

4. Set your default zone:

   gcloud config set compute/zoneZONE

   ReplaceZONE 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 \--zoneZONE \--machine-type n4-standard-8 \--subnetSUBNET_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 \--zoneZONE \--machine-type n4-standard-8 \--subnetSUBNET_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, seeCreate 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 theVM Instances page.

      Go to VM instances

   2. Click the name of the SQL server VM.

   3. Click theSet Windows password button.

   4. Enter a password and clickSet 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 theVM Instances page and credentials saved from the previous step to connect to your SQL Server VM using Microsoft Remote Desktop (RDP).

   2. RunSQL Server Management Studio (SSMS) as an administrator.

   3. Verify that theTrust server certificate checkbox is selected and clickConnect.

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, seeCreate 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 sampleAdventureWorks2022 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, clickTasks from the menu.

5. ClickBack Up to open the database backup wizard.

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

   2. ClickAdd under the destination for the full backup.

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

   4. ClickOK to set the filename andOK again to set the destination.

      

   5. ClickOK 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. ClickOK 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, seeTransfer 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 theDatabases folder and clickRestore Database.

4. For theSource, clickDevice and the ellipses icon (...) to open the Select backup device page.

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

   

6. ClickOK to set the backup file as the restoration device.

7. ClickOK 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 thedatabases 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 thedatabases folder in the Object Explorer.

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

5. ClickExport Data-tier Application to open the export wizard.

   1. ClickNext.

   2. ClickBrowse in theSave to local disk option and select the BACPAC file.

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

   4. ClickNext to advance to the summary.

   5. ClickFinish to export the BACPAC file and wait for the export to complete.

   6. ClickClose 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, seeTransfer 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 theDatabases folder and clickImport Data-tier Application.

4. ClickNext.

5. ClickBrowse and select the BACPAC file that you want to restore, and then clickNext.

6. Verify the New database name and clickNext.

7. ClickFinish and wait for the import to complete.

8. ClickClose to exit the wizard.

9. To verify if the process has completed successfully, you can expand thedatabases 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 connectionbetween AWS and Google Cloud. For more information, seeCreate HA VPN connections between Google Cloud and AWS.

• Ensure that the source database is running in standalone mode, and both thesource and destination servers are joined to an Active Directory (AD). If the sourcedatabase is already part of a WSFC cluster using an AOAG, seeMigrate usingdistributed 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. OpenSQL Server Configuration Manager.
   2. In the navigation pane, selectSQL Server Services.
   3. In the list of services, right-clickSQL Server (MSSQLSERVER) and selectProperties.
   4. UnderLog 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. ClickOK.

   6. When prompted to restart SQL Server, selectYes.

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

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 thedestination 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]GOCREATE LOGIN [NET_DOMAIN\sql_server] FROM WINDOWSGOUSE [DATABASE_NAME]GOCREATE USER [NET_DOMAIN\sql_server] FOR LOGIN [NET_DOMAIN\sql_server]GOUSE [master]GOCREATE ENDPOINT migration_endpoint    STATE=STARTED    AS TCP (LISTENER_PORT=5022)    FOR DATABASE_MIRRORING (ROLE=ALL);GOGRANT CONNECT ON ENDPOINT::[migration_endpoint] TO [NET_DOMAIN\sql_server]GO

   ReplaceNET_DOMAIN with the NetBIOS name of yourAD domain, andDATABASE_NAME with thename of the database to migrate.

   Tip: The default listening port is 5022, but you can use any other available ports.

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

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

   ReplaceNET_DOMAIN with the NetBIOS name of your AD domain.

5. Verify the endpoints by navigating toServer 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 fullbackup.

   USE [master]GOALTER 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';

   ReplaceDATABASE_NAME with the name of the database to migrate.

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

   USE [master]GOCREATE 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 ONN'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.
   Note: The AOAG uses asynchronous commit mode and hasautomatic failover disabled to minimize the impact on the primary SQL Server.

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

   USE [master]GOALTER 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 theObject Explorer or by running the following T-SQL command.

   SELECT * FROM sys.dm_hadr_availability_group_statesGO

   

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, seeWindows Server Failover Clustering with SQL Server andWhat is an availability group listener.

Migrate using distributed availability groups

A distributed availability group is a special type of availability group thatspans two separate availability groups. It is designed to provide highavailability and disaster recovery capabilities across geographically dispersedlocations. This architecture enables seamless data replication and failoverbetween the primary and secondary availability groups, ideal for data migration.For more detailed information seeDistributed 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, seeConfigure SQL Server Always-on availability groups with synchronous commit using an internal load balancer.

2. InObject Explorer, verify thatbookshelf-ag has been created and is replicating thebookshelf 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 tonode-1 in SSMS and save the IP address of thebookshelf listener.

   SELECT * FROM sys.availability_group_listeners

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

   USE masterGODROP AVAILABILITY GROUP [bookshelf-ag]GOALTER DATABASE [bookshelf] SET SINGLE_USER WITH ROLLBACK IMMEDIATEGODROP DATABASE [bookshelf]GO

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

   USE masterGODROP 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 onnode-1.

   USE masterGOCREATE AVAILABILITY GROUP [gcp-dest-ag]FORREPLICA 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;GOALTER AVAILABILITY GROUP [gcp-dest-ag]ADD LISTENER N'gcp-dest-lsnr' (WITH IP ((N'LISTENER_IP', N'255.255.255.0')),PORT = 1433);GO

   ReplaceLISTENER_IP with the IP address of the listener.

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

   USE masterGOALTER 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]GOCREATE 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

   ReplaceAWS_AG with the name of the availabity group in AWS andAWS_LISTENER with the listener of the AWS availability group.

   Note: The listener URL specified for each availability group is the listener hostname along with the database mirroring endpoint port. If you're using a different port than 5022, make sure to change the ports before running the T-SQL.

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

   Tip: Use a tool such as Netcat orTest-NetConnection to verify that the source and destination listeners are reachable on the endpoint port (5022) from the primary replicas of the source and destination. Additionalfirewall rules may be required to allow the traffic.

   USE [master]GOALTER AVAILABILITY GROUP [distributed-ag]JOINAVAILABILITY 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

   ReplaceAWS_AG with the name of the availabity group in AWS andAWS_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_statesGO