Elastic Jobs

An elastic jobs runs as a cloud service in your Azure environment and allows you to execute a T-SQL script across one or more databases in:

  • An elastic pool
  • A shard set
  • A customer-defined database collection

Elastic jobs can be scheduled as and when required. Job successes and failures are logged for troubleshooting and you can also enable retries after a failure.

Use Cases

Elastic jobs are commonly used for:

  • Database Management and Maintenance

    Elastic jobs can be used for deploying schema changes across, running database maintenance jobs such as index rebuild, collecting database performance data, or updating reference data in a shard set.

  • Reporting

    Elastic jobs can be used to aggregate data from a shard set and into a single reporting table. The reporting table can then be fed to Power BI, SSRS, or any of the reporting or visualization tools for creating reports. Normally, you would have to connect to each and every shard in a shard set to run the report query and insert the data into a single reporting table. Elastic jobs makes it easier to do this, wherein you only have to schedule the T-SQL and it is automatically executed on the shards.

Architecture

An elastic job runs as a cloud service in your Azure environment. The following diagram illustrates the architecture and components of an elastic job:

Architecture

Figure 7.1: Architecture of an Elastic Job

An elastic job consists of the following four components:

  • Control Database

    This is an Azure SQL Database that stores the job metadata and state. The default service tier of the control database is Standard S0.

  • Job Service

    This is an Azure Cloud Service which is hosted in your Azure environment. The job service is responsible for launching and tracking jobs.

  • Azure Service Bus

    Azure Service Bus is a Microsoft offering that allows a Microsoft Service or an application to interact with other applications or services. In elastic jobs, Job Service uses the Azure Service Bus internally for the coordination of work. Azure Service Bus isn't shown in the architecture diagram.

  • Azure Storage

    Azure Storage is used to log diagnostic data resulting from job execution for troubleshooting and debugging job failures.

Elastic Job Workflow

Here's how an elastic job works:

  1. The user submits an elastic job from an Azure portal or PowerShell. The job's details are added to the Control database.
  2. The Job service picks up the new job and creates child jobs for each individual database in the collection.
  3. The Job service controller creates a job task to execute the T-SQL script against each database in the collection.
  4. The Job task execution runs each of the created tasks against the databases.
    • The following job tasks perform job execution:
    • ShardMapRefresh: Executes a query against the shard map manager to find out all the available shards
    • ScriptSplit: Splits the scripts into individual batches with "GO" as a delimiter
    • ExpandJob: Creates child jobs for each database in the group or collection
    • ScriptExecution: Executes the script against the given database with specified credentials
    • Dacpac: Applies the dacpac to a database with the given credentials
  5. Once the job tasks are completed, the job service updates the job status in the controller database.

Create an Elastic Job using the Azure Portal

