配置新安装SQL Server实例的最佳做法

Often we install SQL Server on clients or we get to clients where they have pre-installed SQL Server Instances. As a DBA we have the Primary responsibility of keeping the Database Up and Running. This responsibility leads to optimizing, performance tuning and many other stuff related to database’s internal objects. While keeping the other aspects in mind many of us have to go through the configuration of the newly installed or pre-installed but un-configured instances. Configuring SQL Server newly instances are not an easy task and a DBA might want to configure an Instance in many ways.

通常，我们在客户端上安装SQL Server，或者到达客户端上已预安装SQL Server实例的客户端。作为DBA，我们的主要职责是保持数据库的正常运行。这种责任导致优化，性能调整以及与数据库内部对象有关的许多其他事项。在牢记其他方面的同时，我们中的许多人都必须经历新安装或预安装但未配置的实例的配置。重新配置SQL Server实例并非易事，DBA可能希望通过多种方式配置实例。

Here, I am going to discuss some basic and most important configurations of the SQL Server Instance and Database Level which might be ignored and can cause performance issues. Although this is not the complete list of configurations as that would be too long and it’s not a good idea to discuss all the settings under a single article but some of them are basic and critical for initial setup.

在这里，我将讨论SQL Server实例和数据库级别的一些基本和最重要的配置，这些配置可能会被忽略并可能导致性能问题。尽管这不是完整的配置列表，因为配置列表太长了，在一篇文章中讨论所有设置不是一个好主意，但是其中一些对于初始设置至关重要。

Mentioned below are the items discussed one by one which we should configure according to the organization’s or client’s requirement on a new instance.

下面提到的是一个接一个地讨论的项目，我们应根据组织或客户对新实例的要求进行配置。

1. SQL Server最大内存设置： (1. SQL Server Maximum Memory Setting: )

By default, SQL Server is allowed to use up to 2 Peta Byte maximum SQL Server Memory. As we all know, normally, we do not have this much of memory in the SQL Server Box so we need to keep this amount much lower than the default. So it would be good to keep 20% or 20 GB (whichever is lower) for the operating system and give rest of the amount to SQL Server by specifying that particular amount in MBs in the Maximum Server Memory option shown below:

默认情况下，允许SQL Server使用最多2 Peta Byte的最大SQL Server内存。众所周知，通常情况下，SQL Server Box中没有这么多的内存，因此我们需要使此数量比默认值低很多。因此，最好为操作系统保留20％或20 GB（以较低者为准），然后通过在“最大服务器内存”选项中指定以MB为单位的特定数量，将剩余的数量分配给SQL Server，如下所示：

2.数据库默认位置： (2. Database Default Locations:)

Ideally speaking we should configure the instance to have separate locations for Data, Log and Backup files. These default locations on instance will be used to auto fill the default location for creating new databases. Before assigning the locations make sure you have allocated enough space for the new databases and mention the default values for each option i.e. Data Files, Log Files and Backup Files as shown below:

理想情况下，我们应该将实例配置为具有分别用于数据，日志和备份文件的位置。这些实例上的默认位置将用于自动填充用于创建新数据库的默认位置。在分配位置之前，请确保为新数据库分配了足够的空间，并提及每个选项的默认值，即数据文件，日志文件和备份文件，如下所示：

3.最大并行度： (3. Maximum Degree of Parallelism: )

It’s one of the critical settings to be configured and setup. To oversimplify things, I would suggest to use the 8 as a thumb rule number. Just keep a simple formula in your mind that if you have 8 or more Logical Processor in one NUMA Node (Physical Processor) then use 8 as the MAXDOP settings number. If you have less than 8 Logical Processor in one NUMA Node, then use that number instead.

这是要配置和设置的关键设置之一。为了简化起见，我建议使用8作为拇指法则数字。只需记住一个简单的公式，即如果一个NUMA节点（物理处理器）中有8个或更多逻辑处理器，则使用8作为MAXDOP设置编号。如果一个NUMA节点中的逻辑处理器少于8个，则改用该数字。

4. TempDB文件增长和位置设置： (4. TempDB File growth and location settings: )

TempDB File growths are by default set in percentage, unlimited and created at C Drive and have only 1 Data file. This configuration is not appropriate though in newer versions of SQL Server 2016 or higher we have better defaults but not all the organizations use the latest version. So, first the TempDB should be on a separate drive preferably on an SSD with at least 4 files. The file growth has many views but I would prefer to go with extending the initial size of the TempDB file to be maximum possible like if you have 10 GB drive for TempDB then the Data files should be around 2 GB each for data file and 2 GB for log file. Here I want to be clear that I am not giving an equal amount of file size to data and log. The data files should all be equally sizes but log file can be different depending upon your needs.

默认情况下，TempDB文件的增长是按百分比设置的，不受限制，并且在C Drive中创建，并且只有1个数据文件。尽管在新版本SQL Server 2016或更高版本中，我们具有更好的默认设置，但并非所有组织都使用最新版本，因此此配置不合适。因此，首先，TempDB应该位于单独的驱动器上，最好在具有至少4个文件的SSD上。文件的增长有很多视图，但我更希望最大程度地扩展TempDB文件的初始大小，例如，如果您有10 GB的TempDB驱动器，那么数据文件的每个数据文件应该大约2 GB，而2 GB用于日志文件。在这里，我想清楚地说，我没有为数据和日志提供相同数量的文件大小。数据文件的大小均应相同，但日志文件可以根据您的需要而有所不同。

5.服务帐号 (5. Service Accounts)

The Service Account by default are NTService and this should be changed to a dedicated SQL Server Domain account which should have file and system level access. The Services like SQL Server Instance and SQL Server Agent should be separately handled. Keep in mind that changing the SQL Server Service account needs a Service Restart so change the SQL Server Service Accounts when the SQL Server is not deployed to production or if it’s already in production then do plan ahead the downtime. The downtime should not exceed couple of minutes but to be on the safer side get a 10-minute downtime if possible.

默认情况下，服务帐户是NTService，应将其更改为专用SQL Server域帐户，该帐户应具有文件和系统级别的访问权限。诸如SQL Server实例和SQL Server代理之类的服务应单独处理。请记住，更改SQL Server服务帐户需要重新启动服务，因此，如果未将SQL Server部署到生产环境中或已经投入生产，请更改SQL Server服务帐户，然后提前计划停机时间。停机时间不应超过几分钟，但为了安全起见，请尽可能地停机10分钟。

6. SQL Server端口 (6. SQL Server Port )

SQL Server uses the default port of 1433 which is widely known and most often DBAs don’t change it. This is again a bad practice to use the defaults. Although one can argue that the SQL Server Boxes are secured by firewalls and antiviruses and all that fancy stuff but still it would be good to change the default port to someone else and get an additional security.

SQL Server使用众所周知的默认端口1433，大多数DBA不会更改它。再次使用默认值是一种不好的做法。尽管可以说SQL Server Box由防火墙和防病毒软件以及所有这些花哨的东西保护，但是将默认端口更改为其他人并获得额外的安全性仍然是很好的。

You can change the port in the TCP/IP settings under the SQL Server Network Configuration settings by opening up the SQL Server Configuration Manager.

您可以通过打开SQL Server配置管理器来在SQL Server网络配置设置下的TCP / IP设置中更改端口。

7. SQL Server模型数据库设置： (7. SQL Server Model Database Settings: )

As you have set the default database file locations at instance level, now you also need to set the default settings for any new database you create. There are a lot of settings which can be tweaked here but some of them are critical which will benefit you in the longer term.

在实例级别设置默认数据库文件位置后，现在还需要为创建的任何新数据库设置默认设置。这里有许多设置可以调整，但其中一些至关重要，从长远来看将使您受益。

You can set the initial size of the Model database to be 10 GB, Auto Growth to be 1 GB instead of percentage (Prior to SQL Server 2016) or 64MB (SQL Server 2016) which causes problem like extended growths and high volume of VLFs.

