配置新安装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:

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