Just looking for some feedback here, there are lots of replication options out there, and I'd like to try something that those more expert than I have found to be successful.
Here's my scenario:
- 1 SQL STD 2008 R2 SP2, few desktop clients, many web-clients edit and read;
- 1 SQL ENT 2008 R2 SP2, desktop clients only;
lots of SP's and triggers;
Many (but not all) tables have App-managed Primary key (not an identity!) and/or a GUID;
Many (but not all) tables that have a GUID, use that GUID in 1-n relationships with 1 or more tables; Some app-managed tables have neither a PK or a GUID;
The schema of the app-managed tables and sp's cannot be changed in order to satisfy a replication requirement (e.g remove @@rowcount or add not for replication contraint to a PK);
Schema changes will be frequent;
I'd like to try to achieve two-way replication, as updates can occur at either end. Redundancy (hot-swap, failover) is not an issue for us, as our business strategey isn't dependent upon 5-9's uptime, instead the ability to recover from failures, which we can do very well. Real-time replication may be very limited due to horrific WAN performance, is log shipping a good solution here? The goal is to make a DB available to clients in two physcial locations whom cannot oridinarily communicate otherwise. In addition, having updates lag a few hours or even a day is not a roadblock, as long as we can force a synch if we need a more recent one. Icing on the cake would be the ability to have SQL express clients participate in this topology as well, giving our mobile clients the ability to grab a copy of the DB when they can, and check in edits when they can (again).
I look forward to hearing how others have achieved this sort of environment. Thanks!