Using NOT FOR REPLICATION
-
Hello all.
I've tried searching the forum for 'NOT FOR REPLICATION' but as one would expect in a forum dedicated to replication, there were many, many non-related records found.
I've read up on this NOT FOR REPLICATION option for IDENTITY field and table triggers and from what I can gather, it will suit my situation. I'm just checking to see if anybody has any real experience with this and if there is any 'gotchas' to watch out for.
Here is the situation at my firm. We're trying to set up a disaster recovery plan. We have two offices in separate countries. Office A is the main office with the live databases and is much more likely to suffer an outage due to its location. Office B is the support office, where we currently replicate our live databases to.
The idea is that if Office A goes offline, we want our users (working remotely) to use the databases in Office B, hopefully within the hour.
Our initial tests revealed that we were missing some IDENTITY attributes and table triggers in the replicated version of the databases. Some of my IT colleagues originally told me these couldn't be in the replicated copy because they would cause problems when the replicated data comes over (e.g. any trigger would fire). I've countered with the NOT FOR REPLICATION option and it seems have been accepted as the way to go.
The only skepticism left my with fellow IT guys is whether or not the replicated copy of the database could be used as a live database without any further tweaks. They could not see how SQL Server would be able to distinquish between a replication agent updating the data in the replicated copy or another user or process.
I'm assuming we could run with the replicated copy without any further tweaks (i.e. any non-replication agent updating the data would cause triggers to fire and IDENTITY fields to be handled correctly).
Any comments are most appreciated.
- Mike
-
Yes that's how it works. We use NOT FOR REPLICATION on our identity columns, triggers, and constraints. The replication agent is integrated with SQL Server so it knows whether to fire the triggers etc. As long as you set up the identity ranges to be compatible you shouldn't have a problem. You will probably want to test it on a test database first, if it will make everyone comfortable.
Dylan Peters
SQL Server DBA -
Thanks Dylan.
I'm not sure what you meant by setting up the identity ranges to be compatible but I think I might.
It likely won't apply to us, however, because if we do need to use the replicated copy in the event of a disaster, when things are back up in running in office A, we would just do a backup in office B and subsequent restore in office A.
- Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply