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?
Asked
Active
Viewed 197 times
0
Stefan Mai
- 322
- 2
- 9
-
Apparent DBCC CHECKIDENT offers some ability to reseed the identity column. – Stefan Mai Mar 03 '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 05 '11 at 00:00
-
No, I'd just like to fix the identity problem. – Stefan Mai Mar 05 '11 at 01:10
1 Answers
1
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.
Ian Chamberland
- 680
- 4
- 8