This section explains how to create an elastic pool using the Azure portal through the following scenario: Mike plans to create an index on the Customer table on all the databases in Toyfactory Ltd. He creates an index using an elastic job through the Azure portal. The below steps are to be followed:

  1. Open a browser and login to the Azure portal (https://portal.azure.com) using your Microsoft Azure credentials.
  2. Open the toyfactory Azure SQL Server and locate the toyfactorypool under the overview blade. Click the pool's name to open the toyfactorypool overview blade:
    Create an Elastic Job using the Azure Portal
  3. In the toyfactorypool overview blade, click on Create job to create an elastic job:
    Create an Elastic Job using the Azure Portal
  4. In the Install services blade, click Preview terms. In the Preview terms blade, check the preview feature checkbox and click OK to continue.
    • Elastic job is a preview feature and isn't available generally. All preview features require the acceptance of preview terms before use:
      Create an Elastic Job using the Azure Portal
  5. You'll be taken back to the Install services blade. Select the Job credentials option. In the Job credentials blade, specify the control database, Azure SQL server SQL admin, and the password:
    Create an Elastic Job using the Azure Portal
    • These credentials are for the Azure SQL Server that hosts the control database.
  6. You'll be taken back to the Install services blade. Click on Create to provision the cloud services, SQL Database, Service Bus, and the Storage, as discussed earlier in the chapter:
    Create an Elastic Job using the Azure Portal
    • It may take 5-10 minutes to deploy the resources. The resources are created under the __ElasticDatabaseJob resource group.
    • Once the resources are provisioned, from the left-hand navigation blade, select resource groups and then select __ElasticDatabaseJob. You should see the following resources under the __ElasticDatabaseJob resource group:
      Create an Elastic Job using the Azure Portal
  7. Click on the Azure Cloud Service resource to open the overview blade:
    Create an Elastic Job using the Azure Portal
    • Make sure that the Elastic Database Job Worker instance is running. If not, then wait for the worker instance to start running.
    • Close the elastic job cloud service blade.
  8. Navigate to the toysfactorypool overview blade and click Create job. In the Job credentials blade, provide the Azure SQL Server Database admin login and password for the control database, which were given in step 5:
    Create an Elastic Job using the Azure Portal

    Click Ok to continue.

    Note

    If you try and provide credentials while elastic job resources are being created, you will get an error saying Elastic Database Job deployment is in progress.

  9. In the Create job blade, provide the Job name and the target database admin login and password.
    • Copy and paste the following T-SQL script in the editor:
      IF NOT EXISTS(SELECT 1 
              FROM   sys.indexes 
              WHERE  NAME = 'IX_PaymentDays' 
                     AND object_id = Object_id('Sales.Customers')) 
  BEGIN 
      CREATE INDEX ix_paymentdays 
        ON Sales.Customers(paymentdays) 
  END
      Create an Elastic Job using the Azure Portal

For example, in the given create index query, if you don't include IF NOT Exists (to verify that an index with the same name doesn't exist in the customers table), the job will fail if the index with the same name exists in the table.

  • Click on Save to save the job. The job will be saved and you'll be taken to the CreateCustomersPaymentDaysIndex job blade:
    Create an Elastic Job using the Azure Portal
  • Click Run to execute the job.
    • Once the job completes, the job blade will list the job's status:
      Create an Elastic Job using the Azure Portal
    • Observe that the job runs successfully on all four databases in the elastic pool.
    • You can verify whether the index has been created by executing the following query on the databases in the elastic pool:
      SELECT DB_Name() AS DatabaseName,Name AS IndexName FROM sys.indexes 
WHERE object_id=Object_id('Sales.Customers')
    • You should get the following output:
      Create an Elastic Job using the Azure Portal

Activity: Exploring Elastic Pools

Let’s go back to our example of ToyStore Ltd. Mike finds out that the toystore sharded databases can be put into an elastic pools to save cost and get the benefits of vertical stability. In order to do a proof of concept, he uses PowerShell to create an elastic pool and add databases to that elastic pool. He also writes down PowerShell script to delete the elastic pool after he is done with the proof of concept. In this activity, we will create a new elastic pool, add databases to the elastic pool, and delete the elastic pool using PowerShell using the following steps:

Note

If you are short of time, you can execute the C:CodeLesson07ElasticPoolManage-ElasticPool.ps1 file, providing the appropriate parameters.

  1. Press Window + R to open the Run command window. Type PowerShell_ISE.exe in the run command window and hit Enter. This will open a new PowerShell ISE editor window. This is where you'll write the Power Shell commands:
    Activity: Exploring Elastic Pools
    • In the PowerShell ISE, select File from the top menu and click Save. Alternatively, you can press Ctrl + S to save the file. In the File Save Dialog box, browse to the C:CodeLesson07 directory. In the file name textbox, type Manage-ElasticPool and click Save to save the file:
      Activity: Exploring Elastic Pools
  2. Copy and paste the following code snippets into the Manage-ElasticPool.ps1 file, one after another. The code's explanation, wherever required, is given in the following code snippet and in the comments within the code snippet.
  3. Copy and paste the following code to define the script parameters:
    param
