Introduction
Consolidating SQL Server databases and instances is a practice which, under certain criteria, allows organizations to benefit as they can reduce licensing costs, and if properly designed, hardware costs.
The key phrase used in the above statement is “under certain criteria”. Usually, consolidation of SQL Server databases and instances takes place in large organizations where the licensing and infrastructure costs can be very high. However, even though consolidation can reduce costs, if not properly designed, it can lead to security holes, performance degradation and even service disruption.
First let’s define what database consolidation is. Database consolidation is the process of centralizing multiple databases and instances in order to share resources and thus, among other, cut licensing and hardware costs. There are three types of consolidation:
- Host multiple databases on a single SQL Server instance
- Host multiple SQL Server instances on a single machine
- Host multiple virtual SQL Server machines on a single physical machine
This article mainly focuses on the consolidation type of hosting multiple databases on a single, highly available SQL Server instance and suggests a methodology of assessing the candidate databases. Some of the principles described in the article can also be used when consolidating SQL Server instances as well.
An example of a database consolidation process would be having 3 databases on each of 3 SQL Server instances and after consolidating them to end up with a single SQL Server instance with 9 databases sharing the same hardware resources such as CPU, RAM, I/O, network, etc.
The main benefits of SQL Server consolidation are:
- Reduce licensing cost
- Easier high availability and disaster recovery
- Share hardware resources/reduce hardware cost
- Centralization
Even though SQL Server consolidation is a practice used often, in order to have the above benefits, you need to thoroughly assess each candidate SQL Server database (or instance) for consolidation. Your primary goal must be, after consolidating SQL Server databases and instances, to have at least the same level of performance, security and stability as before, if not more.
To this end, prior to consolidating a database or a full instance, you need to assess it against a list of factors such as:
- Security
- High Availability and Disaster Recovery
- Resource Utilization
- SQL Server Instance Versions and Database Compatibility Levels
- Special Maintenance Needs
- Licensing Cost
- Hardware Cost
Let’s see the above factors in more detail.
Security
Prior to give the green light for the security factor, you need to check if the candidate SQL Server databases and instance use any features that you might not want to migrate to the consolidated environment. For example, you should check:
- Who has SysAdmin Access? Is it really justified? Note than you should be extremely cautious if and when providing SysAdmin access. In most of the cases you can avoid that by giving only explicit permissions to database objects.
- The permissions of SQL logins.
- Any surface area features that might be enabled and how they are used.
- The service accounts that are used for the SQL Server database engine and agent services and what privileges they have on the instance and OS.
Regarding the security assessment factor, there are tools you can use in order to make it easier for you to collect security-related information for SQL Server instances and their databases. One of those tools is DBA Security Advisor. DBA Security Advisor is a security assessment tool for SQL Server with more than 30 security checks. The tool allows you to run security assessments against multiple SQL Server instances and generates security reports with the detected security risks. Furthermore, it provides recommendations on how you can mitigate these risks.
In the generated report you will be able to access critical security information about your SQL Server instances and databases. This information can help you assess, when it comes to the security factor, whether your SQL Server databases (or the full instance) can be candidates for consolidation or not. For example, if the candidate SQL Server instance has “Database Mail XPs” and “xp_cmdshell” enabled for accommodating specific business requirements, this could be a reason to think twice prior to consolidating this instance’s databases without first deciding what to do with those two features.
High availability and disaster recovery
Having as a baseline the high availability and disaster recovery setup of the centralized/consolidated environment, you should check if the Recovery Point Objectives and Recovery Time Objectives for the databases (and thus applications) hosted on the candidate/to-be-consolidated SQL Server instance, can be accommodated if moved on the centralized system. Furthermore, you should check the current high availability setup of the candidate SQL Server instance and see if the uptime guarantee/SLA can be accommodated as well if you move its databases on the centralized system.
Resource utilization
One of the advantages of consolidation is that you can share resources. However, if not properly sized, this can create severe performance problems on the centralized system. If for example you consolidate 3 databases and each one of them was fully utilizing 8 GB RAM on its dedicated instance, then you should take this into consideration along with the RAM requirements for the OS of your centralized server. In this case, your centralized database server should at least have 28 GB RAM (3 x 8 GB for your centralized SQL Server instance’s needs, and 4 GB for the OS). Note that the above numbers are just used for illustration purposes. You should always calculate the required resources based on the real needs of your databases and OS.
The same practice must be followed for CPU utilization, I/O requirements (IO/sec and Data Transfer Rate MB/sec) as well as for Network requirements. In order to get representative statistics for the above, you need to perform a proper analysis of the operation for all candidate databases/SQL Server instances. There are useful monitoring tools that can help you with that, but make sure that you analyzed the workload for at least one full business cycle for the applications supported by the databases or instances that are candidates for consolidation.
SQL Server versions and database compatibility levels
The versions of the candidate SQL Server instances and the compatibility levels of their databases, also play a significant role in consolidation’s design process.
In order to better understand this, consider a scenario where you plan to consolidate all the databases of 3 SQL Server instances to one centralized SQL Server 2016 SP1 instance. SQL Server 2016 provides backward compatibility support for SQL Server 2014, 2012 and 2008/R2. If in one of your candidate SQL Server instances you have databases with SQL Server 2005 compatibility levels, you will then have to choose one from the below two options:
- Upgrade the compatibility levels of these databases to a newer compatibility level which is supported by the centralized SQL Server instance, after of course you test the relevant applications and certify that they support newer compatibility levels.
- Disqualify these candidate SQL Server databases from the consolidation process.
So, SQL Server instance versions and database compatibility levels is definitely one of the things you need to take into consideration for any SQL Server consolidation design process.
Special maintenance needs
By the time you are moving a database onto a centralized SQL Server instance, your flexibility of maintenance actions is somehow limited. Because the centralized instance hosts many other databases, in the case where the specific database requires any maintenance-related actions that affect the entire SQL Server instance, then this is something that you also need to take into consideration during the consolidation’s design process.
Licensing cost
Another factor to take into consideration is the licensing cost. If for example you have 3 SQL Server instances running Enterprise Edition, then by consolidating all their databases onto a single instance you can save licensing costs. If, however you have 3 instances that run on Standard Edition, by the time you are creating a centralized one, you might consider using Enterprise Edition for the centralized box, in order to have more options when it comes to high availability, as well as overcome some other limitations of the non-Enterprise editions. In any case, you should do the math by comparing licensing costs.
At this point, a special note must be added for SQL Server 2016. The release of Service Pack 1 (SP1) for SQL Server 2016 brought breakthrough changes. It unlocked advanced features that until recently were only available in the Enterprise Edition of SQL Server. Now, along with the Enterprise Edition of SQL Server 2016 with SP1, the Standard, Web and Express Editions have the following advanced features available as well:
- Row-Level Security
- Dynamic Data Masking
- Database Snapshot
- Columnstore
- Partitioning
- Compression
- In-Memory OLTP
- Always Encrypted
- PolyBase
- Fine Grained Auditing
- Multiple File Stream Containers
Furthermore, along with the Enterprise Edition, the Standard and Web editions of SQL Server 2016 SP1 support Change Data Capture (not supported in Express Edition because it requires SQL Server agent which is also not supported).
To this end, please take into consideration this exciting new development when assessing the licensing costs and considering licensing options for SQL Server, in combination with any advanced features you need to use.
Note: Even though the above features are available in the Standard, Web and Express Editions of SQL Server 2016 with SP1, note that other limitations between the Enterprise and other editions are still in place. For example number of nodes in failover cluster, availability groups, etc.
Hardware cost
This factor is related to the “Resource Utilization” factor. The real deal with resource utilization is to manage to get a thorough view and foresee if the resources on the centralized SQL Server instance/machine can be shared among databases up to a point without affecting performance. So, in the previous example with the 3 candidate databases on 3 standalone instances using 8GB RAM each, if you manage to “see” that they do not actually need this amount of RAM all the time and instead of adding 3 x 8GB RAM to the centralized system, add less without affecting the databases performance, then it is only at this point where you will manage to save hardware cost without affecting performance. The same stands for other hardware resources as well such as CPU, I/O, network, etc.
Conclusion
Consolidating SQL Server databases or even full instances is a way of not only cutting licensing and possibly hardware costs, but also a way of easier enforcing different policies such as high availability and disaster recovery for large sets of databases. However, if not properly designed, consolidation might cause serious performance problems or even service disruptions on the centralized system. To this end, you always need to thoroughly analyze the candidate SQL Server instances and databases in all aspects. This article suggests the main factors you need to take into consideration during SQL Server consolidation design process. Carefully planning your consolidation strategy can ensure stability and performance for the consolidated databases along with saving various costs.
References:
- SQL Consolidation Planning and recommended practices
- SQL Server Consolidation Guidance
- Consolidating SQL Server Databases
View all posts by Artemakis Artemiou
- Certificate Management in SQL Server 2019 - May 31, 2019
- SQL Server consolidation – Hosting multiple databases on a single SQL Server instance - December 2, 2016
- How to create and manage T-SQL code snippets - October 28, 2016