It's not uncommon for IT shops to find their SQL Server installations growing exponentially: Non- IT departments may install their own SQL Servers; shrink-wrap installations often require dedicated servers; and then there's always the small Microsoft Access application that grows to need its own server as well. Of course this creates a management problem for the DBA team and ultimately wastes financial, hardware and administrative resources.
Consolidating multiple SQL Servers offers various benefits to an organization. Consolidation involves analyzing SQL Servers in your enterprise and grouping databases onto a smaller number of servers without compromising the performance of any applications using the SQL Servers. This tip looks at the pros and cons of SQL Server consolidation. The benefits may include a reduction in licensing and hardware costs as well as eased administrative burdens.
Pros
Consolidation can help you can achieve significant licensing cost savings. The current Microsoft SQL Server licensing model consists of two modes: per-seat and per-processor licensing.
The per-seat license mode requires any user or device accessing SQL Server to have a client access license (CAL). For instance, a Pocket PC running SQL CE and accessing SQL Server using remote data access (RDA) or merge replication would need a per-seat license. The per-processor license permits an unlimited number of users to access your SQL Server provided each processor running SQL Server has a license. A per-processor license is intended for situations where a large number of users accesses SQL Server. For instance, you may use a per-processor license in an environment where users access SQL Server off a Web server facing the Internet.
Figures vary but if you have over 25 users on a SQL Server, typically you will get more value using a per-processor license (these calculations are based on a per-CAL cost of $130.00 and a per-processor cost of $4,300.00 on a single-processor machine). Many departmental or smaller applications using SQL Server have a single application account or few user accounts accessing SQL Server databases, which is ideal for per-seat licensing with five or 10 user CALs.
Cons
By consolidating multiple SQL Server databases onto a single SQL Server the CAL requirement may increase. As you decommission SQL Servers you may be able to cannibalize some of these CALs, but you will probably have to upgrade to a per-processor license. Consolidating also typically requires increased hardware resources. For instance, you may have to use dual processors, or a quad or eight-way box (with four or eight processors respectively).
Furthermore, you will probably have to upgrade SQL Server from Standard to Enterprise Edition for two reasons:
1. Increased RAM requirements will require you to use advanced memory support only offered on SQL Server 2000 Enterprise Edition.
2. Consolidating SQL Servers brings higher visibility to downtime, which means your service level agreements (SLAs) may increase. For maximum uptime you will want to support clustering.
Clustering increases availability by virtualizing your SQL Server. In other words, your clients connect to a virtual SQL Server that resides on any one of the nodes in your cluster. Each node has its own hardware resources and only shares the virtual SQL Server's databases, which are on a common drive shared among all cluster nodes. When a node goes down, clients will automatically reconnect to the virtualized SQL Server, which will now be running on a new node.
Consolidating SQL Servers frequently brings a higher level of uptime and performance to individual applications, as SQL Servers are typically consolidated on machines with considerably more horsepower than they had previously. Even though the hardware is now shared among multiple databases, the overall throughput is greatly increased due to the faster disk response, increased RAM, larger number of processors and higher CPU-processor speed. However, a pre-consolidation outage may only have affected a single application or department, whereas, in a heavily consolidated environment, many applications or departments will be affected, and the visibility of these failures is much greater. Users are no longer as tolerant of failure.
Consolidation tends to be a high-profile activity that is typically politically charged; outages can undermine the entire project. As a result, DBAs must closely monitor consolidated servers and be proactive about preventing downtime as much as possible.
Keep in mind that clustering also requires experienced DBAs and Windows administrators: Under SQL Server 7.0, clustering was difficult to configure and maintain. In SQL Server 2000, it is much simpler, and SQL Server 2005 clustering is largely unchanged.
Even though you may find you need to change your licensing model to the more expensive per-processor mode, you should find that the overall licensing burden is reduced.
Occasionally you will need to insulate an application using SQL Server from other applications (i.e. an application with high RAM or processor requirements). This may be best solved using a separate instance of SQL Server; SQL Server can support up to 16 instances if needed. However, your licensing cost will increase as each instance requires its own license. Use multiple instances sparingly.
Pros
Consolidating to a smaller number of SQL Servers will typically free up hardware resources for other applications. You may find that many low-use databases can be consolidated to a single SQL Server with no loss of overall performance.
Cons
There are two hardware downsides to SQL Server consolidation:
1. Hardware resources freed up may not be powerful enough swap into other uses.
2. You will need to purchase clusters for your consolidated SQL Server, which has significant processor, disk and RAM requirements.
Newer hardware typically does mean lower TCO because newer hardware means longer life and typically lower maintenance. However, you will be paying a significant up-front cost.
Pros
The fewer the number of SQL Servers you have to maintain and monitor, the lower your monitoring costs will be. Consider using monitoring software like Microsoft Operations Manager or NetIQ tools. These products all have a per-installation cost. By reducing the number of servers you have to monitor you can reduce the TCO of these products.
Then consider that your SQL Server needs regular maintenance for patches, service packs and firmware upgrades. Much of this work can be automated today, but some still requires administrator intervention. Would you prefer to visit 100 SQL Servers for maintenance activity or 10? By reducing the number of SQL Servers your workload will be significantly reduced.
Cons
You will need to monitor SQL Server performance more closely, as one application can now degrade performance for all other applications sharing that SQL Server.
downfalls. Consider these issues before attempting a consolidation.
Reduced TCO and other intangible benefits often make up for the up-front costs of SQL Server consolidation, but consolidation does have its
Runaway applications
Consolidating to a single SQL Server may open the door to runaway applications, which eat up CPU and degrade other applications' performance. Application or ad-hoq queries, temp table or cursor, may also cause tempdb to prevent other applications from using the SQL Server they share. Validating applications and testing their performance characteristics becomes critical to protecting them from rogue applications.
Downtime
Without consolidation, a SQL Server could go offline and only impact a single application or department. In a consolidated environment, downtime could affect hundreds of users and multiple applications or departments. Continual uptime becomes more critical. This problem can be mitigated by clustering, but that may require rebuilding an entire cluster to get SQL Server back online. Clustering expertise is crucial to providing a high level of uptime to meet the increased SLA requirements.
Version-specific problems
Consolidation may create problems when you have to upgrade to new versions of SQL Server or deploy services packs. One application may break when you upgrade, which means your entire SQL Server will have to remain at the current version. To solve this problem you must run the application under the previous version's compatibility mode, take the problem up with the vendor or developers, or move the application to a dedicated SQL Server running at the previous version level.
Consolidating user accounts
Occasionally you will come across applications running under the sa account with a hard-coded password. You may also run across two applications using an account with the same name. These applications are poor candidates for consolidation on a single database.
Planning for SQL Server consolidation is critical to a successful deployment. You must study the resource requirements or performance characteristics of individual databases to determine if they are candidates for consolidation. Ideally many of your databases have low usage and processor and disk requirements. With luck many of your applications also have low SLAs, making uptime less critical and clusters unnecessary.
To determine the performance characteristics of individual databases you will need to monitor the SQL Servers on which your databases reside over several months. There are several Performance Monitor counters to help you with this. Here are a few to try:
Memory – Pages/Sec
PhysicalDisk – Average Disk Queue Length
Processor – Percent Processor Time
Process - % Processor Time – sqlServer
MSSQLServer:Databases – Transactions/Sec – All Instances
Another option is PolyServe's SQL Server Utility, which allows DBAs to dynamically match capacity to need on every SQL Server deployment. It also provides high availability for all instances without introducing unnecessary complexity and extra cost.
Consolidating SQL Servers offers many benefits, including an overall reduction in licensing, hardware, monitoring and administrative costs. However, you need to carefully study the resource requirements of individual databases to determine which ones are ideal candidates for consolidation. Consolidating SQL Servers brings higher levels of reliability and uptime to your enterprise but it also requires careful monitoring of the consolidated SQL Server and will most likely require a clustered solution.
About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of 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 is currently working on books on merge replication and Microsoft search technologies.
More information from SearchSQLServer.com
Webcast: Consolidating SQL Servers on 64-bit for reliability and scalability
Checklist: Prepare SQL Server for peak workloads
Topic: Check out our collection of clustering resources
Licensing
