The goals of the SQL Server Consolidation project are:
– Minimize licensing cost by consolidating servers.
– Reduce operational and maintenance costs.
– Modernize SQL Server version.
First step of the consolidation is to collect data(current situation), with regards to the applications that are dependent on SQL Server databases and the latest version which they support and process and effort of migrating applications to use new SQL Server on different system. To get this information, will need to involve application team or vendors, asking them right questions.
Some of the high level and common questions asked related to SQL Server Consolidation include:
– What is the latest SQL Server version this application supported by application or Vendor? like SQL Server 2008 R2, 2012, 2014, 2016.
– Is the application using any specific feature that is dependent of the Edition? Like transparent data encryption or online reindexing?, etc
– If possible, what are the recommended hardware values for this application? for CPU, Memory and disk IOPS?
– If we migrate to a newer version of SQL Server, do we also need a change in the application? like a service pack or update?
– If we migrate the database to a newer version, are there any backward compatibility requirement? Examples could be older SSIS packages, old syntax requiring us to run in an old compatibility mode.
– If we decide to place the database in a high available environment using Always ON, is there any specific requirement from the application side?
– Is there any specific recommendation or pointer for running the application in a virtual environment?
– Is this application using external code in the database in the form of CLR?
– Are there any specific database properties that we need to know of? Examples could be cross database chaining, read committed snapshots, etc.
Any considerations for or additional data needed before moving with consolidation?
– Performance baseline of the existing servers and application response times. This can be achieved by running performance monitor counters for a week or so and prepare some graphs showing the current resource usage.
– Although it is always preferred to go with latest SQL Server version, but due to application requirements, it is not possible in which case, we can go with multiple SQL Server instances installed side by side and each running different version. It is important to keep in mind that this will cause additional complexity to the consolidation as we need to keep in mind some challenges related to having a common collation suited for the databases, performance of one instance can impact other instances on the server, having different versions will need separate license for each version, shared components failing from time to time, OS patching or other tools updated on the server can impact other instances or applications, etc.
Can all older databases be consolidated?
Technically yes, but depends on migrating to which version of SQL Server to which version, but we can use stage servers to migrate first and then to final version. Important thing is to check with the application team or vendors regarding the support to the SQL Server version and edition. From operations side, we can run upgrade advisor or upgrade assistent to get analysis of the current database and see which objects will be impacted due to migration. It is always advised to run tests in a UAT environment just to make sure the application is working as expected.
Is is suggested to implement high availability? Is it feasible or beneficial without causing too much additional admin or support overhead?
Having High Availability like AlwaysON Availability groups or other technologies is always a good practice, and this will also depend on the SLA. We could create set of databases, the ones that need HA and the ones that don’t High availability and may be we can consider have 2 servers, 1 standalone with Standard Edition and 1 cluster with Enterprise edition. The SLA will determine how much time you have to recover incase of an error. Having HA allows you to recover in minutes, not having it might force you to recover in hours and doing manual work, it would be good to align the strategy with the business need. There are small applications that can be down for 8 hours without causing a big issue, for these you could just resort to take backups in a remote location and also to tape and then use a RAID with hot swapping, but if the application can’t be down for more than 30 minutes, then HA might be an obligation.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.