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



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


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.


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


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


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.


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.


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


Step 2: Create Publication Publication_AdventureWorks2008R2



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


Select Transactional Replication as the publication type.


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.


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.


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.


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.


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


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.


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.


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.




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.


Click close to close the create publication wizard.

Step 3: Enable Allow initialization from backup


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.




Leave a Reply