How to Set Transnational Replication for SQL Server Database?

Introduction:

                This Document will guide you in setting Transactional Replication for SQL Server Database. Here I am going to guide you for the questions like ‘How to’ not like ‘What is’. If you need answer of ‘What’ in between, you can refer the Microsoft detailed documentation.

                This Replication is done on SQL Server 2014 Database to Azure SQL Database.

TO DO:

A.      Setup Publication for Database.

B.      Setup Subscription for Database.

C.      Monitor Replication.

Steps for Publication Setup:

1. Login to Server where your Database is, you want to setup transactional Replication.Go To     Replication > Local Publications > Select New Publication.

2. Publication Database: Select The Database for which you want to set Transactional Replication.

3.       Publication Type: Select Publication Type Transactional. 

4.       Articles: Select what you want to replicate from the Database. Here I have selected all.

5. Article Issues: This will show the dependent object required to access. For example if you select the Views only in Article this will ask for Table Reference as views are derived from Tables.

6. Filter Table Rows: This will allow you to exclude tables Rows from Replication. This is useful when you can to replicate the data after some date. For example if your database contains the 5 years backlog data and you want only current month’s onward update in Replica. Then you can set filter to exclude before current month data.

7.  SnapShot Agent: This is one time activity. This will allow to copy the current data from database to the Replica. SnapShot Agent uses BCP (Bulk Copy Program) to copy data. You can also schedule this to run. I am selecting immediately.

8. SnapShot Security: These credentials are uses for SnapShot Agent. Select > Security Settings

9.    SnapShot Agent Security: Select the Security type and enter credentials for it.

10. Script File Properties: This will allow you to script all your publisher settings. 

11. Complete the wizard: This will allow you to verify all your selection.

12. Creating Publication :

13.  If this is not success, check that database is enabled for Replication. If not do check Replication > Right Click > Publication properties. Check this checkbox is checked for your database where you want to set replication. And run the Publication wizard again.

Steps for Subscription Setup:

1. Login to server go to Replication> Local Subscription > Right Click > New Subscription.

2.  New Subscription Wizard

3. Publication: Enter publisher name you just created and select the Publication.

4.  Distribution Agent: Select Run All Agents at the Distributor.

5. Subscriber: Select Add Subscriber. I already have one so it’s showing in.

6.    Add Subscriber will ask you for Credentials. Enter the Replica server credentials. I am using Azure Database server for the replica.

7.   Select Database from the server where the replica will be set.

8.  Distribution Agent Security: Enter the publisher login details as well as Subscriber login details.

9.   Synchronization schedule: This will allow you to set schedule to synchronize the transactions. Select Run Continuously.

10. Initialize Subscription: Select Immediately.

11. Wizard Action: Select all check box and select next.

12. Script file Properties: This allows you to script all the Subscriber settings in a file.

13. Complete Wizard: You can verify all the selection in this. Click Finish.

14. Creating Subscriptions :

15. Done!! Replication is set.

Check the Replication:

You can check the replication by replication monitor. Login to publication server, Go to Replication > Right Click > Launch Replication Monitor