Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Jan 17 '12 at 11:33

This question came from our site for professional system and network administrators.

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
    
Great! Any specific MySQL sharding literature/tutorials/links you recommend? Hopefully I can apply some of those patterns to SQL Server. I'm learning (quick!) and you're quite the resource!! –  DeepSpace101 Jan 18 '12 at 8:23
1  
And modern wintel servers can scale up to some pretty high-spec hardware. If you have to scale beyond that you're comfortably into VLDB territory and expensive, specialised kit. –  ConcernedOfTunbridgeWells Jan 18 '12 at 9:27
    
@Sid, Moving SQL Server into a config where a single database is shared across multiple physical servers isn't something for the new user to try. There's only a few people that have been able to successfully do it as it is very tricky and requires a VERY stable schema and a solid understanding of the data patterns within the database (I've taught classes on the subject). The basic ideas from other platforms are a good place to start but the actual techniques used are quite different. –  mrdenny Feb 20 '12 at 1:14

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.

share|improve this answer

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.