sql azure 语法_Azure SQL –使用Azure自动化的索引表

sql azure 语法

This article provides an overview of indexing tables in Azure SQL database using Azure Automation and how to schedule indexing jobs to run at specific intervals.

本文概述了使用Azure自动化的Azure SQL数据库中的索引表以及如何安排索引作业以特定的间隔运行。

There are different ways to schedule jobs in Azure. Please refer to Azure SQL- Elastic Job Agent for configuring and scheduling jobs to run on Azure SQL databases.

有多种在Azure中安排作业的方式。 请参考Azure SQL-弹性作业代理以配置和安排要在Azure SQL数据库上运行的作业。

The following are steps involved in creating and scheduling the indexing Jobs using Azure Automation.

以下是使用Azure自动化创建和安排索引作业的步骤。

1. Creating an Automation account
   创建一个自动化帐户
2. Creating a runbook
   创建一个运行手册
3. Creating credentials
   创建凭证
4. Schedule the runbook
   安排运行手册

Let’s go over these steps one by one.

让我们一步一步地进行这些步骤。

创建一个自动化帐户 (Creating an Automation account)

Login to your Azure portal. Search for automation and select the Automation Accounts from the list.

登录到您的Azure门户。 搜索自动化，然后从列表中选择自动化帐户 。

In the Automation Accounts page, Click on Add.

在“ 自动化帐户”页面中，单击“ 添加”。

Enter the name of the Automation account, select the subscription, resource group, and location.

输入自动化帐户的名称，选择订阅，资源组和位置。

Click on Create.

单击创建。

Once you click on Create, a deployment is created which will create a resource (Automation account) in the specified resource group and location.

单击创建后，将创建一个部署，该部署将在指定的资源组和位置中创建一个资源（自动化帐户）。

创建一个运行手册 (Creating a runbook)

Click on the Automation account which takes you to the automation account page. Click on the Runbooks gallery. The runbook gallery has runbook samples that are already created by Microsoft and the community team. Search in the runbook gallery by typing index in the search box and select Indexes tables in an Azure database if they have a high fragmentation.

单击自动化帐户，该页面会将您带到自动化帐户页面。 单击“ Runbooks”库。 Runbook库包含Microsoft和社区团队已经创建的Runbook示例。 通过在搜索框中键入index，在runbook库中进行搜索， 如果Azure数据库中的碎片很大 ，则选择Indexes表。

Click on Import to import it to your runbook list in the Azure Automation account. Give the name of the runbook and Click Ok.

单击“ 导入”以将其导入到Azure自动化帐户中的Runbook列表中。 输入运行手册的名称，然后单击“ 确定”。

Once the runbook is created, Navigate to Runbooks in the Automation account page. This page shows all the Runbooks that are created by you and Runbooks imported from the Runbook gallery.

一旦创建了运行本，请导航到“自动化帐户”页面中的“运行本”。 此页面显示您创建的所有Runbook以及从Runbook库导入的Runbook。

We need to publish the Runbook which indexes tables in Azure SQL database to run it manually or to schedule it.

我们需要发布在Azure SQL数据库中索引表的Runbook，以手动运行它或安排它。

To publish the Runbook, click on the Runbook you imported and edit it. Click on Publish.

要发布Ru​​nbook，请单击导入的Runbook并进行编辑。 点击发布 。

You can also create your own Runbook by clicking on Create a runbook in the Runbooks page.

您也可以通过单击“ Runbooks”页面中的“ Create a runbook”来创建自己的Runbook。

Enter the name of the Runbook, select the Runbook type and click on Create.

输入Runbook的名称，选择Runbook类型，然后单击Create。

Write your own script in the editor to automate your regular database tasks and click on publish to publish the Runbook to Azure Automation account.

在编辑器中编写自己的脚本以自动化常规数据库任务，然后单击“发布”以将Runbook发布到Azure自动化帐户。

创建凭证 (Creating credentials)

We need to create a credential which will be used to connect the Azure database. In the Automation account page, Click on Credentials. Click on Add a credential. Enter the name of the credential, user name, and password. Click on Create to create credential.

我们需要创建一个凭据，该凭据将用于连接Azure数据库。 在“自动化帐户”页面中，单击“ 凭据” 。 单击添加凭据。 输入凭证名称，用户名和密码。 单击创建以创建凭据。

Now login to Azure SQL database using SQL Server management studio and create a login with the same username and password used in the credential. Please refer to the following T-SQL script.

现在，使用SQL Server Management Studio登录到Azure SQL数据库，并使用该凭据中使用的用户名和密码创建一个登录名。 请参考以下T-SQL脚本。

