SQL Server基于策略的管理–最佳实践

最新推荐文章于 2021-04-16 12:48:11 发布

culuo4781

最新推荐文章于 2021-04-16 12:48:11 发布

阅读量269

点赞数

文章标签：数据库 python java mysql linux

原文链接：https://www.sqlshack.com/sql-server-policy-based-management-best-practices/

版权

SQL Server Policy Based Management allows monitoring of best practices for SQL Server Database Engine. A set of policies in a form of XML files is provided within all SQL Server 2008 and later versions. These files can be imported into SQL Server instances as best practice policies, and then evaluated against a target set that includes instances, instance objects, databases, or database objects. The best practice policies can be evaluated manually, on desired schedule, or (if supported by the utilized policy facet) on a change event. For more information about evaluation modes, see the SQL Server security and Policy Based Management – Introduction online article.

基于SQL Server策略的管理允许监视SQL Server数据库引擎的最佳实践。所有SQL Server 2008和更高版本中都提供了一组XML文件形式的策略。这些文件可以作为最佳实践策略导入到SQL Server实例中，然后针对包含实例，实例对象，数据库或数据库对象的目标集进行评估。可以根据需要的时间表手动评估最佳实践策略，或者（如果使用的策略方面支持）最佳实践策略。有关评估模式的更多信息，请参见SQL Server安全和基于策略的管理–简介在线文章。

最佳做法政策文件 (The best practice policies files)

The XML files which contain information about each best practice policy are by default installed with SQL Server. By default the files are installed to the C:\Program Files\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033 folder. Note that in case of a 64-bit Windows version, these XML files are installed to the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033 folder in order to ensure compatibility with 32-bit applications such as some of Microsoft Best Practices Analyzer versions were.

默认情况下，SQL Server会安装包含有关每个最佳实践策略信息的XML文件。默认情况下，文件安装在C：\ Program Files \ Microsoft SQL Server \ 110 \ Tools \ Policies \ DatabaseEngine \ 1033文件夹中。请注意，在64位Windows版本的情况下，这些XML文件将安装到C：\ Program Files（x86）\ Microsoft SQL Server \ 110 \ Tools \ Policies \ DatabaseEngine \ 1033文件夹中，以确保与32-位应用程序，例如某些Microsoft Best Practices Analyzer版本。

There are 50 best practice policy files in SQL Server 2012. To import the files into a SQL Server instance using SQL Server Management Studio:

SQL Server 2012中有50个最佳实践策略文件。使用SQL Server Management Studio将文件导入到SQL Server实例中：

Management node in 对象管理器中展开“ Object Manager and select the 管理”节点，然后在“ Import Policy option in the 策略”上下文菜单中选择“ Policies context menu导入策略”选项
Use the “…” button of the Files to import option to browse folders and select desired best practice policy files
使用“ 要导入的文件 ”选项的“…”按钮浏览文件夹并选择所需的最佳实践策略文件
Replace duplicates with items imported option if needed (e.g. in case the policies were previously imported to SQL Server instance, modified, and you need to replace them with their default versions). In case you try to import the policy which already exists in SQL Server and do not use this option, an error will occur and the import process will be terminated. 使用导入的项目替换重复项”选项（例如，如果以前已将策略导入到SQL Server实例中，并且已对其进行了修改，则需要将其替换为默认版本）。如果您尝试导入SQL Server中已经存在的策略并且不使用此选项，则会发生错误并且导入过程将终止。
Select the desired state of once imported policies using the Policy state dropdown. Note that all of the best practice policies are set with the disabled state in their XML files.

使用策略状态下拉列表选择一次导入策略的所需状态。请注意，所有最佳做法策略均在其XML文件中设置为禁用状态。
OK to complete the import process 确定以完成导入过程

Once the policies are imported, both the policies and their conditions are located under the Policies and Conditions nodes, respectively.

导入策略后，策略及其条件分别位于“ 策略和条件”节点下。

The policies can be evaluated against their default targets or modified as needed. The conditions of the imported policies can be altered to meet the requirements of a specific SQL Server environment, too. Each policy uses certain default targets based on the facet tied to the policy’s condition. In addition, a SQL Server Agent alerts can be declared for the certain policies, if the policies are set to use the On schedule evaluation mode.

可以针对策略的默认目标进行评估，也可以根据需要进行修改。也可以更改导入策略的条件，以满足特定SQL Server环境的要求。每个策略都基于与策略条件相关的方面使用某些默认目标。此外，如果将策略设置为使用按计划评估模式，则可以为某些策略声明SQL Server代理警报。

最常用的最佳做法政策 (The most commonly used best practice policies)

Although there are a number of provided best practice policies, some of them are more often used in common SQL Server environments than others.

尽管有许多提供的最佳实践策略，但是其中一些在其他通用SQL Server环境中使用率更高。

The Asymmetric Key Encryption Algorithm policy is defined against every asymmetric key and on every database in a SQL Server 2005 instance or later. It checks whether RSA 1024 or RSA 2048 encryption algorithms are used.

针对每个非对称密钥以及在SQL Server 2005实例或更高版本中的每个数据库上定义了“ 非对称密钥加密算法”策略。它检查是否使用RSA 1024或RSA 2048加密算法。

