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!

  • Will users be editing the same data or different data at the two locations? – Jon Seigel Jul 6 '12 at 14:19
  • The receiver in log shipping is not online, re: active on both ends requirement. Merge replication would allow you to update independently @ remote sites. Schema changes will be frequent? Why? – Eric Higgins Jul 6 '12 at 20:55
  • Yes, users will (or can) edit data from either end. – tpcolson Jul 7 '12 at 1:40
  • That is the nature of the application: new data is developed and captured in new tables, will have different columns, triggers, and SP's than other tables. – tpcolson Jul 7 '12 at 1:41

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.


You will have to stop replication, make the schema changes and then reestablish replication. Also, since you are running SQL express clients, you will have to run the publisher at the main server instead of the clients. This means you will have to have your publisher push the entire database to the client. You mentioned that there will be updates at both ends. How many updates at a time? If you use any identity column types in your DB, merge replication will manage those for you but it will increase by default 2,000 to the values of your identity. Will you be replicating tables, or everything [stored procecedures,view and UDF's]? It sounds like merge replication would be best since your WAN connection isn't always the best.

I have about 13 databases that I use merge replication in pretty much the same kind of world [also have 95 other databases that I use Merge Replication but those are able to be the publisher at the remote location]. We use SQL Express on the remote locations but the publisher is located here at Corp. Yes, it is a bit messy and it does mess with your identity data types but it does work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.