Introduction

Shard Elasticity enables cloud admins to automate the vertical scaling (dialing up and down the edition of a single shard) and horizontal scaling (adding or removing shards from a shard map) of their sharded environment via PowerShell scripts and by means of the Azure Automation Service.  Automating such actions enables a frictionless scaling environment that is void of manual human actions.  This example provides a number of PowerShell runbooks that leverage the Elastic database tools client libraries as well as the Azure Automation service.

For more information about Azure SQL Database Elastic database tools and Shard Elasticity, please see the following:

Azure Automation Service

Azure Automation brings a powerful, much needed PowerShell Workflow execution service to the Azure platform. Now you can automate maintenance/adminstrative tasks from within the common Azure portal experience. Simply author a PowerShell Workflow (called a runbook in Azure Automation), upload it to the cloud, and schedule when you want the runbook to execute. This document provides end-to-end setup of Azure Automation for a handful of shard elasticity examples. For more information, see the preview announcement. Or sign up for an Azure subscription.

In this example, Azure Automation is used as the schedule and workload execution framework. Think of Azure Automation as your SQL Agent in the Cloud.

In addition to this document, here are other resources:

Prerequisites

Shard Elasticity PowerShell Files

The following set of PowerShell files contain the basic commands to accomplish horizontal and vertical scaling scenarios using Azure Automation.

These examples illustrate how to use the PowerShell sample modules to perform basic shard elasticity tasks. In combination with the Microsoft Azure Automation service and corresponding Azure Automation runbooks, you can create automated and scheduled jobs that provision a new shard and/or change the performance level of specific shards based on a set of rules.

SetupShardedEnvironment.ps1: This PowerShell runbook performs a one-time setup of a sharded environment complete with a shard map manager and range shard map.

ProvisionShardByDate.ps1: Provisions a new database in advance of the upcoming day's workload. The database is created and named based on date stamp (YYYYMMDD) and is registered with the shard map manager as a range [YYYYMMDD, YYYYMMDD + 1D).

ProvisionShardBySize.ps1: Provisions a new database when the current database is running out of capacity.

ReduceServiceTier.ps1: Iterates through the shards in a provided shard map and determines if each individual shard is a candidate for performance tier reduction. Two criteria determine whether or not the shard is a candidate: 1) the current service tier of the shard and 2) the age of the database.

ShardManagement.psm1: Provides a set of methods to interact with the shard map manager.

SqlDatabaseHelpers.psm1: Provides a set of methods to interact with Azure SQL databases.

ShardElasticity.psm1: Provides a set of methods to perform horizontal scaling as well as vertical scaling operations.

ShardElasticity.psd1: Provides a set of methods to interact with Elastic database tools and Azure SQL DB.

The example below provides an illustration of the integration between the methods in ShardManagement module and the Elastic database tools contructs that are referenced via the Elastic database tools client DLL.

PowerShell
Edit|Remove
        # Shard map manager provides utilities to add, alter, and remove shards and shard maps among other actions 
        $ShardMapManager = Get-ShardMapManager -UserName $UserName -Password $Password -SqlServerName $SQLServerName -SqlDatabaseName $ShardMapManagerDb -AppName $AppName 
 
        # If the shard map manager does not exist on $ShardMapManagerDb it will be created 
        if($ShardMapManager -eq $Null) 
        { 
            Write-Output "`tDid not find a Shard Map Manager on $ShardMapManagerDb, creating..." 
            $ShardMapManager = New-ShardMapManager -UserName $UserName -Password $Password -SqlServerName $SQLServerName -SqlDatabaseName $ShardMapManagerDb -AppName $AppName 
            Write-Output "`tCreated Shard Map Manager on $ShardMapManagerDb" 
        } 
        else 
        { 
            # Shard map manager already exists on $ShardMapManagerDb 
            Write-Output "`tShard Map Manager exists on $ShardMapManagerDb" 
        }
 
 

 

Costs

Note that the execution of the PowerShell example scripts will result in the creation of databases that will incur real costs to the subscription owner. The underlying Azure SQL DBs will be charged at a rate no different than any other Azure SQL DB database. The costs starting November 1 are:

