One Approach to Managing SQL Server Sprawl

But here’s my advice: start small.

For us, the first step was to "stop the bleeding". We had new projects coming into the pipeline on a continual basis, mostly purchased applications with a narrow purpose and set of users. When I came into the department, the old process was to ask the vendors for their hardware requirements, they’d (of course) spec out a really nice SQL Server, and we’d buy it, dedicated to that one application. In fact, we’d often buy two servers: one for test, and one for production. In every case we followed the vendor’s specifications and as a result had over 100 grossly-underutilized servers. Oh, the joys of working in a large corporation! 🙂

We stopped that practice.

Instead, we’d take a hard look at what each new application was meant to do, how much data was likely to be produced (with vendor help), how many simultaneous users were expected, etc. We established a policy of which applications’ databases could share an instance with other applications. For example, if the application installed anything to system databases, or required server-level roles, it couldn’t share an instance. We’d compare the estimated requirements with the load on our existing servers and decide where to place the application. We discovered that when approached this way we could put nearly all new applications on a shared server, many in the same instance.

Whenever possible we’d put the application’s databases into a named instance. We started aliasing server names, hiding the true name from anyone but admins. The idea was that if desired we should be able to move an instance to another server transparently other than the downtime to make the switch. I left the company before we ever needed to move an instance, so I can’t say whether there were glitches in that approach.

Because so many applications shared a server (and usually an instance), the cost of server-failures increased. It was no longer just one application that would be down in that case. We were already well into the above approach when we were finally approved to use clustering to mitigate this risk. Thereafter we put all new applications into a cluster, and we moved a few existing ones (which were easily movable) into the cluster as well.

We also began using VMs, which were a new technology in the company, for the test servers. In time we could have expanded our use of VMs for some applications.

This is hardly a full consolidation strategy. But we needed no grand approval process or funding or detailed analysis or any other high-cost item. In fact, our manager didn’t even know (initially) that we were doing it. We just got started, and received immediate payback. I can say for sure that we saved tens of thousands of dollars in under a year with no investment. We also got lots of feedback which would enable us to create an effective larger-scale consolidation effort.