Problem
I have transactional replication configured in my production environment with multiple subscribers. The business team has requested that one of the subscriptions be reinitialized, because they think there is some missing data. In this tip we look at the different options that you can use to reinitialize a subscription for transactional replication.
Solution
If we come across this requirement there are different ways by which we could achieve this. In this tip, we will accomplish this by using SQL Server Management Studio (SSMS) and Replication Monitor. Note, both options assume transactional replication is already configured in your server.
Option 1 : Using SQL Server Management Studio (SSMS).
In SSMS go to Replication -> Local Publications -> Locate your publication and expand it. The below screenshot shows the details of the subscriptions.
Suppose, you wish to reinitialize only the subscription [PHOENIX].[REP_S1]. To do this, right click that subscription and select the ‘reinitialize’ option and you will get a dialog window as shown in the below screenshot.
Here, we have the option to select either ‘Use the current snapshot’ or ‘Use a new snapshot’. The use current snapshot will use the existing snapshot and the use a new snapshot will use a new current snapshot. Based on your requirement, select the desired option and then click on ‘Mark for Reinitialization’ which will enable you to reinitialize the subscription.
If you wish to reinitialize all subscriptions, you would need to right click on the publication and select ‘Reinitialize All Subscriptions’ as shown in the screenshot below, which would enable you to reinitialize all your subscriptions.
Option 2: Using Replication Monitor
In SSMS go to Replication -> right click on replication and select ‘Launch Replication Monitor’, as shown below.
The ‘replication monitor’ screen should open as shown below. On the left pane, under ‘My publishers’, click on the publisher node and expand to get a list of the subscriptions.
In the ‘All Subscriptions’ tab, you need to select the appropriate subscription and click on ‘Reinitialize Subscription’ which would enable you to reinitialize only that subscription in the list. Once done, you would encounter the same window (image 2) as shown in option 1, when you reinitialize a subscription and you would need to either select the existing snapshot or opt for a new one.
If you wish to reinitialize all your subscriptions using replication monitor, you could just right click on the publication node and select ‘Reinitialize All Subscriptions’ as shown below.
Both options could be tested easily by configuring a simple replication setup and performing the sequence of steps as shown above.
Things to note:
- If you select “Use a new snapshot” the snapshot process will run automatically as long as the job is not disabled.
- If you select the reinitialize option this will start automatically as long as the distribution agent is running continuously and the snapshot exists. If the distribution agent is scheduled to run at intervals then the snapshot would be applied to the subscription the next time this job runs.
- It is important to know the implications of reinitializing a subscription. When this is run all data at the subscriber will be replaced with the new data for all articles in the subscription. Refer to this tip about implications of applying a new snapshot: https://www.mssqltips.com/sqlservertip/2375/space-impact-of-replication-snapshot-agent-job-in-sql-server
- It is not possible to reinitialize a subscription using a backup. If you try you will get this error: “This subscription already exists”.
- The above steps were performed using SQL Server 2008 R2, but should be similar for SQL Server 2005 and later.
Next Steps
- Consider testing this scenario through a simple transactional replication setup
- Refer to other related tips on replication to get familiar with the concepts
