SQL Server Consolidation Tutorial

In this video, Brent Ozar explains the phases of a consolidation project: from the problems to the planning to post-consolidation:

http://tutorials.sqlserverpedia.com/SQLServerPedia-20090105-ConsolidationPlanning.flv

For more tutorial videos like this, check out the SQL Server Tutorials page.

Questions Involved in a Consolidation Project

Consolidation projects should answer the following questions (and this is not intended to be a complete list):

What is the deadline for the consolidation?

How much time do you have? This information will drive how you proceed. Ideally, you’ll want a minimum of 3-6 months to determine all the characteristics of both the source and the target environments so you can accurately determine the nuances of all of the environments involved. Information is your best friend here, but be aware that the information is only as good as your ability (read: time and resources) to process it.

What is involved in the consolidation?

When you look to consolidate SQL Server environments, you have to bear in mind that you’re dealing with more than just the SQL Server instances and their databases. There are always a number of people and applications that rely on an instance of SQL Server, including websites, internal applications, third-party tools, and existing maintenance plans to name a few. The best way to determine what uses the SQL Server is to set a period of time when that SQL Server environment will be monitored to determine exactly who or what uses the SQL Server that will be moved or migrated before the project is undertaken. You’re going to need time to plan for outages, to schedule migrations and to simply communicate the venture to all of the appropriate parties.

What are the performance characteristics of the environments you’re consolidating?

If you consolidate high-traffic environments, its possible that you’ll be creating performance bottlenecks where there weren’t any before. Again, your best defense here is to gather enough data about those environments to ascertain these types of issues. Also, what about the hardware on the consolidation target? Having good, thorough performance data that includes the specs of the hardware you were monitoring is critical in defining the subsystem of the consolidation target environment. And crunching numbers is just the first step here - once you have the hardware configured, you’d be well-advised to stress test that environment, preferably using real databases and real application code. There are some good products in the marketplace from Microsoft, Quest and others that will help you simulate user loads using the application SQL you captured during your monitoring period.

What are the growth trends of the environments?

Whenever you deal with a SQL Server database, you’ll inevitably have to deal with database growth trends. Are you still allowing data and log files to auto-grow? This could pose a fatal problem in a consolidated environment where it was just a periodic performance-killer in the past. The 3-6 month monitoring period I suggested should also be used to determine, pretty accurately if you’re allowing that much time, how your databases grow. Whether you use complex algorithms or simple linear plotting, having an idea of your consolidated environments disk storage needs will help you order the right hardware and save a lot of time and hassle with Systems Administrators, Storage Administrators and management in the future.

What kind of maintenance windows will you have once you’ve consolidated?

Database backups, index maintenance, ensuring up-to-date statistics, and maybe creating data-marts are activities all DBAs are familiar with. But even though a DBA is familiar with maintenance plans, there is usually some degree of superstition or other “don’t touch it unless something breaks” attitude in every environment. Well, when you’re gearing up to perform a consolidation you’d better get familiar with what’s running and when. Conflicting backups are just one of many issues you could run into. The monitoring you’re performing will tell you when different activities are being performed and should expose when certain additional or different maintenance windows will be available. If you realize you won’t have time for maintenance under the current application and environment settings, you’ll need to adjust your consolidation plan because this is definitely a step you can’t ignore. Missing a backup is something every DBA understands the criticality of, but not maintaining indexes or statistics can ultimately lead to decreased performance, throughput and even to shorter maintenance windows.

Summary

In summary, you need to plan, monitor, diagnose, and test before you start consolidating environments. Good communication is important throughout the process, both to ensure that everyone that needs to be in the loop is, in fact aware, but also because this is one of the best ways to expose potential problems before they arise.

Continue Reading in Getting Bonuses from a Consolidation Project