SQL Server consolidation & keeping up performance at the same time

So what are your options in Server consolidation and not to harm the performance for the applications that are happily sitting across your data center and you have this mammoth task to consolidate the servers to save physical space etc. 

No doubt that Server Consolidation and avoiding performance loss is a major job for a Systems Administrator and DBA. In case you have already multiple SQL Server instances that are need to be migrated to a consolidated environment or using third party tools such as Polyserve then there are various factors involved to take care.

First thing to check is performance monitoring, capturing the SQL Server database usage using SYSMON (PERFMON) is best option to start, all the issues are dependant on the CPU, memory and Disk configuration. If you collect the information from SYSMON on one server you can’t correlate the same with other server because your server specification may not the same as the source or between the multiple servers.

There are few counters you can get information on the high-level performance monitoring by using:

Transactions/sec, Physical Disk - Disk Avg. Reads, Disk Avg. Writes, CPU, Number of users. For the detailed file-statistics usage refer to SQLDEV article and this IOStatistics reference.

Further to the above reference again you have to identify each and every information on the servers for software, application, services, SLAs and users connectivity etc. before even planning such actions. Review this Ebook  that will bring you the actions and reactions involved in such exercises and also ProsCons article. If you are running various applications that are needed a consolidation task then where many services are running under distinct user accounts, be aware that this may exhaust the desktop heap resources and you may need to do some tuning in order to ensure that services are able to start successfully.

Each Windows station can contain zero or more desktops and each desktop object has a desktop heap associated with it. A finite amount of desktop heap is available to Windows, and when the available desktop heap has been exhausted, failures may occur when starting the services. The performance of SQL Server in consolidated environments depends greatly on the hardware you are using and on the characteristics of the SQL Server workloads that are involved. Additionally do not forget to refer the Technet paper on the consolidation.

Further the server & data migrations are time-consuming and without a planning it is very hard to implement, or even test them before. So it is best to test a server consolidations and virtualization using a test platform with a small application, the access of application should be based on logical view and not on a physical location. All the changes must be made without impacting the live systems.

There are many third party tools out-there provides such flexibility of virtualization such as Polyserve and with minimum capital expenditure and they must be tested and refer to the reviews on web before even going towards the evaluation edition.