If you haven’t heard the term “SQL Server sprawl” before, you will soon enough. The term refers to the large numbers of uncoordinated SQL Server installations in an enterprise. Some of these installations are due to SQL Server being included with many Microsoft products (such as Office and Visual Studio), but also because it was brought in “through the back door” of IT as departments started using it for small applications they needed. Over time, this resulted in many enterprises finding themselves with tens and even hundreds of separate SQL Server machines running one or two applications, with central IT database administrator (DBA) teams frequently quite unaware of them.


The Problem with SQL Server Sprawl


This uncoordinated landscape is a hugely inefficient use of resources. What typically happens is that the department manages its own SQL Servers until some failure or problem occurs that is beyond the expertise of these “amateur database administrators (DBAs)” and requires in the centralized IT database management team.


Professional SQL Server DBAs soon find managing and maintaining these ad hoc SQL Server applications completely untenable. The first and most obvious problem is that they have typically been deployed without following any best practices. They are often security risks, behind in their patch levels, and may not even use current or supported versions of SQL Server. Their performance is sometimes suboptimal, and they have little or no documentation.


Frequently, the hardware the department purchased for the server is overpowered for the workload it supports. Although this may have been done purposely to try to avoid performance problems, it is a gross and wasteful inefficiency.


A third problem these deployments have is the hodgepodge of licensing that is in play. Recall that SQL Server has two licensing models: per seat (each user accessing the server has a license) and per processor (one license for the server). Most uncoordinated deployments use the per-seat model because it is cheaper when the number of users is relatively small. But imagine five users accessing five different servers this way – this would require 25 per-seat licenses, so you can see how it adds up.


Finally, the edition or version of SQL Server being used in these deployments is not optimal or as easily managed. For example, departmental deployments often use a starter or more limited edition of SQL Server, and while this may work in a standalone departmental environment, it does not work very efficiently, especially in conjunction with all the other departmental servers and applications. Quite simply, it does not scale.


Benefits of Consolidating the Sprawl


The solution that many enterprises adopt is to consolidate the multiple user databases running on these departmental SQL Servers to a much smaller number of SQL Servers running on powerful machines. There are many benefits to doing so; let us discuss a few.


The most overarching benefit is that it enables you to enforce standards. Running SQL Server with a common, documented configuration in a common environment makes all aspects of management much more efficient. Administration can be handed over to new staff more quickly, and the common environment makes scripting easier. Patch and upgrade management can also be handled more systematically.


Another benefit is that it makes monitoring easier. Fewer servers means fewer events for the DBA team to react to, and costs for monitoring tools is reduced too. With best practices enforced, the servers will have far fewer failure events and be more reliable.


Consolidation also enables easier integration into enterprise storage and backup plans. Again, this is due to fewer consistently set-up servers. This also typically improves the performance of the SAN because, although the amount of data to back up remains the same, the bandwidth required is reduced.


DBA teams can be more focused and efficient in a consolidated SQL Server environment. A smaller team can handle far more, become specialized and cross train one another.


Performance and availability improvements are another benefit of consolidation. In a consolidated environment, high availability becomes a key requirement because of the multiple databases and applications relying on the one server – which impacts everything if it fails. Performance improvements are realized by the greater horsepower offered by the consolidated server, even when it is handling multiple databases and users.


Overview of Consolidation Process


The solution is to consolidate all the user databases on departmental servers onto a much smaller number of powerful clustered SQL Server machines, ideally running the latest version of SQL Server Enterprise Edition on a 64-bit platform. This level of server can support hundreds of databases on a single instance (if running SQL Server 2005) and utilize all available physical memory. Previous versions required setting up multiple instances, which, while workable, was a bit more challenging to manage.


To avoid spectacular failure, the consolidation project must be planned and handled carefully, involving all stakeholders, negotiating politically charged issues of ownership and business processes. The key is to execute the project in waves, starting with the smallest department first to work out kinks and build confidence.


Entire articles have been written about the people and project management side of IT projects like this, so let’s just touch on the highlights here. A dedicated consolidation team should be formed to drive the process. The team must first interview the business owners to set and manage their expectations; agree on software license agreements (SLAs) for recovery, performance and maintenance; determine load and growth patterns; and conduct an inventory of all applications and users using the database.


