Step 2: Analyze consolidation candidate databases and servers

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 l...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server High Availability, Scalability and Reliability SQL Server high availability: Options and caveats High availability and the database Are data warehouses made for the cloud? Top load balancing methods for SQL Server Maintaining high availability of SQL Server virtual machines Creating fault-tolerant SQL Server installations Scaling up vs. scaling out with SQL Server 2008 How to configure storage in SQL Server database with more writes than reads SQL Server database replication tutorial Licensing a standby server for SQL Server replication SQL Server High Availability, Scalability and Reliability Research

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

oad. 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.

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