Introduction

Moving data from a virtual machine (VM) to Google cloud is a crucial step for organizations looking to leverage the power of managed databases and harness the scalability and flexibility of the cloud. Google cloud SQL offers fully managed databases with built-in replication, automated backups, and high availability, making it an ideal choice for business seeking robust data management solutions. In this blog, we will walk you through the process of migrating data from a VM to Google cloud SQL using transactional replication, ensuring a smooth and successful transition.

SQL Server Replication Terminology

It’s best if we familiarize ourselves with the terminology used in SQL Server Replication.

Article

• An article is an object that can be replicated. These include tables, views, stored procedures, and user defined functions.

Publication

• A publication is a collection of articles that exist in a publisher.

Publisher (Source Server)

• A Publisher is a SQL server instance that has publications which contain articles to be published to subscribers.

Distributor

• A Distributor collects the data from publisher and delivers to subscribers.

Subscriber (Destination Server)

• Subscribers is the destination server that receives the data from publisher.

Getting started

To set up transactional replication, we must configure the SQL replication Distributor and create SQL replication Publication, replication snapshot folder and a SQL replication Subscription.

Step 1: Configure Distributor

1. Open SSMS and connect to the SQL Server instance.
2. In object explorer browse to Replication folder, right click on Replication folder, and select configure Distribution.                                                                                                                                                                       
3. The first page of Distribution configuration wizard appears. Click on next                                                                                                     
4. On the next page we have an option to choose to setup up the current instance to be a distributor or we can select another instance that’s already been configured as a distributor. In this case the distributor is local. “SQL server will create a distribution database and log” select next.                           
5. Specify the Snapshot Folder, select the path where snapshot should be stored. The snapshot folder is used for initial data synchronization of transactional replication.                                                                   
6. Specify the name of the distribution database and the folders where the data and log files should be located.
   
7. In the Publisher page specify the publishers that are going to access the distributor.
   
8. In wizard actions, we have the option to configure the distribution immediately or create a script that can be executed later.
   
9. In the complete the wizard, Review the setting and configurations, and select Finish to enable the distributor.
   
10. Setting up the distribution is completed.
    
11. Now we can see that distribution database has been created under System Databases.
    

Step 2: Configure Publisher

1. In the object explorer, under the replication folder, right click Local Publication, and then select New Publication.
   
2. A new Publication Wizard will appear that will give us the general information about creating Publication.
   
3. In the Publication Database, select a database which needs to be published and click next.
   
4. In the Publication Type page, select Transactional Replication, and click next.
   
5. In the Articles page, choose the articles that should be replicated.
   
6. In the Filter Table Rows page, defines filters that should be applied to our articles. Let us go with the default values.
   
7. In the Snapshot Agent page, we can specify when to run the Snapshot Agent. It can run immediately, or we can schedule to run later, we are going to create a snapshot immediately.
   
8. Now, on the Agent Security page, specify the account to use to run the Snapshot agent.
   
9. In the Wizard Actions page, we have two options. We can create the Publication immediately or save the configuration in the script file to run later.
   
10. In the Complete the Wizard page, Enter the Publication name and review the options and click Finish.
    
11. In the Creating Publication page, we can see the progress of each step.
    
12. Now, we see the Publication is created under the local publication folder.
    

Step 3: Configure Subscriber

The final step is to create the Subscription.

1. In the object explorer expand the replication folder, right click on the Publication we have created and click on New Subscriptions.
   
2. The New Subscription Wizard appears that tells us the general information.
   
3. On the Publication page, select Publication and click next.
   
4. On the next page, select “Run all agents at the Distributor” and click next.
   
5. On the Subscribers page, select add subscriber, and then select add SQL server subscriber from the drop down. This opens the connect to server dialog box. Enter the subscriber instance name, username and password and then click connect.
   
6. After the subscriber SQL instance is added, select the drop down then select the database name where data should be replicated.
   
7. On the Distribution Agent Security page, select the ellipsis (…) button.
   In the next page enter the process account details and subscriber account details that is used to connect to subscriber database (Make sure that user has the db_owner permission on the subscriber)
   
8. In the Synchronization Schedule page, click on next.
   
9. In the Wizard Actions page, we have two options. We can create the subscription immediately or save the configuration in the script file to run later.
   
10. In the complete Wizard page, we can see summary of the newly created subscription. Click finish.
    
11. In the final page we will get to know whether process has been successful or not.
    

Monitoring Replication

1. Connect to Publisher, in the object explorer right click the replication folder, and then select launch replication monitor to verify the status of newly created transactional replication setup.
   
   

Conclusion

Setting up transactional replication in SQL server is a powerful way to maintain data consistency across distributed environments. By following the step-by-step guide provided in this blog, we can configure transactional replication efficiently and seamlessly replicate data changes from the publisher to subscribers. Transactional replication empowers you with a reliable data management solution and facilitates various critical tasks. Happy Replicating!

Pramodh P