安装Microsoft SQL Server之后的最佳做法-CSDN博客

介绍 ( Introduction )

Working as a DBA, the simplest task requested from you is installing SQL Server. As a start you will check the server’s hardware and software specs to make sure that you can start the installation, checking which services will be installed and the security authentication type required. After this pre-installation plan, you will start the normal installation process.

作为DBA，您要求的最简单的任务是安装SQL Server。首先，您将检查服务器的硬件和软件规格，以确保可以开始安装，检查将安装哪些服务以及所需的安全身份验证类型。完成此预安装计划后，您将开始正常的安装过程。

After completing the installation, you need to apply specific configurations on the server to make sure that the server can host production databases and the users can connect to it successfully.

完成安装后，您需要在服务器上应用特定的配置，以确保服务器可以承载生产数据库并且用户可以成功连接到该数据库。

In this article, I will describe the common configurations that you should apply after the SQL Server installation.

在本文中，我将介绍在安装SQL Server之后应应用的常见配置。

使用最新的Service Pack和更新修补SQL Server ( Patch the SQL Server with the latest Service Pack and Updates )

Once you finish the SQL Server installation successfully, you need to look for the latest Service Pack and Cumulative Update, which are available on the Microsoft Download website. In this way you can make sure that your server is patched with the latest version of Microsoft SQL Server and most of the old bugs are fixed. Reboot your SQL Server after each Service Pack or Cumulative Update installation.

成功完成SQL Server安装后，您需要查找最新的Service Pack和累积更新，这些更新可在Microsoft下载网站上找到。这样，您可以确保服务器已使用最新版本的Microsoft SQL Server进行了修补，并且大多数旧错误已得到修复。每次安装Service Pack或累积更新后，重新启动SQL Server。

禁用不需要的服务 ( Disable Unneeded Services )

Deciding the SQL components to be installed is part of the pre-installation plan. But the requirements could be changed with time. So, it is better to check the SQL Server services installed on your server, and disable any installed service that will not be used. If you install a default SQL instance and will not install another named instance on your server, disable the Browser service. In order to apply these changes to the SQL Services, it is better to use the SQL Server Configuration Manager.

确定要安装SQL组件是预安装计划的一部分。但是需求可能会随着时间而改变。因此，最好检查服务器上安装SQL Server服务，并禁用所有将不使用的已安装服务。如果安装默认SQL实例，并且不会在服务器上安装另一个命名实例，请禁用浏览器服务。为了将这些更改应用于SQL Services，最好使用SQL Server配置管理器。

Disable SQL Server VSS Writer service if you are not using an application that backup SQL databases with the Windows Volume Shadow Copy mechanism. This can be done from the Services within the Control Panel.

如果不使用通过Windows卷影复制机制备份SQL数据库的应用程序，则禁用SQL Server VSS Writer服务。这可以通过“控制面板”中的“服务”来完成。

更改服务启动模式 ( Change the Service Start Mode )

If you are using SQL Server Maintenance Plans, SQL Server Replication or any SQL Agent jobs, it is recommended to change the start mode of the SQL Agent and the SSIS services to Automatic, so on each server restart the service will start automatically. You can configure the start-up property of these services from the SQL Configuration Manager.

如果使用的是SQL Server维护计划，SQL Server复制或任何SQL Agent作业，建议将SQL Agent和SSIS服务的启动模式更改为自动，这样在每台服务器重新启动时，该服务将自动启动。您可以从SQL配置管理器配置这些服务的启动属性。

指定服务恢复 ( Specify the Service Recovery )

The “Recovery” tab of the service property allows you to define how the services should behave if they fail. From the service property in the Services within the Control Panel, you can decide what action the service will take a specific action after the first, second or subsequent failure as below:

服务属性的“恢复”选项卡允许您定义服务失败时的行为。从“控制面板”中“服务”中的服务属性，可以确定在第一次，第二次或后续失败后，服务将采取什么特定操作，如下所示：

Also from the SQL Server Agent’s property page. You can ensure that SQL Service or SQL Server Agent service will restart automatically “if they stop unexpectedly”.

同样从SQL Server代理的属性页。您可以确保“如果它们意外停止”，则SQL Service或SQL Server代理服务将自动重新启动。

更改SQL默认目录 ( Change the SQL Default Directories )

The location of SQL Server files is set during the SQL Server installation process, that can be provided or the SQL engine will use the default locations. After completing the installation, you still have the ability to change these default locations.

