使用 PowerShell 创建弹性作业代理(预览版)Create an Elastic Job agent using PowerShell (preview)

适用于: Azure SQL 数据库

弹性作业(预览版)可以跨多个数据库并行运行一个或多个 Transact-SQL (T-SQL) 脚本。Elastic jobs (preview) enable the running of one or more Transact-SQL (T-SQL) scripts in parallel across many databases.

本教程介绍跨多个数据库运行查询所需的步骤:In this tutorial, you learn the steps required to run a query across multiple databases:

  • 创建弹性作业代理Create an Elastic Job agent
  • 创建作业凭据,使作业可以在其目标上执行脚本Create job credentials so that jobs can execute scripts on its targets
  • 定义要对其运行作业的目标(服务器、弹性池、数据库、分片映射)Define the targets (servers, elastic pools, databases, shard maps) you want to run the job against
  • 在目标数据库中创建数据库范围的凭据,以便代理在连接后执行作业Create database scoped credentials in the target databases so the agent connect and execute jobs
  • 创建作业Create a job
  • 向作业添加作业步骤Add job steps to a job
  • 开始执行作业Start execution of a job
  • 监视作业Monitor a job

先决条件Prerequisites

弹性数据库作业的升级版本有一组新的 PowerShell cmdlet,可在迁移期间使用。The upgraded version of Elastic Database jobs has a new set of PowerShell cmdlets for use during migration. 这些新的 cmdlet 将所有现有的作业凭据、目标(包括数据库、服务器、自定义集合)、作业触发器、作业计划、作业内容和作业转移到新的弹性作业代理。These new cmdlets transfer all of your existing job credentials, targets (including databases, servers, custom collections), job triggers, job schedules, job contents, and jobs over to a new Elastic Job agent.

安装最新的弹性作业 cmdletInstall the latest Elastic Jobs cmdlets

如果还没有 Azure 订阅,请在开始前创建一个试用帐户If you don't have already have an Azure subscription, create a trial account before you begin.

安装 Az.Sql 模块以获取最新弹性作业 cmdlet。Install the Az.Sql module to get the latest Elastic Job cmdlets. 以管理访问权限在 PowerShell 中运行以下命令。Run the following commands in PowerShell with administrative access.

# installs the latest PackageManagement and PowerShellGet packages
Find-Package PackageManagement | Install-Package -Force
Find-Package PowerShellGet | Install-Package -Force

# Restart your powershell session with administrative access

# Install and import the Az.Sql module, then confirm
Install-Module -Name Az.Sql
Import-Module Az.Sql

Get-Module Az.Sql

Az.Sql 模块外,本教程还需要 SqlServer PowerShell 模块。In addition to the Az.Sql module, this tutorial also requires the SqlServer PowerShell module. 有关详细信息,请参阅安装 SQL Server PowerShell 模块For details, see Install SQL Server PowerShell module.

创建所需资源Create required resources

创建弹性作业代理需要一个用作作业数据库的数据库(S0 或更高级别)。Creating an Elastic Job agent requires a database (S0 or higher) for use as the Job database.

下面的脚本创建新的资源组、服务器以及可用作作业数据库的数据库。The script below creates a new resource group, server, and database for use as the Job database. 第二个脚本创建另一个服务器,其中包含两个用于对其执行作业的空数据库。The second script creates a second server with two blank databases to execute jobs against.

弹性作业没有特定的命名要求,因此可以使用所需的任何命名约定,只要其符合 Azure 要求即可。Elastic Jobs has no specific naming requirements so you can use whatever naming conventions you want, as long as they conform to any Azure requirements.

# sign in to Azure account
Connect-AzAccount -EnvironmentName AzureChinaCLoud

# create a resource group
Write-Output "Creating a resource group..."
$resourceGroupName = Read-Host "Please enter a resource group name"
$location = Read-Host "Please enter an Azure Region"
$rg = New-AzResourceGroup -Name $resourceGroupName -Location $location
$rg

