oracle ola_Ola HallengrenSQL Server维护解决方案–安装和SQL Server备份解决方案

最新推荐文章于 2023-05-23 08:05:51 发布

culuo4781

最新推荐文章于 2023-05-23 08:05:51 发布

阅读量312

点赞数

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

原文链接：https://www.sqlshack.com/ola-hallengrens-sql-server-maintenance-solution-installation-sql-server-backup-solution/

版权

oracle ola

Database administrators tend to use various scripts or applications, to make the daily SQL Server database maintenance task easier.

数据库管理员倾向于使用各种脚本或应用程序来简化SQL Server数据库的日常维护任务。

Some more experienced administrators prefer to design and use their own scripts for these tasks. The scripts are usually designed to fit the requirements imposed by the specific environment. After the scripts are thoroughly tested, they often get included in a maintenance plan, or SQL Server agent job to automate their execution. Such solution is usually optimal for some demanding environments like high traffic servers or databases that are still in development. Designing these solutions usually take time, and require an experienced DBA skilled in T-SQL or PowerShell scripting.

一些更有经验的管理员更喜欢为这些任务设计和使用自己的脚本。脚本通常被设计为适合特定环境所强加的要求。脚本经过全面测试后，通常会被包含在维护计划或SQL Server代理作业中以自动执行它们。对于某些要求苛刻的环境（例如高流量服务器或仍在开发中的数据库），这种解决方案通常是最佳的。设计这些解决方案通常需要时间，并且需要具有T-SQL或PowerShell脚本编写经验的DBA。

On the other hand, some administrators like the simplicity offered by the 3^rd party applications. The companies that design these applications invest a great amount of effort to make their apps simple to use and feature-rich at the same time. These applications usually come with the nicely-packed interfaces and do not require much of the experience from their users. The drawback to this solution is certainly its price.

在另一方面，一些管理员喜欢通过^第三方应用程序提供的简单性。设计这些应用程序的公司投入了大量精力，以使其应用程序易于使用且功能丰富。这些应用程序通常带有包装精美的界面，不需要太多用户经验。这种解决方案的缺点当然是它的价格。

Ola Hallengren’s maintenance solution could be considered a hybrid of these two solutions, as it takes the best perks from both options: it is simple, highly customizable, and it’s free. As it takes many parameters into account, it can be configured to fit most demanding environments. But it can also be used successfully with the default settings on simpler servers and databases. The jobs created by the Maintenance Solution automatically process all user databases on the server. So be aware of that if databases on your server require different maintenance scenarios.

Ola Hallengren的维护解决方案可以被视为这两种解决方案的混合体，因为它从两种选择中都获得了最佳收益：它简单，高度可定制且免费。由于考虑了许多参数，因此可以对其进行配置以适合大多数苛刻的环境。但是它也可以在简单服务器和数据库上的默认设置下成功使用。维护解决方案创建的作业将自动处理服务器上的所有用户数据库。因此请注意，如果服务器上的数据库需要不同的维护方案。

Ola’s SQL Server Maintenance Solution consists of various scripts that create jobs and stored procedures once installed on the SQL Server. The entire solution was designed by Ola Hallengren, an MCITP database administrator and database developer, who has been working with SQL Server since 2001. The solution consists of three parts:

OlaSQL Server维护解决方案由各种脚本组成，这些脚本一旦安装在SQL Server上即可创建作业和存储过程。整个解决方案由MCITP数据库管理员和数据库开发人员Ola Hallengren设计，他自2001年以来一直使用SQL Server。该解决方案包括三个部分：

This article series will cover all three parts of this maintenance solution. In this part, installation and SQL Server Backup solution will be described in more detail.

本系列文章将涵盖此维护解决方案的所有三个部分。在这一部分中，将更详细地描述安装和SQL Server Backup解决方案。

安装 (Installation)

Before starting, make sure that all software requirements have been met. This should not be a problem, as the solution supports all Windows versions of SQL Server, starting from SQL Server 2005. It is also supported on all editions of SQL Server. Support for SQL Server 2017 on Linux is still work in progress.

在开始之前，请确保已满足所有软件要求。这应该不是问题，因为该解决方案支持从SQL Server 2005开始的所有Windows版本SQL Server。所有版本SQL Server也都支持该解决方案。 Linux上仍在支持SQL Server 2017。

To install the complete maintenance solution, perform the following steps:

要安装完整的维护解决方案，请执行以下步骤：

Navigate to Ola Hallengren’s site, and download the file MaintenanceSolution.sql

