Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Replication support only option with SQL Server 2005 replication


By:   |   Read Comments (4)   |   Related Tips: More > 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.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, December 04, 2014 - 8:45:37 AM - Greg Robidoux Back To Top

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: http://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/

 


Thursday, December 04, 2014 - 1:43:38 AM - Jay J Back To Top

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

 

 

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 08, 2012 - 1:09:06 PM - Deepak Kumar Back To Top

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

(4)

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.

(5)

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

 

 


Learn more about SQL Server tools