# create a server
Write-Output "Creating a server..."
$agentServerName = Read-Host "Please enter an agent server name"
$agentServerName = $agentServerName + "-" + [guid]::NewGuid()
$adminLogin = Read-Host "Please enter the server admin name"
$adminPassword = Read-Host "Please enter the server admin password"
$adminPasswordSecure = ConvertTo-SecureString -String $AdminPassword -AsPlainText -Force
$adminCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $adminLogin, $adminPasswordSecure
$agentServer = New-AzSqlServer -ResourceGroupName $resourceGroupName -Location $location `
    -ServerName $agentServerName -ServerVersion "12.0" -SqlAdministratorCredentials ($adminCred)

# set server firewall rules to allow all Azure IPs
Write-Output "Creating a server firewall rule..."
$agentServer | New-AzSqlServerFirewallRule -AllowAllAzureIPs
$agentServer

# create the job database
Write-Output "Creating a blank database to be used as the Job Database..."
$jobDatabaseName = "JobDatabase"
$jobDatabase = New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $agentServerName -DatabaseName $jobDatabaseName -RequestedServiceObjectiveName "S0"
$jobDatabase
# create a target server and sample databases - uses the same credentials
Write-Output "Creating target server..."
$targetServerName = Read-Host "Please enter a target server name"
$targetServerName = $targetServerName + "-" + [guid]::NewGuid()
$targetServer = New-AzSqlServer -ResourceGroupName $resourceGroupName -Location $location `
    -ServerName $targetServerName -ServerVersion "12.0" -SqlAdministratorCredentials ($adminCred)

# set target server firewall rules to allow all Azure IPs
$targetServer | New-AzSqlServerFirewallRule -AllowAllAzureIPs
$targetServer | New-AzSqlServerFirewallRule -StartIpAddress 0.0.0.0 -EndIpAddress 255.255.255.255 -FirewallRuleName AllowAll
$targetServer

# create sample databases to execute jobs against
$db1 = New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $targetServerName -DatabaseName "database1"
$db1
$db2 = New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $targetServerName -DatabaseName "database2"
$db2

使用弹性作业Use Elastic Jobs

若要使用弹性作业,请运行以下命令,在 Azure 订阅中注册此功能。To use Elastic Jobs, register the feature in your Azure subscription by running the following command. 针对要在其中预配弹性作业代理的订阅运行此命令一次。Run this command once for the subscription in which you intend to provision the Elastic Job agent. 如果订阅只包含属于作业目标的数据库,则不需注册此类订阅。Subscriptions that only contain databases that are job targets don't need to be registered.

Register-AzProviderFeature -FeatureName sqldb-JobAccounts -ProviderNamespace Microsoft.Sql

创建弹性作业代理Create the Elastic Job agent

弹性作业代理是用于创建、运行和管理作业的 Azure 资源。An Elastic Job agent is an Azure resource for creating, running, and managing jobs. 此代理执行的作业是按计划的,或者是一次性的。The agent executes jobs based on a schedule or as a one-time job.

New-AzSqlElasticJobAgent cmdlet 要求 Azure SQL 数据库中已存在一个数据库,因此,resourceGroupName、serverName 和 databaseName 参数必须都指向现有的资源 。The New-AzSqlElasticJobAgent cmdlet requires a database in Azure SQL Database to already exist, so the resourceGroupName , serverName , and databaseName parameters must all point to existing resources.

Write-Output "Creating job agent..."
$agentName = Read-Host "Please enter a name for your new Elastic Job agent"
$jobAgent = $jobDatabase | New-AzSqlElasticJobAgent -Name $agentName
$jobAgent

创建作业凭据Create the job credentials

作业在执行以及执行脚本时使用数据库范围的凭据连接到目标组指定的目标数据库。Jobs use database scoped credentials to connect to the target databases specified by the target group upon execution and execute scripts. 也可使用这些数据库范围的凭据连接到 master 数据库,以便枚举服务器或弹性池中的所有数据库,前提是将这其中的一个用作目标组成员类型。These database scoped credentials are also used to connect to the master database to enumerate all the databases in a server or an elastic pool, when either of these are used as the target group member type.

必须在作业数据库中创建数据库范围的凭据。The database scoped credentials must be created in the job database. 所有目标数据库必须有一个具有足够权限的登录名,否则作业无法成功完成。All target databases must have a login with sufficient permissions for the job to complete successfully.

弹性作业凭据

请注意,除了图像中的凭据,还在以下脚本中添加了 GRANT 命令。In addition to the credentials in the image, note the addition of the GRANT commands in the following script. 这些权限是为此示例作业选择的脚本所需要的。These permissions are required for the script we chose for this example job. 由于此示例会在目标数据库中创建新表,每个目标 DB 需要适当的权限才能成功运行。Because the example creates a new table in the targeted databases, each target db needs the proper permissions to successfully run.

