“The Only Thing That Is Constant Is Change”

How to rename the subscriber’s database name in the transactional replication without breaking replication.

Posted by database-wiki on April 11, 2016

Create a publication and a subscription bring sql server in single user mode – use
sp_renamedb and changed the subscriber database to new value.
After that start the distribution agent it will fail with an error:-
General network error. Check your network documentation.
(Source: SUMEETSEC (Data source); Error number: 11)
Communication link failure
(Source: ODBC SQL Server Driver (ODBC); Error number: 08S01)

– Stop distribution and log reader agent.
– Bring SQL server in subscriber’s end in single user mode and rename the
subscriber database to a new value using sp_renamedb
– Go to syssubcription table in publisher database and changed the dest_db name to
new value.
– Goto distribution database inside mssubscription table and change
‘subscriber_db’ name to new value.
– Go to distribution database inside msdistribuition_agent table and change
‘subscriber_db’ name to a new value.
– Finally, go to distribution agent right click and properties (go to agent
properties, steps tab and the run agent click on edit ) change subscriber_db name
to new value.
– Restart the agent.
– Agent will start running with new database name.

Note: changing the system tables is not recommended\supported. Use these steps if
customer doesn’t agree to drop the subscription and recreate it back.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: