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.
Be the first to comment on this post using the section below.