导航到Ola Hallengren的站点，然后下载文件MaintenanceSolution.sql
Save the file anywhere on the hard drive, and open it in SSMS or any other script editor

将文件保存在硬盘驱动器上的任何位置，然后在SSMS或任何其他脚本编辑器中将其打开

Locate the following lines of code near the top of the script (around line 32):

在脚本顶部附近（第32行附近）找到以下代码行：

SET @CreateJobs          = 'Y'         
SET @BackupDirectory     = N'C:\Backup'
SET @CleanupTime         = NULL         
SET @OutputFileDirectory = NULL         
SET @LogToTable          = 'Y'

If needed, change the settings for the following variables:

如果需要，请更改以下变量的设置：
- Replace the value of the BackupDirectory variable (’C:\Backup’) with the preferred backup path. The provided path will be used as a root directory for storing backup files. The network share can also be used, but in this case, both SQL Server and SQL Server Agent services need to be run under a domain account with full control of the directory and the network share
  将BackupDirectory变量（'C：\ Backup'）的值替换为首选的备份路径。提供的路径将用作存储备份文件的根目录。也可以使用网络共享，但是在这种情况下，SQL Server和SQL Server代理服务都必须在具有对目录和网络共享的完全控制权的域帐户下运行
- Set the integer value for the CleanupTime. This value specifies how long (in hours) will each backup file be kept on the drive. After the specified time passes, the file will be deleted automatically. If default NULL value is left in the script, the backup files will never be deleted automatically
  设置CleanupTime的整数值。此值指定每个备份文件在驱动器上保留多长时间（以小时为单位）。经过指定的时间后，文件将被自动删除。如果脚本中保留了默认的NULL值，则备份文件将永远不会被自动删除。
- OutputFileDirectory variable: this variable specifies the path for the log files that will be created after each job is run. Specify a custom path if needed or leave the NULL value to use the default SQL Server error log directory
  OutputFileDirectory变量：此变量指定将在每个作业运行后创建的日志文件的路径。如果需要，请指定一个自定义路径，或者保留NULL值以使用默认SQL Server错误日志目录
- LogToTable variable has only values for Yes and No. Default value is set to Yes, and leaving this value as is will log the results of each created job in the CommandLog table
  LogToTable变量仅具有“是”和“否”值。默认值设置为“是”，并且将此值保持不变将在CommandLog表中记录每个已创建作业的结果
Open SSMS, and connect to the server where you want to install the solution

打开SSMS，然后连接到要在其中安装解决方案的服务器
Make sure that the SQL Server agent is installed and running. To start the SQL Server Agent, expand the node of the connected server, right-click on the SQL Server Agent, and click on Start in the context menu

确保已安装并正在运行SQL Server代理。要启动SQL Server代理，请展开已连接服务器的节点，右键单击SQL Server代理 ，然后在上下文菜单中单击“ 启动” 。
Execute the MaintenanceSolution.sql against the server. The script creates one table (dbo.CommandLog), one function, and four stored procedures in the master database. It also creates 11 pre-created SQL Server Agent jobs. Each of the created jobs will be discussed separately

对服务器执行MaintenanceSolution.sql。该脚本在master数据库中创建一个表（dbo.CommandLog），一个函数和四个存储过程。它还创建11个预创建SQL Server代理作业。每个创建的工作将单独讨论

安装的组件 (Installed components)

As mentioned above, the script installed the CommandLog table, four stored procedures and 11 agent jobs.

如上所述，该脚本安装了CommandLog表，四个存储过程和11个代理作业。

CommandLog table is used for logging. Parameters such as: DatabaseName, Command, CommandType and StartTime are logged for each operation, while the SchemaName, ObjectName, ObjectType, StatisticsName, PartitionNumber and ExtendedInfo only get logged for the specific operations

CommandLog表用于记录日志。对于每个操作，将记录以下参数：DatabaseName，Command，CommandType和StartTime，而仅为特定操作记录SchemaName，ObjectName，ObjectType，StatisticsName，PartitionNumber和ExtendedInfo。
Stored procedures determine the settings for each created job

