Artemakis Artemiou

SQL Server consolidation – Hosting multiple databases on a single SQL Server instance

December 2, 2016 by

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.


Figure 1: SQL Server Database Consolidation Example

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.


Figure 2: DBA Security Advisor – SQL Instance Connection Dialog



Figure 3: DBA Security Advisor – Security Checks Dialog



Figure 4: DBA Security Advisor – Sample Report

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:


Artemakis Artemiou

Artemakis Artemiou

Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has 15 years of experience in the IT industry in various roles. Artemakis is the author of The SQL Server and .NET Blog eBook Series which features three free eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally, he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com and blog at: aartemiou.blogspot.com
Artemakis Artemiou
SQL Database design

About Artemakis Artemiou

Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has 15 years of experience in the IT industry in various roles. Artemakis is the author of The SQL Server and .NET Blog eBook Series which features three free eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally, he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com and blog at: aartemiou.blogspot.com

383 Views