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/




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |