How does one scale SQL Server 2008 or 2012?

Question

How does one scale SQL Server 2008 (or 2012)? At its basic, I understand there are two options:

Scale up:

If CPU bound, I can clearly see going from 1 CPU core to 2 to 4. Or if RAM usage rockets, just adding more RAM. Does SQL Server 2008/2012 actually pick up the slack and scale up that way assuming NO application level changes? To minimize speculation, lets assume I'm not doing something dumb like burning CPU cycles, doing cross joins etc.

Scale out:

It's not very clear how scaling out would work. I mean if I added another SQL server right next to my first one, how does the query know which server to run on? Is there some load balancer at the front (and does it come with the SQL Server software?) ? Does it entail application level changes for scaling out to work? Or do I have to shard the data and have custom code that calls up the correct database server depending on the data sharding key?

Would appreciate input from more experienced folks.

Answer 1

SQL Server doesn't scale out as such. It scales up.

There are 3 areas to do this, subject to edition limitations

• CPU cores
• RAM
• Storage

And of course, use a higher edition eg Enterprise

SQL Server doesn't shard and any such solution (you can research MySQL sharding solutions) adds complexity and overhead to a system.

Scaling up one server (+ standby nodes/mirror) is usually quite straightforward with RAM, SSDs, more disk volumes to spread IO, separate drives for tempdb and logs etc

Also, if you find SQL Server is CPU bound then it's usually poor design and/or indexes and/or poorly written queries unless you have a massive load.

Answer 2

Like gbn says, SQL doesn't really scale out like other RDBMs's do. However, there is one aspect of scaling out that many people overlook and that is to always have a separate system for reporting purposes.

Never allow reports to run against production. Build yourself a reporting database on another server.

Ideally, your reporting system would only contain data that the reports need, and would be structured and optimized differently from your production system.

Data would be fed into the reporting system as required (i.e. an hourly trickle update from production, daily feed, etc.).

A quick and dirty (and highly inefficient) approach is to simply have a full copy of the production database on another server. This copy can be maintained via full backups, transaction log shipping, mirroring (with snapshot), replication, etc.

I don't recommend this approach however. Full backups and restored take time, especially on larger databases. Replication is complicated and problematic. Log-shipping leaves you with a readonly database. Mirroring with snapshots may be a good answer, but you are still stuck with a production schema that is not optimized for reporting purposes.

A separate reporting system is the way to go.

Answer 3

If CPU bound, I can clearly see going from 1 CPU core to 2 to 4. Or if RAM usage rockets, just adding more RAM. Does SQL Server 2008/2012 actually pick up the slack and scale up that way assuming NO application level changes?

The different editions of SQL Server have different limitations in terms of CPU and memory they are going to use. But aside from that the answer is yes - if vacant CPU cycles or memory pages are available, the server is typically going to use them when needed, unless configured otherwise.

Or do I have to shard the data and have custom code that calls up the correct database server

Basically, yes. "Scaling out" is often done when you need to avoid lock contention. If you have long-running queries with extensive locking, you might want to separate these from "real-time" interactive queries or query-update-cycles started by users operating some kind of interface and awaiting prompt response. Obviously, taking care of this would require application changes (or at least middleware changes, if you have a 3-tier-design).