patternsqlMinor
what replication option should I choose?
Viewed 0 times
whatchoosereplicationoptionshould
Problem
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:
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!
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!
Solution
As some of your tables don't have a primary key, and I assume they don't have a unique index on them either, you are pretty much hosed. Anything which will be real time (transactional, merge, or peer to peer) will require either a primary key or that replication put it's own column in place called a rowguid (basically it's own guid column).
Since your schema can't be changed there aren't going to be any replication options which satisfy your requirements.
Since your schema can't be changed there aren't going to be any replication options which satisfy your requirements.
Context
StackExchange Database Administrators Q#20476, answer score: 4
Revisions (0)
No revisions yet.