We will now create a Publisher:
2. Expand the menu under your Instance in SSMS. Go to Replication -> Local Publications
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
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.
Make sure that the Database Owner is set and the user has the permissions.
This can be configured on the Database Properties
Now let's have a look at the subscription status for the Replication.
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.