I'm setting up a bunch of servers that will be located in various locations around the world. All of them will have a local database server, and that will communicate back to the main database server. Updates can occur at any point, and any updates will need to be replicated out to all of the other servers. I can get servers with Windows 2003 Standard/SQL Server Standard, Windows 2003 Standard/MySQL 5, or Linux (Red Hat or CentOS)/MySQL 5. I know that SQL Server can be configured for multiple replication partners and is pretty reliable. I haven't done any replication with MySQL on Windows or Linux. Has anybody else done this? Are there any limits on the number of replication partners with MySQL? How is the performance? How is the reliability? Does it support multiple masters or is it a single master and multiple slaves?
I guess that if you can afford SQL Server go with it. It will be easier to configure. And SQL Server 2005 is with SP2 so it is patched enough. MCDBA
Depending on your budget, and your expected usage, there is a variety of configurations you can go with. What sort of load are you expecting on each node? Also, what is going to be accessing the independent databases, web applications, desktop applications, etc.?
Since it is primarily for web usage, I think that I would recommend a sort of wagon wheel setup. You would have a central master database or cluster (for reliability). The central master database replicates to each slave node. Any time you need to read from the database you read from the individual nodes, and any time you need to insert or update, you query the master database, which then replicates the changes to the nodes. This will be the easiest way to keep the data consistent across all of the nodes. The other option would be to run multiple master/slave databases, but this can be really messy especially if the databases are a long distance away, or when you start getting a bunch of them. The wagon wheel idea would be fairly easy to setup with either MSSQL or MySQL. If most of your web servers are *nix based, then I would go with MySQL on Linux servers. Otherwise if the budget supports it, and you don't need to connect with a lot of *nix servers, go with MSSQL. MSSQL will be significantly easier to setup, but the licensing is really expensive, and integrating a linux server with MSSQL is hardly worth the effort. The key in this situation is to make sure the central database is very reliable, as the entire system relies on it. However, even if it crashed, the other databases would still be fully functional for querying, or could be used to restore the master database. Anyway, this is the way I would do it.
That's an interesting idea... But I think that it won't work in this scenario. There will be a "master" database that will be centrally located in the US. There will be other databases scattered throughout the world, some on very slow links. The users who would be using these scattered databases will be local to the server it's running on, so the only time the slow link would be into play is when the user or database would have to go back to the master. The local users will be updating the local database, so I figured those databases would send their data back to the master. Updates could also occur at the master, so there would have to be two-way replication. One thing to note is that the data from the scattered databases wouldn't have to be sent back to the "master" in real time, that could occur on a schedule - once per day for example. Some - but not all of the data updated on the "master" may have to be sent to the other databases immediately. The web servers are going to be IIS, and in most cases the database server will run on the same machine as the web server. Since we're going the Microsoft route so far I'm kind of leaning towards going with MSSQL. Plus from what I've ready it's much easier to configure replication on SQL Server than it is on MySQL.
Since you are already using MS for everything else and Replication on SQL Server is so configurable and reliable I would go that route. I current manage over 200 SQL Servers that replicate every night in both directions. Stores replicate sales and corporate replicates changes to prices, products etc. All of it is scheduled through out the day depending on the links between the sites. I have a very easy job and little work to do. Just don't tell my boss.
Your secret is safe with me. And the few hundred million other people on the Internet who may see this thread. ;-) Thanks for the info.