存储过程确定每个创建的作业的设置
- CommandExecute stored procedure is the crucial component of the maintenance solution, as it is used by all other created procedures and jobs. Its main purpose is executing various T-SQL commands, recording the starting and ending times for each command to the log, as well as the final operation outcome CommandExecute存储过程是维护解决方案的关键组成部分，所有其他创建的过程和作业都使用它。其主要目的是执行各种T-SQL命令，将每个命令的开始和结束时间记录到日志中，以及最终的操作结果
- DatabaseBackup stored procedure is designed to manage all backup maintenance tasks. It is usually used with the default settings, but in some specific cases, parameters like Verify, Compress, ChekSum can be changed from ’N’ to ’Y’ in order to verify, compress, or run cheksum on the created database backups DatabaseBackup存储过程旨在管理所有备份维护任务。它通常与默认设置一起使用，但是在某些特定情况下，可以将“ Verify”，“ Compress”，“ ChekSum”等参数从“ N”更改为“ Y”，以便在创建的数据库备份上验证，压缩或运行cheksum
- DatabaseIntegrityCheck stored procedure is used for integrity check jobs. More details will be provided in the upcoming article of the SQL Server maintenance solution series DatabaseIntegrityCheck存储过程用于完整性检查作业。更多详细信息将在SQL Server维护解决方案系列的下一篇文章中提供
- IndexOptimize stored procedure is used by IndexOptimize job. All its parameters will be elaborated in the third article of the maintenance Solution series IndexOptimize存储过程。维护解决方案系列的第三篇文章将详细说明其所有参数
SQL Server Agent jobs are the final product of the script. All jobs are created without a schedule, and these need to be set manually by the user. There are total of 11 jobs: 4 backup jobs, 2 database integrity check jobs, one index optimize job, and 4 cleanup jobs. This article will focus on the backup jobs

SQL Server代理作业是脚本的最终产品。所有作业都是在没有计划的情况下创建的，这些作业需要由用户手动设置。一共有11个作业：4个备份作业，2个数据库完整性检查作业，一个索引优化作业和4个清理作业。本文将重点介绍备份作业

The names for the backup jobs are pretty much self-explanatory. To confirm that these jobs are configured properly, it is best to run each job separately.

备份作业的名称几乎是不言自明的。要确认正确配置了这些作业，最好单独运行每个作业。

数据库备份– SYSTEM_DATABASES –已满 (DatabaseBackup – SYSTEM_DATABASES – FULL)

As its name suggests, this job creates a full database backup of each system database. Since all system databases use Simple Recovery Model, it is not possible to backup their transaction logs. To run this job, right click on it in Object Explorer, and click on Start job at step… option in context menu.

顾名思义，此作业将为每个系统数据库创建完整的数据库备份。由于所有系统数据库都使用简单恢复模型，因此无法备份其事务日志。要运行此作业，请在“ 对象资源管理器”中右键单击它，然后单击上下文菜单中的“ 在步骤…处开始作业”选项。

If the job was configured properly, the success message will be displayed:

如果作业配置正确，将显示成功消息：

To confirm that backup files were created, navigate to the backup folder that was specified in the script. The backup job created a new folder on the specified path:

要确认已创建备份文件，请导航到脚本中指定的备份文件夹。备份作业在指定路径上创建了一个新文件夹：

The folder is named after the server where jobs were configured (DOMENATOR$MAIN). The backup job created files in separate folders with the following structure:

该文件夹以配置作业的服务器命名（DOMENATOR $ MAIN）。备份作业在具有以下结构的单独文件夹中创建文件：

[Root]\Server$Instance\Database\BackupType\Server$Instance_database_BackupType_Date_Time.bak

[root] \ Server $ Instance \ Database \ BackupType \ Server $ Instance_database_BackupType_Date_Time.bak

In this example, the full path to the backup file is:

在此示例中，备份文件的完整路径为：

E:\Backup\DOMENATOR$MAIN\master\FULL\DOMENATOR$MAIN_master_FULL_20171201_081822.bak

E：\ Backup \ DOMENATOR $ MAIN \ master \ FULL \ DOMENATOR $ MAIN_master_FULL_20171201_081822.bak

数据库备份– USER_DATABASES –已满 (DatabaseBackup – USER_DATABASES – FULL)

Creates one full database backup file for each of the user databases, regardless of the recovery model. The job is run from the context menu in the same way as in the previous example. A success message is generated by the completed job.

无论恢复模型如何，都为每个用户数据库创建一个完整的数据库备份文件。从上下文菜单中以与前面示例相同的方式运行作业。完成的作业将生成成功消息。

Folder hierarchy and backup file naming rules are also identical to the previous example: each database has its own folder that further contains a folder for each backup type. As this job creates full database backup files, the full filepath to the AdventureWorks2014 backup file is:

文件夹层次结构和备份文件命名规则也与前面的示例相同：每个数据库都有自己的文件夹，该文件夹进一步包含每种备份类型的文件夹。在此作业创建完整的数据库备份文件时，AdventureWorks2014备份文件的完整文件路径为：

