Stacking Part 1: Why Don’t SQL Server Pros Stack Instances?

How many applications do you have open on your PC or Mac right now? I would guess probably email, a web browser, a word processor and maybe more—but for our purposes, let’s just call it more than one app.

On the contrary, Microsoft SQL Server users typically deploy 1 instance per OS even though Microsoft lets you install up to 50 instances on any one licensed host. And SQL Server licensing is per machine whether you have 1 or 50 instances installed.

So the obvious question becomes: why aren’t SQL Server users stacking to up utilization and reduce licensing costs? Turns out there are some really good reasons why stacking for maximum utilization just doesn’t happen:

  • Organizations want to allow for growth on their boxes instead of unsustainably sealing multiple growing instances on a single OS until it’s impossible to meet SLAs on all workloads.
  • What if firms get the stacking ratios wrong in their environment? That means having to migrate instances one at a time—an especially painful and challenging endeavor due to the changing connection strings of the apps during the process.
  • If something goes wrong with the machine on which the workloads are hosted, ALL of the instances are affected.
  • When it comes time to do modernization or maintenance on a machine it can be a nightmare to try and coordinate planned outage windows with the various business units that reside on that same machine.

A typical approach to enable instance stacking is using Windows Server Failover Clustering FCIs. However, Failover Cluster Instances have drawbacks of their own. Firstly, doing so on Standard Edition SQL Server only allows for a 2-node cluster. This doesn’t allow for very effective stacking at all. The reason being, you are limited to only 50% utilization on either node since you must maintain enough capacity for all of your workloads in the case of one of the servers going down.

With that being said, if you want to enable larger clusters you are forced to Enterprise Edition SQL Server. That is 3.5x the cost of Standard. The price isn’t the only deterrent though. With both Standard and Enterprise Edition clusters, configuration, troubleshooting and management can be a pain and migrations can be lengthy. Also, you can’t use WSFC to manage an existing workload—you have to create a new cluster and migrate to it.

Check out Part 2 to see how you can safely stack instances within an agile, highly available SQL Server architecture.

Connor Cox