Step-by-Step Guide: Migrating SQL Server Databases to Google Cloud with Minimal Downtime

Migrating SQL Server to Google Cloud isn’t simply dumping data over the wire. Miss a collation mismatch, overlook log growth, or underestimate network throughput—expect outages and unhappy users. This guide is an engineer’s checklist anchored in real-world production experience.

1. Snapshot: Assess the Existing SQL Server Footprint

Before any migration discussion, complete an inventory. Without accurate sizing and dependency mapping, expect post-migration headaches.

• Database footprint: File sizes (MDF, LDF), table counts, index fragmentation.
• SQL Server version/edition: 2012? 2017 Enterprise? Features/packages in use (Full-Text Search, CLR, etc.)
• HA/DR topology: AlwaysOn AG, mirroring, or legacy failover clusters?
• Peripheral dependencies: Linked servers, external jobs (agent), SSIS/SSRS/SSAS packages.
• Peak load windows: Heavy ETL overnight? 24/7 OLTP across time zones?
• Security/Compliance: Existing AD integration, auditing, PCI-DSS controls.

Gotcha: On-prem collation, especially if case-sensitive or non-default, can corrupt query results if mismatched post-migration.

2. Selecting the Right Landing Zone on GCP

Google Cloud offers multiple targets for SQL Server workloads—choices affect migration technique, control, and long-term maintenance.

For the majority, Cloud SQL for SQL Server strikes the right balance. Note: Features like cross-database queries or SQLCLR require Compute Engine; verify before you build.

3. Provision the Target Environment

With Cloud SQL for SQL Server, carefully align the configuration with current production settings:

• Instance: SQL Server Standard/Enterprise 2017+ (Cloud SQL supports Standard/Enterprise 2017, 2019, 2022)
• Resources: Configure vCPU, RAM at least equal to current; quotas can block you, so request them in advance.
• Storage: Use SSD-backed persistent disks; set at 20% above your estimated requirement (I/O bottlenecks are common post-migration).
• Networking: Private IP preferred. Setup VPC peering from on-prem, not just public access.
• HA/DR: Regional (zone-level) HA or single-zone? Don’t overlook maintenance windows.

Template:

gcloud sql instances create my-sql-prod \
  --database-version=SQLSERVER_2019_STANDARD \
  --tier=db-custom-4-28672 \
  --storage-size=200 \
  --region=us-central1 \
  --availability-type=REGIONAL \
  --root-password=$(openssl rand -base64 36)

4. Network Connectivity: Bandwidth, Security, and Latency

Bulk data movement is throttled by your weakest network link—not your SQL Server engine. For production, use Cloud VPN or Cloud Interconnect for persistent, secure transfer. Test with iperf3 or file copy to measure throughput; anything under 200 Mbps should be flagged.

Sample bandwidth test:

iperf3 -c <gcp-vm-external-ip>

Firewall configuration: restrict access to GCP Cloud SQL proxy IPs only. If public endpoints are exposed, use enforced SSL—accept the operational risk only if temporary.

5. Migration Method Selection: Downtime vs. Complexity

• Backup/Restore (Full Outage): Reliable, but requires coordinated downtime. Recommended for databases >1TB only with well-communicated maintenance windows.
• Google Database Migration Service (DMS): Orchestrates initial full load and ongoing change data capture (CDC). Minimal downtime—ideal for zero-downtime migrations, but currently not all SQL Server features are supported.
• Transactional Replication/Log Shipping: Only possible to Compute Engine SQL, not Cloud SQL. Use when you require continuous sync during transition.

Example (Backup/Restore to Cloud SQL):

# Back up database on-prem
BACKUP DATABASE [prodDB] TO DISK = 'C:\Backup\prodDB.bak' WITH COMPRESSION

# Copy to GCS bucket
gsutil cp C:\Backup\prodDB.bak gs://bucket-name/

# Import into Cloud SQL
gcloud sql import bak my-sql-prod gs://bucket-name/prodDB.bak --database=prodDB

DMS migration is UI-driven, but inspect job logs for lines like:

2024-05-29 09:15:31 Started CDC for Database 'prodDB': Initial Resync complete.
2024-05-29 13:00:47 Replication caught up to LSN 00000032:0001e1c0:0001 in 4h

If these logs lag, expect long cutover delays.

6. Pre-Migration Validation: Trust But Verify

• Schema and collation checks: Pre-generate DDL, diff against Cloud SQL metadata.
• Security: Migrate logins (generate scripts with sp_help_revlogin), test with least-privilege accounts.
• Data consistency: Use rowcounts, checksums, or a third-party diff. For small tables:

  SELECT COUNT(*) FROM [TableName]
  CHECKSUM_AGG(BINARY_CHECKSUM(*))
  

• Performance sanity: Run baseline read/write tests. Expect minor differences—disk subsystem and network latency change.

7. Cutover: Orchestrate, Don’t Fumble

Backup/Restore:

• Stop all application writes.
• Backup transaction logs (if needed).
• Restore latest backup to Cloud SQL.
• Point connection strings to new host, flush DNS cache, closely monitor error logs.

DMS/CDC Approach:

• Pause source instance; wait for DMS “Replication lag: 0s.”
• Promote target.
• Repoint applications—have a rollback plan.

Side effect: SQL Agent jobs don’t transfer automatically. Manually re-create maintenance plans (use Ola Hallengren’s scripts?).

8. Monitor, Tune, Repeat

Migration exposes latent performance issues—don’t treat “migration complete” as finished.

• Monitoring: Cloud SQL → Query Insights; Stackdriver for metrics/alerts.
• Index and statistics refresh:

  EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
  DBCC CHECKDB WITH NO_INFOMSGS
  

• Error logs: Watch for authentication errors, network drops, or blocked queries:

  Login failed for user 'app_user'
  Deadlock encountered. Try restarting the transaction.
  

Tip: Shrink the transaction log before moving a rarely-rebooted production database to avoid nasty surprises when the new log grows rapidly.

Common Pitfalls and Practical Avoidance

Closing

Successful SQL Server migrations to Google Cloud hinge on thorough discovery, real-world bandwidth testing, and operational discipline during cutover. There’s no single “correct” path—trade-offs exist between downtime, feature support, and operational complexity. Most failures stem from underestimated dependencies or skipped validation.

When in doubt, prototype first using a non-prod workload; learn from your test’s surprises. For features that Cloud SQL doesn’t (yet) support, Compute Engine remains a solid fallback.

Questions about cross-region AG, custom backup retention, or automating rollback? Leave them below or ping me directly.

Happy migrating. Data, like entropy, always finds a way—but the method matters.