E:\Backup\DOMENATOR$MAIN\AdventureWorks2014\FULL\DOMENATOR$MAIN_AdventureWorks2014_FULL_20171201_094214.bak

E：\ Backup \ DOMENATOR $ MAIN \ AdventureWorks2014 \ FULL \ DOMENATOR $ MAIN_AdventureWorks2014_FULL_20171201_094214.bak

数据库备份– USER_DATABASES – DIFF (DatabaseBackup – USER_DATABASES – DIFF)

The job creates differential backups of all user databases. Make sure to create at least one full database backup for user databases before running this job, or else the job might fail. Same folder hierarchy and naming rules apply to this job. For example, the differential backup created by this job for the AdventureWorks2008 database will be located at:

该作业将为所有用户数据库创建差异备份。在运行此作业之前，请确保至少为用户数据库创建一个完整的数据库备份，否则作业可能会失败。相同的文件夹层次结构和命名规则适用于此作业。例如，此作业为AdventureWorks2008数据库创建的差异备份将位于：

E:\Backup\DOMENATOR$MAIN\AdventureWorks2008\DIFF\DOMENATOR$MAIN_AdventureWorks2008_DIFF_20171201_101016.bak

E：\ Backup \ DOMENATOR $ MAIN \ AdventureWorks2008 \ DIFF \ DOMENATOR $ MAIN_AdventureWorks2008_DIFF_20171201_101016.bak

数据库备份– USER_DATABASES –日志 (DatabaseBackup – USER_DATABASES – LOG)

This job creates the transaction log backup of all databases that use Full or Bulk-logged recovery models. Databases in Simple recovery model are skipped automatically. Here is the path of the transaction log backup created by the job for the AdventureWorks2014 database:

该作业将为使用完整或批量记录的恢复模型的所有数据库创建事务日志备份。简单恢复模型中的数据库将自动跳过。这是作业为AdventureWorks2014数据库创建的事务日志备份的路径：

E:\Backup\DOMENATOR$MAIN\AdventureWorks2014\LOG\DOMENATOR$MAIN_AdventureWorks2014_LOG_20171201_103452.trn

E：\ Backup \ DOMENATOR $ MAIN \ AdventureWorks2014 \ LOG \ DOMENATOR $ MAIN_AdventureWorks2014_LOG_20171201_103452.trn

流程自动化 (Automating the process)

In order to be able to run any of the created jobs automatically, and use the backup solution to its full potential, it is necessary to configure the schedule for each job. This step is left out of Ola’s script deliberately, as each environment requires specific backup schedule that heavily depends on predefined RPO (Recovery Point Objective) and RTO (Recovery Time Objective).

为了能够自动运行任何已创建的作业，并充分利用备份解决方案，必须为每个作业配置计划。由于每个环境都需要特定的备份计划，而该计划很大程度上取决于预定义的RPO（恢复点目标）和RTO（恢复时间目标），因此该步骤被故意排除在Ola的脚本之外。

To define a schedule for any of the four backup jobs, perform the following steps:

要为四个备份作业中的任何一个定义计划，请执行以下步骤：

In Object Explorer, expand SQL Server Agent and Jobs nodes. Right click on the backup job to open the context menu, and click on Properties. In this example, the schedule will be configured for DatabaseBackup – SYSTEM_DATABASES – FULL job. The schedule is configured in the same way for all other backup jobs from the solution

在“ 对象资源管理器”中 ，展开“ SQL Server代理和作业”节点。右键单击备份作业以打开上下文菜单，然后单击“ 属性” 。在此示例中，将为DatabaseBackup – SYSTEM_DATABASES – FULL作业配置计划。对于解决方案中的所有其他备份作业，以相同的方式配置计划
The Job Properties window is opened. Select the Schedules tab, and click on the New button to create a new schedule

“ 作业属性”窗口打开。选择“ 计划”选项卡，然后单击“ 新建”按钮以创建新的计划
In New Job Schedule window, provide the schedule name, and set the type and frequency for the schedule. As this schedule is used by the job that backs up the system databases, it will be set to run once per day at 12:00 AM. Optionally, set the start and end dates for the schedule. Click OK to save changes

在“ 新作业计划”窗口中，提供计划名称，并设置计划的类型和频率。由于该计划由备份系统数据库的作业使用，因此它将被设置为每天12:00 AM运行一次。（可选）设置计划的开始和结束日期。单击确定保存更改
Click OK button in Job Properties window to use created schedule. The job will run automatically on the specified schedule from now on

在“ 作业属性”窗口中单击“ 确定”按钮以使用创建的计划。从现在起作业将按指定的时间表自动运行