Replication support only option with SQL Server 2005 replication

By:   |   Comments (4)   |   Related: > Replication


When setting up transactional replication with SQL Server 2000 there were only two options for getting your subscribers the data and the objects necessary for replication.  The first option was "none" which meant you had to do everything manually and the second option was "automatic" which did everything from creating the tables, creating the stored procedures needed for replication and synchronizing the data. The automatic option was great if you needed to do all of these steps, but in some cases the data already existed and you really only needed the support objects to handle replication.  The issue with the "none" option is you had to do everything manually which often led to mistakes.


With SQL Server 2005, two new sync options have been added "replication support only" and "initialize with backup".  In this tip we will discuss the "replication support only" option.

Replication support only
This new option allows you to manually synchronize your data and schema on your publisher and your subscriber, but then the support objects needed to make replication work are pushed automatically to the subscriber. The benefit is that you can still take advantage of the replication tools to setup replication, but you also have more control over getting your data and schema in sync.  This is great if you already have a database in place and you only want to setup replication. Instead of having to drop and recreate the tables, this option will just push out the objects needed for replication and not affect your existing schema or data.

Unfortunately this option is not available via the GUI, so this needs to be done using the sp_addsubscription stored procedure.  You can still use the GUI tool to setup your publisher and your publications, but instead of using the GUI to setup the subscriber you need to use this stored procedure. 

One way to make sure you have all of your options set, is to setup a subscriber using the GUI.  At the very end of the setup process select "Generate a script..." instead of "Create the subscription(s)".  This way you can see the commands that are being used and then you can modify as necessary.



This is the command that gets created with the "do not initialize" option.  The @sync_type = 'none'.

EXEC sp_addsubscription @publication N'Test'
 @subscriber N'EdgeTest'
 @destination_db N'TestDB'
 @subscription_type N'Push'
 @sync_type N'none'
 @article N'all'
 @update_mode N'read only'
 @subscriber_type 0

This needs to be changed to use the "replication support only" option.  The @sync_type = 'replication support only'.

EXEC sp_addsubscription @publication N'Test'
 @subscriber N'EdgeTest'
 @destination_db N'TestDB'
 @subscription_type N'Push'
 @sync_type N'replication support only'
 @article N'all'
 @update_mode N'read only'
 @subscriber_type 0

At this point this command can be executed in a query window to setup the subscriber with the replication support only objects and the data and schema remain unchanged.

Next Steps
  • When using this method you need to ensure that the data and schemas are the same for both the publisher and the subscriber.  Replication setup will not do this for you.
  • If you have tables that use identity values make sure to change the "Not For Replication" property to "Yes" on both the publisher and subscriber.
  • Explore other replication changes that been implemented with SQL Server  2005 replication.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, December 4, 2014 - 8:45:37 AM - Greg Robidoux Back To Top (35504)

Hi Jay,

you will need to setup replication from Server A to Server C and when you set it up you want to use the initialize from a backup option.  Then you can take the backup and restore it to Server C and then turn on the replication agents for this new subscriber.  This should then keep Server B and Server C in synch with Server A.

Take a look at this tip for more information:


Thursday, December 4, 2014 - 1:43:38 AM - Jay J Back To Top (35496)

I wonder if you can help me out in the following scenario:

The scenario is:
We have a SQL 2008 server A replicating (transactional) to a SQL 2008 server B. We want to create a SQL 2014 server C so that our users of server B can switch to server C instantly at some point of time and have access to the same data that is present on server B.
For that we proposed to create a parallel replication from Server A to Server C. We decided to backup the databases from server A and restore them on server C. After that we would have to "something smart " in replication to ensure that after a short  period of time the data on server C would match the data on server B in real-time. We want to avoid doing a manual synchronization of the data differences between B and C.
My question is...what is the "something smart " that we should to achieve our objective ?


Friday, October 19, 2012 - 5:37:15 PM - sunny Back To Top (20001)



hi My Name Is Sunny 


We have a dedicated server that will host our client's database for a web portal. To connect to this database, you need an ip address.



When I attempt to create a subscription from a client, it will not let me put the ip address in, I am getting:


SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name.


Do I have my publication and subscription confused? How do I connect to a Publication that is not local?


Kindly Solve Me This Issue ,if you have Any Video, Link or Information About Merge-Replication Configuration  Dedicated Server IP


please send me My Email Id Is ([email protected])


Please Help Me out About thie Issue....I m Very Thank Full To You


Sunday, April 8, 2012 - 1:09:06 PM - Deepak Kumar Back To Top (16811)

Hi Greg,

I always read your articles. I am big fan of your articles. In our environment we have configured Transactional Replication. But I am new to Replication, I have got lot of knowledge after reading your and other brilliant authors articles and got lot of knowledge. But I always got the following kind of alert, It would be great if you can write step by step remediation steps for the below said alerts. This would definately going to resolve millions of DBA's problem in their day 2 day life.

(1) DESCRIPTION:      Replication-Replication Distribution Subsystem: agent Machine Name-XXX-Global Tables - Desti-MachineName-323 failed. Cannot insert duplicate key row in object 'dbo.ABC' with unique index 'AK_ABCRoleName'.

(2) DESCRIPTION:      Replication-Replication Distribution Subsystem: agent Machine-DBName-Machine Name scheduled for retry. The process could not connect to Subscriber 'Machine Name'.


(3) The Distribution Agent reached the end of current command (transaction: 0x0x00002ad50000cc8a00ce00000000 command 28), but the number of bytes read (157) is less than the total size (3211318) indicated in command header. When troubleshooting, backup the di


Replication-Replication Distribution Subsystem: agent MachineName-DBName-DBName Tables -MachineName-443 scheduled for retry. TCP Provider: An existing connection was forcibly closed by the remote host.


Replication-Replication Distribution Subsystem: agent MachineName-DBName-MachineName-111 failed. The row was not found at the Subscriber when applying the replicated command.



get free sql tips
agree to terms