An interesting question was posed to me today. What's a good strategy for scaling out SQL Server?
My answer: It kind of depends on your situation, one of the best ways is to have hardware (active / passive ) clusters at multiple sites and replicate between them. Here are a few scale out options (in no particular order).
1. Peer to Peer Replication - Multiple servers across the planet can be used for read/write access and all of the data can be replicated to each server in the group. This is great if every server needs to have all of your data, you can scale that back a little and replicate only that data relevant to the site.
2. One great way is to separate out processes. OLTP goes to this server, ADHOC / Reporting goes to another server, batch processes go to a third and a fourth server is set apart for data warehouse staging. You can accomplish this with log shipping (still viable in my opinion) or one way replication. The key is to get the extra processing stuff that doesn't need to be on your OLTP server off onto a secondary system. Reduce the traffic your real time app needs to contend with.
3. Horizontal partitioning - There are plenty of articles out there about it, but simply put you break apart your data into manageable chunks and place it on different database servers.
I am not aware of any practical way to have Active Active SQL databases. Unless you are partitioning your data to different databases and then putting them in a 2 way cluster. That scares the willies out of me though, since when you fail one of the boxes over you will be putting the load of both databases onto one machine.
Posts
It depends on goal is. More throughput or more redundancy. Typically when someone says scaling to me I think they want more throughput. One way that this can be done is by simply spreading the data across many servers and building a directory. For example if you have a million customers maybe you put 500k on one server and the rest on another and applications or access layers use a directory to know when server the customer is on. This is a simple example but you get what I mean.
ReplyDeleteFor sure, horizontal partitioning :) Break the data apart and balance the load across servers. It works, just a pain to manage.
ReplyDelete