(
    [parameter(Mandatory=$true)]
    [String] $ResourceGroup,
    [parameter(Mandatory=$true)]
    [String] $SqlServer,
    [parameter(Mandatory=$true)]
    [String] $UserName,
    [parameter(Mandatory=$true)]
    [String] $Password,
    [parameter(Mandatory=$true)]
    [parameter(Mandatory=$false)]
    [int] $eDTU,
    [parameter(Mandatory=$false)]
    [int] $MaxeDTU,
    [parameter(Mandatory=$false)]
    [int] $MineDTU=0,
    [parameter(Mandatory=$false)]
    [String] $AzureProfileFilePath,
    [parameter(Mandatory=$false)]
    # Create/Remove an elastic Pool
    [String] $Operation = "Create", 
    # Comma delimited list of databases to be added to the pool
    [parameter(Mandatory=$false)]
    [String] $DatabasesToAdd  

)
    • The parameter description is as follows:
    • ResourceGroup: The name of the resource group in which the elastic pool will be created. It should be the same as that of the Azure SQL Server.
    • SqlServer: The Azure SQL Server name in which the elastic pool has to be created.
    • UserName: The Azure SQL Server database admin username.
    • Password: The Azure SQL Server database admin password.
    • ElasticPoolName: The name of the elastic pool to be created or deleted.
    • eDTU: The elastic pool eDTU.
    • MaxeDTU: The maximum eDTU available per database in the pool.
    • MineDTU: The minimum eDTU available per database in the pool.
    • AzureProfileFilePath: The full path of the JSON file that has your Azure profile information.
    • Operation: The operation to be performed. Accepts two values: Create and Remove.
    • DatabasesToAdd: A comma-delimited list of the databases to be added to the elastic pool.
  4. Copy and paste the following code to log in to Microsoft Azure and set the Azure context to your subscription:
    # log the execution of the script
Start-Transcript -Path ".LogManage-ElasticPool.txt" -Append

# Set AzureProfileFilePath relative to the script directory if it's not provided as parameter

if([string]::IsNullOrEmpty($AzureProfileFilePath))
{
    $AzureProfileFilePath="....MyAzureProfile.json"
}
    
#Login to Azure Account

if((Test-Path -Path $AzureProfileFilePath))
{
   …
    # Provide your Azure Credentials in the login dialog box
    $profile = Login-AzureRmAccount
    $SubscriptionID =  $profile.Context.Subscription.SubscriptionId
}

#Set the Azure Context
Set-AzureRmContext -SubscriptionId $SubscriptionID | Out-Null
    • The preceding code starts by logging into the Manage-ElasticPool.txt file created under the Log directory within the parent directory of the Manage-ElasticPool.ps1 script.
    • It then checks for the profile information in the json file provided by the AzureProfileFilePath variable. If found, then it sets the PowerShell context to the subscription ID, as specified in the profile file, otherwise it asks to manually log in to the Azure account to set the context.
  5. Copy and paste the following code to create the elastic pool, if it doesn't already exist:
    #Check if the pool exists 
Get-AzureRmSqlElasticPool -ElasticPoolName $ElasticPoolName -ServerName $SqlServer -ResourceGroupName $ResourceGroup -ErrorVariable notexists -ErrorAction SilentlyContinue

if($Operation -eq "Create")
{
if([string]::IsNullOrEmpty($ElasticPoolEdition))
   …
$CreateElasticPool = @{
    ElasticPoolName = $ElasticPoolName;
    Edition = $ElasticPoolEdition;
    Dtu = $eDTU;
    DatabaseDtuMin = $MineDTU;
    DatabaseDtuMax = $MaxeDTU;
    ServerName = $SqlServer;
    ResourceGroupName = $ResourceGroup;
    };
  New-AzureRmSqlElasticPool @CreateElasticPool;

}
else
{
Write-Host "Elastic pool $ElasticPoolName already exists!!!" -ForegroundColor Green
}
    • The preceding code uses the Get-AzureRmSqlElasticPool cmdlet to get the details of the given elastic pool name. If the elastic pool with the specified name is found in the given resource group, it succeeds; otherwise, it returns an error: "Get-AzureRmSqlElasticPool : ResourceNotFound: The Resource 'Microsoft.Sql/servers/toyfactory/elasticpools/adasdas' under resource group 'toystore' was not found".
    • The error is recorded in the notexists variable specified in the ErrorVariable parameter.
    • The code then uses New-AzureRmSqlElasticPool to create the elastic pool if the specified operation is create ($operation parameter) and the $notexists variable isn't empty.
  6. Copy and paste the following code to add the databases to the pool:
    # Add databases to the pool 
