Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Due to a business need, I may need to disable transactional replication in my environment (Sql 2k -> Sql 2008). Functionally, I understand this to mean that I will need to drop subscriptions and articles. Is getting the create scripts enough to restore replication back to original state when the conflicting need is addressed?

Thanks.

share|improve this question
1  
A couple of questions. Do you want to disable just the subscriber or the publisher and the subscriber ? Are you replicating the entire database or are you filtering out some objects? –  Craig Efrein Feb 14 '12 at 7:56
1  
We would need to disable it on both the publisher and the subscriber. Also, we are replicating a large subset of the tables in the database, but not all of them. –  swasheck Feb 14 '12 at 15:32
2  
I just put in my answer based on disabling the subscription. The subscription would be disabled on both the publisher and subscriber. –  Craig Efrein Feb 14 '12 at 15:39

2 Answers 2

up vote 8 down vote accepted

I tested this example using SQL Server 2000 (MS2000) as the publisher and SQL Server 2008 (MS2008 )as the subscriber. On the publisher, one database called arp has several tables, but only the table users is included as an article in the publication. This example removes the subscription from the both servers and then puts it back again.

On the Subscriber MS2008

Removed the existing subscription from MS2008

USE arp
GO

EXEC sp_droppullsubscription @publisher='MS2000', @publisher_db='arp', @publication='arp_pub'

On the publisher MS2000

Dropped the Subscription

-- dropped subscription
exec sp_dropsubscription @publication = N'Arp_pub', @subscriber = N'MS2008', @destination_db = N'arp', @article = N'all'

Dropped the Subscriber

-- On MS2000, dropped subscriber from MS2008 after removing it from MS2008
exec sp_dropsubscriber @subscriber = N'MS2008'

Later on, I recreate the subscription.

On the Publisher, MS2000

Add back in the subscriber

use [master]
exec sp_addsubscriber @subscriber = N'MS2008', @type = 0, @description = null, @security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 99991231

exec sp_changesubscriber_schedule @agent_type = 1, @subscriber = N'MS2008', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 99991231
GO

Add back in the subscription

use [Arp]
exec sp_addsubscription @publication = N'Arp_pub', @subscriber = N'MS2008', @destination_db = N'arp', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO

On the Subscriber MS2008

Create the database

CREATE DATABASE arp;
GO

Add the pull subscription

use [arp]
exec sp_addpullsubscription @publisher = N'MS2000', @publication = N'Arp_pub', @publisher_db = N'Arp', @independent_agent = N'False', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0

Add the agent

The @job_login is a ntlm user that has rights to the default snapshot folder. That user also has access to the publication on the publisher

The snapshot folder is a shared folder accessible by both Publisher and Subscriber.

exec sp_addpullsubscription_agent @publisher = N'MS2000', @publisher_db = N'Arp', @publication = N'Arp_pub', @distributor = N'MS2000', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20120214, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'MS2008\replication_user', @job_password = 'test123', @publication_type = 0
GO

On the publisher MS2000, I ran the snapshot job

The replicated table was once again visible on MS2008.

share|improve this answer
    
Thank you so much. This is very helpful. –  swasheck Feb 14 '12 at 15:42
1  
Your welcome. I did leave in place the users, snapshot folder, shares. All this does is remove the subscription from both servers, but everything else is left alone. –  Craig Efrein Feb 14 '12 at 15:46

To Disable Replication:

-- disabling the replication database (removes publisher and subscribers)
-- this will allow you to detach your database as well:
use master
exec sp_replicationdboption 
@dbname = N'yourdb', 
@optname = N'publish', 
@value = N'false'
GO

To Enable Replication (do this before you remove replication):

Go to Replication > Local Publications > Right click on the publisher you wish to disable, and click on Generate Scripts, from there choose to create or enable components and click on Generate Scripts (to clipboard or to new query window).

The script will re create your publisher and subscribers

** Please remember to fill in subscriber and publisher passwords **

This script will re create your publisher with one article (table) and subscriber:

-- Enabling the replication database
use master
exec sp_replicationdboption 
@dbname = N'YourDatabase', 
@optname = N'publish', 
@value = N'true'
GO

