Consultants need to get around.

One of the key differences between consultants and contractors is that contractors tend to stay at the same company for weeks or months, while consultants jump around very quickly from project to project.  Most of my engagements are just 3-4 days long: I parachute in, put out the burning fires, train the employees how to use my fire extinguishers, and then jet off to the next company.  I get to see a lot of cutting-edge SQL Server implementations, people doing wild and crazy things with SQL Server.  Sometimes the most valuable thing in my report is a line that says, “You’re outside the bell curve of normal implementations.”  Because I see a lot of shops, and because I talk to a lot of DBAs when I travel to present, I’ve got a pretty good handle on what’s a normal SQL Server environment versus someone who’s pushing the boundaries.

I recently spent a day at Fog Creek Software in Manhattan training their developers. These guys (including the StackOverflow team) are among my favorite clients because they’re really solid developers.  Joel Spolsky (Blog – @Spolsky) co-founded both companies, and he wrote Smart and Gets Things Done – a guide to hiring the best coders.  You would expect that his team would push SQL Server a little harder than the average company.

One of their design patterns is to use a separate database per client.  This approach has its pros and cons, but the important thing to note is that they’re running over 4,000 databases per SQL Server.

I’ll let that sink in – over 4,000 databases per server.

And did I mention that they’re using log shipping for disaster recovery?

It works, but SQL Server doesn’t ship with tools that make life easy for their developers or database administrators.  Managing version deployments across thousands of databases is a nightmare, but they’ve built their own schema tools. DBA tasks like index tuning, index defragmenting, statistics updates, procedure cache analysis, you name it, it’s harder to manage.  As a consultant, I can’t waltz in and say, “Well, golly, you need to throw all that out and rewrite your application to host lots of clients in a single database.”  I can make a case for it, but there’s a big business cost at this point, and as long as it’s working for them, it’s tough for me to justify it.

My most valuable advice in that case is to say something like:

“You’re outside of the typical bell curve for SQL Server implementations.  I usually see servers up to around 100-200 databases, but beyond that point it’s usually easier to manage by splitting the databases into multiple instances or multiple virtual servers.  I’m not recommending that you simply build more SQL Server instances and divide the load – I don’t see a gain there – but there may be tools or features you can’t utilize due to this design pattern.  For example, database mirroring won’t scale to this number of databases, and some off-the-shelf performance tools will have a tough time aggregating similar queries across databases.  Your infrastructure isn’t broken – but you’re probably in the top .01% of SQL Server implementations in terms of number of databases per server.”

Clients need to know when they’re doing something unusual – even when you’re not recommending that they do anything about it.

(I got Joel’s approval before writing this, by the way – ordinarily I would never mention a client by name in public.)