elastic 索引库_使用Elastic Job Agents自动执行Azure SQL数据库索引维护

elastic 索引库

In this article, I am going to explain how we can automate the index and statistics maintenance of Azure SQL Database using an Elastic Job Agent.

在本文中，我将解释如何使用弹性作业代理自动执行Azure SQL数据库的索引和统计信息维护。

The Microsoft SQL Server Agent component is used to automate the various tasks including database maintenance. But in Azure, the SQL Server Agent is only available in the Azure managed instances. We cannot schedule the database maintenance tasks on a single database. To automate the database maintenance, we can use the Elastic Job agent. It gives us the ability to execute the database maintenance on a single database or a group of database servers. This article explains the process of configuring the SQL Elastic Jobs Agent.

Microsoft SQL Server代理组件用于自动执行各种任务，包括数据库维护。 但是在Azure中，SQL Server代理仅在Azure托管实例中可用。 我们无法在单个数据库上安排数据库维护任务。 为了自动进行数据库维护，我们可以使用Elastic Job代理。 它使我们能够在单个数据库或一组数据库服务器上执行数据库维护。 本文介绍了配置SQL Elastic Jobs Agent的过程。

Before we configure the Elastic job agent, we must create an Azure SQL database. The elastic job agent creates required stored procedures and tables. To create an Azure SQL Database, launch SQL Server management studio and connect to the Azure SQL Server. See the following image:

在配置弹性作业代理之前 ，我们必须创建一个Azure SQL数据库。 弹性作业代理创建所需的存储过程和表。 若要创建Azure SQL数据库，请启动SQL Server管理工作室并连接到Azure SQL Server。 见下图：

Once connected, execute the following query to create a database.

连接后，执行以下查询以创建数据库。

Create database elasticjobs

Once the database is created, let us configure the elastic job agent. To do that, navigate to the “All resources“, Click on Databases and choose “Elastic Job Agents.” See the following image:

创建数据库后，让我们配置弹性作业代理。 为此，请导航至“ 所有资源 ”，单击“ 数据库 ”，然后选择“ Elastic Job Agents” 。 见下图：

On Elastic Job agents screen, click on Add. See the following image:

在Elastic Job Agent屏幕上，单击Add 。 见下图：

On the next screen, provide the name of the elastic job agent, select the subscription. Now, as I mentioned above, we must configure the Agent Job database. To do that, click on “Job database” See the following image:

在下一个屏幕上，提供弹性作业代理的名称，选择订阅。 现在，如上所述，我们必须配置代理作业数据库。 为此，请单击“作业数据库”。请参见下图：

On the job database screen, select the azure server from the “select server” drop-down box. When you select the desired server, it populates the list of the Azure SQL databases hosted on the server. Choose the database from the list and click on OK.

在作业数据库屏幕上，从“ 选择服务器 ”下拉框中选择azure服务器。 选择所需的服务器后，它将填充服务器上承载的Azure SQL数据库的列表。 从列表中选择数据库，然后单击“ 确定” 。

Back to Elastic Job agent screen, click on Create. See the following image:

返回弹性作业代理屏幕，单击创建 。 见下图：

Once the agent is configured successfully, we will

成功配置代理后，我们将

1. Elastic agent database 弹性代理数据库上创建数据库范围的凭证
2. Define the target group and its members
   定义目标组及其成员
3. master database and 主数据库和target database 目标数据库上创建所需的登录名
4. Create an Index and Statistics Maintenance Jobs
   创建索引和统计信息维护作业

In this article, we are going to run the maintenance jobs on the AdventureWorksLT database; therefore, our target database is AdventureWorksLT, and as mentioned, the agent database is elasticJobs.

在本文中，我们将在AdventureWorksLT数据库上运行维护作业。 因此，我们的目标数据库是AdventureWorksLT，并且如上所述，代理数据库是elasticJobs 。

创建数据库范围的凭证 (Create database scoped credentials)

The database scoped credentials are used to connect to the target database. This credential must be created on the agent database. The following query creates a credential named JobExecuter. It is used to connect to the target database and execute the maintenance scripts. Execute the following T-SQL Script:

数据库范围的凭据用于连接到目标数据库。 必须在代理数据库上创建此凭证。 以下查询创建一个名为JobExecuter的凭证。 它用于连接到目标数据库并执行维护脚本。 执行以下T-SQL脚本：

/*This script will be executed on the agent database (elasticJobs)*/
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD='AzureSQL@123';   
CREATE DATABASE SCOPED CREDENTIAL JobExecuter WITH IDENTITY = 'SQLJobUser',
    SECRET = 'AzureSQL@123';  
