SQL Server Consolidation

Nowadays I deal with some SQL Server consolidation engagements. Many customers have hundreds of SQL Server instances with many different versions (7.0, 2000 SPx, 2005 SPx) and they have some difficulties managing these due to the over-distribution fact.

The main idea is to consolidate these SQL Server instances to central servers so that

  • you have the ability to manage them easier

  • and have better utilisation of shared resources (cpu, memory etc)

Compared to OS Virtualisation I believe application consolidation (Database, Web application etc) have many advantages (maybe I should write another post for this as well)

We use some tools to make our lives easier

  • Microsoft Software Inventory Analyzer – To locate the SQL Server instances (believe me many times they are more than you are told)

  • SQLH2 – To collect detailed information on these SQL Server instances

  • SQLH2 Performance Collector – To centrally collect performance counters

  • SQL Server Upgrade Advisor – To detect issues for possible 2000 to 2005 upgrades

  • A custom tool we created to to prevent some manual tasks (mostly editing config files, loading data etc)

Comments (2)

  1. You’ve been kicked (a good thing) – Trackback from DotNetKicks.com

  2. andyjball@hotmail.com says:

    interesting stuff . Two questions here

    1. Would be nice to see a document highlighting benefits of SQL Server 2008 over 2005 for consolidation

    – ie a lot of shops that have put off upgrading to 2005 from 2000 will now be thinking more seriously about it given 2000 is now in Extended Support .. Resource Governor is an obvious benefit

    2. Running SQL Server in Production on Hyper-v

    whether this becomes an attractive solution given the superior performance of Hyper-V (over say Virtual Server) . Obviously doesn’t help with instance sprawl but potentially gives a bit more flexibility in ramping up / down processor / memory resources for non-linear workloads – ie end of month processing etc.

    keep it real