exec [YourDatabase].sys.sp_addlogreader_agent 
@job_login = null, 
@job_password = null, 
@publisher_security_mode = 0, 
@publisher_login = N'sa', 
@publisher_password = N'YourPassword'
GO
-- Adding the transactional publication
use [YourDatabase]
exec sp_addpublication 
@publication = N'YourDatabase', 
@description = N'Transactional publication of database ''YourDatabase'' from Publisher ''YourServerName''.', 
@sync_method = N'concurrent', 
@retention = 0, 
@allow_push = N'true', 
@allow_pull = N'true', 
@allow_anonymous = N'true', 
@enabled_for_internet = N'false', 
@snapshot_in_defaultfolder = N'true', 
@compress_snapshot = N'false', 
@ftp_port = 21, 
@ftp_login = N'anonymous', 
@allow_subscription_copy = N'false', 
@add_to_active_directory = N'false', 
@repl_freq = N'continuous', 
@status = N'active', 
@independent_agent = N'true', 
@immediate_sync = N'true', 
@allow_sync_tran = N'false', 
@autogen_sync_procs = N'false', 
@allow_queued_tran = N'false', 
@allow_dts = N'false', 
@replicate_ddl = 1, 
@allow_initialize_from_backup = N'false', 
@enabled_for_p2p = N'false', 
@enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot 
@publication = N'YourDatabase', 
@frequency_type = 1, 
@frequency_interval = 0, 
@frequency_relative_interval = 0, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 0, 
@frequency_subday_interval = 0, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, 
@active_start_date = 0, 
@active_end_date = 0, 
@job_login = null, 
@job_password = null, 
@publisher_security_mode = 0, 
@publisher_login = N'sa', 
@publisher_password = N'YourPassword'
exec sp_grant_publication_access 
@publication = N'YourDatabase', 
@login = N'sa'
GO
exec sp_grant_publication_access 
@publication = N'YourDatabase', 
@login = N'NT AUTHORITY\SYSTEM'
GO

-- Adding the transactional articles
use [YourDatabase]
exec sp_addarticle 
@publication = N'YourDatabase', 
@article = N'app_installation', 
@source_owner = N'dbo', 
@source_object = N'YourDatabase_Table', 
@type = N'logbased', 
@description = N'', 
@creation_script = N'', 
@pre_creation_cmd = N'drop', 
@schema_option = 0x000000000803509F, 
@identityrangemanagementoption = N'none', 
@destination_table = N'YourDatabase_Table', 
@destination_owner = N'dbo', 
@status = 24, 
@vertical_partition = N'false', 
@ins_cmd = N'CALL [sp_MSins_dboYourDatabase_Table]', 
@del_cmd = N'CALL [sp_MSdel_dboYourDatabase_Table]', 
@upd_cmd = N'SCALL [sp_MSupd_dboYourDatabase_Table]'
GO

-- Adding the transactional subscriptions
use [YourDatabase]
exec sp_addsubscription 
@publication = N'YourDatabase', 
@subscriber = N'YourSubscriberServerName', 
@destination_db = N'YourDatabase', 
@subscription_type = N'Push', 
@sync_type = N'automatic', 
@article = N'all', 
@update_mode = N'read only', 
@subscriber_type = 0
exec sp_addpushsubscription_agent 
@publication = N'YourDatabase', 
@subscriber = N'YourSubscriberServerName', 
@subscriber_db = N'YourDatabase', 
@job_login = null, 
@job_password = null, 
@subscriber_security_mode = 0, 
@subscriber_login = N'sa', 
@subscriber_password = 'yourpassword', 
@frequency_type = 64, 
@frequency_interval = 1, 
@frequency_relative_interval = 1, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 4, 
@frequency_subday_interval = 5, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, 
@active_start_date = 0, 
@active_end_date = 0, 
@dts_package_location = N'Distributor'
GO
share|improve this answer
4  
What does this add over and above the accepted answer? Other than a mess someone has to clean up? –  Mark Storey-Smith Jul 9 '13 at 22:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.