Implementing a Distributed Database

[abcfantasy]

Hello,

I need to implement a distributed database and I have been trying to look up information about this but can't seem to find the right information. All I wish is that someone guides me to articles or books that can help me on this.

What I wish is to implement this database (using MS SQL Server), that will be geographically distributed in different servers. I need guides to set up the servers and the databases and apply partitioning, fragmentation and data replication.

Any help will be much appreciated.
Thanks.

 

[mrdenny]

Sounds like Federated Databases is what you are looking for.

What's the end result that you are looking for?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[abcfantasy]

Thanks for the reply.

We're planning an application that will be used world-wide, by numerous users, so obviously it's not feasible to use one server. A possibility is to use a database server for each state/region. Most information in each database will only concern that region they are physically located in. We want to handle user requests quickly and efficiently. Typically, user requests only require information that concerns their area (i.e. information found in the database found there), but some times they may require information from other regions, therefore there must be communication through the different databases (along with synchronization of data and so on).

I'll look into federation databases. Thanks.

|| ABC

 

[abcfantasy]

After reading a little about federated databases, I'm not sure it's that what we're looking for. The following quotes is what turned me away from them:

"A database federation is not a high availability solution."

"If any server in the federation is taken offline, the entire database system will become unavailable."

We want to achieve:
- Transparency (Users just work on a single web portal, they won't know on which database they are working on).
- High Availability (If one server fails, the system still should be able to carry on working (at least most of it)).
- Efficiency (Even with many users worldwide perfoming requests and transations, the response should be quick)

|| ABC

 

[SQLWilts]

How about replication? Will all of the servers be permanently connected?

Look up transactional replication - loads about that out there

HTH

 

[mrdenny]

Based on what you have described a federated database is overkill.

Federated databases are used to scale out systems which host trillions of records.

Placing records on different servers in different states will only save milliseconds of time as data transfer across the internet is extremely quick. 99% of speed issues across the internet are because of slow network access time between the end user and the internet.

Based on the information you have provided I would recommend a single highly available solution with a web farm to handle user requests.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog