How does one scale SQL Server 2008 or 2012?

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.

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.

StackExchange Database Administrators Q#10776, answer score: 10