Using Azure Automation to generate data in your WideWorldImporters database

by Apr 17, 2020

CASE:

For my test environment I want to load every day new increments into the WideWorldImporters Azure SQL Database with Azure Automation. The following Stored Procedure is available to achieve this.

 EXECUTE DataLoadSimulation.PopulateDataToCurrentDate

@AverageNumberOfCustomerOrdersPerDay = 60,

@SaturdayPercentageOfNormalWorkDay = 50,

@SundayPercentageOfNormalWorkDay = 0,

@IsSilentMode = 1,

@AreDatesPrinted = 1;

For this case I’m going to use Azure Automation to Execute this Stored Procedure on a daily base.

More details on above Stored Procedure can be found here.

In case you don’t have a WideWorldImporters database in your Azure environment you can download it from GitHub.

  1. Create an Azure Automation Account
  2. Add module to Azure Automation Account
  3. Adding a SQL Server Credential 
  4. Create a Runbook
  5. Create a Schedule

Create an Azure Automation Account

First of all we need to create an Azure Automation Account in our Azure Environment.

Go to the portal and search for Automation and click op Create

Create Azure Automation

Fill in the details and select the correct subscription and Resource Group.

Create Azure Automation

Click on create and wait for the account to be created. The new Automation Account configuration blade will be opened once the provision is completed.

Add module to Azure Automation Account

The next step is that we need to add the “SQLSERVER” module to our Automation Account.

Select Modules and Browse Galery.

Create Azure Automation

Search for sqlserver Created by matteot_msft.

Create Azure Automation

Then click on Import and OK and wait for the import to complete.

Add SQL Server Credentials

Click on add a Credential, this is a secure way to hold your login name and password that will be used to access the Azure SQL Database.

Create Azure Automation

Add a Credential name AzureSQLDBAutomationAccount and define a password.

Create Azure Automation

If this account is not created in your Azure SQL Database, then you need to add this account as well:


--Execute in Master Database

create login AzureSQLDBAutomationAccount with password='Your defined Password'           


--Execute in WideworldImporters Database

create user AzureSQLDBAutomationAccount


for login AzureSQLDBAutomationAccount


with default_schema= dbo


--add user to the dabase role


exec sp_addrolemember  N'db_owner' , N'AzureSQLDBAutomationAccount'

Create a Runbook

The next step is to create a Runbook.

You can Import an existing Runbook or Create a new Runbook.

For now we will Create a new RunBook.

Create Azure Automation

Name = “AzureSQLDB_Run_StoredProcedure”

Runbook Type =”Powershell”

Create Azure Automation

    
     param(
         # Fully-qualified name of the Azure DB server
        [parameter(Mandatory=$true)]
       
        [string] $AzureSQLServerName = 'DBSERVER',

        # Name of database
        [parameter(Mandatory=$true)]
       
        [string] $AzureSQLDatabaseName = 'DBNAME',
        
        # Name of Procedure  'exec dbo.xxxxxxxx'
        [parameter(Mandatory=$true)]
       
        [string] $ProcedureName = 'exec name',


        # Credentials for $SqlServerLogin stored as an Azure Automation credential asset
        [parameter(Mandatory=$true)]
      
         [string] $SqlCredential 
    )
$Credential = Get-AutomationPSCredential -Name $SqlCredential
$AzureSQLServerName = $AzureSQLServerName + '.database.windows.net' 

$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Credential.UserName -Password $Credential.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query $ProcedureName -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1 

Write-Output $SQLOutput

This a simple Powershell script which can execute a Stored Procedure. More details about the PS Invoke-Sqlcmd can be found here.  You can download the Powershell script from my Github

Save the Runbook and then test the runbook. After you tested your Runbook you can Publish the Runbook

Create Azure Automation

Fill in the requested Parameters as showed in the picture above and click on Start. The requested procedure will be executed.

 

Create A Schedule

That last step is create a Schedule which will run every day to incremental load your data.

Create Azure Automation

 

Create Azure Automation

Define a Name, Schedule Start Time and the Recurrence for the Schedule and click on Create

Create Azure Automation

 

Fill in the requested Parameters and click on Save.

I assume that you have now succeeded in creating an Azure Automation Account with these steps and that you could execute a Stored Procedure in an Azure SQL Database. If you have any questions, leave a message in the comments below. Thanks for reading.

TIP:

To update your database to the current date you have to run this procedure quit  lot of times so create a query where you execute the Procedure 100x times in  a row. The downloaded database has data up to may 2016.

In case you get error regarding the Temporal Tables, check your errors in this blog post. I helped me a lot.

 

I my next blog I will describe how you easily can extract data(full and Incremental) from your WideWorldImporters Database to Azure Data Lake Gen2 with Azure Data Factory.

Feel free to leave a comment

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

two + ten =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Azure Data Factory Let’s get started

Creating an Azure Data Factory Instance, let's get started Many blogs nowadays are about which functionalities we can use within Azure Data Factory. But how do we create an Azure Data Factory instance in Azure for the first time and what should you take into account? ...

Azure Synapse Analyics costs analyis for Integration Runtime

AutoResolveIntegrationRuntime! The last few days I've been following some discussions on Twitter on using a separate Integration Runtime in Azure Synapse Analytics running in the selected region instead of auto-resolve. The AutoResolveIntegrationRuntime is...

Azure Synapse Pause and Resume SQL Pool

Pause or Resume your Dedicated SQL Pool in Azure Synapse Analytics Azure Synapse Analytics went GA in beginning of December 2020, with Azure Synapse we can now also create a Dedicated SQL Pool(formerly Azure SQL DW). Please read this document to learn what a Dedicated...

Scale your SQL Pool dynamically in Azure Synapse

Scale your Dedicated SQL Pool in Azure Synapse Analytics In my previous article, I explained how you can Pause and Resume your Dedicated SQL Pool with a Pipeline in Azure Synapse Analytics. In this article I will explain how to scale up and down a SQL Pool via a...

Migrate Azure Storage to Azure Data Lake Gen2

Migrate Azure Storage to Storage Account with Azure Data Lake Gen2 capabilities Does it sometimes happen that you come across a Storage Account where the Hierarchical namespace is not enabled or that you still have a Storage Account V1? In the tutorial below I...

Exploring Azure Synapse Analytics Studio

Azure Synapse Workspace Settings In my previous article, I walked you through "how to create your Azure Synapse Analytics Workspace". It's now time to explore the brand new Synapse Studio. Most configuration and settings can be done through the Synapse Studio. In your...

Azure SQL Data Warehouse: Reserved Capacity versus Pay as You go

How do I use my Reserved Capacity correctly? Update 11-11-2020: This also applies to Azure Synapse SQL Pools. In my previous article you were introduced, how to create a Reserved Capacity for an Azure SQL Datawarehouse (SQLDW). Now it's time to take a look at how this...

Azure Purview announcements and new functionalities

This week the Azure Purview Product team added some new functionalities, new connectors(these connectors where added during my holiday), Azure Synapse Data Lineage, a better Power BI integration and the introduction of Elastics Data Map. Slowly we are on our way to a...

Azure Purview Pricing example

Azure Purview pricing? Note: Billing for Azure Purview will commence November 1, 2021. Updated October 31st, 2021 Pricing for Elastic Data Map and Scanning for Other Sources are changed and updated in the blog below. Since my last post on Azure Purview announcements...

Create Virtual Machines with Azure DevTest Lab

A while ago I had to give a training. Normally I would roll out a number of virtual machines in Azure. Until someone brought my attention to an Azure Service, Azure DevTest Labs. With this Azure service you can easily create a basic image and use this image to roll...