Step 1: Create a SQL Server consolidation methodology

To successfully consolidate SQL Servers enterprise wide you must define goals and choose your consolidation methodology. Get help in this step excerpted from Chapter 2, 'Planning your SQL Server consolidation,' of our original expert e-book, "Consolidate SQL Servers for availability, scalability and cost savings."

To perform a successful enterprise-wide SQL Server consolidation you must first define goals for your consolidation team and the client, the business owner of user databases. These goals will vary depending on your consolidation approach: Consolidating on a virtual machine, stacking SQL Server instances, using a storage area network (SAN), etc.

It is critical that the consolidation team negotiate in advance realistic service level agreements (SLAs) with the client. These SLAs will not only set expectations for availability, support, change control and monitoring, but also performance. An SLA that sets supportable expectations goes a long way in building confidence in the consolidation effort.

Mission-critical applications should be identified. Their SLAs tend to be more stringent than other SLA, either requiring that these applications not be consolidated or careful planning takes place to ensure SLAs can be met or exceeded in a consolidated environment. Standards should be applied and those applications should be brought under the consolidation teams' ownership and control.

Another reason to negotiate SLAs in advance is to avoid scope creep, in which your consolidation team would have to solve unexpected performance problems and enhance functionality.

Your team must consider various scenarios when working with SLAs. For instance, someone may discover poorly performing applications while identifying databases that are good candidates for consolidation. Ideally clients should be asked to take these applications back for optimization. If your team elects to optimize, you'll be taking ownership of any performance problems or bugs that arise in the future. The wise option is to merely identify and return these databases to the business owners and specify this action in the SLA.

If the business units are unwilling or unable to reclaim and optimize these SQL Servers, migrate them into your data center and enforce standards as much as possible, but do not consolidate these databases with another SQL Server. Consolidating a poor-performing user database may degrade the performance of all other user databases on that SQL Server.

Once the SLA is negotiated, your consolidation team should create a schedule that breaks down the enterprise-wide plan into phases.

The first phase should involve departments with the least complex user databases. That gives team members a chance to practice before tackling the more difficult consolidations. This phased approach also teaches them to be more adept at juggling user databases between consolidated SQL Servers as database loads change over time. For example, as a particular user database grows, it may degrade the performance of all user databases on that consolidated SQL Server. On the other hand, as the life cycle for a particular application winds down, the resource requirements of that user database may decline and make it viable for a move to a lower horsepower server.

Test scripts should be created to help survey existing SQL Server applications. That will familiarize team members with performance monitoring and SQL Server Profiler to capture and replay representative loads and monitor the consolidated solution.

The consolidation team should also break into specialized groups to simplify monitoring the consolidated solution.

Once members of the consolidation team understand their individual tasks and prepare for the consolidation, the next step is analysis.

The above tip is excerpted from Chapter 2, 'Planning your SQL Server consolidation,' of our original expert e-book, "Consolidate SQL Servers for availability, scalability and cost savings." This chapter explains six steps to consolidation and other key consolidation considerations.

How to consolidate SQL Servers

 Home: Introduction
 Step 1: Create a SQL Server consolidation methodology
 Step 2: Analyze candidate databases, servers and more
 Step 3: Test your consolidation
 Step 4: Deploy consolidated SQL Servers
 Step 5: Monitor and stabilize consolidated SQL Servers

Hilary Cotter
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.
Copyright 2006 TechTarget

Dig Deeper on SQL Server High Availability, Scalability and Reliability