Offsite databases - setup recommendations

[Simon P Stevens]

We currently have a system with a central database. (SQL server 2000). Around the company we have several applications that read/write to the database. The company is distributed across several sites. Currently the database is at site A. There are two sites B and C that have leased lines to connect their networks to site A. From a computer point of view, it's all one big domain. Sites A, B and C are all in the same city (within about 15 miles of each other). We are about to expand our network to a location in Dubai (Site D). What is the best way to achieve the database connections for the application? Would I be best to look at still having one central database and everything connecting to that (Via something like VPN or whatever), or should I be considering having a cloned/replicated database for the Dubai offices that is somehow synced up after hours? Do I need to be looking at the reliability of the internet connection in Dubai? Can anyone with experience of this kind of thing offer any guidance? I'm a software developer, and we have IT/network admins who will be doing the actual networking, but I need to be thinking about where/how I want my databases set-up.

Simon

[J4amieC]

This is a bit of a can of worms... If I was sitting down to design the architecture for a distrubuted application like this from scratch, i'd be very tempted to expose the data as services (web/wcf/whatever) and have applications at each site use these services. However, it seems you're doing a straight database connection from each site to the central database - which is fine, but as you move further away from the database you have network latency and connectivity issues. Which is I think what you're asking about. In terms of a solution - your suggestion of replicated database for Dubai is one way of solving the problem. You could also accept that the connection from any site to the database may slow down/fail and make your applications resilient to this (they should already be!). Perhaps think about hosting the database at a highly reliable site, with a fast connection - such as one of the big hosted data centres.

[Simon P Stevens]

Thanks J4amieC, With regards to data services yes I kind of agree here, given a choice from scratch I think I'd probably go down the same route, unfortunately this is an existing system that has grown beyond it's original intentions (as is so common with these kind of things) and I don't think a re-architecture like this is really an option - although it does have a nicely separated DAL so the option is there. We are actually building another system in a distributed style like this for exactly these kind of reasons. Yes I am kind of asking about latency & connectivity etc. The application currently has an online and offline mode and falls back silently into offline mode if any database connection errors occur, and attempts to reconnect every 15 minutes, so the system is already fairly resilient to that kind of outage. I'm also asking if there is anything else I should be aware of with the above approach. Finally I'm interested if anyone has any experience with a replicated database. It's not something I've ever tried, and I'd have concerns that there would be problems, but as I understand it there are features like this that are transparently built into SQL server so I could just configure it and then carry on as if it was just a normal local database. I'd be concerned about how it would handle conflicts and locks.

Simon

[Johan Hakkesteegt]

Hi, Just thinking out loud, mind you, so for what it is worth: The main question is, what sort of data would you need at each site, at the same time, that must be in absolute real time sync? The different sites of your company have been established for a reason, and I suppose it is not because all the people just won't fit into a single building. So I am guessing each site has its own customers, or vendors, or specialized work crew, or warehouse, or something. That means that although each site will be organized with the same application(s), each will still mostly be working with unique data. In other words, each site will be creating its own orders, deliveries, invoices, or whatever other business objects, that are specifically related to that site. So adding site identifiers to all business objects in your database, should allow you to have each site run its own server/database/copy of the application. You can keep a central database at a single location that is updated every night (or whenever), through synchronization, or even simpler, using xml files. This central database could send certain common master data to each site's own database in turn, every night, hour, 15 minutes, etc. depending on requirements. Usually even things like warehouse stock levels needn't be more up to date then 5 minutes. This approach shouldn't need any kind of drastic measures, and might even offer greater flexibility for future development. Cheers, Johan

My advice is free, and you may get what you paid for.

[Simon P Stevens]

Thanks Johan, There's some good points in there. Like you say, the company is divided by sites for a reason, and yes large parts of the data is specifically for an individual site. I'm going to take a look at each table and decide how up to date it needs to be with respect to each site. Thanks for the suggestions.

Simon