Migrate MS SQL database to GCP cloud native - Cloud SQL MS SQL server(Part 1)

[raghavendras]

MS SQL to Cloud SQL - Transactional Replication

In this guide we are going to take a look at the migration of Onprem / other cloud deployed MS SQL Server Database(data) to GCP’s native cloud sql offering.

As this document outlines the migration of data from MS SQL the pre-requisite is intended only to MS SQL server hosted on windows server(stand-alone or clustered environments)

• Source MS SQL server(2015 onwards)
• Target Cloud SQL server (2017 onwards)

Migration methodology:

MS SQL server has got its default out of the box replication technologies that can be utilized to migrate data from source(onprem, other cloud hosted) to GCP cloud native MS SQL server.

Here in this document we are going to use transactional replication technology for migrating the data to the target state. However apart from transactional replication technology there are other options too which can be utilized depending upon the use case and requirements. The below table outlines a brief overview of the same.

Type	Description
Transactional replication	Changes at the Publisher are delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred on the publisher.

Source: Link

MS SQL Server Transaction replication comprises of : 

• Publisher
• Distributor 
• Subscriber components 

It is implemented with the help of SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent.

Installation / configuration - Source and Target:

Prerequisite

• Connectivity mode / type (cloud VPN, Interconnect) configuration applied and communication is established between onprem / other cloud service provider to GCP
• Configure private google access for the assigned cloud sql subnets
• Establish connectivity from source to cloud SQL
• Apply firewall and routing related configurations to ensure source MS SQL server can communicate with target cloud sql IP / subnet
•  Gather the inventory of DB, Tables and other required parameters that are required for enabling and setting up transactional replication between the source and cloud sql MS sql Server
• Deploy Cloud SQL MS SQL server with the required config spec

Assumptions :-

1. Primary Keys set for tables that are required to be set for replication to cloud sql
2. Cloud sql user created and configured with relevant access permissions to aid replication

In this POC we have created a 2 node MS SQL cluster on a different subnet, created cloud sql MS SQL Enterprise 2017 edition.

Step-by-Step Procedure:

In the next section of this document  we will look at the complete procedure which is required to setup transactional replication between source and target databases:

Migrate MS SQL database to GCP cloud native - Cloud SQL MS SQL server (Part 2)