SQL Server文件的位置是在SQL Server安装过程中设置的，可以提供该位置，否则SQL引擎将使用默认位置。完成安装后，您仍然可以更改这些默认位置。

Following the best practice, it is better to isolate the user database’s data files (.mdf) from the transaction log files (.ldf) in a separate physical drives and folders. The same should be applied for the backup files and the TempDB files location.

按照最佳实践，最好将用户数据库的数据文件（.mdf）与事务日志文件（.ldf）隔离在单独的物理驱动器和文件夹中。备份文件和TempDB文件的位置应相同。

You can configure the locations of the Data, Log and Backup from the Database Settings in the SQL Server Properties:

您可以从“ SQL Server属性”中的“数据库设置”配置数据，日志和备份的位置：

To make sure that the changes take effect, restart the SQL Service.

为确保更改生效，请重新启动SQL Service。

设置数据库属性 ( Set the Database Properties )

The model system database can be defined as “template” for all newly created user databases. In order to set common properties across all new databases, set them in the model database.

可以将模型系统数据库定义为所有新创建的用户数据库的“模板”。为了在所有新数据库中设置通用属性，请在模型数据库中进行设置。

Depending on the business needs of your company, you can decide the Recovery Model of your databases, such as using the SQL Mirroring, TRN backup or Always ON Availability groups. So, to make all new databases with Full or Simple Recovery Model, just change the model database with that Recovery Model. The same applied if you want to configure these databases with a specific Auto Growth configuration, which is, by default, 1 MB growth of the data files and ten percent of the log file size.

根据公司的业务需求，您可以确定数据库的恢复模型，例如使用SQL镜像，TRN备份或Always ON可用性组。因此，要使所有新数据库具有完整或简单恢复模型，只需使用该恢复模型更改模型数据库。如果要使用特定的“自动增长”配置来配置这些数据库，则默认情况是相同的，默认情况下，数据文件增长1 MB，日志文件大小的百分之十。

If you have an expectation about how big the data and log files will be and grow, disable the Auto Growth and fix the data and log files size of the model database. It is better to set the increments of the growth to be in megabytes rather than percentage.

如果您对数据和日志文件的大小和增长有期望，请禁用“自动增长”并修复模型数据库的数据和日志文件的大小。最好将增长增量设置为兆字节而不是百分比。

TempDB数据库最佳实践 ( TempDB Database Best Practices )

The TempDB database is used to store the user objects, internal objects, and version stores.

临时数据库数据库用于存储用户对象，内部对象和版本存储。

As a best practice, place the TempDB database files in a separate disk drive in order to isolate its heavy workload from the other physical storages.

最佳做法是放置TempDB 数据库文件放在单独的磁盘驱动器中，以便将其繁重的工作负载与其他物理存储区分开。

To change the path of the TempDB use the below T-SQL code, then restart the SQL service, so the files will be created on that new path:

要更改TempDB的路径，请使用下面的T-SQL代码，然后重新启动SQL服务，因此将在该新路径上创建文件：

 
USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\MSSQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'D:\MSSQLData\templog.ldf');
GO

The number of allocated data files in the TempDB database depends on the logical or physical CPUs number on your server, which can be viewed from Windows Task Manager’s Performance tab. Having four CPUs in your physical server, you should allocate four data files for your TempDB.

TempDB中分配的数据文件数数据库取决于服务器上的逻辑或物理CPU编号，可以从Windows任务管理器的“性能”选项卡中查看。物理服务器中有四个CPU，应为TempDB分配四个数据文件。

Also, the TempDB data files should have the same size and growth as it used in a proportional fill mechanism. Having two data files in your database, one 2 GB and the other one is 4 GB, Each set of data written to the first 2 GB file, two sets will be written in the 4GB file.

另外，TempDB 数据文件的大小和增长应与比例填充机制中使用的文件大小和增长相同。数据库中有两个数据文件，一个为2 GB，另一个为4 GB，每组数据写入前2 GB文件，两组将写入4GB文件。

Applying these best practices, you can make sure that the CPU load is distributed across all TempDB data files.

应用这些最佳实践，您可以确保CPU负载分布在所有TempDB数据中文件。

Restart the SQL Server service to make sure that these changes will take effect.

重新启动SQL Server服务，以确保这些更改将生效。

设置最小和最大服务器内存 ( Set the Minimum and Maximum Server Memory )

The minimum and maximum server memory is used to configure the amount of memory, in megabytes to establish upper and lower limits of memory used by the buffer pool on the Microsoft SQL Server. SQL Server Engine starts with only the memory required to initialize. As the workload increases, it keeps acquiring the memory required to support the workload, and never acquires more than the level specified in max server memory. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB.

