solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

SQL Sentry, Inc. - SQL Sentry Performance Advisor for SQL Server

SQL Sentry Performance Advisor for SQL Server delivers an advanced performance dashboard with relevant Windows and SQL Server metrics in a single view along with detailed insight of heavy SQL, blocking, deadlocks, and disk bottlenecks. Performance Advisor is packed with ground-breaking features that are not found in any other performance monitoring software, all designed with the singular goal of simplifying the process of optimizing your SQL Server performance.

Learn more!

Options to reinitialize subscriptions in SQL Server replication

By: Mohammed Moinudheen | Read Comments (4) | Print

Mohammed is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

Related Tips: More

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.

sql replication publications

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.

sql server reinitialize subscription

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.

sql reinitialize all subscriptions

Option 2: Using Replication Monitor

In SSMS go to Replication -> right click on replication and select 'Launch Replication Monitor', as shown below.

sql server launch replication monitor

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.

sql replication monitor reinitialize subscription

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.

sql replication monitor reinitialize all subscription

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: http://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

Related Tips: More | Become a paid author

Last Update: 12/14/2011

Share:

Connect with MSSQLTips.com

Comments and Feedback:

Wednesday, December 14, 2011 - 1:45:23 PM - Ludwig Guevara

Read The Tip

Hi:

This only works with SQL Server 2005, because I couldn't find the same dlls or even the same objects I used for this program.

I create a C# program using Replication DLL

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Replication;
using Microsoft.SqlServer.Server;

Object ReplicationMonitor method EnumLogReaderAgents to get Publishers data

public static DataSet GetPublishers(string ServerName)
{
// 1 per database replicated
DataTable dtLogReaderAgents = new DataTable();
ServerName = GetDistributionServer(ServerName);
ServerConnection cnn = new ServerConnection(ServerName);

cnn.Connect();
if (cnn.IsOpen)
{
ReplicationMonitor replicationMonitor = new ReplicationMonitor(cnn);
replicationMonitor.Load();
replicationMonitor.LoadProperties();
// dbname, name, status, publisher, publisher_db, start_time, time, duration, comments, delivery_time,
// delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency,
// error_id, job_id, local_job, profile_job, agent_id, local_timestamp
dtLogReaderAgents = replicationMonitor.EnumLogReaderAgents().Tables[0];
}
if (cnn.IsOpen)
cnn.Disconnect();
return dtLogReaderAgents;
}

Then look for these columns:

ServerName
string sLogReader = dr["Name"].ToString();
string sComments = dr["comments"].ToString()

Build an email Alert to know which Publisher Server and run T-SQL

[usp_SendLogAgentIsRetryingAfterAnErrorMessage] {0}= ServerName, {1} = LogAgent, {2} = sComments

To reinitialize subscription run job (pointed to msdb), used: sLogReader

Reference:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.replicationmonitor.enumlogreaderagents.aspx

Regards

Thursday, December 15, 2011 - 2:29:03 AM - bojanna mk	Read The Tip
Thanks Moinu.The article looks good and useful.

Thursday, December 15, 2011 - 2:30:02 AM - Srinath	Read The Tip
Thats really good stuff, Moinu....Thanks much for this writing......Keep posting,.

Monday, January 09, 2012 - 5:00:25 PM - bebimbop

Read The Tip

I've tried each of these methods multiple times and ALWAYS fall back to recreating the subscriptions. I've wasted many an hour trying to figure out which piece is misbehaving and what to restart, replication monitor is woefully inadequate in that regard.

If time is of the essence, just recreate the subscription. You will get back online faster.

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!"

Quickly and accurately deploy database changes with Red Gate's SQL Compare - the industry standard comparison and deployment tool.

Need SQL Server help and not sure where to turn? Reach out to the Edgewood consultants for a Health Check.

Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST