SQL Server整合–在单个SQL Server实例上托管多个数据库

介绍 (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.

整合SQL Server数据库和实例是一种做法，在一定条件下，它可以使组织从中受益，因为他们可以减少许可成本，如果设计得当，还可以减少硬件成本。

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.

上述声明中使用的关键词是“根据某些标准”。 通常，SQL Server数据库和实例的合并在大型组织中进行，在大型组织中许可和基础结构成本可能很高。 但是，即使整合可以降低成本（如果设计不当），也可能导致安全漏洞，性能下降甚至服务中断。

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
  在单个SQL Server实例上托管多个数据库
• Host multiple SQL Server instances on a single machine
  在一台计算机上托管多个SQL Server实例
• Host multiple virtual SQL Server machines on a single physical machine
  在单个物理计算机上托管多个虚拟SQL Server计算机

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.

本文主要关注在单个高可用性SQL Server实例上承载多个数据库的合并类型，并提出一种评估候选数据库的方法。 合并SQL Server实例时，也可以使用本文中描述的某些原理。

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.

数据库整合过程的一个示例是在3个SQL Server实例中的每个实例上拥有3个数据库，并在将它们合并为一个SQL Server实例后，最终9个数据库共享相同的硬件资源，例如CPU，RAM，I / O，网络等

The main benefits of SQL Server consolidation are:

SQL Server合并的主要好处是：

• 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.

尽管SQL Server合并是经常使用的做法，但是为了获得上述好处，您需要彻底评估每个候选SQL Server数据库（或实例）进行合并。 您的主要目标必须是，在整合SQL Server数据库和实例之后，至少要具有与以前相同的性能，安全性和稳定性，如果不是更多的话。

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
  SQL Server实例版本和数据库兼容性级别
• 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:

在为安全因素开绿灯之前，您需要检查候选SQL Server数据库和实例是否使用了可能不想迁移到统一环境的任何功能。 例如，您应该检查：

• 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.
  谁拥有SysAdmin访问权限？ 真的有道理吗？ 请注意，如果以及在提供SysAdmin访问权限时，您应该格外谨慎。 在大多数情况下，您可以通过仅授予数据库对象显式权限来避免这种情况。
• The permissions of SQL logins.
  SQL登录的权限。
• 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.
  用于SQL Server数据库引擎和代理服务的服务帐户，以及它们在实例和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.

关于安全评估因素，可以使用一些工具来使您更容易收集SQL Server实例及其数据库的与安全相关的信息。 这些工具之一是DBA Security Advisor。 DBA Security Advisor是用于SQL Server的安全评估工具，具有30多项安全检查。 该工具使您可以对多个SQL Server实例运行安全评估，并生成具有检测到的安全风险的安全报告。 此外，它还提供有关如何减轻这些风险的建议。

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.

在生成的报告中，您将能够访问有关SQL Server实例和数据库的关键安全信息。 当涉及到安全因素时，此信息可以帮助您评估SQL Server数据库（或完整实例）是否可以进行合并。 例如，如果候选SQL Server实例启用了“数据库邮件XP”和“ xp_cmdshell”以适应特定的业务需求，则这可能是在合并该实例的数据库之前三思而后又没有先决定如何处理这两项功能的原因。 。

高可用性和灾难恢复 (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.

将集中式/合并式环境的高可用性和灾难恢复设置作为基准，您应该检查是否存在候选/将要合并SQL Server上的数据库（以及应用程序）的恢复点目标和恢复时间目标例如，如果在集中式系统上移动，则可以容纳。 此外，您应该检查候选SQL Server实例的当前高可用性设置，并查看是否可以在集中式系统上移动其正常运行时间保证/ SLA。

资源利用率 (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.

合并的优点之一是可以共享资源。 但是，如果大小不正确，则会在集中式系统上造成严重的性能问题。 例如，如果您整合了3个数据库，并且每个数据库在其专用实例上都充分利用了8 GB RAM，那么您应该将其与集中式服务器操作系统的RAM要求一起考虑在内。 在这种情况下，您的集中式数据库服务器至少应具有28 GB的RAM（为满足集中式SQL Server实例的需要3 x 8 GB，为操作系统提供4 GB）。 请注意，以上数字仅用于说明目的。 您应该始终根据数据库和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.

CPU使用率，I / O要求（IO /秒和数据传输速率MB /秒）以及网络要求必须遵循相同的做法。 为了获得上述数据的代表性统计信息，您需要对所有候选数据库/ SQL Server实例的操作进行适当的分析。 有有用的监视工具可以帮助您解决此问题，但是请确保您为数据库或支持合并的实例所支持的应用程序分析了至少一个完整业务周期的工作负载。

SQL Server版本和数据库兼容性级别 (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.

候选SQL Server实例的版本及其数据库的兼容性级别在合并的设计过程中也起着重要作用。

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:

为了更好地理解这一点，请考虑一种方案，您计划将3个SQL Server实例的所有数据库合并到一个集中式SQL Server 2016 SP1实例中。 SQL Server 2016为SQL Server 2014、2012和2008 / R2提供向后兼容性支持。 如果在一个候选SQL Server实例中您具有具有SQL Server 2005兼容性级别的数据库，则必须从以下两个选项中选择一个：

• 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.
  将这些数据库的兼容性级别升级到集中式SQL Server实例支持的更高的兼容性级别，当然，在测试相关应用程序并确认它们支持更高的兼容性级别之后，再进行升级。
• Disqualify these candidate SQL Server databases from the consolidation process.
  从合并过程中取消这些候选SQL Server数据库的资格。

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.

因此，对于任何SQL Server整合设计过程，SQL Server实例版本和数据库兼容性级别绝对是您需要考虑的事项之一。

特殊维护需求 (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.

当您将数据库移至集中式SQL Server实例时，维护操作的灵活性受到某种程度的限制。 因为集中化实例承载许多其他数据库，所以在特定数据库需要影响整个SQL Server实例的任何与维护相关的操作的情况下，那么在合并的设计过程中还需要考虑这一点。

许可费用 (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.

要考虑的另一个因素是许可成本。 例如，如果您有3个运行Enterprise EditionSQL Server实例，则通过将其所有数据库整合到一个实例中，可以节省许可成本。 但是，如果有3个实例在Standard Edition上运行，则在创建集中实例时，您可能会考虑将Enterprise Edition用于集中框，以便在涉及高可用性时有更多选择，以及克服了非企业版的其他一些限制。 无论如何，您应该通过比较许可成本来进行数学计算。

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:

此时，必须为SQL Server 2016添加特别说明。SQLServer 2016的Service Pack 1（SP1）发行版带来了突破性的变化。 它解锁了直到最近才在SQL Server企业版中可用的高级功能。 现在，除了具有SP1SQL Server 2016企业版之外，标准版，Web版和Express版还具有以下高级功能：

• Row-Level Security
  行级安全
• Dynamic Data Masking
  动态数据屏蔽
• Database Snapshot
  数据库快照
• Columnstore
  列存储
• Partitioning
  分区
• Compression
  压缩
• In-Memory OLTP
  内存中OLTP
• Always Encrypted
  始终加密
• PolyBase
  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).

此外，SQL Server 2016 SP1的标准版和Web版与企业版一起支持更改数据捕获（Express Edition中不支持，因为它需要也不支持SQL Server代理）。

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.

为此，在评估许可成本和考虑SQL Server的许可选项时，请结合这一令人振奋的新发展，并结合使用您需要使用的任何高级功能。

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.

注意：尽管上述功能在带有SP1SQL Server 2016的标准版，Web版和Express版中可用，但请注意，企业版与其他版本之间的其他限制仍然存在。 例如故障转移群集，可用性组等中的节点数。

硬件成本 (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.

该因素与“资源利用率”因素有关。 资源利用率的真正解决方法是设法获得全面了解，并预见集中式SQL Server实例/计算机上的资源是否可以在不影响性能的情况下在数据库之间共享。 因此，在上一个示例中，在3个独立实例上使用3个候选数据库，每个实例均使用8GB RAM，如果您设法“看到”它们实际上一直不需要此数量的RAM，而是向其添加3 x 8GB RAM集中式系统，在不影响数据库性能的情况下添加更少的内容，只有在这一点上，您才能设法节省硬件成本而不影响性能。 其他硬件资源（例如CPU，I / O，网络等）也一样。

结论 (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.

整合SQL Server数据库或什至完整实例不仅是减少许可和可能的硬件成本的一种方式，而且是一种更轻松地实施不同策略（例如针对大型数据库的高可用性和灾难恢复）的方法。 但是，如果设计不当，则合并可能会导致严重的性能问题，甚至在集中式系统上造成服务中断。 为此，您始终需要在所有方面全面分析候选SQL Server实例和数据库。 本文提出了在SQL Server整合设计过程中需要考虑的主要因素。 仔细计划您的合并策略可以确保合并数据库的稳定性和性能，并节省各种成本。

参考文献： (References:)

• SQL Consolidation Planning and recommended practicesSQL整合计划和建议的做法
• SQL Server Consolidation GuidanceSQL Server合并指南
• Consolidating SQL Server Databases整合SQL Server数据库

翻译自: https://www.sqlshack.com/sql-server-consolidation-hosting-multiple-databases-single-sql-server-instance/