服务器的最小和最大内存用于配置内存量（以兆字节为单位），以建立Microsoft SQL Server上缓冲池使用的内存上限和下限。 SQL Server Engine仅从初始化所需的内存开始。随着工作负载的增加，它会不断获取支持工作负载所需的内存，并且永远不会获取超过最大服务器内存中指定的级别的内存。最小服务器内存的默认设置为0，最大服务器内存的默认设置为2147483647 MB。

Before you set the maximum server memory for SQL Server, subtract the memory required for the OS and any other instances of SQL Server from the total physical memory.

在设置SQL Server的最大服务器内存之前，请从总物理内存中减去操作系统和SQL Server的任何其他实例所需的内存。

You can configure these values from the Memory tab of the Server Properties dialog box below:

您可以从下面的“服务器属性”对话框的“内存”选项卡中配置这些值：

设置默认索引填充因子 ( Set the Default Index Fill Factor )

The fill-factor value determines the percentage of space on each leaf-level 8K data page to be filled with data when an index is created or rebuilt, reserving the rest on each page as free space for future growth.

填充因子值确定在创建或重建索引时每个叶子级8K数据页面上要用数据填充的空间百分比，将每个页面上的其余部分保留为可用空间以供将来增长。

In other words, if you set the fill-factor value to 90, this means that 10 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table in order to save page splitting in indexes in the future.

换句话说，如果将fill-factor值设置为90，则意味着每个叶级页面的10％都将保留为空，从而在将数据添加到基础表中以节省索引时为索引扩展提供了空间。在将来的索引中。

The fill-factor value is a percentage from 1 to 100, and the default is 0, same as 100, which means that the leaf-level pages are totally filled. If your system has lots of OLTP transactions, then a Fill Factor between 70 and 90 provides better results.

填充因子值为1到100之间的百分比，默认值为0，与100相同，这意味着叶级页面已完全填充。如果您的系统有很多OLTP事务，则70至90之间的填充因子可提供更好的结果。

To configure fill factor value, browse the Database Settings of the Server Properties dialog box below:

要配置填充因子值，请浏览下面的“服务器属性”对话框的“数据库设置”：

配置处理器最大并行度 ( Configure Processor Max Degree of parallelism )

The max degree of parallelism specifies the number of processors used in parallel plan execution.

最大并行度指定并行计划执行中使用的处理器数量。

To enable the maximum degree of parallelism, set this option to 0, which is the default value. To disable the parallel plan generation, set max degree of parallelism to 1. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution.

要启用最大并行度，请将此选项设置为0，这是默认值。要禁用并行计划生成，请将最大并行度设置为1。将值设置为大于1的数字可限制单个查询执行使用的最大处理器数量。

You can configure the maximum degree of parallelism value from the Advanced tab of the Server Properties dialog box below:

您可以从下面的“服务器属性”对话框的“高级”选项卡中配置最大并行度值：

$D:\SQL Shack\Best Practices After Installing Microsoft SQL Server\Images\MaxDegreeOfP.GIF$

配置远程查询超时 ( Configure Remote Query Timeout )

The SQL Server remote query is an outgoing connection initiated by the Database Engine. The remote query timeout is used to decide how long a remote query will take before SQL Server times out. The default value is 600 seconds, allowing a 10-minute wait. Setting the value to 0 will disable the time-out, so the query will wait until it is canceled. This is not recommended for production servers.

SQL Server远程查询是由数据库引擎启动的传出连接。 远程查询超时用于确定SQL Server超时之前远程查询将花费的时间。默认值为600秒，允许等待10分钟。将该值设置为0将禁用超时，因此查询将等待直到被取消。不建议将其用于生产服务器。

This value can be modified from the Connections tab of the Server Properties dialog box below:

可以从下面的“服务器属性”对话框的“连接”选项卡中修改此值：

$D:\SQL Shack\Best Practices After Installing Microsoft SQL Server (Part 2)\Images\RemoteQyeryTimeout.GIF$

配置身份验证模式和C2审核跟踪 ( Configure the Authentication Mode and C2 Audit Trace )

There are two possible authentication modes in SQL Server: Windows Authentication mode and Mixed mode.

SQL Server中有两种可能的身份验证模式：Windows身份验证模式和混合模式。

The Windows Authentication mode allows a user to connect through a Microsoft Windows account only. Mixed Mode allows the users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication.

