Temporarily Disable Sql Replication

Question

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.

Answer 1

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.