GO

定义目标组及其成员 (Define the target group and its member)

Once credentials are defined, we will create a target group. To do that, execute the following script on the agent database.

定义凭据后，我们将创建一个目标组。 为此，请在代理数据库上执行以下脚本。

/*This script will be executed on the agent database (elasticJobs)*/
 
EXEC jobs.sp_add_target_group ‘AzureProductionServers’
GO

The above script creates a target group named AzureProductionServers. Once the target group is created, execute the following script to add the server as a member of the target group. Execute following T-SQL script on agent database.

上面的脚本创建一个名为AzureProductionServers的目标组。 创建目标组后，执行以下脚本将服务器添加为目标组的成员。 在代理数据库上执行以下T-SQL脚本。

/*This script will be executed on the agent database (elasticJobs)*/
 
EXEC jobs.sp_add_target_group_member
'AzureProductionServers',
@target_type =  N'SqlDatabase',
@server_name='a********.database.windows.net',
@database_name =N'AdventureworksLT'
GO

Once target group and members have been created, we will create required logins on master and target database.

创建目标组和成员后，我们将在主数据库和目标数据库上创建所需的登录名。

在主数据库和目标数据库上创建所需的登录名 (Create required logins on the master database and target database)

Now, we will create a SQL login on the master database. The login name and password must be the same that we used as an identity to create a database scoped credential. Execute the following T-SQL script on the master database (system database).

现在，我们将在master数据库上创建一个SQL登录名。 登录名和密码必须与用于创建数据库范围凭证的身份相同。 在master数据库（系统数据库）上执行以下T-SQL脚本。

/*This script will be executed on master (System database) database */
 
CREATE LOGIN SQLJobUser WITH PASSWORD = 'AzureSQL@123';

The above script creates a user named SQLJobsUser. Next, we will create a user on the target database. Make sure that the user must have appropriate permissions on the target database. Here I am granting db_owner permission to make sure that the SQL job executes successfully. Execute the following script:

上面的脚本创建一个名为SQLJobsUser的用户。 接下来，我们将在目标数据库上创建一个用户。 确保用户必须对目标数据库具有适当的权限。 在这里，我授予db_owner权限以确保SQL作业成功执行。 执行以下脚本：

/*This script will be executed on target database (AdventureWorksLT) */
 
Create user SQLJobUser
from login SQLJobUser
     
ALTER ROLE db_owner 
ADD MEMBER [SQLJobUser] ;  
GO

The above script creates a user named SQLJobUser, and the db_owner permission is also granted to the user.

上面的脚本创建一个名为SQLJobUser的用户，并且还将db_owner权限授予该用户。

创建SQL作业以维护Azure SQL数据库的索引 (Create SQL Job for index maintenance of Azure SQL Database)

The script to create the SQL Job must be executed on the agent database. The following code creates a SQL Job named DBA – Index Maintenance. Execute the following script.

创建SQL Job的脚本必须在代理数据库上执行。 以下代码创建一个名为DBA – Index MaintenanceSQL作业。 执行以下脚本。

/*This script will be executed on the agent database (elasticJobs)*/
EXEC jobs.sp_add_job @job_name='DBA – Index Maintenance', @description='This Job performs index maintenance on every sunday at 12:00 AM'

I have created a stored procedure named sp_index_maintenance on the agent database. If the index fragmentation percentage is less than 30%, then it reorganizes the index, and index fragmentation is higher than 30%, than it rebuilds the entire index. Following is the code:

我已经在代理数据库上创建了一个名为sp_index_maintenance的存储过程。 如果索引碎片百分比小于30％，则它将重新组织索引，而索引碎片碎片则高于30％，而不是重建整个索引。 以下是代码：

/*This script will be executed on agent database (elasticjobs)*/
Create procedure sp_index_maintenance
As
begin
DECLARE @DBName varchar(500) 
declare @SQLCmd nvarchar(max)
declare @FregmentedIndexes int
declare @i int=0
declare @TableName varchar(500)
declare @indexName varchar(500)
declare @SchemaName varchar(500)
declare @FregmentationPercent float
declare @RebuildCommand nvarchar(max)
set  @DBName= 'AdventureWorks_2016-TestInstall'
if exists (select name from tempdb.sys.tables where name like '%#FregmentedIndexes%')
drop table #FregmentedIndexes
create table #FregmentedIndexes
    (
        ID int identity (1,1),
        TableName varchar(500),
        indexName varchar(500),
        SchemaName varchar(500),
        Fregmentation_Percentage float
    )