Windows身份验证模式仅允许用户通过Microsoft Windows帐户进行连接。混合模式允许用户使用Windows身份验证或SQL Server身份验证连接到SQL Server实例。

The C2 audit is used to capture all the user activities such as SQL commands, failed and successful attempts of logins, logouts and the server events such as shutdown or restart. C2 audit data are saved in a file in the default data directory of the SQL instance.

C2审核用于捕获所有用户活动，例如SQL命令，成功和失败的登录尝试，注销以及服务器事件（例如关闭或重新启动）。 C2审核数据保存在SQL实例的默认数据目录中的文件中。

The changes can be done from the Connections tab of the Server Properties dialog box below:

可以从下面的“服务器属性”对话框的“连接”选项卡中进行更改：

You need to restart the SQL service in order to make sure that the changes will take effect.

您需要重新启动SQL服务，以确保更改将生效。

配置SQL Server网络 ( Configure SQL Server Network )

SQL Server Network Configuration defines the communication between the SQL Server and the other servers and machines.

SQL Server网络配置定义了SQL Server与其他服务器和计算机之间的通信。

SQL Server Network Configuration node of the SQL Server Configuration Manager is used to enable or disable the network protocols used by SQL Server after installation. These protocols are used for the client’s applications incoming traffic.

SQL Server网络配置安装后，SQL Server配置管理器的“节点”用于启用或禁用SQL Server使用的网络协议。这些协议用于客户端应用程序的传入流量。

The default SQL Server instance listens on TCP port 1433. Named SQL instances are configured for dynamic ports by selecting an available port when the SQL Server service is started. This means that the port will be changed each time SQL Server started. The SQL Server Browser service helps the clients in identifying the port when they connect.

默认SQL Server实例在TCP端口1433上进行侦听。启动SQL Server服务时，通过选择可用端口为动态端口配置命名SQL实例。这意味着该端口将在每次启动SQL Server时更改。 SQL Server浏览器服务可帮助客户端在连接时识别端口。

Having the windows firewall enabled, you should open the port used by SQL Server. So it is recommended to configure the SQL Server to use a specific port for each SQL instance installed on your server from the TCP/IP Properties dialog box below:

启用Windows防火墙后，您应该打开SQL Server使用的端口。因此，建议通过下面的“ TCP / IP属性”对话框将SQL Server配置为对服务器上安装的每个SQL实例使用特定的端口：

Many older applications require server aliases to connect to a named instance. The alias is just an alternative name of the server with the TCP port that can be used to make a connection. You can create the server alias as below:

许多较旧的应用程序需要服务器别名才能连接到命名实例。别名只是具有可用于建立连接的TCP端口的服务器的备用名称。您可以如下创建服务器别名：

The SQL Server instance can be configured to force encryption when communicating with client applications.

可以将SQL Server实例配置为在与客户端应用程序进行通信时强制加密。

In order to encrypt the connections, a certificate should be provided to the SQL Server, otherwise, SQL Server will generate a self-signed certificate once the instance is started.

为了加密连接，应向SQL Server提供一个证书，否则，一旦实例启动，SQL Server将生成一个自签名证书。

You can enable the Force Encryption from the Protocols Properties dialog box below:

您可以从下面的“协议属性”对话框中启用“强制加密”：

Most of the Server Properties changes require restarting the service.

大多数服务器属性更改都需要重新启动服务。

配置SQL安全性 ( Configure SQL Security )

As a first step of the security check, make sure that there are few members in the sysadmin server role as possible.

作为安全检查的第一步，请确保sysadmin服务器角色中的成员人数尽可能少。

The SA user is a SQL login administrator account that can be used if Mixed mode Authentication is enabled on SQL Server. The SA login is hardcoded to be a member of the sysadmin server role that you can’t revoke its permissions. The SA password should be chosen to be a strong password. Rename or disable the SA account if your business allows that.

SA用户是SQL登录管理员帐户，如果在SQL Server上启用了混合模式身份验证，则可以使用该用户。 SA登录名被硬编码为sysadmin服务器角色的成员，您无法撤消其权限。应该将SA密码选择为强密码。重命名或禁用SA帐户（如果您的公司允许）。

The guest user is a database user that is used to provide anonymous access to any database. It allows any login that is not explicitly mapped in the database to a user, in order to connect to the database as a guest. This kind of access is not recommended and disabled by default.

来宾用户是一个数据库用户，用于提供对任何数据库的匿名访问。它允许未在数据库中显式映射到用户的任何登录名，以便以访客身份连接到数据库。不建议使用这种访问方式，并且默认情况下将其禁用。