if([string]::IsNullOrEmpty($DatabasesToAdd) -and $Operation -eq "Create")
    {
        Write-Host "Please provide a valid value for DatabasesToAdd parameter" -ForegroundColor yellow
        Write-Host "Exiting...." -ForegroundColor Yellow
        break;
    }
$Databases = $DatabasesToAdd.Split(',');
foreach($db in $Databases)
{
Write-Host "Adding database $db to elastic pool $ElasticPoolName " -ForegroundColor Green
Set-AzureRmSqlDatabase -ResourceGroupName $ResourceGroup -ServerName $SqlServer -DatabaseName $db -ElasticPoolName $ElasticPoolName
}
}
    • The preceding code splits the comma-delimited values, as specified in $DatabasesToAdd. It adds a variable into an array variable database. It then iterates through each of the databases in the array and sets the elastic pool using the Set-AzureRmSqlDatabase cmdlet.
  7. Copy and paste the following code to remove or delete an existing elastic pool:
    #remove an elastic pool

if($Operation -eq "Remove")
{
#Get all databases in the elastic pool
$epdbs = Get-AzureRmSqlElasticPoolDatabase -ElasticPoolName $ElasticPoolName -ServerName $SqlServer -ResourceGroupName $ResourceGroup

# iterate through the databases and take them out of the pool.
foreach($item in $epdbs)
{
$db = $item.DatabaseName;

#Take database out of pool
…
#Remove elastic pool 
Write-Host "Removing Elastic Pool $ElasticPoolName " -ForegroundColor Green
$RemovePool = @{
ResourceGroupName = $ResourceGroup;
ServerName = $SqlServer;
ElasticPoolName = $ElasticPoolName;
};

Remove-AzureRmSqlElasticPool @RemovePool -Force;

}
    • The preceding code only works when the $operation parameter is set to Remove. An elastic pool can't be removed or deleted if it has databases assigned to it. First, the code gets all the databases in an elastic pool using the cmdlet Get-AzureRmSqlElasticPoolDatabase.
    • It then iterates through each database and takes them out of the pool using Set-AzureRmSqlDatabase.
    • It then removes the elastic pool using the Remove-AzureRmSqlElasticPool cmdlet.
    • This completes the script. Click Save under the File menu or press Ctrl + S to save the script.

Executing the PowerShell Script

  1. Press Window + Run to open the run command window. Type PowerShell and hit Enter to open a new PowerShell console window.
  2. Change the directory to the folder that has the Manage-ElasticPool.ps1 script in it. For example, if the script is at the C:CodeLesson07 directory, then run the following command to switch to this directory:
    cd C:CodeLesson07
  3. To delete an existing pool, execute the following command. You will have to change the parameter values as per your environment.

Note

Note

If you created toyfactorypool earlier in the chapter, then run this command to delete the pool. If you don't have an existing pool, then proceed to the next step, which is creating a pool.

If you have an existing pool and you don't want to remove it, then you will have to create a pool and a separate set of databases for it:

.Manage-ElasticPool.ps1 -ResourceGroup toystore -SqlServer toyfactory -UserName sqladmin -Password Packt@pub2 -ElasticPoolName toyfactorypool -Operation Remove -AzureProfileFilePath C:CodeMyAzureProfile.json
  1. To create a new pool and add databases to it, execute the following command. You will have to change the parameter values as per your environment:
    .Manage-ElasticPool.ps1 -ResourceGroup toystore -SqlServer toyfactory -UserName sqladmin -Password Packt@pub2 -ElasticPoolName toyfactorypool -ElasticPoolEdition Standard -eDTU 100 -MaxeDTU 100 -MineDTU 10 -AzureProfileFilePath C:CodeMyAzureProfile.json -Operation Create -DatabasesToAdd "toystore_Shard_1_50,toystore_Shard_50_100,toystore_Shard_100_150,toystore_Shard_150_200"
    • The preceding command will create the toyfactoryelasticpool with eDTU 100 and the databases, as specified by the DatabasesToAdd parameter.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.123.155