Demo 2 - Migrate from SQL Server to AlloyDB using Database Migration Service¶

Overview¶

In this lab, you will learn how to migrate an enterprise Microsoft SQL Server database (Cloud SQL for SQL Server) to AlloyDB for PostgreSQL using Google Cloud Database Migration Service (DMS).

Migrating across different database engines—known as a heterogeneous migration—requires transforming both the database structures and stored database logic (T-SQL to PL/pgSQL) before replicating data in real time with minimal downtime.

System Architecture¶

The following diagram illustrates the complete end-to-end lab architecture, highlighting the network isolation across VPCs, Change Data Capture (CDC) replication through Database Migration Service, and the proxy connectivity bridge into AlloyDB:

flowchart TD
    subgraph VPC1 ["VPC 1 - Source Network (10.10.0.0/24)"]
        LOADER["fa:fa-server Helper VM: mssql-loader-vm
(BigQuery Data Preloader)"]
        MSSQL["fa:fa-database Cloud SQL SQL Server: mssql-source
(Port 1433 | DB: thelook_ecommerce)"]
        PROXY["fa:fa-network-wired TCP Proxy VM: tcp-proxy-vm
(Port 1433 Bridge)"]
        LOADER -->|"Preload standard dataset"| MSSQL
        PROXY --> MSSQL
    end

    subgraph PEERING ["VPC Peering (vpc1-to-vpc2)"]
        ROUTES["fa:fa-route Bidirectional Route Exchange
(Auto-created routes)"]
    end

    subgraph DMS ["Database Migration Service (Serverless Control Plane)"]
        CW["fa:fa-code-branch Conversion Workspace
(T-SQL to PL/pgSQL Translation)"]
        JOB["fa:fa-sync Continuous Migration Job
(Change Data Capture Replication)"]
    end

    subgraph VPC2 ["VPC 2 - Destination Network (10.20.0.0/24)"]
        ALLOYDB["fa:fa-database AlloyDB Cluster: alloydb-destination
(PostgreSQL 15 Compatible)"]
        STUDIO["fa:fa-terminal AlloyDB Studio
(Query Verification & Cutover)"]
        STUDIO --> ALLOYDB
    end

    VPC1 <--> PEERING
    PEERING <--> VPC2
    MSSQL -.->|"Schema Snapshot"| CW
    CW -.->|"Apply Converted DDL"| ALLOYDB
    JOB ==>|"CDC Data Stream over Peering"| PROXY

    style LOADER fill:#4285F4,stroke:#3c4043,color:#FFFFFF
    style MSSQL fill:#34A853,stroke:#3c4043,color:#FFFFFF
    style ROUTES fill:#F8F9FA,stroke:#4285F4,color:#202124
    style CW fill:#FBBC04,stroke:#3c4043,color:#202124
    style JOB fill:#FBBC04,stroke:#3c4043,color:#202124
    style PROXY fill:#4285F4,stroke:#3c4043,color:#FFFFFF
    style ALLOYDB fill:#34A853,stroke:#3c4043,color:#FFFFFF
    style STUDIO fill:#EA4335,stroke:#3c4043,color:#FFFFFF

Objectives¶

1. Environment Verification: Inspect the pre-provisioned source SQL Server database containing the thelook_ecommerce dataset imported from BigQuery.
2. VPC Networking: Establish bidirectional VPC Peering between the source network (vpc-1) and destination network (vpc-2).
3. Schema & Code Conversion: Utilize the DMS Conversion Workspace to automatically translate T-SQL schema definitions, data types, constraints, and stored procedures to PostgreSQL PL/pgSQL syntax.
4. Destination Provisioning: Deploy a fully managed, high-performance AlloyDB for PostgreSQL cluster in vpc-2 and apply the converted schema.
5. Continuous Replication: Configure and launch a serverless DMS continuous migration job leveraging a TCP Proxy VM to stream Change Data Capture (CDC) events with near-zero downtime.
6. Data Validation & Cutover: Verify table row counts and schema integrity in AlloyDB Studio, followed by promoting the AlloyDB cluster as the primary writable database.

Prerequisites¶

• A Google Cloud Project with Editor IAM permissions.
• Basic familiarity with Google Cloud Console navigation, SQL Server (T-SQL), and PostgreSQL concepts.

Setup & Verification¶

The initial environment is automatically provisioned using Terraform. Upon deployment, vpc-1 contains a Cloud SQL for SQL Server 2022 instance (mssql-source) and a helper Compute Engine VM (mssql-loader-vm) that populates the sample dataset.

1. Open Cloud Shell and verify that the lab infrastructure resources are deployed.

terraform state list

1. Confirm that the helper VM has finished loading the sample thelook_ecommerce dataset (7 tables sourced from BigQuery public data) into SQL Server.

gcloud compute ssh mssql-loader-vm --zone=us-central1-a --command="tail -f /var/log/startup-script.log"

Task 1. Migration Preparation & Network Connectivity¶

In enterprise environments, source databases and target managed instances often reside in isolated Virtual Private Clouds (VPCs). To allow DMS and proxy services to communicate securely over private internal IPs without exposing traffic to the public internet, you must configure VPC Network Peering.

Step 1.1: Create Bidirectional VPC Peering between VPC 1 and VPC 2¶

1. In Cloud Shell, establish the first peering link from vpc-1 (source network) to vpc-2 (target network).

gcloud compute networks peerings create vpc1-to-vpc2 --network=vpc-1 --peer-network=vpc-2 --auto-create-routes

1. Create the complementary reverse peering link from vpc-2 back to vpc-1.

gcloud compute networks peerings create vpc2-to-vpc1 --network=vpc-2 --peer-network=vpc-1 --auto-create-routes

1. Confirm that both peering connections report an ACTIVE state.

gcloud compute networks peerings list --network=vpc-1
gcloud compute networks peerings list --network=vpc-2

Explanation: Bidirectional route exchange allows resources in vpc-2 (such as the TCP proxy and DMS connectors) to route traffic directly to the private IP address of mssql-source in vpc-1.

Step 1.2: Enable the Database Migration Service API¶

1. In the Google Cloud Console top search bar, search for Database Migration and select Database Migration from the results.
2. Click Enable to activate the Database Migration API for your project.
3. Once activated, select Conversion workspaces from the left navigation menu.

Task 2. Schema and Code Conversion (T-SQL to PL/pgSQL)¶

Because Microsoft SQL Server and PostgreSQL handle data types, system functions, primary keys, and procedural logic differently, heterogeneous migrations require a Conversion Workspace. DMS parses the source SQL Server DDL and automatically generates compatible PostgreSQL DDL.

Step 2.1: Create a Conversion Workspace¶

1. On the Conversion workspaces page, click Create Workspace.

2. Configure the workspace parameters:

3. Workspace name: mssql-to-alloydb-schema

4. Source database engine: Microsoft SQL Server
5. Destination database engine: AlloyDB for PostgreSQL

6. Destination region: us-central1

7. Click Create & Continue.

Step 2.2: Define the Source Connection Profile¶

1. On the Define source connection page, click Create a connection profile.
2. Set Connection profile name to mssql-source-cp.
3. Under Connection configurations, select SQL Server to PostgreSQL.
4. In Cloud Shell, retrieve the private IP address of your source SQL Server instance.

gcloud sql instances describe mssql-source --format="value(ipAddresses[0].ipAddress)"

1. Return to the console and enter the connection credentials:

2. Host / IP: Enter the private IP address retrieved above (e.g., 10.10.0.X).

3. Port: 1433
4. Database: thelook_ecommerce
5. Username: sqlserver
6. Password: Retrieve from the Student Details Panel under SQL Server Password.
7. Encryption type: Select None.
8. Connectivity method: Select IP allowlist.

1. Click Save, then click Create to initialize the profile.
2. Select mssql-source-cp from the list and click Save & continue.

Step 2.3: Convert Schema Objects and Review DDL Translation¶

1. Select the thelook_ecommerce database from the schema list.
2. Click Convert to trigger the automated conversion engine.

3. Once conversion completes, click the Review and convert tab to view the side-by-side DDL comparison:

4. Observe how SQL Server types like VARCHAR or DATETIME are converted to PostgreSQL TEXT or TIMESTAMP WITH TIME ZONE.

5. Check for any conversion warnings or action items under the Issues tab.

6. Click Apply to destination.

Step 2.4: Provision Target AlloyDB Cluster & Apply Converted Schema¶

Since the target AlloyDB cluster does not exist yet, DMS allows you to provision an enterprise cluster directly from the conversion workflow.

1. Under Define a destination, choose New cluster.

2. Configure the AlloyDB cluster settings:

3. Cluster ID: alloydb-destination

4. Database version: PostgreSQL 15 compatible (or latest available)
5. Password: Enter a secure password for the default postgres database user (e.g., AlloyDBAdmin2026!). Make a note of this password.

6. Network: Change from default to vpc-2.

7. Click Confirm network setup to establish Private Services Access on vpc-2.

8. Configure the Primary Instance:

9. Instance ID: alloydb-destination-primary

10. Zonal availability: Single zone

11. Machine Type: 2 vCPU, 16 GB

12. Click Create Destination & Continue.

13. Wait 5–10 minutes for AlloyDB cluster creation. Once provisioned, click Apply to execute the converted DDL scripts onto your new AlloyDB cluster.

Task 3. Continuous Data Replication with Minimal Downtime¶

With the schema and tables established in AlloyDB, you can now launch a Continuous Migration Job. DMS takes an initial data snapshot and continuously streams ongoing changes (Change Data Capture) from SQL Server to AlloyDB until cutover.

Step 3.1: Create the Continuous Migration Job¶

1. Navigate to Database Migration > Migration jobs from the left menu and click Create migration job.

2. Set the migration parameters:

3. Migration job name: mssql-to-alloydb-data

4. Source database engine: Microsoft SQL Server
5. Destination database engine: AlloyDB for PostgreSQL
6. Migration job type: Continuous

7. Conversion Workspace: Select mssql-to-alloydb-schema (created in Task 2).

8. Click Save & continue.

Step 3.2: Select Source Profile and Destination Cluster¶

1. For Source, select the mssql-source-cp connection profile. Click Save & continue.
2. For Destination, select alloydb-destination. Click Save & continue.

Step 3.3: Deploy TCP Proxy VM for Cross-VPC Connectivity¶

Because Cloud SQL SQL Server resides inside vpc-1's Private Services Access (PSA) network and GCP's VPC Peering is non-transitive (vpc-2 $\rightarrow$ vpc-1 $\rightarrow$ PSA), DMS uses a small Compute Engine VM in vpc-1 (vpc-1-subnet) as a TCP Proxy to bridge 1-hop connectivity to SQL Server over VPC Peering.

1. Select Proxy via cloud-hosted VM - TCP as the connectivity method.

2. Enter the VM configuration:

3. VM Name: tcp-proxy-vm

4. Subnetwork: vpc-1-subnet

5. Click Continue.

6. Click View script and copy the generated deployment script.
7. In Cloud Shell, create and run deploy-tcp-proxy.sh:

chmod +x deploy-tcp-proxy.sh
./deploy-tcp-proxy.sh

1. Copy the INTERNAL_IP address outputted at the end of the script (e.g., 10.10.0.X).
2. Paste the internal IP into the TCP Proxy private IP field in the console and click Configure & continue.

Step 3.4: Select Migration Objects & Start Replication¶

1. Select thelook_ecommerce under Objects to migrate.
2. Click Save & continue.
3. Review the summary details and click Create & start job.
4. Confirm by clicking Create & start.
5. Monitor the job status until it transitions from Starting to Full dump and finally to Running (Continuous replication phase).

Task 4. Data Integrity & Schema Validation in AlloyDB Studio¶

Before performing cutover, validate that all tables, rows, and relationships were correctly migrated from SQL Server into AlloyDB.

1. Navigate to AlloyDB > Clusters in the Google Cloud Console and click alloydb-destination.
2. Click AlloyDB Studio in the left menu.

3. Authenticate with the following details:

4. Database: thelook_ecommerce

5. User: postgres

6. Password: The password configured in Step 2.4 (e.g., AlloyDBAdmin2026!).

7. Click Authenticate.

8. In the SQL Editor, run query scans to verify row counts across the migrated tables:

SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_items;
SELECT COUNT(*) FROM inventory_items;
SELECT COUNT(*) FROM distribution_centers;
SELECT COUNT(*) FROM events;

Verification Check: Confirm that row counts match the source dataset (e.g., events contains 100,000 rows).

Task 5. Promote AlloyDB Cluster to Primary (Cutover)¶

Once continuous replication is verified and the replication latency reaches zero seconds, you perform the final migration cutover by promoting the AlloyDB instance.

1. Navigate back to Database Migration > Migration jobs.
2. Click mssql-to-alloydb-data.
3. Check the Replication delay chart and verify that the delay is 0 seconds.
4. Click Promote on the top action bar.
5. In the confirmation dialog, click Promote.
6. Wait for the job status to update to Completed.

Explanation: Promoting the migration job stops CDC replication, disconnects the source SQL Server instance, and converts the AlloyDB cluster into a standalone, fully writable primary database ready to serve production workloads.

Congratulations¶

You have successfully executed a heterogeneous database modernization from Microsoft SQL Server to AlloyDB for PostgreSQL using Google Cloud Database Migration Service.

Key Milestones Achieved:

• Created cross-VPC networking via VPC Peering.
• Transformed T-SQL database logic and schema to PostgreSQL PL/pgSQL using DMS Conversion Workspaces.
• Established continuous Change Data Capture (CDC) streaming with a TCP Proxy VM bridge.
• Verified schema integrity using AlloyDB Studio and completed near-zero downtime cutover promotion.