SQLServerCentral Article

Distribution Database Migration

,

Sometimes business needs to move a distribution db to a new MS SQL Server instance. The most common solution would be to drop and recreate a replication topology with a new distributor instance.However, you will need to synch data on subscribers afterwards or reapply newly generated replication snapshots.This is not very graceful approach and can take hours to complete  if publications are highly transactional and articles contain lots of data. Moreover, it can cause downtime in production systems if the latest data is retrieved by customers on subscribers. A better solution would be to restore existing distribution db on the new distributor using the following step-by-step guide:

1.Create distribution database

We need to create a database on a new distributor instance.

--sqlcmd command to open connection to a new distributor:
:CONNECT $(NewDistributor)
GO
--sql code to create a new distribution db on a new distributor instance:
EXEC sp_adddistributiondb @database = N'$(DistributionDB)',
    @data_folder = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA', @log_folder = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA',
    @log_file_size = 2, @min_distretention = 0, @max_distretention = 72,
    @history_retention = 48, @security_mode = 1
GO

Note: All sql code examples has to be executed in sqlcmd mode  with correct $(NewDistributor), $(PUBLISHER),$(PUBLISHER_DB), $(PublicationName) ,$(SUBSCRIBER),$(SUBSCRIPTION_DB) , $(DistributionDB),$(CURRENT_DISTRIBUTOR), $(LogreaderAgentLoginName), $(DistributorAgentLoginName), @working_directory  variables values which are specific for your db servers estate.

2.Add the publisher

We need to add the publisher values on a new distributor instance.

--sqlcmd command to open connection to a  new distributor
:CONNECT $(NewDistributor)
GO
use master
GO
--sql code to add a publisher to a new distribution db on a new distributor instance:
EXEC sp_adddistpublisher @publisher = N'$(PUBLISHER)',
    @distribution_db = N'$(DistributionDB)', @security_mode = 1, @working_directory = N'\\VL1DB020\ReplData',    @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

3. Linked Servers

Create the missing linked servers on the new distributor.

--sqlcmd command to open connection to a  new distributor
:CONNECT $(NewDistributor)
GO
use master
GO
DECLARE @server_name sysname;
-- sql code to create linked server and access data from the current distributor
EXEC master.dbo.sp_addlinkedserver     @server = '$(CURRENT_DISTRIBUTOR)',     @srvproduct=N'SQL Server' ;
--  get one missing linked server on the new distributor server;
 select top 1 @server_name=srvname from [$(CURRENT_DISTRIBUTOR)].master..sysservers
  except
 select  srvname from master..sysservers

-- sql code to create missing linked server on the new distributor.
EXEC master.dbo.sp_addlinkedserver     @server = @server_name,     @srvproduct=N'SQL Server' ;

3.1. Check Profiles

Make sure msdb.dbo.MSagent_profiles are matching between distributors.

--sqlcmd command to open connection to a  new distributor
:CONNECT $(NewDistributor)
GO
use master
GO
-- retrieve all different profiles from the current distributor
select * from [$(CURRENT_DISTRIBUTOR)].msdb.dbo.MSagent_profiles
except
select * from msdb.dbo.MSagent_profiles

4.Set distribution db to full recovery mode

We need to change the recovery model and set the sync with backup option.

--sqlcmd command to open connection to a current distributor:
:CONNECT [$(CURRENT_DISTRIBUTOR)]
GO
USE [master]
--sqlcmd command to set distribution db options:
ALTER DATABASE [$(DistributionDB)] SET RECOVERY FULL WITH NO_WAIT
exec sp_replicationdboption '$(DistributionDB)', 'sync with backup', true

5.Create missing logins

This script will create the missing logins on the new distributor and add distribution agent  and log reader agent accounts to db_owner role on the distribution db:

