What is SQL Server Consolidation? You can simply assume that SQL Server databases in different instances from different servers come and reside together at one high performance server hardware.
Early during consolidation project, we always need to find some answers for some questions by assessment such as
- What is the resource usage per SQL Server instance?
- What is the overall performance of the server?
- When is the peak hours for SQL Server?
- Do we need to keep all databases in one hardware?
- Do we need to upgrade databases as well?
- Do we need to change application codes?
- Do we need to use virtualization?
- Do we need to architecture base on databases or instances?
And a lot more questions. Any mistake in this early stage can cause project to fail.
After all those questions answered by assessment result, the next step is to implement and kick off the project. In this stage we always face some difficulties, for example during consolidation some of the databases are in need to be up all the time! or the security credentials need to be migrate without any changes! And also the migration time is limited!
In the last stage of consolidation project, you will face some challenges such as database performance, security hardening, high availability, disaster recovery and in some cases OLAP performance degradation, which needs to be settled otherwise the consolidation project is considered fail project.