若要(在作业数据库中)创建所需的作业凭据,请运行以下脚本:To create the required job credentials (in the job database), run the following script:

# in the master database (target server)
# create the master user login, master user, and job user login
$params = @{
  'database' = 'master'
  'serverInstance' =  $targetServer.ServerName + '.database.chinacloudapi.cn'
  'username' = $adminLogin
  'password' = $adminPassword
  'outputSqlErrors' = $true
  'query' = "CREATE LOGIN masteruser WITH PASSWORD='password!123'"
}
Invoke-SqlCmd @params
$params.query = "CREATE USER masteruser FROM LOGIN masteruser"
Invoke-SqlCmd @params
$params.query = "CREATE LOGIN jobuser WITH PASSWORD='password!123'"
Invoke-SqlCmd @params

# for each target database
# create the jobuser from jobuser login and check permission for script execution
$targetDatabases = @( $db1.DatabaseName, $Db2.DatabaseName )
$createJobUserScript =  "CREATE USER jobuser FROM LOGIN jobuser"
$grantAlterSchemaScript = "GRANT ALTER ON SCHEMA::dbo TO jobuser"
$grantCreateScript = "GRANT CREATE TABLE TO jobuser"

$targetDatabases | % {
  $params.database = $_
  $params.query = $createJobUserScript
  Invoke-SqlCmd @params
  $params.query = $grantAlterSchemaScript
  Invoke-SqlCmd @params
  $params.query = $grantCreateScript
  Invoke-SqlCmd @params
}

# create job credential in Job database for master user
Write-Output "Creating job credentials..."
$loginPasswordSecure = (ConvertTo-SecureString -String "password!123" -AsPlainText -Force)

$masterCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "masteruser", $loginPasswordSecure
$masterCred = $jobAgent | New-AzSqlElasticJobCredential -Name "masteruser" -Credential $masterCred

$jobCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "jobuser", $loginPasswordSecure
$jobCred = $jobAgent | New-AzSqlElasticJobCredential -Name "jobuser" -Credential $jobCred

定义要对其运行作业的目标数据库Define the target databases to run the job against

目标组定义可以在其上执行作业步骤的数据库集(包含一个或多个数据库)。A target group defines the set of one or more databases a job step will execute on.

以下代码片段创建两个目标组: serverGroupserverGroupExcludingDb2The following snippet creates two target groups: serverGroup , and serverGroupExcludingDb2 . serverGroup 的目标是执行时存在于服务器上的所有数据库, serverGroupExcludingDb2 的目标是服务器上的所有数据库, targetDb2 除外:serverGroup targets all databases that exist on the server at the time of execution, and serverGroupExcludingDb2 targets all databases on the server, except targetDb2 :

Write-Output "Creating test target groups..."
# create ServerGroup target group
$serverGroup = $jobAgent | New-AzSqlElasticJobTargetGroup -Name 'ServerGroup'
$serverGroup | Add-AzSqlElasticJobTarget -ServerName $targetServerName -RefreshCredentialName $masterCred.CredentialName

# create ServerGroup with an exclusion of db2
$serverGroupExcludingDb2 = $jobAgent | New-AzSqlElasticJobTargetGroup -Name 'ServerGroupExcludingDb2'
$serverGroupExcludingDb2 | Add-AzSqlElasticJobTarget -ServerName $targetServerName -RefreshCredentialName $masterCred.CredentialName
$serverGroupExcludingDb2 | Add-AzSqlElasticJobTarget -ServerName $targetServerName -Database $db2.DatabaseName -Exclude

创建作业和步骤Create a job and steps

此示例为要运行的作业定义作业和两个作业步骤。This example defines a job and two job steps for the job to run. 第一个作业步骤 ( step1 ) 在目标组 ServerGroup 的每个数据库中创建新表 ( Step1Table )。The first job step ( step1 ) creates a new table ( Step1Table ) in every database in target group ServerGroup . 第二个作业步骤 ( step2 ) 在除 TargetDb2 之外的每个数据库中创建新表 ( Step2Table ),因为之前定义的目标组已指定将其排除。The second job step ( step2 ) creates a new table ( Step2Table ) in every database except for TargetDb2 , because the target group defined previously specified to exclude it.

