We have a client that's geographically distributed in remote communities, with somewhat unreliable network/internet connectivity between each of the physical locations. There is a single central location, and 52 satellite locations.

Normally, our application is deployed as a central solution where there is only a single database, and all locations connect to the one database. The data itself is partitioned by a location column in most of the tables (some data is centralized), and the application operates such that touching data at one physical location does not touch data at another physical location, including the centralized data (at least, we're pretty sure it doesn't -- if it does, it's likely a bug). Our client would like to do centralized reporting (which we normally support), and synchronization of several centralized configuration tables across the enterprise (same, because there's normally only one copy of the tables).

Every location has its own data center, and is licenced for SQL Server 2008 R2 Enterprise.

The goal is to deploy our application such that broken network/internet connectivity must not prevent local read/write operations, as our application is mission-critical.

My first thought was to use a giant peer-to-peer topology that replicates every object in the database. I haven't been able to find any documentation or references of any installation that's scaled out this much using this method, so I have no idea if it's technically feasible, and if so, what hardware is going to be required. Would this work with a centralized distribution database (for ease of management)? Is this even a good idea at all? How are database schema changes to be deployed? Does index maintenance generate a huge amount of network overhead?

There's also the option of creating a roll-our-own solution, which I figured would involve log shipping separate copies of the database (each non-central location would only contain its partitioned portion of the data) to the central location, and then using a merging tool (which we already have) to create a reporting database. Configuration table changes would be pushed out by some other mechanism, possibly merge replication, possibly a homebrew solution.

I considered and tested using merge replication on the entire database, but we require quite a lot of database and application changes/redesign/etc. to make this work correctly, so I've ruled that out already due to time constraints. (Thoughts on this?)

What I'm looking for here is some guidance from people with more experience. Obviously, planning for this is critical. Am I on the right path? Are there other options? (URLT?)

2 Answers 2


For a meer 52 client sites replication will do just fine. Consider your local copy as the 'master' site and have the application work only on the local copy, always. Use replication to aggregate the data to the central repository for aggregated reporting. Index maintenance operations normally do not generate replicaiton traffic. Manage schema changes through application deployment migrations, ie. have upgrade scripts for each released version.

As the number of sites increases, managing replication becomes harder and harder. Also licensing would push toward deploying Express on periphery. And SQL Server upgrade is an absolute pain when replication is involved, as the order of upgrading the components (publisher, distributor, subscriber) is critical is but difficult to coordinate with many sites. Deployments that I know of with +1500 sites use customized data movement based on Service Broker. See Using Service Broker instead of Replication for an example. I've even seen designes that use Service Broker to push out to periphery new bits (application changes) which in turn deploy, when activated, migrations and schema changes, making the entire deployment operated from the center.

  • Thanks for your answer. We already have upgrade/schema change scripts; my question is about the best way to deploy them in this kind of topology (see my comments on Aaron's answer). What I don't understand is the recommendation to deploy Express -- what I've suggested is peer-to-peer, which is Enterprise only? (Note: the number of nodes is very unlikely to increase from the initial set.) Are you proposing an alternative topology, and if so, could you please expand your answer to state it more clearly?
    – Jon Seigel
    Commented Jun 20, 2012 at 13:06
  • 1
    I mentioned Express in the light of the comment about sites doing this for +thousand nodes, where EE licensing would be prohibitive. Doing something like custom SSB driven replacement for replication means you take control over every part of the deployment, and the code would handle all/any topology (peer-to-peer, flat hierarchical, deep tree). But it is new, custom code, not an out-of-the-box solution like built-in replication. Commented Jun 20, 2012 at 13:34
  • Okay, right. As I mention in the comments on Aaron's answer, the number of nodes is not likely to change (and if it does, it will be +1 or -1 at a time).
    – Jon Seigel
    Commented Jun 20, 2012 at 13:41

Why is there one database? Seems like it would make more sense to have each location keep a copy of the database schema locally for their own read/write operations and their own slice of the data, and the reporting portion of the data could be replicated centrally. Is there any reason site A would have to make its non-reporting data available to site B, or vice-versa? The reporting data could be distributed back to the central location using a variety of techniques, including roll-your-own (I have some experience there if you want further info). Or if the reporting data is most of the data size, you could just use log shipping or copy_only backups, keeping a copy of the whole database, with central reporting using views or other techniques against multiple databases when they're restored.

Schema changes are easy. I dealt with this quite a bit at my old job, where we had ~500 databases with identical schema. The key to deploying changes is to make sure the changes are backward compatible. If you can build a script that doesn't break one database, you can write a loop that deploys those changes to n databases / servers / environments. We used Red Gate to build our deployment scripts based on comparisons between beta and production, and then a home-grown sp_msforeachdb on each instance (because you can't trust the built-in one - see here and here). With a good mix of source control as well.

In this case I'm not sure how index maintenance would cause network overhead. Index maintenance should only be done on the source and not "replayed" if it can be avoided.

  • "Is there any reason site A would have to make its non-reporting data available to site B, or vice-versa?" -- yes, the geographical distribution dictates this need (the closer the satellite locations, the more data sharing there is). For this deployment, if we go with a roll-our-own, the plan is to lock out those features (which will rarely be used). This deployment is the exception; the application was not originally designed with this topology in mind. Using a central single database works great for all our other clients; however, they don't have the issue of spotty network connectivity.
    – Jon Seigel
    Commented Jun 20, 2012 at 13:01
  • In terms of schema changes, in this kind of peer-to-peer topology as I propose, would you recommend replicating schema changes, then simply running a script on the central node? Quiescing all activity in the enterprise should be possible if necessary (it's mission-critical only for 9-5 operation, not 24/7).
    – Jon Seigel
    Commented Jun 20, 2012 at 13:01
  • Personally I would rather build a deployment module than rely on replication for schema changes. You never know when you're going to want to introduce schema changes slowly, to a few sites at a time (like a beta test), instead of all at once. Commented Jun 20, 2012 at 13:03
  • Hmm... we sometimes do selective deployment for customers in our hosting environment, but that's for a whole customer, not part of a customer. I'm really wary of dealing with complications due to different database versions between 53 nodes. To take a step back for a second, do you think that using peer-to-peer replication is the correct topology for this scenario? Or are you thinking more of just bidirectional transactional replication between each of the satellite sites and the central site? I think figuring that out first is the best place to start.
    – Jon Seigel
    Commented Jun 20, 2012 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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