The inventory process is critical – it needs to capture the key technical details, including:


  • Versions and editions of SQL Server used in the standalone environment. If you find legacy versions of SQL Server that cannot be upgraded, these can be good candidates for a virtualization approach (discussed below).
  • All SQL Server names and instances to be consolidated, including whether the application needs to access SQL Server using the default instance. For example, if the application code is lost, you may be unable to recompile it to use a different server or instance name. You may also find there are multiple databases on different servers or instances that have the same name, and you will need to determine whether any of them are redundant, can be consolidated into a single database, possibly renamed, or must be placed in separate SQL Server instances.
  • Internal dependencies, such as user databases with dependencies on other user databases, or that reference objects in the master database. Also external dependencies, such as linked servers or file system path or components.
  • Login and account information (logins, even domain and local Windows accounts, etc.), and application considerations such as requiring a specific account, server name or connection pooling requirements.
  • Baseline performance metrics and benchmarks. It is essential to identify databases that are high CPU, memory or I/O consumers, to determine which can realistically coexist in a consolidated environment. The baseline enables you to compare relative performance changes when on the consolidated environment and determine whether it is still in line with SLAs. Benchmarks collect performance metrics under a variety of load conditions and help avoid situations where multiple user databases have their peak load cycles at the same time while on the same SQL Server instance.

Of course, there will be “gotchas” as the consolidation project proceeds. The majority of problems are due to dependencies that cannot be migrated to the consolidated environment, such as account name collisions. It is important not to attempt to solve performance problems during the consolidation effort. A performance fix may introduce other problems or bugs and in the long run distracts the team from their main task, executing the consolidation project.


Where Does Virtualization Fit?


So far, I have been talking about consolidating SQL Servers to single machines hosting multiple SQL Server instances. But now you also have the option of consolidating to a virtualized environment, which offers an attractive option for many situations.


Virtualization uses software to create multiple independent servers that appear to users to run simultaneously on the same physical machine. In reality, it works much like a time share where the property is shared by multiple owners who take turns occupying it.


What’s the difference between traditional consolidation and a virtualization approach? In SQL Server consolidation, multiple databases are stacked on multiple instances on a single physical machine – resources are shared between instances. But in virtualization, multiple operating systems are stacked on a single physical machine – each OS occupies the physical machine’s attention for an instant in turn, thus sharing the physical machine. Figure 1 illustrates the use of components in virtualized and consolidated SQL Server environments.



Virtualization continues to grow in popularity because it saves on the fixed costs of physical machines, occupies less physical space, and reduces cooling and power requirements. The biggest benefits specific to SQL Server consolidation are that virtualization:


  • Enables you to run different versions of SQL Server and different operating system versions, which may be the only way to migrate legacy applications or applications where the code has been lost; and
  • Allows you to migrate SQL Server instances quickly from one virtual server to another.

There is some performance degradation with running multiple servers in a virtualized environment, but for many workloads it is quite acceptable. Tests have shown that each virtual instance can support 80 percent of the load in a virtual environment that it could in a nonvirtual environment. Still, virtualization is not a good choice for mission-critical SQL Servers running large workloads. New processor developments are planned that will reduce CPU bottlenecks with virtualization and support terabytes of RAM providing a better memory access. These developments could change the playing field, or at least allow you to squeeze more virtual instances on a single physical machine.


Another aspect to consider is that virtualization software often provides more control of memory and CPU resource allocation. For example, performance degradation of one database instance that would slow down everything in a consolidated environment can potentially be isolated to that one instance in a virtualized environment.


Of course, in a virtualized environment, your servers are vulnerable to the virtual host software crashing, which would be analogous to the host operating system crashing in a consolidated SQL Server environment. However, most companies running virtualized environments are comfortable enough with the uptimes provided by the virtualization software and have business continuity plans in place to deal with this single point of failure.


SQL Server sprawl is a real problem in enterprises today. However, IT organizations can bring order to the chaos by consolidating a wide array of SQL Servers into much fewer properly configured and managed enterprise-level SQL Servers. Virtualization adds a new dimension to this process, offering cost-effective options that should be considered in any major SQL Server consolidation plan.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access