March 19, 2003 at 11:40 am
Q:
I have a transactional replication set up on our prod server and an anonymous pull subscription to it. I'm trying to use the db created by the subscription as our main db.
Here's what I've done:
I've turned off replication to avoid insertion of new rows from replication. I have all Identity columns set to NOT FOR REPLICATION.
My problem is this, when inserting a row, it complains that it violates the PK Constraint.
Only work around I found so far was to change from not for replication to "yes", save, then set it back to NOT for replication.
Is there an easier way to turn a replica into a useable production db?
thanks.
March 19, 2003 at 1:44 pm
What do you mean by 'as our main db.' Are you saying to use the db for the subscriber as a production server?
March 19, 2003 at 2:05 pm
basically, I want to have transactional replication set up and at a certain point in time, flip our applications to use the subscriber as the db. I got most of it working but the only thing I find a problem with is manually having to change my IDENTITY columns from NOT FOR REPL back to YES, save then switch back to NOT FOR REPL.
So, to answer the question, yes, I'm trying to use the subscriber db as production db.
March 20, 2003 at 10:59 am
It sounds like the current identity value needs correcting. Use dbcc checkident('<table>',NORESEED) to check if that is the problem. If it is, use dbcc checkident('<table>',RESEED) to reset it.
Jay Madren
Jay Madren
March 20, 2003 at 11:26 am
Jay,
I checked the identity values for the problematic tables and here's what I found:
For each table that had rows inserted into them by the replication, the current dentity value seemed to be stuck with it's initial value at snapshot time.
Doing a reseed corrected the issue but raised a question. Why wouldn't the current identity value be incremented? Is this information stored in a system table on the local database? You would think it would update this information through replication.
I suppose I could now write a script that reseeds all my tables prior to using it as a fully functional production db.
Thanks for the help!
March 20, 2003 at 12:05 pm
The identity values aren't incremented because of the NOT FOR REPLICATION option. The purpose of this option is to allow the replication agent to specify values for the identity column instead of being automatically assigned by the server (much like SET IDENTITY_INSERT). But it also prevents the current identity value from being altered, which is for when identity ranges are used with updateable subscribers.
If you reset the identity seeds after the snapshot and before any transactions are processed, then it would probably be incremented like you expect. But I've found that it doesn't always work. I mostly use Merge replication and kept finding that occasionally the current identity got hosed. So I made a script like you're talking about that resets the seed on all tables after each replication run (replication is user driven in my case, not scheduled or automatic).
Jay Madren
Jay Madren
March 20, 2003 at 12:17 pm
Great help and information Jay!
I suppose it's safe to say I may have found my "warm stand-by" database solution. The whole purpose of this exercise was to have a replicated db with minimal latency that could be used as a backup to fall on in case of failure of the primary server.
And all that for free!
Hopefully there won't be any more subtleties with this.
Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply