I've been doing some Google searching and high availability is EASY to find... but I'm looking to do a high speed replication of an SQL database.

Basically, I have a DB that is accessed by a number of machines to check is a customer has enough credits before making a transaction. Currently the number of machines accessing the DB is so high we've built a queue to be able to process all the requests.

We're looking to replicate the "transaction" DB in three geographical areas USA, Europe, and Asia. The problem is that all three servers need to be kept in sync very quickly.

I'm wondering if this is even possible OR is we will need to work in some special logic with a geographical load balancer to keep a customer from using too many credits.

For example, customer has 1000 credits, uses 500 on Asia's server, 500 on the USA server and is then permitted to use 500 on the European server... this can't happen.

Ideas???

Suggestions on where to find more information???

12/15/2009 6:26:53 AM

Out of curiousity, how many connections/second are you dealing with? MS. SQL Server can handle thousands at a time under nominal conditions.

I'm interested in the proper solution to your problem.

12/15/2009 8:22:59 AM

I'm no expert, but is there any particular reason you'd think mysql multimaster replication wouldn't work for you?

http://en.wikipedia.org/wiki/Multi-master_replication
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html

12/15/2009 8:42:04 AM

This is sort of what Amazon does.

Yes, your best bet is to use a load balancer to direct users. It will reduce your efficiency slightly, but will allow you to scale and will prevent the critical data mismatch scenario you just explained.

12/15/2009 12:17:35 PM

llama

Quote :

"Disadvantages of Multi-Master Replication #1 Most multi-master replication systems are only loosely consistent, i.e. lazy and asynchronous, violating ACID properties."

Though, I do appreciate the links, and will see if I can combine some of the technologies available in them.

Unfortunately the transactions being used will be subtracted at a VERY fast rate. Basically every time somebody hits our server, we need to subtract a transaction... it gets QUITE overwhelming fast. We've already had to implement MSMQ with a service to read it and perform the functions in MS-SQL2000 because MS-SQL couldn't keep up.

Right now our database system is not scalable OR fault tolerant because of its implementation. I've been looking into MS SQL Broker versus the MSMQ and found some interesting information, anybody had experience?

I'm working on the numbers now, but currently we have gotten usage spikes of over 3m transactions per hour, which would kill our connection to the internet if we sent the data out of the cluster.


Noen currently we're looking at using a geo-load balancer since we're first implementing a co-location in the USA (currently in EU) then another in Asia, with more possible. The biggest issue is the DB server closet to the services cluster needs to be VERY accurate with the number of transactions available. The alternate DB servers need to be kept in sync, but since these shouldn't be used until the primary DB server for that location goes offline, it isn't as important the information be real time.

I'll see if I can draw something up in Visio to illustrate things when I'm back in the USA. Unfortunately this seems to be an interesting problem because most transaction (read prepaid system) based replication doesn't have the high number of transactions per an hour that we do. If the DB server is too slow on recording the transactions from the services cluster, we will easily have many customers placing their accounts with a negative number of transactions (which we can't bill for).

12/17/2009 11:04:17 PM

llama looking at the article http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html it seems that what I'm looking to do might be possible with the circular replication failover recovery.

The problem comes into the speed and bandwidth required to do so and how this can be done from our services cluster.

12/17/2009 11:18:21 PM

3M per hour?

holy shit

12/18/2009 9:11:49 AM

Quote :

"We're looking to replicate the "transaction" DB in three geographical areas USA, Europe, and Asia. The problem is that all three servers need to be kept in sync very quickly. I'm wondering if this is even possible OR is we will need to work in some special logic with a geographical load balancer to keep a customer from using too many credits."

There are a few ways to tackle this. If you have data as to where the customer normally is, then you can set "their" master to be the db in their general location. when they try to access the site (where ever) you use something like a citrix global distribution appliance to point them to the db resource in their home db. the reads/writes happen on that one, then you can asynchronously queue mirrored write backs with a storage virtualization appliance like the product from falconstor. you can also do things like snap mirror on netapp if you have netapp in all locations. I'm pretty sure emc and symantec not offer products for remote asynchronous writes as well. if you use something like iscsi for the sync traffic, you'll be able to take advantage of wan accelerators as well, like a riverbed box or a cisco waas.

12/18/2009 9:55:24 AM

^^3M/hour is only slightly more than 800/second, which is well within SQL Server tolerance of 2-3,000/second.

But yeah, he already said it was an issue with the internet connection being choked; and it makes more sense now.

12/18/2009 2:56:51 PM

Quote :

" SQL Server tolerance of 2-3,000/second"

where are you getting this figure?

12/18/2009 3:04:36 PM

Well, it's not capped by any means, but those numbers are easily achievable. There's a site that performs transaction benchmarks by hardware and database types to prove my point:

Here's a server that handles 1,379 transactions/second (tps) ...
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107032701

And here's a really expensive server that achieved 20,523 tps ...
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=105112801

Interesting article, by the way...
http://sqlblog.com/blogs/paul_nielsen/archive/2007/12/12/10-lessons-from-35k-tps.aspx

12/18/2009 6:17:00 PM

http://www.NetApp.com

12/19/2009 11:45:06 AM

^^ ok, i was wondering if you knew something i didn't about inherent limitations to SQL server. where i work, our database handles about 2k tps at peak so it got me thinking we might be hitting a wall at some point.

[Edited on December 19, 2009 at 12:28 PM. Reason : i'm not a dba by any means so i wasn't sure]

12/19/2009 12:25:53 PM