使用 PowerShell 创建弹性作业代理Create an Elastic Job agent using PowerShell

使用弹性作业,可以跨多个数据库并行运行一个或多个 Transact-SQL (T-SQL) 脚本。Elastic jobs 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 1.1.1-preview 模块以获得最新弹性作业 cmdlet。Install the Az.Sql 1.1.1-preview module to get the latest Elastic Job cmdlets. 以管理员访问权限在 PowerShell 中运行以下命令。Run the following commands in PowerShell with administrative access.

# Installs the latest PackageManagement powershell package which PowershellGet v1.6.5 is dependent on
Find-Package PackageManagement -RequiredVersion 1.1.7.2 | Install-Package -Force

# Installs the latest PowershellGet module which adds the -AllowPrerelease flag to Install-Module
Find-Package PowerShellGet -RequiredVersion 1.6.5 | Install-Package -Force

# Restart your powershell session with administrative access

# Places Az.Sql preview cmdlets side by side with existing Az.Sql version
Install-Module -Name Az.Sql -RequiredVersion 1.1.1-preview -AllowPrerelease

# Import the Az.Sql module
Import-Module Az.Sql -RequiredVersion 1.1.1

# Confirm if module successfully imported - if the imported version is 1.1.1, then continue
Get-Module Az.Sql

创建所需资源Create required resources

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

下面的脚本创建新的资源组、服务器以及可用作作业数据库的数据库。下面的脚本还创建了另外一个服务器,其中包含 2 个可以对其执行作业的空数据库。The script below creates a new resource group, server, and database for use as the Job database. The script below also creates a second server with 2 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 your 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 SQL 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 some sample databases - uses the same admin credential as the agent server just for simplicity
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 some sample databases to execute jobs against...
$Db1 = New-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $TargetServerName -DatabaseName "TargetDb1"
$Db1
$Db2 = New-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $TargetServerName -DatabaseName "TargetDb2"
$Db2

启用适合订阅的弹性作业预览版Enable the Elastic Jobs preview for your subscription

若要使用弹性作业,请运行以下命令,在 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 数据库,因此 ResourceGroupNameServerNameDatabaseName 参数必须都指向现有的资源。The New-AzSqlElasticJobAgent cmdlet requires an 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 job credentials so that jobs can execute scripts on its targets

作业在执行时使用数据库范围的凭据连接到目标组指定的目标数据库。Jobs use database scoped credentials to connect to the target databases specified by the target group upon execution. 也可使用这些数据库范围的凭据连接到 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
# - Create the master user from master user login
# - Create the 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 of the target databases
# - Create the jobuser from jobuser login
# - Make sure they have the right permissions for successful 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 you want 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

Write-Output "Creating a new job"
$JobName = "Job1"
$Job = $JobAgent | New-AzSqlElasticJob -Name $JobName -RunOnce
$Job

创建作业步骤Create a job step

此示例为要运行的作业定义两个作业步骤。This example defines 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 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 中的新表验证

监视作业执行操作的状态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

计划要在以后运行的作业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

清理资源Clean up resources

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

Tip

如果打算继续使用这些作业,请勿清除本文中创建的资源。If you plan to continue to work with these jobs, do not clean up the resources created in this article. 如果不打算继续学习,请通过以下步骤删除本文中创建的所有资源。If you do not plan to continue, use the following steps to delete all 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