Write-Output "Creating a new job..."
$jobName = "Job1"
$job = $jobAgent | New-AzSqlElasticJob -Name $jobName -RunOnce
$job

Write-Output "Creating job steps..."
$sqlText1 = "IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('Step1Table')) CREATE TABLE [dbo].[Step1Table]([TestId] [int] NOT NULL);"
$sqlText2 = "IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('Step2Table')) CREATE TABLE [dbo].[Step2Table]([TestId] [int] NOT NULL);"

$job | Add-AzSqlElasticJobStep -Name "step1" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText1
$job | Add-AzSqlElasticJobStep -Name "step2" -TargetGroupName $serverGroupExcludingDb2.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText2

运行作业Run the job

若要立即启动作业,请运行以下命令:To start the job immediately, run the following command:

Write-Output "Start a new execution of the job..."
$jobExecution = $job | Start-AzSqlElasticJob
$jobExecution

成功完成以后,应该会在 TargetDb1 中看到两个新表,在 TargetDb2 中只看到一个新表:After successful completion you should see two new tables in TargetDb1, and only one new table in TargetDb2:

SSMS 中的新表验证

还可以将作业计划为在以后运行。You can also schedule the job to run later. 若要计划在特定时间运行的作业,请运行以下命令:To schedule a job to run at a specific time, run the following command:

# run every hour starting from now
$job | Set-AzSqlElasticJob -IntervalType Hour -IntervalCount 1 -StartTime (Get-Date) -Enable

监视作业执行操作的状态Monitor status of job executions

以下代码片段获取作业执行详细信息:The following snippets get job execution details:

# get the latest 10 executions run
$jobAgent | Get-AzSqlElasticJobExecution -Count 10

# get the job step execution details
$jobExecution | Get-AzSqlElasticJobStepExecution

# get the job target execution details
$jobExecution | Get-AzSqlElasticJobTargetExecution -Count 2

下表列出了可能的作业执行状态:The following table lists the possible job execution states:

状态State 说明Description
创建时间Created 作业执行刚刚创建,还没有进行。The job execution was just created and is not yet in progress.
InProgressInProgress 作业执行目前正在进行中。The job execution is currently in progress.
WaitingForRetryWaitingForRetry 作业执行无法完成其操作,正在等待重试。The job execution wasn't able to complete its action and is waiting to retry.
成功Succeeded 作业执行已成功完成。The job execution has completed successfully.
SucceededWithSkippedSucceededWithSkipped 作业执行已成功完成,但跳过了它的一些子项。The job execution has completed successfully, but some of its children were skipped.
失败Failed 作业执行失败,已用尽重试次数。The job execution has failed and exhausted its retries.
TimedOutTimedOut 作业执行已超时。The job execution has timed out.
已取消Canceled 作业执行已取消。The job execution was canceled.
已跳过Skipped 已跳过作业执行,因为同一作业步骤的另一个执行已在同一目标上运行。The job execution was skipped because another execution of the same job step was already running on the same target.
WaitingForChildJobExecutionsWaitingForChildJobExecutions 作业执行正在等待其子执行完成。The job execution is waiting for its child executions to complete.

清理资源Clean up resources

通过删除资源组来删除本教程中创建的 Azure 资源。Delete the Azure resources created in this tutorial by deleting the resource group.

提示

如果打算继续使用这些作业,请勿清理本文中创建的资源。If you plan to continue to work with these jobs, you do not clean up the resources created in this article.

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

后续步骤Next steps

本教程介绍了如何对一组数据库运行 Transact-SQL 脚本。In this tutorial, you ran a Transact-SQL script against a set of databases. 此外还介绍了如何执行以下任务:You learned how to do the following tasks:

  • 创建弹性作业代理Create an Elastic Job agent
  • 创建作业凭据,使作业可以在其目标上执行脚本Create job credentials so that jobs can execute scripts on its targets
  • 定义要对其运行作业的目标(服务器、弹性池、数据库、分片映射)Define the targets (servers, elastic pools, databases, shard maps) you want to run the job against
  • 在目标数据库中创建数据库范围的凭据,以便代理在连接后执行作业Create database scoped credentials in the target databases so the agent connect and execute jobs
  • 创建作业Create a job
  • 将作业步骤添加到作业Add a job step to the job
  • 开始执行作业Start an execution of the job
  • 监视作业Monitor the job