I have a 4 node cluster, 1 subscriber and 3 publishers, all running SQL Server 2008 R2 Enterprise. The intention is that if the subscriber goes down, we can use one of the publishers to quickly build up its replacement. Our testing reveals a problem though: the subcriber databases all have Not For Replication set to Yes on the identity columns so that they can maintain the identity set in the subscriber. This causes a problem when they become subscribers because now we don't have identity insert functionality: we get a primary key error. Any way to "promote" a subscriber to publisher?

  • Apparent DBCC CHECKIDENT offers some ability to reseed the identity column. – Stefan Mai Mar 3 '11 at 18:22
  • Are you looking to have this subscriber promted to publisher automatic when there is a fail over detected? – Ian Chamberland Mar 5 '11 at 0:00
  • No, I'd just like to fix the identity problem. – Stefan Mai Mar 5 '11 at 1:10

I would look at something like this: http://www.mssqltips.com/tip.asp?tip=1274

You could prepare scripts needed before hand and if your publisher goes down you can have all the scripts ready before hand to build up the replacement.

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.