How does one scale SQL Server 2008 (or 2012)? At its basic, I understand there are two options:
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.
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.