Replication, SQL Server Administration

Configure Transactional Replication

This document describes the steps required to configure transactional replication between two SQL Server instances initialized using database backup. Transaction replication can be initialized by a snapshot or database backup. In case of large databases, initializing via snapshot may take longer than expected. Therefore, it’s better to initialize the replication via backup.

Step 1: Configure Distribution Wizard

The first step is to configure the distributor. This step creates the distribution database on the given SQL Server instance.

Replication: Configure Distribution Wizard

2_replication

3_replication

It’s better to have a separate server as distributor. However, you can setup either the publisher or a subscriber as the distributor.

4_replication

The above window will only come up if SQL Server Agent Service is not set to start automatically when computer is started. SQL Server Agent is required for replication and therefore the service should be up and running when the computer starts.

5_replication

Leave the snapshot folder as default as you won’t require snapshot when initializing transaction replication from backup.

6_replication

As a best practice don’t the change the distribution database name. However, you should certainly change the distribution database file paths.

7_replication

A distributor database can be used as a distributor to multiple publishers. Add all SQL Server Instance you wish to configure as publisher for this distributor. Observer that in this case, the distributor instance is also the publisher instance.

8_replication

It is good to generate the distribution script and save it in a file. That way, you can use the script in future to configure distribution. You would be required to change the publisher and distribution SQL Instance in the script when configuring on different SQL Server Instances. In here, I’ll use wizard to configure the distribution.

9_replication

Review the wizard summary to make sure all of the settings are correct. Then click Finish to configure the distributor.

10_replication

Step 2: Create Publication Publication_AdventureWorks2008R2

11_replication

23_replication

Select the database you want to replicate. I selected AdventureWorks2008R2. The next step to configure transactional replication is to select the type of replication.

13_replication

Select Transactional Replication as the publication type.

24_replication

Choose the objects you wan’t to replicate. When initializing from backup you won’t be able to add any object once the replication is up and running. Therefore, choose your objects carefully and be aware of this limitation. You would have to reconfigure the publication and subscription to replicate a new object.

14_replication

The window gives you a heads up to few common issues. First is that when choosing an object to replication you should make sure that all the dependent objects are also selected for replication. For example, if you select a view to replication make sure that the dependent tables are also selected for replication, otherwise the view will error out at the subscriber. So is the case with stored procedures, functions, triggers and indexed views.

15_replication

This allows you to filter out rows you don’t want to replication. This is useful when you don’t want to replicate the historical data. For example, you can filter out sales made before 2015 and replicate sales made in 2015 and 2016. You can’t join two tables when specifying the filter criteria.

25_replication

The above filter will only replicate the transactions from the TransactionHistory table where Transaction Year is greater than or equal to 2015. You can choose to skip this step.

16_replication

As snapshot is not required when initializing from, uncheck the create snapshot option.

26_replication

Snapshot agent isn’t required here. Therefore, uncheck the “Use the security settings from the snapshot agent” checkbox. Click on security settings button besides Log Reader Agent.

27_replication

Specify the windows account to run the log reader agent. Provide a publisher SQL Server login and password to allow log reader agent to connect to the publisher.

28_replication

Though we aren’t using snapshot agent, however, we need to specify the security settings for snapshot agent to proceed. Follow the above steps to specify the same security settings for snapshot agent.

29_replication

30_replication

31_replication

It is recommended to generate and save the publication script to quickly reconfigure the replication as and when required. However, I will use the wizard to create the publication.

20_replication

Click close to close the create publication wizard.

Step 3: Enable Allow initialization from backup

32_replication\

Step 4: Backup Publisher database

The backup should be taken only after you enable “ALLOW INITIALIZE FROM BACKUP”.

Step 5: Restore Publisher Database on Subscriber

Step 6: Add Subscription to the Publication

Step 7: Add Subscription to the Publication

This is how you can configure transactional replication initialize via backup.

Step 7: Verify Replication

We’ll talk about troubleshooting replication in future blogs.

 

Thanks,

Ahmad

Leave a Reply