Server mirroring and load balancing

[justsomeguy]

I'm looking for people who have implemented or seen some type of mirroring environment with servers. We need to set up a mirrored system here, and most of it seems pretty straight-forward except for a couple points. We can get a 1U rack device from a place like Barracuda to take care of load balancing, so we can set up several servers behind the load balancer and it will make sure that those servers are each receiving the same amount of traffic. The main problem I'm thinking about is maintaining data consistency. For files that will be fairly easy, if I need to upload a file then I just connect to each of the servers and upload the same files to the same locations, and when a request comes in for that file no matter which server the request gets sent to it should find the same thing. It would be ideal if the servers showed up as a single logical server where we could connect to the one logical server and upload the file and the file will be automatically sent to each individual physical server, similar to RAID mirroring. But even if we have to upload the one file to several servers that's not the end of the world. The problem is databases. I'm not sure how to make sure that every database server has the same information on it. One of the issues is that we will need to replicate both MS SQL Server databases as well as MySQL databases. The main thing is that when anyone updates any of the databases, the same updates need to be applied to every database as soon as possible. Any of the servers could accept an incoming request to update the database, so the server would need to both update its own local database and also send the updates to the other servers so that the databases on each server are identical.Has anyone seen any environments that do this, or has anyone actually set one up themselves?

[aspnetguy]

for the ms sql take a look at redgate http://www.red-gate.com/, they have great products to sync structure and data of databases on different servers. I am not sure if it can be automated though (maybe run every 15 minutes).

[Reg Edit]

I haven't come across a "one-hit" deployment; typically the deployment process should be fully scripted anyway (and hence testable and repeatable) so running the script against each box generally isn't too painful.I assume you are load-balancing a number of application server boxes and maintaining a (probably different) number of separate database server boxes. SQL Server has built-in support for such things as clustering, mirroring and replication; I'd look at these first of all to see if any of the built-in stuff meets your needs. MySQL... not used yet, but planning to start looking at it soon as I'm sure there's some good stuff there and I'd like to know more about it. Thinking of putting WAMP on a virtual machine soon to have a look at all that.

[boen_robot]

You could have your application update a single SQL server. From that machine on, set a cron job that will check for modifications every few minutes, and update the rest of the databases, so that they can be queried.Note that this is just a guess. I haven't implemented any such system. But if it works, a similar thing could be done for files as well.

[AElliott]

For MySQL I suppose you would be looking at clusters? I've not used them, but it seems close to what you're describing.

[jesh]

We currently have two web servers behind a load balancer and a single MS SQL server. All of the data requests that come from each of the web servers is simply handled by the single SQL server. For files, we built a Windows service that operates as a file sync'er. Any time we save or delete a file in the file system, we also write a log to a directory that the sync'er looks at every second or so. It finds a log and then propagates it across the cluster (which is currently two machines).We have also implemented a caching mechanism that stores recent data and is accessible from both web servers. In each of the data objects, any method that retrieves data from the database first checks to see if there is a version of that data currently stored in the cache. This cuts down traffic to the SQL server tremendously. The SQL server is usually running at around 6% to 8% processor load - it peaks at about 25%, but only if we're VPN'd into it and running an extensive query.We're also in the process of expanding the farm to include two additional web servers and this has made us rethink the file sync'ing solution that we're using. Sync'ing files between two servers is pretty trivial, sync'ing between three, four, or more becomes a lot more complicated because of server downtimes and having to keep track of which files have been sync'ed to which computers. So, we're considering using one of the two new servers as a web server and using the other as a single resource server from where all the web servers retrieve any images, pdfs, zips, etc. that have been uploaded by our users.In short, in our particular situation, because of the caching mechanism that is in place, multiple web servers are being adequately served by the single database sever.

[justsomeguy]

Thanks everyone for the replies. I've looked into what SQL Server offers out of the box, and I'm not sure it's specifically what I'm looking for. Unfortunately, I might have to change the network topology to adjust to what SQL Server allows. The mirroring in SQL Server is set up where you have one primary database that synchronizes other databases, or if you use replication then you have one publisher and several subscribers. We want each server to be the same though, so no matter which database server receives the update, we want that to be propagated to the other servers. Setting up mirroring or replication like SQL Server allows wouldn't help us if the non-primary database server is the one that received the update, that update would be overwritten when the database gets synced with the master, or at the least that update would stay on the secondary server and never make it to the primary server.The point of all of this is so that we can lose any server and maintain our level of service to our customers. So it's something like what SQL Server allows, where we would set up a mirroring system where the primary database would update the secondary databases, and that would work if the secondary server was offline unless the primary server failed, and then the secondary server came up and started getting requests. But if we're going to have a second server sitting there it doesn't make sense to leave that one offline until the primary fails, it makes more sense to put it to work and have both servers responding to any request. Right now our web and database servers are the same thing, so it might make the most sense to change that to have 2 dedicated web servers that are being load balanced where either one could fail, and each of them connect to the same dedicated database server(s). Some of our older software doesn't scale very well so the load on our database is pretty high for what we're trying to do. If we really want high availability then we can have two database servers and set up a MySQL cluster and a SQL Server mirror.Thanks for the info.