truncate table #FregmentedIndexes
set @SQLCmd='SELECT distinct b.name,c.name,d.name, avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(''' +@DBName +'''), null, null, null, null) a 
inner join ['+@DBName+'].sys.tables b on a.object_id=b.object_id
inner join ['+@DBName+'].sys.indexes c on a.object_id=c.object_id
inner join ['+@DBName+'].sys.schemas d on b.schema_id=d.schema_id
Where 
b.schema_id>1'
Print @SQLCmd
insert into #FregmentedIndexes (TableName,indexName,SchemaName,Fregmentation_Percentage)
exec sp_executesql @SQLCmd
    
set @FregmentedIndexes=(select count(1) from #FregmentedIndexes)
while @i<@FregmentedIndexes
begin
select top 1 @TableName = TableName, @SchemaName=SchemaName, @indexName = indexName, @FregmentationPercent = Fregmentation_Percentage from #FregmentedIndexes
if @FregmentationPercent >30
Begin 
set @RebuildCommand ='Alter index ['+@indexName + '] on ['+ @SchemaName+ '].['+@TableName+'] Rebuild'
exec @RebuildCommand
End
Else if @FregmentationPercent < 30
Begin 
set @RebuildCommand ='Alter index ['+@indexName + '] on ['+ @SchemaName+ '].['+@TableName+'] REORGANIZE'
exec (@RebuildCommand)
End
set @i=@i+1
delete from #FregmentedIndexes where TableName=@TableName and indexName=@indexName
End
End

Now, we will create a job step to execute the stored procedure across all the servers within the target group. Execute the following T-SQL script.

现在，我们将创建一个作业步骤，以在目标组内的所有服务器上执行存储过程。 执行以下T-SQL脚本。

/*This script will be executed on the agent database (elasticJobs)*/
 
EXEC jobs.sp_add_jobstep @job_name='DBA – Index Maintenance',
@command=N' exec sp_Index_Maintenance',
@credential_name='JobExecuter',
@target_group_name='AzureProductionServers'

The above script creates a job step named Execute Index Maintenance in SQL Job named DBA – Index Maintenance job. We want to run this Job on every Sunday at 12:00 AM. To configure the schedule, execute the following T-SQL code.

上面的脚本在名为DBA的 SQL作业-索引维护作业中创建了一个名为执行索引维护的作业步骤。 我们想在每个星期日的12:00 AM运行此作业。 要配置计划，请执行以下T-SQL代码。

/*This script will be executed on the agent database (elasticJobs)*/
 
EXEC jobs.sp_update_job
    @job_name='DBA – Index Maintenance',
    @enabled=1,
    @schedule_interval_type='Weeks',
    @schedule_interval_count=1,
    @schedule_start_time= N'20200706 12:00';

测试SQL作业 (Test the SQL Job)

Once the job is created, you can use [jobs].[sp_start_job] stored procedure. The following script starts the execution of the script.

创建作业后，可以使用[ 作业]。[sp_start_job]存储过程。 以下脚本开始执行脚本。

/*This script will be executed on the agent database (elasticJobs)*/
 
exec [jobs].[sp_start_job] 'DBA – Index Maintenance'

You can see the execution status of the Job from the Overview page of the Elastic job agent. See the following image:

您可以从弹性作业代理的“概述”页面查看作业的执行状态。 见下图：

As you can see that SQL Job is executed successfully.

如您所见，SQL Job已成功执行。

You can see the execution status of the job by querying the [jobs_internal].[job_executions] and [jobs_internal].[jobs] tables. Following is the query that populates the name of the job, status of the job, start time and end time of the job.

您可以通过查询[jobs_internal]。[job_executions]和[jobs_internal]。[jobs]表来查看作业的执行状态。 以下是填充作业名称，作业状态，作业开始时间和结束时间的查询。

select b.name,a.lifecycle,start_time, end_time from 
[jobs_internal].[job_executions] a 
inner join 
[jobs_internal].[jobs] b on 
a.job_id=b.job_id
where b.name='DBA – Index Maintenance'

Following is the output:

以下是输出：

摘要 (Summary)

In this article, I have explained how we can use the Elastic Job Agent to automate the index maintenance of the Azure SQL Database. You can use the Elastic Job Agent to automate other database maintenance tasks.

在本文中，我已经解释了如何使用弹性作业代理来自动执行Azure SQL数据库的索引维护。 您可以使用弹性作业代理自动执行其他数据库维护任务。

翻译自: https://www.sqlshack.com/automating-azure-sql-database-index-maintenance-using-elastic-job-agents/

elastic 索引库