The Backup and Data File Location policy enforces the practice of storing backups and data files on separate logical volumes.

备份和数据文件位置策略实施了在单独的逻辑卷上存储备份和数据文件的做法。

Similarly, the Data and Log File Location best practice policy evaluates whether data and transaction log files are using separate logical values. In addition, the policy checks whether the database is less than 5GB in size, is a system database, or is not online. If any of the latter three conditions is true, the database will be considered as policy compliant.

同样，“ 数据和日志文件位置”最佳实践策略评估数据和事务日志文件是否使用单独的逻辑值。此外，该策略还会检查数据库的大小是否小于5GB，系统数据库还是不在线。如果后三个条件中的任何一个为真，则该数据库将被视为符合策略。

The Database Auto Close policy evaluates all databases and checks whether the AUTO_CLOSE database option is set. The option is used to release all resources used by the database after the last connection is closed and can cause serious performance issues.

数据库自动关闭策略评估所有数据库，并检查是否设置了AUTO_CLOSE数据库选项。该选项用于在最后一个连接关闭后释放数据库使用的所有资源，并且可能导致严重的性能问题。

Similarly to the previously described policy, the Database Auto Shrink policy checks whether all non-system and online databases use the Auto Shrink option. The option is considered as a SQL Server performance killer and should be avoided.

与前面描述的策略类似，“ 数据库自动收缩”策略检查是否所有非系统数据库和联机数据库都使用“ 自动收缩”选项。该选项被视为SQL Server性能杀手，应避免使用。

For compatibility and consistency purposes, the Database Collation policy evaluates whether SQL Server databases use the same collation as used by the master or model databases.

出于兼容性和一致性的目的， 数据库排序规则策略评估SQL Server数据库是否使用与主数据库或模型数据库相同的排序规则。

For various reasons (e.g. corruption or hardware failure) database pages can enter the Suspect mode and become unavailable for production purposes. The Database Page Status uses the following advanced condition to verify whether such suspect pages exist on SQL Server instance:

由于各种原因（例如损坏或硬件故障），数据库页面可以进入“ 可疑”模式，并且无法用于生产目的。 数据库页面状态使用以下高级条件来验证此类可疑页面在SQL Server实例上是否存在：

 
ExecuteSql('Numeric', 'SELECT COUNT(*) AS [Total_Suspect_Pages] FROM msdb.dbo.suspect_pages WHERE event_type IN (1,2,3) AND database_id = DB_ID(DB_NAME()) ')

In order to ensure database reliability, it’s recommended to enforce usage of the checksum page integrity check type. The Database Page Verification policy evaluates this value against all SQL Server databases.

为了确保数据库的可靠性，建议强制使用校验和页面完整性检查类型。 数据库页面验证策略针对所有SQL Server数据库评估该值。

For the security purposes, the Guest Permissions policy checks whether the guest account has access to any user-defined and model databases.

为了安全起见，“ 来宾权限”策略检查来宾帐户是否有权访问任何用户定义的模型数据库。

The Last Successful Backup Date policy checks whether all SQL Server databases have recent backups, not older than one day.

“ 上次成功备份日期”策略检查所有SQL Server数据库是否都具有最近的备份（不早于一天）。

For security and troubleshooting purposes, SQL Server enables the default trace during the installation by default. The SQL Server Default Trace policy checks whether the default trace is currently disabled.

为了安全和疑难解答，SQL Server默认情况下在安装过程中启用默认跟踪。 SQL Server默认跟踪策略检查当前是否禁用了默认跟踪。

One of primary security concerns for SQL Server environments is the server authentication mode. It is highly recommended to use the Windows Authentication mode for a number of reasons (e.g. weak SQL Server authentication resistance to brute-force attacks or advanced Windows account lock-out features). The SQL Server Login Mode policy checks whether the Windows Authentication mode is used on the SQL Server instance or not.

SQL Server环境的主要安全问题之一是服务器身份验证模式。出于多种原因，强烈建议使用Windows身份验证模式（例如，SQL Server身份验证对强力攻击的抵抗力较弱或高级Windows帐户锁定功能）。 SQL Server登录模式策略检查SQL Server实例上是否使用Windows身份验证模式。

If the SQL Server and Windows Authentication mixed mode is used, the SQL Server Password Expiration and SQL Server Password Policy policies evaluate existing logins that use SQL Server Authentication. The policies check whether the Enforce password expiration and Enforce password policy settings are set on the logins.

如果使用SQL Server和Windows身份验证混合模式，则SQL Server密码过期和SQL Server密码策略策略会评估使用SQL Server身份验证的现有登录名。这些策略检查是否在登录名上设置了“ 强制密码过期”和“ 强制密码策略”设置。

The best practice policies provide a range of states that can be evaluated on SQL Server and its objects at any time. Whether a SQL Server instance is a new one that needs to be established by the recommendations, or an existing one that needs to be fixed by the recommendations, the best practice policies provide a good starting point.

最佳实践策略提供了可以在SQL Server及其对象上随时评估的一系列状态。无论SQL Server实例是需要通过建议建立的新实例，还是需要通过建议进行固定的现有实例，最佳实践策略都是一个很好的起点。