SQL Server Consolidation Frequently Asked Questions and Answers

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.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


Considerations for SQL Server Instances and Databases for Virtualization


Virtualization has growing demand and implementation after many success stories. Previously SQL Server instances were installed on servers with their own physical hardware, but that also introduced challenges of effective usage of the available hardware. Over a period of time, the hardware has dramatic changes which made available high configurations for cheaper price and smaller sizes and servers have become more powerful and can support a lot of CPUs, high amount of memory in terabytes, support for high storage, local, SAN and SSDs. So, virtualizaiton helps in using these powerful tools in an effective manner by allowing to create multiple virtual machine guests on one host server, thus making the hardware usage effectively.

With latest virtualization technologies have their own virtualization operating system which is installed on top of hardware even before the installation of the Windows Server Operating Systems. Once the Virtualization OS is installed, now it allows creation of multiple virtual machines each with certain set of CPU, Memory and Storage. Each VM acts as independent server and any operating system can be installed and any applications can be installed, this helps segregate multiple applications running on server server to running each application on its own virtual machine. When it comes to SQL Server, where the SQL Server instances are spreads cross many different servers need to be consolidated and moved into their own virtual machine or combine multiple databases and host them on their own VM and SQL Server instance.

To consolidate multiple SQL Server instances on different physical servers into their own VM ot on to a single SQL Server instance, we will need to follow a proper approach or consider various options to choose what suits the requirement better. Below are some of the options to consider inorder to choose which databases, instances and servers can be consolidated.

Version upgrade paths – Need to understand if we can group the databases which does not have issues upgrading to higher versions as soon as one is available. If the databases cannot be upgraded and requires lot of planning and testing before hand then such databases are better of to have their own dedicated VMs.
Maintenance Windows – Need to group databases which can have maintenance performed at certain time, such can be hosted on same VM and which have different maintenance times, can be moved to their dedicated VMs.
Security – Databases or applications which require high security are better of hosted on their own VMs.
Performance – Critical servers which require high performance can be left running on its own physical hardware or on a server which is shared by non-critical applications to avoid other servers using high amount of resources from the host server.
High Availability – Databases or applications which are very critical and require high availability needs their own dedicated VMs.
Licensing – If licensing cost needs to be reduced, then need to try and group databases with similar characteristics on one SQL Server instance or few SQL Server instances on different VMS.
Backup and Restore – Databases or applications which require very good planning for recovering data and time taken to recover the data is very small, such are to be hosted on own servers.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


What is SQL Server Consolidation and the Benifits of it

What is SQL Server Consolidation?
Consolidation is a process where we combine various units into more efficient and stable larger units. We here talk about Database(SQL Server) consolidation, which is one of the important components in the IT environment. Databases are used in many business systems and in different departments, so it is possible that we may easily lose control of the number of databases that need to be maintained, because each group may simply create their own database for their requirement alone. This leads to many servers, many database instances and databases. Thus databases are one of the prime candidates for consolidation.

Benefits of SQL Server Consolidation
Reduce cost – Consolidation reduces the cost greatly on Hardware, Software, labor time, licenses, hosting, etc.

Reduce management overhead – It reduces the overhead of maintaining and managing activities like server, SQL Server Patching, taking care of security, Performing and maintenance of backups, etc.

Increase resources utilization – It is possible that some machines with higher configuration are under utilized and are almost idle, so it was logical to just move the instances/databases. Upgrading to fewer machines and newer hardware allows for reductions in rack space, power, and cooling needs and allows utilizing the resources more efficiently.

Upgrade – In IT industry, on periodic basis old hardware is retired and replaced with newer hardware. Consolidation should come into picture during those time to bring efficiency.

Compliance – During the consolidation, documenting the process and reviewing the security will get the systems under compliance. Using new software features will bring environment to more unification.

Bring things under control – There are many clients who are unaware where their data is stored, used by whom, managed by whom, etc. Consolidation will get things under control with fewer machines and documentation.

What are the levels of SQL Server Consolidation?
SQL Server consolidation can be done at various levels as mentioned below

Database – At Database Level, multiple databases used by different application will be moved/hosted on a single SQL Server Instance.

Disadvantage with this approach would be managing security issues at instance level where users of one application should not be able to access data of other application, need to ensure that all applications use the shared resources like tempdb, hardware and software resources efficiently.

Instance – At Instance Level, multiple databases used by different application which are part of separate instances on their dedicated servers are moved/hosted on a single Server with multiple SQL Server Instances.

Disadvantage with this approach would be managing Security at OS and Network level. Need to ensure that each instance does not use all the available hardware resources, causing problems to other instances.

Virtualization – In Virtualization level, multiple physical servers are removed and are hosted on a single physical machine as Virtual Machine and share the Hardware resources.

Disadvantage with this approach would be managing Virtualization like ensuring that all VMs are granted appropriate hardware resources.

All these three levels can be mixed to get optimal SQL Server Consolidation for your environment.

Happy Reading,
SQLServerF1 Team