How to change server name when replication is enabled.

How to change server name when replication is enabled.

The following steps will change the server name when replication is enabled:

1.       Backup all the replication stored procedures.

2.       Script the replication objects

3.       Disable distributors and publications

4.       Change the server name (This will require rebooting the server)

5.       Log into SQL Server preferably using the sa account. There is an issue with the Windows account after changing the name as shown   below:

 

6.       Re-create windows authentication.

 /****** Object:  Login [SERVER2Administrator]    Script Date: 04/06/2010 14:51:51 ******/
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N’SERVER2Administrator’)
DROP LOGIN [SERVER2Administrator]
GO

 /****** Object:  Login [SERVER3Administrator]    Script Date: 04/06/2010 14:51:51 ******/
CREATE LOGIN [SERVER3Administrator] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

7.       Run following stored procs

sp_dropserver SERVER2
GO
sp_addserver SERVER3, local
GO

8.       Restart SQL Server Service

9.       Verify name has changed correctly.

sp_helpserver
 Select @@SERVERNAME

10.   Enable Distributor

11.   Re-create the replication stored procedure scripts. If you don’t have these scripts, you can generate them by using Exec sp_scriptpublicationcustomprocs  ‘Publishername’

12.   Run the replication scripts. Marked lines are changed. First two are commented and third one is change to none from automatic. Servername was changed from old to new.

— Enabling the replication database

use master

exec sp_replicationdboption @dbname = N’ReplTest’, @optname = N’publish’, @value = N’true’

GO

–exec [ReplTest].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1

–GO

exec [ReplTest].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1

GO

— Adding the transactional publication

use [ReplTest]

exec sp_addpublication @publication = N’Pubs’, @description = N’Transactional publication of database ”ReplTest” from Publisher ”SERVER3”.’, @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’Pubs’, @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 = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

exec sp_grant_publication_access @publication = N’Pubs’, @login = N’sa’

GO

exec sp_grant_publication_access @publication = N’Pubs’, @login = N’NT AUTHORITYSYSTEM’

GO

exec sp_grant_publication_access @publication = N’Pubs’, @login = N’SERVER3Administrator’

GO

exec sp_grant_publication_access @publication = N’Pubs’, @login = N’NT SERVICESQLSERVERAGENT’

GO

exec sp_grant_publication_access @publication = N’Pubs’, @login = N’NT SERVICEMSSQLSERVER’

GO

exec sp_grant_publication_access @publication = N’Pubs’, @login = N’distributor_admin’

GO

— Adding the transactional articles

use [ReplTest]

exec sp_addarticle @publication = N’Pubs’, @article = N’Test’, @source_owner = N’dbo’, @source_object = N’Test’, @type = N’logbased’, @description = N”, @creation_script = N”, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509F, @identityrangemanagementoption = N’manual’, @destination_table = N’Test’, @destination_owner = N’dbo’, @status = 24, @vertical_partition = N’false’, @ins_cmd = N’CALL [dbo].[sp_MSins_dboTest]’, @del_cmd = N’CALL [dbo].[sp_MSdel_dboTest]’, @upd_cmd = N’SCALL [dbo].[sp_MSupd_dboTest]’

GO

— Adding the transactional subscriptions

use [ReplTest]

exec sp_addsubscription @publication = N’Pubs’, @subscriber = N’SERVER3′, @destination_db = N’Subs_ReplTest’, @subscription_type = N’Push’, @sync_type = N’none’, @article = N’all’, @update_mode = N’read only’, @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N’Pubs’, @subscriber = N’SERVER3′, @subscriber_db = N’Subs_ReplTest’, @job_login = null, @job_password = null, @subscriber_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 = 0, @dts_package_location = N’Distributor’

GO

Resources

 http://msdn.microsoft.com/en-us/library/ms143799.aspx

http://www.coderjournal.com/2008/02/how-to-change-instance-name-of-sql-server/

]]>