Sql-server – what replication option should I choose

sql serversql-server-2008

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!

Best Answer

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.