Problem solve Get help with specific problems with your technologies, process and projects.

Pitfalls of SQL Server sprawl

Get a thorough understanding of the SQL Server sprawl boom in this excerpt from Chapter 1 of our e-book, "Consolidate SQL Servers for availability, scalability and cost savings."

The following tip is excerpted from Chapter 1, The case for SQL Server consolidation, from of our expert e-book, "Consolidate SQL Servers for availability, scalability and cost savings." This chapter addresses how to determine if consolidation is right for you and critical considerations to ponder before taking on such a project. Click to download Chapter 1.

The boom in uncontrolled and uncoordinated SQL Server deployments has led to numerous challenges for the enterprise.

Let's start with wasted hardware resources. In many departmental implementations of SQL Server the hardware is grossly oversized for the workloads. Sometimes excess hardware is pushed by overzealous hardware vendors; and sometimes the department overestimates its workload or growth. You may also find that the workload would have been more appropriate for a desktop PC. Checking CPU usage reveals that these SQL Server configurations seldom consume more than 5 to 10% CPU utilization.

SQL Server sprawl spells headaches for managers when it comes to maintaining or patching large numbers of SQL Server installations distributed over a LAN or WAN. These problems are exacerbated by the fact that DBAs or network administrators may not have such base information as who administers individual machines, the administrator passwords or the machines' locations. They may not have negotiated a maintenance window with the clients or know the consequences of the servers going off line. Consider the case where a virus targeting SQL Server hits your network, and you need to patch all of your SQL Servers immediately. Locating them and patching them is a challenge when there is no central point of administration. This becomes even more problematic with developer machines when many of them are running MSDE, SQL Server Express or SQL Server Personal Edition.

SQL Server sprawl spells headaches for managers when it comes to maintaining or patching large numbers of SQL Server installations distributed over a LAN or WAN.
Hilary Cotter
SQL Server MVP

 Individual SQL Servers in such dispersed environments are poorly managed. For example, they may not have the latest service pack or patch level, or may be two versions out of sync. This leads to two problems. First, the version might be scheduled to retire from active support; and, second, the DBA team may not be familiar with the version. That means that it takes the DBA longer to fix the problem, and it takes him away from other essential tasks at hand. In addition, gaps in management may mean that indexes are not maintained, which hampers overall performance for applications using the database.

Standardization suffers when SQL Server implementations get out of hand. With SQL Server 7 it was common to have SQL Servers using mixed-mode authentication with a blank "sa" password. This was not only a security fault, but Trojans could scan a network looking for such unsecured machines and launch exploits on them. A lack of standards can cause other problems, such as the lack of backup procedures or transaction log maintenance, or unchecked code, which might leave SQL Servers prone to SQL injection attacks. Microsoft Baseline Security Analyzer does help to identify servers with security vulnerabilities.

Smaller, departmental SQL Servers do not have high availability requirements, so an uptime of 9-to-5, Monday-through-Friday is sufficient for most database applications. However, these departmental SQL Servers can consume large mounts of a DBA's time trying to recover from failures if enterprise-level disaster recovery plans don't factor in a department's reduced availability requirements.

Such firefighting is an ineffective use of the DBA's workday. Unless rigorous planning is done to ensure point-in-time backups have been completed and verified, there may be partial or complete data loss. Part of this equation entails frequently moving the backups off the machine to a vault, preferably offsite. If copying the dumps to tapes is part of this solution, the tapes must be tested and archived as they age and outlive their useful life span. Remember that tapes received a bad name for some companies affected by the Sept. 11 terrorist attacks when they found the tape backups to be unusable.

The dispersed server also may house the little application that grew. This is an application that was written by an amateur or someone with a lower skill set, and has become critical despite its shortcomings. While this application may have initially performed well, it simply is not scalable. Solving one bottleneck only reveals another. This application may require more maintenance than a better written application due to flaws such as its reliance on cursors or poor indexes.

SQL Server's two licensing modes

Per-seat licensing is best used when you have a small, known number of clients connecting to your SQL Server.

Per-processor licensing will work best if you have a large number of users.

 There's a financial impact to SQL Server sprawl that goes beyond hardware and administrator costs. That is wasted licensing. A single SQL Server implementation with the correct licensing actually may be over-licensed. There are two licensing modes for SQL Server: per-seat and per-processor. Per-seat licensing is best used when you have a small, known number of clients connecting to your SQL Server. Per-processor licensing will work best if you have a large number of users.

Things get tricky and problems can emerge when you start consolidating SQL Servers and stack databases on a single server or cluster. With luck, you will have the same users accessing different application databases on the same server, and this will result in an overall net savings in per-seat licensing. Assume that you had an accounting group of five users accessing two SQL Servers. Without consolidation this would require two SQL Server licenses with five client access licenses (CALs). Consolidating these two databases on a single SQL Server installation would mean a savings of a server license.

However, you will commonly find the number of users connecting to your consolidated SQL Server solution increase, so the per-processor license will make more sense. By consolidating on state-of-the-art machines you can leverage Moore's law and get more performance for your hardware dollars.

Some workloads require you to consolidate SQL Servers running on machines with greater horsepower and memory. On SQL Server 2000, this may mean upgrading to Enterprise Edition, which offers access to greater memory and clustering.

Unlike its predecessor, SQL Server 2005 Standard Edition already offers better accessible memory and clustering. You will also find a limit on how many databases you can support on a single instance of SQL Server. I don't mention operating system license consolidations because most people who decommission a SQL Server repurpose the machine for some other use, and the operating system license is also repurposed. You may save costs in monitoring licenses through use of NetIQ Corp. tools or Microsoft Operations Manager (MOM).

Work on developing a standardized and centralized database environment. Read the complete chapter on considerations you must take to resolve SQL Server sprawl, and determine if consolidation is right for you. Download Chapter 1: The case for SQL Server consolidation.

About the author: Hilary Cotter has been involved in IT for more than 20 years a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and currently working on books on merge replication and Microsoft search technologies.

Dig Deeper on SQL Server High Availability, Scalability and Reliability

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.