3

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.

1

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) 
  • Thanks for a detailed answer. I have some clarifications though. The current setup is that the distributor db and subscriber db is in one sql server. The publisher is in different sql server. Now after i switch the publisher and the subscriber, the distributor db should remain and not switch server (in the new setup the publisher and distributor will now be in same sql server). This is because of the space limitation of the sever on the previous publisher. In your instruction the distributor is to be on the new publisher. Is it okay the dist db will remain on current server? – engrmigs Apr 26 '17 at 21:23
  • Yes it is ok, I meant to add distribution database as remote distributor if this is on a separate instance(same host), if not (on the same instance) you can skip that step. – SqlWorldWide Apr 27 '17 at 1:51
  • i know i already marked this as answer but I recently encountered error in the latter part. the publisher to distributor have no issue, but on monitoring the distributor to subscriber is always on pending status. I always receive this message 'Procedure or function sp_MSreplraiserror has too many arguments specified. (Source: MSSQLServer, Error number: 8144 ) Get help: help/8144' I have checked and did the steps on the attached KB article, but nothing happened and I still receive the same error. Appreciate your feedback. – engrmigs May 3 '17 at 13:24
  • Did you try running 'sp_scriptpublicationcustomprocs'? – SqlWorldWide May 3 '17 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.