CREATE LOGIN Jobrun  
  WITH PASSWORD = 'Password@123' 
GO

Create a user from the login you created above in the database you are going index and give appropriate privileges to create or alter the index else the indexing may fail.

从您要在上面建立索引的数据库中的上面创建的登录名创建一个用户，并给予适当的特权来创建或更改索引，否则索引编制可能会失败。

CREATE USER Jobrun
FROM LOGIN Jobrun
 
ALTER ROLE db_owner 
ADD MEMBER Jobrun ;  
GO

手动运行Runbook (Running the Runbook manually)

Navigate to Azure Automation account page, Click on the Runbooks. In the Runbooks page, click on the Runbook and click on Start.

导航到“ Azure自动化帐户”页面，单击“运行手册” 。 在Runbooks页面中，单击Runbook，然后单击Start。

Once you click on start it will ask you to enter the parameters. Enter the mandatory input parameters Azure SQL database server name, database name, and credentials which you created earlier.

单击开始后，它将要求您输入参数。 输入必需的输入参数Azure SQL数据库服务器名称，数据库名称和您之前创建的凭据。

SQLSERVERPORT: Enter the port number of the database if the database server is running on specific port else leave it blank which will take the default value i.e. 1433.

SQLSERVERPORT ：如果数据库服务器在特定端口上运行，请输入数据库的端口号，否则将其保留为空白，它将采用默认值，即1433。

FRAGPERCENTAGE: Enter the fragmentation percentage to index only if the fragmentation is greater than the specified value. If left blank it considers default value i.e. 20.

FRAGPERCENTAGE ：仅当碎片大于指定值时，才输入要索引的碎片百分比。 如果保留为空白，则视为默认值，即20。

REBUILDOFFLINE: The default value is false. Select true from the drop down if you want to rebuild the index online.

REBUILDOFFLINE：默认值为false。 如果要在线重建索引，请从下拉列表中选择true。

TABLE: By default, all the tables are re-indexed. If you want to re-index indexes on a specific table in Azure SQL database, mention the table name.

表：默认情况下，所有表都重新索引。 如果要在Azure SQL数据库中的特定表上重新建立索引，请提及表名称。

After entering the input parameters, click on Create which will create a Job. Once the indexing is completed, the status of the job is changed to Completed as shown in the below image.

输入输入参数后，单击创建将创建一个作业。 索引完成后，作业的状态将更改为“已完成”，如下图所示。

Click on the errors tab to check if the job reported any errors or not. To check the input parameters that were passed to the job, click on the Input tab.

单击错误选项卡以检查作业是否报告任何错误。 要检查传递给作业的输入参数，请单击“输入”选项卡。

安排运行手册 (Scheduling the runbook)

We can also schedule the Runbook to run at specific intervals. To schedule a Runbook, Navigate to Automation account page and Click on Runbooks. Click on the Runbook from the list. In this case, it is SQLIndexRunbook which will index tables in Azure SQL database. Click on Schedules and Click on Add a Schedule.

我们还可以安排Runbook在特定间隔运行。 要计划运行手册，请导航到“自动化帐户”页面，然后单击“运行手册”。 从列表中单击运行手册。 在这种情况下，将使用SQLIndexRunbook索引Azure SQL数据库中的表。 单击时间表，然后单击添加时间表。

Click on Configure parameters and run settings and enter the input parameters. Click OK.

单击配置参数并运行设置，然后输入输入参数。 单击确定。

Click on Link a schedule to your Runbook and create a new schedule.

单击将时间表链接到您的Runbook并创建一个新时间表。

Enter the name of the schedule, select the time zone and the interval. Set the expiration date and time if you want to stop the schedule after the expiry date.

输入时间表的名称，选择时区和间隔。 如果要在到期日期之后停止计划，请设置到期日期和时间。

You can view the job statistics at the Overview section of the Azure Automation account page.

您可以在Azure自动化帐户页面的“概述”部分查看作业统计信息。

结论 (Conclusion)

In this article, we explored how to create Runbooks in Azure Automation to index tables in Azure SQL database, schedule Runbooks to run at a specific interval. In case you have any questions, please feel free to ask in the comment section below.

在本文中，我们探讨了如何在Azure自动化中创建Runbook，以索引Azure SQL数据库中的表，安排Runbook在特定间隔运行。 如果您有任何疑问，请随时在下面的评论部分中提问。

Please refer to SQL Azure category to learn more on Azure SQL.

请参考SQL Azure类别以了解有关Azure SQL的更多信息。

翻译自: https://www.sqlshack.com/azure-sql-index-tables-using-azure-automation/

sql azure 语法