Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Steps to Rename a Subscriber Database for SQL Server Transactional Replication


By:   |   Last Updated: 2012-03-05   |   Comments (7)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | More > Replication

Problem

I have transactional replication configured in production. The business team has a requirement to rename the subscription database. Is it possible to rename the subscription database and ensure that transactional replication will continue to function as before. If so, how could we achieve this?

Solution

The Test

Let's first try renaming a subscription database in our test environment. This tip assumes transactional replication is already configured.

We can use this script to rename a subscription database.

USE master;
ALTER DATABASE Current_subscription_database_name
Modify Name = New_subscription_database_name;

Renaming the subscription database is quite straightforward using the above script. The rename will occur successfully and you won't encounter any error messages or warnings if your distribution agent is not running and if there are no other connections to this database.

However, once you start the distribution agent job you will encounter the below error message.  To view this screen using SQL Server Management Studio (SSMS), navigate to Replication and right click and select 'Launch Replication Monitor'. After this, go to your listed server and expand it. Click on your publication and on the right side pane under the 'All Subscriptions' tab, go to your subscription and double click on it. Once done, click on 'Distributor to Subscriber History' tab to view this screen.

Replication status as viewed on Replication Monitor

In this example, I had renamed the subscription database to REP_Subscriber from REP_S1.  In the error message, you will notice that the distribution agent job is failing because it is trying to connect using the old database name.

The process could not access database 'REP_S1' on server 'P'. (Source: MSSQL_REPL. Error Number: MSSQL_REPL20052)

Cannot open database "REP_S1" requested by the login. The login failed. (Source: MSSQLServer, Error Number: 4060)

You can also check the distribution agent job for more details. Using the 'Job Activity Monitor' in SSMS, we could go to the corresponding distribution agent job and view the second step of this job which is 'Run Agent' as shown below.

Details of Run Agent Step in Distribution Agent job

From this screenshot, it is evident that the distribution agent job is still trying to connect to the subscription database using its old name. Also, you can still find details of the old subscription database in replication system tables like syssubscriptions (in publication database), MSsubscriptions (in distribution database) and MSdistribution_agents (in distribution database).

To fix this issue, we will cover two options for renaming the subscription database, so replication can still function.


Option 1 - Rename the subscription database, create a new subscription and delete the old subscription

From the above, it is evident that the distribution agent job will fail if we just change the subscription database name. So to rename the subscription database, we will need to follow the below steps.  This may seem like unnecessary work, but it is definitely the safest option.

1) Rename subscription database. (you could use the sample script above or use SSMS)

2) Create a new subscription for the renamed database and follow the steps in the wizard to setup replication.

Creating a new subscription

3) Delete the original subscription which referred to the old database name as there is no point in maintaining this subscription any longer.

Deleting the subscription which refers old subsriber DB  

Option 2 - Modifying Replication System Tables

CAVEAT: It is best not to change the replication system tables, but here is an option that works but be aware that this could cause issues if the user makes a mistake. If you are unsure or uncomfortable in changing system tables then you should follow Option 1.

Follow these steps:

1) Stop the distribution agent job for the subscription database. Also ensure there are no other connections to the subscription database otherwise you may get this error message while attempting to rename the database: "The database could not be exclusively locked to perform the operation"

2) Rename the subscription database

3) As discussed earlier, the old subscription database name is stored in certain replication system tables which require SQL updates. You would need to run these scripts:

Update syssubscriptions replication table in Publication database, use sample script

use publication_db_name
update syssubscriptions
set dest_db = 'New_Subscription_db_name' where dest_db = 'Old_Subscription_db_name'

Update MSsubscriptions replication table in distribution database, use sample script

use distribution
update MSsubscriptions
set subscriber_db = 'New_Subscription_db_name' where subscriber_db = 'Old_Subscription_db_name'

Update MSdistribution_agents replication table in distribution database, use sample script

use distribution
update MSdistribution_agents
set subscriber_db = 'New_Subscription_db_name' where subscriber_db = 'Old_Subscription_db_name'

4) Go to the distribution agent job for this subscription using the job activity monitor and edit step 2 of the job to modify the SubscriberDB parameter to refer to the new subscription database. (Refer to the second screenshot in this tip).

5) Start the distribution agent job again

6) Insert some values into the replicated tables in the publication database and verify they are available in the subscription database.

7) Monitor replication status through the replication monitor


Following these steps will ensure that your transactional replication configuration continues to function after the subscription database name has been changed. The above steps were performed using SQL Server 2008 R2, but this should work for other versions of SQL Server as well.

Next Steps


Last Updated: 2012-03-05


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, August 30, 2017 - 4:48:21 AM - Csaba Molnar Back To Top

 

Hi Mohammed,

Thnx Option 2, it worked. I've saved a lot of time!

Csaba


Wednesday, August 19, 2015 - 2:00:12 PM - Jerry Cohen Back To Top

I tried Option 2 and it worked great.  Thanks!

 

-jerrydba

 


Sunday, October 13, 2013 - 9:22:42 AM - Dennis Münninghoff Back To Top

Hi Mohammed. Thanks for this article. I was able to rename a subscriber database via method 2 without any problems.

 


Thursday, March 15, 2012 - 3:42:23 PM - Bhushan Back To Top

This is very nice article. I am wondering which system tables need to be updated if we change the server name instead of database name. I appreciate any feedback.

 

Bhushan


Wednesday, March 14, 2012 - 2:42:40 PM - Ale Back To Top

Tnks for the tip, its good to know.


Wednesday, March 14, 2012 - 10:13:48 AM - RM Back To Top

Thx for the tips.


Wednesday, March 14, 2012 - 5:05:54 AM - senthil Back To Top

Nice Article..


Learn more about SQL Server tools