Lastly, within the scope of the provided examples, the use of Azure Automation currently will not incur any charges to the subscription owner. Please see the Azure Automation pricing page for more details.

 

Quick Example Instructions

  1. Download the ShardElasticity.zip file and extract contents.
  2. Obtain the Elastic Scale client binary (Microsoft.Azure.SqlDatabase.ElasticScale.Client.dll).
  3. Place the DLL in the ShardElasticityModule folder and zip the folder.
  4. In your Azure Automation account, upload the ShardElasticityModule.zip file as an Asset.
  5. In Azure Automation, create an Asset Credential called ElasticScaleCredential that contains the username and password for your Azure SQL Database server.
  6. Create an Asset Variable called SqlServerName for your fully-qualified Azure SQL Database server name.
  7. Upload SetupShardedEnvironment.ps1ProvisionBySize.ps1,ProvisionByDate.ps1, and ProvisionByDate.ps1 as runbooks.
  8. As a one-time operation, test the SetupShardedEnvironment.ps1 runbook to setup the sharded environment.
  9. Publish one or more of the remaining runbooks and link the runbook(s) to a schedule.
  10. Observe the output of the runbook via the JOBS tab.

If the Quick Example Instructions were not successful, please see the Detailed Example Instructions below.

Detailed Example Instructions

  1. Author and package a PowerShell module
  2. Create a Microsoft Azure Automation Account
  3. Upload PowerShell module to Azure Automation as an Asset
  4. Create Azure Automation credential and variable Assets
  5. Upload PowerShell runbooks to Azure Automation
  6. Setup a sharded environment
  7. Test the Automation runbooks
  8. Publish the runbooks
  9. Schedule the runbook

1. Author and Package a PowerShell Module

The first step is to create a PowerShell module that references the Elastic database tools assemblies and package this module so that it is ready to be uploaded to the Azure Automation Service as an asset.

  1. Download the “ShardElasticity.zip” file.
  2. Extract all content.
  3. Obtain the Azure SQL Database Elastic database tools client DLL (i.e., Microsoft.Azure.SqlDatabase.ElasticScale.Client.dll) and copy the file into your local “ShardElasticityModule” folder that was downloaded in step 1. This can be done in two ways: 1) download the DLL via the Elastic Scale NuGet package or 2) from your Elastic database tools Starter Kit project (must be built), go to \bin\Debug\ to obtain the DLL.
  4. Zip the ShardElasticityModule folder.  Note: Azure Automation requires several name conventions - given the module manifest name ShardElasticityModule.psd1, the zip file name must match exactly (ShardElasticityModule.zip). The zip file contains the folder ShardElasticityModule (name matching name of module), which in turn contains psm1 files and the client DLL. If this structure is not followed, Azure Automation will not be able to unpack the module.
  5. Once you have verified that the contents and structure of the zipped folder match requirements, proceed to the next step. It should resemble this:

 

2. Create a Microsoft Azure Automation Account

  1. Go to the Azure Preview Features.
  2. Click Try It.

  3. Go to Microsoft Azure portal.
  4. Click on Automation.
  5. At the bottom of the screen click Create.
  6. In the prompt shown below, please enter a valid account name and click the check in the bottom right-hand corner of the box.
  7. Proceed to the next step. Success resembles the graphic below.

3. Upload PowerShell module to Azure Automation as an Asset

Upload the PowerShell module from above to the your Azure Automation Account - the module contains a set of Shard Elasticity functions and the Elastic database tools client DLL that can be referenced from the runbooks.

  1. Click ASSETS in the ribbon on the top of the screen.
  2. Click IMPORT MODULE at the bottom of the page.
  3. Click BROWSE FOR FILE…, and locate the ShardElasticityModule.zip file from above.
  4. Once the correct file has been chosen, click the check in the bottom right-hand corner of the box to import it. Azure Automation service imports the module.
  5. Proceed to the next step. Success resembles this graphic. If the module was not imported successfully, please ensure that the zip file matches conventions described above.

 

4. Create Azure Automation Credential and Variable Assets 

