Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DanaK
Starting Member
5 Posts |
 Posted - 2010-03-16 : 16:22:14
|
| We have a failover machine that does transactional replication. We have it set to preserve key constraints and default values. I noticed today that when we switched over to it several tables were using primary keys that were at one time deleted in the primary database.Any ideas what causes this or how to stop it? Thanks. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-16 : 16:34:14
|
| Ok, 1st thing is that you should NOT be doing writes on your subscriber(s). You're going to get duplicate key errors, besides the issue you're already encountering.Since you're using it as a failover, you're going to have to manage all of the identity fields manually as part of your failover script. Use DBCC CHECIDENT to reseed the columns.Also see these articleshttp://msdn.microsoft.com/en-us/library/ms152529.aspxhttp://msdn.microsoft.com/en-us/library/ms152543.aspxI would suggest that replication is not the best solution for high availability. It is an outstanding solution for scalability however. |
 |
|
|
DanaK
Starting Member
5 Posts |
Posted - 2010-03-18 : 11:20:22
|
| Thanks. We actually killed the replication before switching over to the failover machine. We are seeing key violation messages now and again on the backup. So using DBCC CHECIDENT should correct the key violation issues you think? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-18 : 13:29:34
|
| only temporarily if you're going to perform writes on the subscriber |
|
|
|
DanaK
Starting Member
5 Posts |
Posted - 2010-03-18 : 13:38:27
|
| Our plan tonight is to use Red Gate and sync the data back to the primary now that it's fixed, and then setup replication again to the subscriber. The machine hasn't been a subscriber since we failed over. We dropped replication before failing over. I'm just nervous that we're going to have key issues from now on. No data has been overwritten or lost, and the keys in general seem fine other than it re using some old ones a few times. |
|
|
|
|
|
|
replicated database re using deleted primary keys?