Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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
up vote 1 down vote accepted

I would look at something like this:

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.

share|improve this answer
This is what I'm looking for, thanks! – Stefan Mai Mar 10 '11 at 0:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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