Instead of hard coding credentials and commonly used variables into the runbooks, Azure Automation can create credential and variable assets respectively that can be referenced across many runbooks. Thus, changing a password then happens in only one location.

  1. Select the new Azure Automation account that you just created.
  2. Under the ShardElasticityExamples account, click ASSETS in the ribbon.
  3. Click ADD SETTING at the bottom of the screen.
  4. Click ADD CREDENTIAL.
  5. Select Windows PowerShell Credential as the CREDENTIAL TYPE and ElasticScaleCredential as the Name. A description is optional.
  6. Click the arrow in the bottom right-hand corner of the box.  Note: To use the runbooks without modification, use the variable names verbatim, as provided in the instructions. The variable names are referenced by the runbooks.
  7. Insert the user name and password (twice) for the Azure SQL DB server on which you wish to run the Shard Elasticity examples.
  8. To create the variable asset, click ADD SETTING, then select ADD VARIABLE.
  9. For this tutorial, create a variable for the fully-qualified Azure SQL DB server name under which the shard map manager and sharded databases will reside. Select String as the VARIABLE TYPE and enter SqlServerName. Click the arrow to proceed.
  10. Enter the fully-qualified Azure SQL DB server name as the VALUE and click on the check.
  11. You have now created both a credential and variable asset that will be used in the Shard Elasticity runbooks. Proceed to the next step.

5. Upload PowerShell runbooks as Azure Automation

Upload the four example PowerShell runbooks provided.

  1. To upload a new Azure Automation Runbook, click RUNBOOKS in the ribbon.
  2. At the bottom of the screen, click IMPORT.
  3. Navigate to the folder holding the file, and select SetupShardedEnvironment.ps1, and click the check mark.
  4. Repeat steps 2 and 3 for the three remaining PowerShell runbooks (ProvisionByDate.ps1ProvisionBySize.ps1, andReduceServiceTier.ps1).
  5. Proceed to the next step.

6. Setup a Sharded Environment 

The next step is to execute the SetupShardedEnvironment runbook which provisions a sharded environment, complete with a shard map manager DB, a range shard map, and a shard for the current day.

  1. Select the SetupShardedEnvironment runbook by clicking on its name.
  2. Click AUTHOR on the ribbon.
  3. From this screen, you will see the code (and be able to edit, if you so choose) that composes the runbook. To set up the sharded environment, click the TEST button at the bottom of the screen. Confirm that you want to save and test the runbook.
  4. You can watch the status of the job in the output pane. When finished, the Azure SQL DB server that you specified will be populated with a shard map manager database as well as a shard database. The SetupShardedEnvironment runbook is only intended to provision the sharded environment and is not intended to run on a reoccurring schedule. Proceed to the next step.

7. Test the Automation runbooks 

Test the successful execution of each of the runbooks before publishing and scheduling the runbook.

  1. Click RUNBOOK on the ribbon at the top of the page.
  2. Click the ProvisionByDate runbook.
  3. Click AUTHOR on the ribbon at the top of the page.
  4. Click **SAVE **then TEST.
  5. Repeat for the ReduceServiceTier.

 Note, since ProvisionBySize and ProvisionByDate both provision new shards (using different algorithms), it is not necessary to run ProvisionByDate at this time.

8. Publish the runbooks

The next step is to publish the runbook so that it can be scheduled to execute on a periodic basis.

  1. Click PUBLISH on the bottom of the page.
  2. Click Published.
  3. Proceed to the next step.

9. Schedule the runbooks

The final step is to create and link a schedule to the runbook published above.

  1. Click SCHEDULE at the top of the page.
  2. Click LINK TO A NEW SCHEDULE.
  3. Name the schedule appropriately and click the right arrow button.
  4. Configure the schedule.
  5. When finished, click the check at the bottom of the box.
  6. Once the job has been executed based on the previously established schedule, click the JOBS option on the ribbon at the top of the page and then select the scheduled job.

10. Conclusion 

You have now successfully authored and packaged a PowerShell module, uploaded the PowerShell module to Azure Automation as an Asset, and created, tested, published and scheduled a runbook. To monitor the health and status of the runbook, use the dashboard and jobs tabs.

The provided examples only scratch the surface for what is possible when combining Elastic database tools, Azure SQL DB, and Azure Automation. Experiment and build upon these examples to enable your Azure SQL DB Elastic database tools application to scale horizontal, vertically, or both.

For more information about Elastic Scale, please see the following: