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.