您可以将Model数据库的初始大小设置为10 GB，将“自动增长”设置为1 GB，而不是百分比（SQL Server 2016之前）或64MB（SQL Server 2016），这会导致诸如扩展增长和VLF数量大的问题。

In Addition to the above mentioned settings you can also make the database level settings like Read Committed Snapshot Isolation Level and some of the newly introduced settings at database level (my personal favorites) like MAXDOP and Legacy Cardinality Estimator Options.

除了上述设置之外，您还可以进行数据库级别的设置，如“读取已提交的快照隔离级别”，以及一些新引入的数据库级别的设置（我个人最喜欢的设置），例如MAXDOP和旧基数估计器选项。

8.注册服务器和多服务器查询： (8. Registered Servers and Multi-Server Query:)

There is a very rare chance in a life of a Senior DBA that he/she gets a job in which only single instance is to be managed. If that is the case, you are blessed . But this is not the case with me. So, I have to deal with 20,30 or even 100s of instances at different clients. So, connecting, executing queries and deploying scripts on multiple instances if really a painful task. The solution is to use the Registered Servers and query your instances using Multi-Server Query option.

在高级DBA的生活中，他/她获得一份仅管理单个实例的工作的机会非常少。如果是这样，您是有福的。但这不是我的情况。因此，我必须在不同的客户端处理20,30甚至100个实例。因此，如果确实是一项艰巨的任务，则在多个实例上连接，执行查询和部署脚本。解决方案是使用“注册服务器”并使用“多服务器查询”选项查询实例。

First you need to create group based on your requirements like Development, Staging and Production as shown below. This option is available under View tab.

首先，您需要根据需求（例如开发，登台和生产）创建组，如下所示。该选项在“视图”选项卡下可用。

Right Click on the Local Server Groups and add appropriate Groups as shown below:

右键单击本地服务器组，然后添加适当的组，如下所示：

Then Right Click on the newly created group and add your Servers one by one as shown in the pictures below:

然后右键单击新创建的组，并如下图所示，一一添加您的服务器：

Finally, the multi-Server query is here. It’s really very simple and effective. Once you have registered your desired instances under one group. You simply right click on that particular group and click on the New Query option. Then a new query window will open as normal query window with one exception and that would be the Pink Bar in the connection status. That will give you the total servers and the connected servers count. For example, if you have 3 Servers and one of those is not available it will show you 2/3 meaning 2 servers are connected out of 3 total instances. So, if you have all the desired instances connected then you can issue any query which will be executed of all the instances and will give you results.

最后，这里是多服务器查询。这真的非常简单有效。将所需实例注册到一个组后。您只需右键单击该特定组，然后单击“新建查询”选项。然后，一个新的查询窗口将作为正常查询窗口打开，但有一个例外，那就是连接状态中的粉红条。这将为您提供服务器总数和已连接服务器的数量。例如，如果您有3台服务器，但是其中一台不可用，它将显示2/3，这意味着总共3台实例中有2台服务器已连接。因此，如果您连接了所有所需的实例，则可以发出将对所有实例执行的任何查询，并将为您提供结果。

So, now you have a handful of settings which you can configure beforehand and it will be a long term benefit for you and your organization. Though, you don’t get limited to these configurations only but most common are listed here. We always have special scenarios where we need customized solutions and that is why experienced DBAs are hired so we can always tweak these and other settings ahead.

因此，现在您有了一些可以预先配置的设置，这将对您和您的组织带来长期利益。但是，您不仅限于这些配置，而且这里列出了最常见的配置。我们总是在特殊的情况下需要定制的解决方案，这就是为什么要聘请经验丰富的DBA以便我们始终可以调整这些设置和其他设置的原因。

Enjoy!

请享用！

参考文献： (References: )

翻译自: https://www.sqlshack.com/best-practices-for-configuring-newly-installed-sql-server-instances/