7

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.

3
  • 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? Feb 14, 2012 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, 2012 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. Feb 14, 2012 at 15:39

1 Answer 1

9

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.

1
  • 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. Feb 14, 2012 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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