Sql-server – Convert existing subscriber Database to a publisher database. Current publisher will be subscriber

replicationsql servertransactional-replication

We have a transactional replication setup. The publisher is on a separate SQL Server and the distributor and the subscriber together on the other server. The replication was done using restore from backup from the publisher.

Now, I have to make my subscriber database as my main database, which will also be my publisher. And the server that have my current publisher database will now be my subscriber. I have several websites using the database said. I stopped the replication agent and tried to 'point' the websites in the subscriber.

However, insert commands are failing on my subscriber (even after stopping replication) with errors relating to primary keys which is also set as auto-increment. I checked the table schema in the subscriber database and the auto increment as well as the identity specification for that certain column is set to yes.

My question is what are the changes need to be done if I want my current subscriber to be a publisher (or main database for that matter)? Will i need to take backup again from my new publisher if I will make a subscriber from my old publisher database?

If it helps, our current database is now 100 GB in size. There is a table having 40m records and changing a schema, to drop and and recreate the whole table 'may' not be an ideal option for us now.

Best Answer

Best option for you will be to reestablish replication with reverse role but avoid a full snapshot and re-sync to minimize downtime. I will outline high level steps here and if you need further details let me know.

Once downtime start (confirm app servers are not making any new connection/writing to publishing articles):

  1. Continue running the distribution agent jobs till undelivered command count is zero.
  2. Stop the distribution agent job/jobs.
  3. Remove replication (publisher and subscriber only) but keep the distributor as is.

  4. Add remote distributor to new publisher if it is a separate instance (old subscriber).

    USE [Master]
GO
exec sp_adddistributor @distributor = N'instance name where distribution database reside', @password = 
N'xxxxxxxxx'
GO

  5. Reinstall replication with 'replication support only' when running sp_addsubscription. See details here.

  6. Run snapshot jobs. It will not resync your table schema+data, rather create system objects if any missing.

replication support only Provides automatic generation at the Subscriber of article custom stored procedures and triggers that support updating subscriptions, if appropriate. Assumes that the Subscriber already has the schema and initial data for published tables. When configuring a peer-to-peer transactional replication topology, ensure that the data at all nodes in the topology is identical. For more information, see Peer-to-Peer Transactional Replication.

Not supported for subscriptions to non-SQL Server publications.

You can use this query to find undelivered commands in distribution database.

SELECT ss.srvname AS publisher, 
       [name]     AS Agent, 
       publication, 
       a.publisher_db, 
       subscriber_db, 
       art.article, 
       art.destination_object, 
       art.source_object, 
       undelivcmdsindistdb, 
       delivcmdsindistdb, 
       s.article_id, 
       agent_id, 
       a.publisher_id, 
       subscriber_id 
FROM   distribution.dbo.msdistribution_status AS s WITH (nolock) 
       FULL OUTER JOIN distribution.dbo.msdistribution_agents AS a WITH (nolock) 
                    ON s.agent_id = a.id 
       JOIN master.dbo.sysservers AS ss WITH (nolock) 
         ON a.publisher_id = ss.srvid 
       JOIN distribution.dbo.msarticles AS art WITH (nolock) 
         ON s.article_id = art.article_id 
WHERE  subscriber_db <> 'virtual' 
       AND subscriber_db = 'SubscriberDBName' 
ORDER  BY undelivcmdsindistdb DESC 
COMPUTE sum(undelivcmdsindistdb)
Related Question