The Headaches of Microsoft SQL Server Sprawl—And How to Make Them Stop

If you’re looking for a popular database management system (DBMS) platform, Microsoft SQL Server is a solid choice. Research from Gartner shows it’s among the most widely deployed platforms in the category—second only to Oracle—with more than 20% of the DBMS market’s $34.4 billion total. SQL Server also is experiencing rapid growth. Industry data from 2016 shows that SQL Server revenue rose more than that of Oracle and even of the market overall, with 10.3% growth.

The reason behind this impressive growth is that Microsoft SQL Server offers undeniable benefits to its users. One of the biggest advantages, as noted by Galen Farrar writing for Turbonomic, is that it’s easy to use—particularly when it comes to development and troubleshooting, two areas that can be tough to get right for organizations that need to move a SQL project into production.

As Farrar points out, “MSSQL comes with excellent tools that will save you a lot of time in these areas—tools like SQL Server Profiler, SQL Server Management Studio, BI tools, and Database Tuning Advisor. Setting up almost everything, from installing on a VM to initial query writing and editing, is incredibly easy with MSSQL—especially in comparison to other SQL products.” Farrar adds that even if you do encounter an issue when using SQL Server, the platform provides robust live product support plus online support and documentation that outshines the support options of its competitors.

Despite its strong growth and the clear benefits it offers to users, SQL Server is not all sunshine and unicorns. In fact, SQL Server is as well known for its familiar pain points as it is for the goodies it delivers. In particular, many SQL Server users have become accustomed to dealing with what’s not-so-affectionately termed “SQL Server sprawl.” It’s a lot like it sounds—a sprawling environment that results in the enterprise on the heels of explosive and uncoordinated growth of SQL Servers. As an example of this, whenever an organization is faced with new database demands, IT may find that it needs a new instance of SQL Server. In most cases, when a new instance is required, it means a new server is also needed, either physically or virtually, since most IT departments deploy a single instance per server. This can quickly spiral out of control as the SQL Server footprint replicates over and over.

What happens as a result? Enterprises can experience pain on more than one level. Two areas in particular take a hit from the sprawl:

  • IT management. Administrators in charge of SQL Server management are among those who suffer the most from SQL Server sprawl, which can take a huge amount of time. Sprawl means IT admins get stuck with tedious tasks such as doing patches, updates, and migrations, as well as other maintenance duties such as troubleshooting and ensuring security. As these jobs take up an increasing percentage of the administrator’s day, IT loses time for other mission-critical activities. Another problem for admins is that such tasks require planned outage windows, so many require time spent at night or on the weekend.
  • The bottom line. When it comes to finances, there is also a huge cost to an enterprise’s bottom line when being forced to deal with SQL Server sprawl. These costs include SQL Server licensing, which even though less pricey than Oracle, can still be considerable. (Keep in mind that there have been some licensing rule changes over the last few years.) Another cost issue is that as there are more SQL Servers, the SQL Server bill may grow accordingly—especially since large multi-core servers have become the norm, resulting in per-core SQL Server licensing that causes a significant hit to the bottom line.

Fortunately, despite these obstacles when working with SQL Server, there are different ways to avoid sprawl—some of which work better than others. Here is a rundown of four possible solutions to help combat sprawl, along with a few of the hard and soft costs of using them, and some recommendations:

Database Merging

By moving multiple databases into the same instance, database merging can cut the number of servers by reducing the number of instances. The problem with this approach is that it involves considerable risk. It gets harder to coordinate planned outages over time, and many users can find themselves impacted by the situation if an instance or server fails without warning.

Large Enterprise Edition WSFC Clusters

Another common solution to sprawl is to create a consolidation platform via creation of large Windows Server Failover Clusters (WSFC) with multiple nodes. But Enterprise Edition is expensive, and all of the servers must be the exact same version. WSFC has many additional complexities for deployment and management to deal with, so it’s not necessarily the best choice if you’re cost conscious and are trying to avoid IT headaches.

Instance Stacking

Another technique is instance stacking, which can help reduce OS numbers and licensed core counts. (Microsoft allows for the installation of up to 50 SQL Server instances per operating system.) But when you have all your eggs in one basket, so to speak, an outage can wreak havoc on many instances. It is also hard to move instances if you don’t have the stacking ratio figured out correctly from the start.

Software-Based Solution

Clearly, the approaches above bring new problems even as they may help to combat sprawl. Another possibility to consider is a software-based approach. With a software-based solution, organizations can safely stack, on average, five to 15 SQL Server instances per licensed operating system—some enterprises run as many as 50. What’s nice here is that the solution can give companies the ability to easily move instances between hosts—and they can do so either manually or automatically if an instance or server fails. The savings from this physical and logical consolidation relates to both time and money. Enterprises save operationally by conserving management time—IT administrators don’t have to oversee as many servers. Also, there are significant cost savings because there are fewer cores to license for SQL Server.

Another reason to consider a software solution is based on its many benefits beyond combating sprawl:

  • Avoiding the need to pay for the expensive SQL Server Enterprise Edition
  • Gaining support for mixed-version clusters
  • Enjoying built-in high availability
  • Simplified disaster recovery
  • Easy patch and upgrade management

In short, a software solution means that organizations need not compromise when using SQL Server. Users can achieve high availability, cost savings, and simplification of the management framework for their total workloads. Plus, it’s just software—so enterprises can keep their existing infrastructure as well as their SQL Server instances.

There’s no need to accept the time drawbacks, management headaches, and financial costs of SQL Server sprawl without a fight. Do your due diligence and find the solution that works best for your organization and your people.