--sqlcmd command to open connection to a current distributor:
:CONNECT [$(NEWDISTRIBUTOR)]
GO
USE [master]
IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = '$(DistributorAgentLoginName)')    BEGIN
  print 'Creating $(DistributorAgentLoginName) login '
  CREATE LOGIN [$(DistributorAgentLoginName)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
END
GO
IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = '$(LogReaderAgentLoginName)')    BEGIN
  print 'Creating $(LogreaderAgentLoginName) login '
  CREATE LOGIN [$(LogreaderAgentLoginName)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
END

use [$(DistributionDB)]
GO
IF NOT EXISTS ( SELECT  *  FROM    sys.database_principals  WHERE   name = '$(DistributorAgentLoginName)' )  BEGIN
   print 'Creating $(DistributorAgentLoginName) db user'
   CREATE USER [$(DistributorAgentLoginName)] FOR LOGIN [$(DistributorAgentLoginName)]                           
END
IF NOT EXISTS ( SELECT  *  FROM    sys.database_principals  WHERE   name = '$(LogreaderAgentLoginName)' )  BEGIN
   print 'Creating $(LogreaderAgentLoginName) db user'
   CREATE USER [$(LogreaderAgentLoginName)] FOR LOGIN [$(LogreaderAgentLoginName)]    
END
--Add Roles
 print 'Add $(LogreaderAgentLoginName) db user to db_owner role'
EXEC [sp_addrolemember]    @rolename = 'db_owner',     @membername = '$(LogreaderAgentLoginName)'
GO
 print 'Add $(DistributorAgentLoginName) db user to db_owner role'
EXEC [sp_addrolemember]    @rolename = 'db_owner',     @membername = '$(DistributorAgentLoginName)'
GO

6.Create missing distribution agent login

We now need to create a lotin and then add it to the db_owner role on the subscriber dbs for pull subscriptions.

--sqlcmd command to open connection to a current distributor:
:CONNECT [$(SUBSCRIBER)]
GO
USE [master]
IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = '$(DistributorAgentLoginName)')    BEGIN
  print 'Creating $(DistributorAgentLoginName) login '
  CREATE LOGIN [$(DistributorAgentLoginName)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
END
GO
use [$(DistributionDB)]
GO
IF NOT EXISTS ( SELECT  *  FROM    sys.database_principals  WHERE   name = '$(DistributorAgentLoginName)' )  BEGIN
   print 'Creating $(DistributorAgentLoginName) db user'
   CREATE USER [$(DistributorAgentLoginName)] FOR LOGIN [$(DistributorAgentLoginName)]
END
--Add Roles
  print 'Adding $(DistributorAgentLoginName) db user to db_owner role'
EXEC [sp_addrolemember]    @rolename = 'db_owner',     @membername = '$(DistributorAgentLoginName)'
GO

7.Stop/disable replication jobs

We need to stop or disable the replication jobs on the current distributor and pull subscribers.

Example:

--sqlcmd command to open connection to a current distributor:
:CONNECT $(CURRENT_DISTRIBUTOR)
GO
--sql batch to stop and disable replication sql jobs:
DECLARE @JobName sysname
DECLARE @CMD varchar(1000)
DECLARE @Name VARCHAR(200) ;        
DECLARE Purge CURSOR
                FOR SELECT j.name
FROM [msdb].[dbo].[sysjobs] j
where j.name like '$(PUBLISHER)%'
           OPEN Purge ;
           FETCH NEXT FROM Purge INTO @Name ;
           WHILE @@FETCH_STATUS = 0
                BEGIN
                                                                EXEC msdb.dbo.sp_stop_job   @job_name = @NAME                                                                                                         WAITFOR DELAY '00:00:03'
                                                                exec msdb.dbo.sp_update_job  @job_name = @NAME, @enabled = 0
                                                               
                                   FETCH NEXT FROM Purge INTO @Name ;
                END
  CLOSE Purge ;
  DEALLOCATE Purge ;

Also you can use SSMS to connect to pull subscribers and current distributor. Later open Job Activity Monitor and disable related replication jobs.

8.Backup distribution db

We always need a backup.

--sqlcmd command to open connection to a current distributor:
:CONNECT $(CURRENT_DISTRIBUTOR)
GO
--sql  command to backup distribution db on a current distributor:
BACKUP DATABASE [$(DistributionDB)] TO  DISK = N'E:\Backup\$(DistributionDB)_FULL.bak' WITH NOFORMAT, INIT,  NAME = N'$(DistributionDB)-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP LOG [$(DistributionDB)] TO  DISK = N'E:\Backup\$(DistributionDB)_LOG.bak' WITH NOFORMAT, INIT,  NAME = N'$(DistributionDB)-Full Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

9.Restore distribution db on the new distributor instance:

Now we need to restore the old distribution database on the new instance so it can be used.

--sqlcmd command to open connection to a new distributor:
:CONNECT $(NewDistributor)
GO
--sql  command to restore distribution db on a new distributor instance:
USE [master]
GO
-- make sure file paths are correct on your distributor
-- set db to single user mode
ALTER DATABASE [$(DistributionDB)] SET single_user WITH ROLLBACK IMMEDIATE;
--restore distribution database on new distributor
RESTORE DATABASE [$(DistributionDB)] FROM  DISK = 'e:\backup\$(DistributionDB)_FULL.bak' WITH  FILE = 1,
   MOVE N'$(DistributionDB)' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\$(DistributionDB).MDF',
   MOVE N'$(DistributionDB)_log' TO N'L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\$(DistributionDB).LDF',   NOUNLOAD, REPLACE, STATS = 5, NORECOVERY
--  restore database log now
RESTORE LOG [$(DistributionDB)] FROM  DISK = 'e:\backup\$(DistributionDB)_LOG.bak' WITH  FILE = 1,
   MOVE N'$(DistributionDB)' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\$(DistributionDB).MDF',
   MOVE N'$(DistributionDB)_log' TO N'L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\$(DistributionDB).LDF',   NOUNLOAD, REPLACE, STATS = 5,  KEEP_REPLICATION
-- set db to multiuser access mode
ALTER  DATABASE [$(DistributionDB)] SET multi_user

10.Update MSpublisher_databases table with correct publisher id:

A last update that will get things ready to sync with the publisher.

--sqlcmd command to open connection to a new distributor:
  :CONNECT $(NewDistributor)
GO
 use [$(DistributionDB)]
 GO
-- sql commands to set publisher_id values
  declare @srvid smallint
  select @srvid = srvid from master..sysservers where upper(srvname) = upper(N'$(PUBLISHER)')
  update t
  set t.publisher_id =@srvid 
  FROM [$(DistributionDB)].[dbo].[MSpublisher_databases] t

11.Script out log reader/snapshot jobs

We need the scripts for the jobs from the current distributor and create these jobs in disabled state on a new distributor with updated server names  in all jobs steps.  

--sqlcmd command to open connection to a new distributor:
  :CONNECT $(NewDistributor)
GO
--create log reader job on msdb
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-LogReader' AND category_class=1)
BEGIN
  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-LogReader'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0)
   GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'$(Publisher)-$(Publisher_DB)-2',
                                @enabled=1,
                                @notify_level_eventlog=0,
                                @notify_level_email=0,
                                @notify_level_netsend=0,
                                @notify_level_page=0,
                                @delete_level=0,
                                @description=N'No description available.',
                                @category_name=N'REPL-LogReader',
                                @owner_login_name=N'distributor_admin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
  GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log Reader Agent startup message.',
                                @step_id=1,
                                @cmdexec_success_code=0,
                                @on_success_action=3,
                                @on_success_step_id=0,
                                @on_fail_action=3,
                                @on_fail_step_id=0,
                                @retry_attempts=0,
                                @retry_interval=0,
                                @os_run_priority=0, @subsystem=N'TSQL',
                                @command=N'sp_MSadd_logreader_history @perfmon_increment = 0, @agent_id = 2, @runstatus = 1,
                                @comments = N''Starting agent.''',
                                @server=N'$(PUBLISHER)',
                                @database_name=N'$(DISTRIBUTIONDB)',
                                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
  GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run agent.',
                                @step_id=2,
                                @cmdexec_success_code=0,
                                @on_success_action=1,
                                @on_success_step_id=0,
                                @on_fail_action=3,
                                @on_fail_step_id=0,
                                @retry_attempts=2147483647,
                                @retry_interval=1,
                                @os_run_priority=0, @subsystem=N'LogReader',
                                @command=N'-Publisher [$(PUBLISHER)] -PublisherDB [$(Publisher_DB)] -Distributor [$(NewDistributor)] -DistributorSecurityMode 1 ',
                                @server=N'$(PUBLISHER)',
                                @database_name=N'$(DISTRIBUTIONDB)',
                                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
  GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Detect nonlogged agent shutdown.',
                                @step_id=3,
                                @cmdexec_success_code=0,
                                @on_success_action=2,
                                @on_success_step_id=0,
                                @on_fail_action=2,
                                @on_fail_step_id=0,
                                @retry_attempts=0,
                                @retry_interval=0,
                                @os_run_priority=0, @subsystem=N'TSQL',
                                @command=N'sp_MSdetect_nonlogged_shutdown @subsystem = ''LogReader'', @agent_id = 2',
                                @server=N'$(PUBLISHER)',
                                @database_name=N'$(DISTRIBUTIONDB)',
                                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
  GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
  GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Replication agent schedule.',
                                @enabled=1,
                                @freq_type=4,
                                @freq_interval=1,
                                @freq_subday_type=4,
                                @freq_subday_interval=1,
                                @freq_relative_interval=0,
                                @freq_recurrence_factor=0,
                                @active_start_date=20130405,
                                @active_end_date=99991231,
                                @active_start_time=160618,
                                @active_end_time=160617
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
  GOTO QuitWithRollback
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
  GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

12.Change publisher_id

We need to change the publisher_id in the restored distribution db

--sqlcmd command to open connection to a new distributor:
  :CONNECT $(NewDistributor)
GO
use [$(DistributionDB)]
GO
-- sql commands to set  publisher_id
  declare @srvid smallint
  select @srvid = srvid from master..sysservers where upper(srvname) = upper(N'$(PUBLISHER)')
  --set correct publisher_id
   update t
   set t. publisher_id  = @srvid
   from dbo.MSpublications  t

13.Change subscriber_id/publisher_id

We also need to change ids in the restored distribution db.

--sqlcmd command to open connection to a new distributor:
  :CONNECT $(NewDistributor)
  GO
  use [$(DistributionDB)]
  GO
-- sql commands to set correct subscriber_id and publisher_id values
  declare @srvid smallint
  select @srvid = srvid from master..sysservers where upper(srvname) = upper(N'$(PUBLISHER)')
  --set correct subscriber_id and publisher_id values
UPDATE t
 SET t.subscriber_id = ls.server_id,  publisher_id = @srvid
 from $(DistributionDB).dbo.MSdistribution_agents t
 left join [$(Current_Distributor)].master.sys.servers ss on ss.server_id = t.subscriber_id
 left join master.sys.servers ls on ls.name = ss.name
--set correct subscriber_id and publisher_id values
UPDATE t
 SET t.subscriber_id = ls.server_id , publisher_id = @srvid
 FROM [$(DistributionDB)].dbo.MSsubscriptions t
  left join [$(Current_Distributor)].master.sys.servers ss on ss.server_id = t.subscriber_id
  left join master.sys.servers ls on ls.name = ss.name
GO

14.Script out push subscription jobs

We need the jobs on the current distributor and create these jobs in disabled state on a new distributor with updated server names  in all jobs steps.

--sqlcmd command to open connection to a new distributor:
 :CONNECT $(NewDistributor)
  GO
Use msdb
GO
--create replication distribution agent jobs on the new subscriber:
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Distribution' AND category_class=1)
BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Distribution'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'$(Publisher)-$(Publisher_DB)-$(PublicationName)-$(Subscriber) -7',
                                @enabled=1,
                                @notify_level_eventlog=0,
                                @notify_level_email=0,
                                @notify_level_netsend=0,
                                @notify_level_page=0,
                                @delete_level=0,
                                @description=N'No description available.',
                                @category_name=N'REPL-Distribution',
                                @owner_login_name=N'distributor_admin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
   GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Distribution Agent startup message.',
                                @step_id=1,
                                @cmdexec_success_code=0,
                                @on_success_action=3,
                                @on_success_step_id=0,
                                @on_fail_action=3,
                                @on_fail_step_id=0,
                                @retry_attempts=0,
                                @retry_interval=0,
                                @os_run_priority=0, @subsystem=N'TSQL',
                                @command=N'sp_MSadd_distribution_history @perfmon_increment = 0, @agent_id = 7, @runstatus = 1, 
                    @comments = N''Starting agent.''',
                                @server=N'$(NewDistributor)',
                                @database_name=N'$(DistributionDB)',
                                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
   GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run agent.',
                                @step_id=2,
                                @cmdexec_success_code=0,
                                @on_success_action=1,
                                @on_success_step_id=0,
                                @on_fail_action=3,
                                @on_fail_step_id=0,
                                @retry_attempts=2147483647,
                                @retry_interval=1,
                                @os_run_priority=0, @subsystem=N'Distribution',
                                @command=N'-Subscriber [$(SUBSCRIBER)] -SubscriberDB [$(SUBSCRIPTION_DB)] -Publisher [$(PUBLISHER)] -Distributor [$(NewDistributor)] -DistributorSecurityMode 1 -Publication [$(PublicationName)] -PublisherDB [$(Publisher_DB)]   ',
                                @server=N'$(NewDistributor)',
                                @database_name=N'$(DISTRIBUTIONDB)',
                                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
   GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Detect nonlogged agent shutdown.',
                                @step_id=3,
                                @cmdexec_success_code=0,
                                @on_success_action=2,
                                @on_success_step_id=0,
                                @on_fail_action=2,
                                @on_fail_step_id=0,
                                @retry_attempts=0,
                                @retry_interval=0,
                                @os_run_priority=0, @subsystem=N'TSQL',
                                @command=N'sp_MSdetect_nonlogged_shutdown @subsystem = ''Distribution'', @agent_id = 7',
                                @server=N'$(NEWDISTRIBUTOR)',
                                @database_name=N'$(DISTRIBUTIONDB)',
                                @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
  GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
   GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Replication agent schedule.',
                                @enabled=1,
                                @freq_type=4,
                                @freq_interval=1,
                                @freq_subday_type=4,
                                @freq_subday_interval=1,
                                @freq_relative_interval=0,
                                @freq_recurrence_factor=0,
                                @active_start_date=20130405,
                                @active_end_date=99991231,
                                @active_start_time=160241,
                                @active_end_time=160240
                                --@schedule_uid=N'83abb125-f445-4e5b-bbed-d4703949e2fc'
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
  GOTO QuitWithRollback
--EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
--IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

15.Alter pull subscribers replication jobs

We need to update the jobs to set  new distributor name.

--sqlcmd command to open connection to  a pull subscriber:
:CONNECT $(SUBSCRIBER)
GO
use [$(SUBSCRIPTION_DB)]
GO
--update replication jobs properties:
EXEC msdb.dbo.sp_update_jobstep @job_id=N'9f743215-ee8a-4f5e-85e5-dbbac4a2d329', @step_id=1 ,
 @command=N'-Publisher $(Publisher) -PublisherDB [$(Publisher_DB)] -Publication [$(PublicationName)] -Distributor [$(NewDistributor)] -SubscriptionType 1 -Subscriber [$(Subscriber)] -SubscriberSecurityMode 1 -SubscriberDB [$(SUBSCRIPTION_DB)]'
GO

16.Alter the distributor name on pull subscribers:

The following  sql code has to be executed in sqlcmd mode  with correct $(NewDistributor),$(PUBLISHER),$(PUBLISHER_DB), $(PublicationName) ,$(SUBSCRIBER),$(SUBSCRIPTION_DB), $(DistributionDB)  variables values:

--sqlcmd command to open connection to  a pull subscriber:
:CONNECT $(SUBSCRIBER)
GO
use [$(SUBSCRIPTION_DB)]
GO
---execute sp to update pull subscriptions properties:
EXEC sp_change_subscription_properties  @publisher =  '$(PUBLISHER)'
        ,  @publisher_db =  '$(PUBLISHER_DB)'
        ,  @publication =  '$(PublicationName)'
        ,  @property =  'distributor'
        ,  @value =  '$(NewDistributor)'
GO

17.Update log reader agent entries

We need to update values to ensure the replication monitor is working.

--sqlcmd command to open connection to a new distributor:
  :CONNECT $(NewDistributor)
  GO
  use [$(DistributionDB)]
  GO
-- sql command to set correct publisher_id values to make  replication monitor working on the new distributor.
  declare @srvid smallint
  select @srvid = srvid from master..sysservers where upper(srvname) = upper(N'$(PUBLISHER)')
     update  t
                  set t.publisher_id = @srvid
     FROM [$(DistributionDB)].[dbo].[MSlogreader_agents] t
--set correct job_id and job_step_uid values
  update t
  set t.job_id = sj.job_id, t.job_step_uid = sjs.step_uid
  from dbo.MSlogreader_agents t
  left join dbo.MSreplication_monitordata   sm on sm.agent_name = t.name
  left join msdb.dbo.sysjobs sj  on sj.name = t.name
  left join msdb.dbo.sysjobsteps sjs on sj.job_id = sjs.job_id and sjs.step_id = 2

18. Change distributor name

We need to change the name on the publisher.

--sqlcmd command to open connection to a publisher:
  :CONNECT $(PUBLISHER)
  GO
  use [master]
  GO
-- sql commands to assign  a new distributor name on a publisher
DECLARE @distributor sysname
select @distributor = upper('$(NewDistributor)')
EXECUTE sys.sp_setnetname 'repl_distributor', @distributor

19.Enable subscriber jobs

We need to enable the jobs one-by-one and resolve permission issues if they exist.

--sqlcmd command to open connection to a current distributor:
:CONNECT $(SUBSCRIBER)
GO
--sql batch to start and enable replication sql jobs on subscriber:
DECLARE @JobName sysname
DECLARE @CMD varchar(1000)
DECLARE @Name VARCHAR(200) ;        
DECLARE Purge CURSOR
 FOR SELECT j.name
 FROM [msdb].[dbo].[sysjobs] j
where j.name like '$(PUBLISHER)%'  -- filter out  and retrieve only replication jobs

OPEN Purge ;
FETCH NEXT FROM Purge INTO @Name ;
WHILE @@FETCH_STATUS = 0   --loop
 BEGIN
   --enable replication sql job
   exec msdb.dbo.sp_update_job  @job_name = @NAME, @enabled = 1
 
   --start replication sql job
   EXEC msdb.dbo.sp_start_job   @job_name = @NAME                                        
   WAITFOR DELAY '00:00:59'
   FETCH NEXT FROM Purge INTO @Name ;
 END
CLOSE Purge ;
DEALLOCATE Purge ;

20.Enable Log-reader agent jobs

We now need to enable these jobs one-by-one and resolve any permission issues.

--sqlcmd command to open connection to a new distributor:
:CONNECT $(NewDistributor)
GO
use msdb
go
DECLARE @JobName sysname
SET @JobName = '$(LogReaderJobName)'  – find out job name on distributor
---enable log-reader agent jobs
exec msdb.dbo.sp_update_job  @job_name = @JobName, @enabled = 1
GO

21. Add distribution db to backup schedule.

Now you need to add the distribution db to the backup routine and do regular db backups.

22. If no errors exist then delete disabled replication jobs on the old distributor.

Connect to the old distributor via SSMS, open Job Activity Monitor and delete the disabled replication jobs.

23. Drop the old publisher

We need to drop the publisher on the old distributor server.

--sqlcmd command to open connection to the distributor:
:CONNECT $(OLD_DISTRIBUTOR)
go
use master
go
--sql command to  remove publisher info on the old distributor:
Exec sp_dropdistpublisher  @publisher =  '$(PUBLISHER)'
     ,  @no_checks = 1
     ,  @ignore_distributor = 0

24. Open replication monitor and verify transactional replication activity.

Replication monitor view snapshot after the change completion:

There should be no errors or red alerts shown in replication monitor if all above steps are done properly with correct variables values.  Replication jobs can be in running or not running states.

Conclusion

This custom solution allows sql dbas quickly move distribution db to a new sql server instance without losing data and having downtime in production environment. Moreover, solution can be quickly reverted back if something goes wrong during the change and implementer has not started step 20. Bear in mind that if log-reader agents are enabled and new transactions have been processed on the new distributor then correct way to rollback would be to drop and recreate replication objects and do data synchronization afterwards.

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.5 (6)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.5 (6)

You rated this post out of 5. Change rating

Related content