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

[raghavendras]

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

Set up Transactional Replication Publication

1. In the VM Instance, Connect to On-Prem DB instance using SSMS.

We will now create a Publisher:

2. Expand the menu under your Instance in SSMS. Go to Replication → Local Publications

3. Right Click on Local Publications and Click on “New Publication”
4. Select the Database to be Publisher.
5. On the Next Screen, Select “Transactional Publication”

6. On the next screen, you are given Objects to set up Transactional Publishing for.

7. Note the Application Changes required if any

8.You may choose to add Filtering on Table Rows in Next Window

9. On the Next screen, choose “Create Snapshot Immediately and keep the snapshot available to initialize subscription”. You can choose to run Snapshot Agent to run on schedule too

10. On the Next Page, there would be Snapshot Agent and Log Reader Agent. Give the Administrator Account to both.

11. On the next page, Give name to Publication and Click on Finish. Monitor the Logs to make sure there are no errors.

12. Check if Snapshot is an Agent running or not. Go to Your Publication and Right Click. If not, enable to service to start running

Set up Transactional Replication Subscription

1. Go to your Publication and Right Click and go to Properties → Publication Access List. Add administrator here if missing.
2. Go to your Publication and Right Click and go to New Subscription
3. On the next screen, select the Publication just created
4. We need a PUSH Based Subscription. On the next window select this. There is also a PULL based subscription on Agent.

5. On the next screen, we will add Subscriber ie. our Cloud SQL Server Details. Click “Add Subscriber”.

6. On the Window that opens, give Public IP of Cloud SQL server and put authentication details for the user set.

7. Once this is created, we need to add Subscription DB. We can use “Create New DB” here.

8. Give DB information on the next screen

9. On the next screen, you will see Distribution Agent Security. For the Distribution Agent, the machine runs on-prem. So keep Windows Authentication for DOMAIN\administrator. For Connecting to Subscriber give Cloud SQL user details in “SQL Server Login”

10. Click on Finish and monitor for any errors.

Database Level Permissions

Make sure that the Database Owner is set and the user has the permissions.

This can be configured on the Database Properties

Monitoring the Subscription

Now let’s have a look at the subscription status for the Replication.

1. Go to Subscription and Right Click. Go to “Replication Monitor”. Check for any errors. There should be no authentication errors in Subscription. If there are errors, these are segregated into 3 categories.
2. Publisher to Distributor History
3. Distributor to Subscriber History
4. Undistributed Command

2. For the current Example, Latency is 4 seconds

3. Connect to Cloud SQL Server using SSMS. You should be able to see the New Database with Tables being replicated.

Key Observations

1. For Creating new Users and correcting Server Names, SQL Server Restart might be needed on the Node.
2. Network Connectivity will be critical to make sure Cloud SQL is reachable from On-Prem
3. We would need a DB User Account for logging in from Cloud SQL to On-Prem and handling credentials for that.
4. To prevent overload of the server, this process should be done in OFF Hours.
5. DB Owner for the set up Databases needs to be accessed and modified if required. This is required for Publisher to Distributor Flow.
6. Snapshot Agent and Log Reader Agent should already be running and not stopped.
7. Cloud SQL DB should be HA by default
8. Authentication to Cloud SQL can also be done using AD accounts.

References

• https://learn.microsoft.com/en-us/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-ver16
• https://cloud.google.com/solutions/migrating-data-from-sql-server-to-cloud-sql-for-sql-server
• https://docs.google.com/document/d/1Wh1hICYSaDjS6yN64vUb4k2hCS9fM5wd_rFHF0HPeW0/edit?pli=1
• https://cloud.google.com/sql/docs/quotas#fixed-limits