By: Mohammed Moinudheen | Comments (7) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | > 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.
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.
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.
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.
3) Delete the original subscription which referred to the old database name as there is no point in maintaining this subscription any longer.
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
- Get familiar with replication concepts and the related system tables and procedures
- Try testing this tip on a development server
- Refer to this tip which describes the options that are available to reinitialize subscriptions in SQL Server replication
- Refer to other related replication tips
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