Remove the BUILTIN\Administrators group from the SQL Server Logins. It is removed in SQL Server 2008 and later.

从SQL Server登录名中删除BUILTIN \ Administrators组。在SQL Server 2008及更高版本中将其删除。

配置SQL错误日志 ( Configure SQL Error Logs )

The SQL Server error log is a great place where you can find information about what is happening on your database server. It contains all the information related to the failures or the errors that has occurred since SQL Server was last restarted or since the last time you have recycled the error logs.

在SQL Server错误日志中，您可以找到有关数据库服务器上发生的情况的信息。它包含与自上次重新启动SQL Server或自上次回收错误日志以来发生的故障或错误相关的所有信息。

By default, there are 7 error log files, 6 archives and the current one. Every time the SQL service starts, the system begins a new log file with name ERRORLOG, the previous log file before restart becomes archived as ERRORLOG.1, and so on. By default, the error logs are located at "Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG " path.

默认情况下，有7个错误日志文件，6个归档文件和当前文件。每次启动SQL服务时，系统都会启动一个名称为ERRORLOG的新日志文件，重新启动之前的前一个日志文件将存档为ERRORLOG.1，依此类推。默认情况下，错误日志位于“ Program Files \ Microsoft SQL Server \ MSSQL.n \ MSSQL \ LOG”路径中。

It is a best practice to increase the number of SQL Server Error Logs from the default value, because the error logs may contain critical information about your database server. When a new error log is created the oldest archive gets removed and that data is then lost forever.

最佳做法是从默认值增加SQL Server错误日志的数量，因为错误日志可能包含有关您的数据库服务器的重要信息。创建新的错误日志时，最早的存档将被删除，然后该数据将永远丢失。

You can override the default value up to 99 files from the Configure SQL Server Error Logs dialog box below:

您可以从下面的“配置SQL Server错误日志”对话框中覆盖默认值（最多99个文件）：

Another type of logs kept in SQL server is the Agent error log. The latest Agent error log is named SQLAGENT.OUT. SQL Server maintains up to nine SQL Server Agent error logs. But here you cannot use a dialogue box to specify the number of archived Agent error logs to keep.

SQL Server中保留的另一种日志类型是代理错误日志。最新的代理错误日志名为SQLAGENT.OUT。 SQL Server最多维护九个SQL Server代理错误日志。但是在这里您不能使用对话框来指定要保留的已存档代理错误日志的数量。

配置操作系统 ( Configure Operating System )

OS level changes can be performed on the server to give extra power to SQL.

可以在服务器上执行OS级别更改，以为SQL提供更多功能。

If the application that will connect to your SQL server is going to participate in distributed transactions through linked servers, make sure that Network DTC is enabled. This could be done from the Computers node of the Component Services in the Administrative Tools.

如果将连接到SQL Server的应用程序将通过链接的服务器参与分布式事务，请确保启用了网络DTC。这可以通过管理工具中“组件服务”的“计算机”节点来完成。

You can also give your SQL Service account the right to “Lock pages in memory”. So that Windows will not swap out SQL pages from the memory on to the paging file on disk.

您还可以授予您SQL Service帐户“锁定内存中的页面”的权限。这样Windows不会将SQL页面从内存交换到磁盘上的页面文件。

To do that, open the Local Group Policy Editor by typing gpedit.msc into the Run box, browsing the User Rights Assignment to open the Lock Page in Memory dialog box and add the SQL Service account to it as below:

为此，请在“运行”框中键入gpedit.msc，打开“本地组策略编辑器”，浏览“用户权限分配”以打开“内存中的锁定页面”对话框，然后向其中添加SQL Service帐户，如下所示：

制定维护计划 ( Set up Maintenance Plans )

There are a variety of tasks that can be scheduled as an automated maintenance plan in order to optimize your SQL Server performance and make the life easier for the DBA.

为了优化SQL Server性能并使DBA的工作更轻松，可以将许多任务安排为自动维护计划。

These tasks include Check database integrity, Shrink database, Reorganize index, Rebuild index, Update statistics and Full, Differential and Transaction Log database backup.

这些任务包括检查数据库完整性，收缩数据库，重组索引，重建索引，更新统计信息以及完整，差异和事务日志数据库备份。

You can create new Maintenance Plans from the SQL Server Management node, using the Maintenance Plan Wizard below, going through simple creation steps.

您可以使用下面的维护计划向导，通过简单的创建步骤从SQL Server管理节点中创建新的维护计划。