Step 2: Analyze consolidation candidate databases and servers

Analyze each SQL Server and its user databases to determine their performance characteristics, resource requirements, dependencies and how to migrate them. 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."

In the analysis stage, the consolidation team will take stock of each SQL Server and its user databases to determine their performance characteristics, resource requirements, dependencies and how to migrate them. In the consolidated environment multiple-user databases will be grouped or stacked together based on performance characteristics, internal and external dependencies, SLAs and versions. They will be grouped onto individual SQL Servers, also known as SQL Server stacks. There are three parts to analyzing candidate SQL Servers and their user databases for consolidation.

1. Analyze data access and usage patterns, and then analyze the SQL Server environment where the user database is installed.

2. Duplicate these patterns in the consolidated environment so the user experience remains the same, if not better.

3. Investigate any other consolidation opportunities beyond the user databases. Can you consolidate the SQL Servers into a cluster? Can you consolidate storage (disk arrays and storage area networks)? Does it make sense to consolidate applications such as SharePoint Team Services and Windows SharePoint Services, which tend to proliferate?

Let's look at the specific aspects of the analysis stage.

You must have a good understanding of the usage and access patterns for the user databases being considered for consolidation. For example, some usage and access patterns are cyclical. Some databases are accessed 24x7 while others are used only 9-to-5 Monday through Friday. Databases experience peak times on an hourly, daily, weekly, monthly, quarterly or yearly basis.

SharePoint databases typically experience uniform usage during business hours, whereas payroll databases experience greater loads during weekly, biweekly or bimonthly payroll cycles. Accounting databases may experience peak loads quarterly or yearly. It is important to understand the cyclical nature of each load so that you can capture a representative load. Then, you can use that load in your test environment and plan accordingly for peak load times without crippling other user databases on your consolidated SQL Server stack.

Client interviews go a long way in determining the cyclical nature of the data access and data usage patterns. They will be able to identify not only peak usage times, but also the time of peak load (i.e., when batch jobs put extreme loads on the SQL Server) and help estimate anticipated growth.

Run basic health checks, such as DBCC CheckAlloc, and review the SQL Server error logs, the log directory for debug dumps and the application log for anomalies, any of which might point to problems or problem-usage patterns. With this knowledge your consolidation team can capture a representative load using SQL Profiler and replay it as a test in the consolidated solution. Then they can replay loads from several individual SQL Servers simultaneously on the test solution. Capture performance metrics on the original system and compare those with the metrics obtained on the test consolidated SQL Server to quantify the performance improvement. Chapter 3 will detail the specific performance counters that should be gathered.

Many dependencies can make a user database a poor candidate for consolidation. It is critical that these dependencies are spotted in advance so you can plan for them or even work around them.

Click here for SQL Server dependencies.

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

ABOUT THE AUTHOR:   
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
This was last published in June 2006

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.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close