Replication support only option with SQL Server 2005 replication

By: Greg Robidoux   |   Updated: 2006-11-20   |   Comments (4)   |   Related: > Replication

Problem

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.

Solution

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.

About the author

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 MSSQLTips.com. 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