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:
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.
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:
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:
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.
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.
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.
3 Answers 3 Sorted by:
Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.
It falls back to sorting by highest score if no posts are trending. 3
Yes, you can give the installations different names. I think you have to choose “advanced options” on installation to be able to set the name.
yep, they need to be named instances though. Run the setup but when you get to the instance name – add a new name for the instance.
Yes, we can use multiple instance of SQL Server of same machine/server.
All instances of the database engine other than the default instance are identified by an instance name specified during installation of the instance.
Thanks for contributing an answer to Stack Overflow!
To learn more, see our tips on writing great answers. Draft saved Draft discarded
Components specific to an instance of SQL Server
Some SQL Server components or services are specific to an instance of SQL Server. These are also known as instance-aware. They share the same version as the instance that hosts them, and are used exclusively for that instance.
Examples: Database Engine, Analysis Services, and Reporting Services.
Components shared by all versions of SQL Server
Certain components are shared by all instances of all installed versions of SQL Server. When you install different versions of SQL Server side by side on the same machine, these components are automatically upgraded to the latest version. Such components are usually uninstalled automatically when the last instance of SQL Server is uninstalled.
Examples: SQL Server Browser and Microsoft SQL Server VSS Writer.
FAQ
Can we have 2 SQL Server on the same machine?
How do I install multiple instances of SQL Server on the same computer?
What is the benefit of installing multiple SQL Server instances on a single server?
How do I connect to multiple SQL Servers?
