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.)
Great piece – couldn’t agree more.
It’s important to explain such things – if you don’t it’s not often understood why they can’t move on to do other things… and invariably blame the consultant!
Hey, that sounds VERY similar to a client of mine! I have been the DBA for a company that made it up to 7400 databases on a single server, and I home-built a custom log shipping system for them for DR and reporting purposes. And did I mention the horrible schema and old ADOc code? 🙂 SQL Server continually AMAZES me with how well it can handle what the world throws at it!!
” I’ll let that sink in – over 4,000 databases per server. ”
Kinda puts things in perspective lmao
This is very timely for me, because I am heading to my first consulting gig in 2 days. It is a one day job where I will help a brand new client with performance issues. Thanks also for defining the terms, because I have been using the term consultant often, but I have really been a contractor for the past year.
4000 is some number! Out of curiosity, what is the average size of the databases and overall size of all 4000?
The most I can claim is around 350 on my development server.
Clive – good question. Most of the databases are relatively small – under 1GB.
We’ve used solutions like that when implementing overly-simplistic off-shelf solutions. (ie-single Company eCommerce solution).
As a DBA for a mid-size company I appreciate being told where we are outside the norm (as we often are) or outside of ‘best practices’. But it would be nice if the consultant could make that case and offer suggestions without implying that we are all idiots 😉