ERWIN & BUSINESS ANALYTICS

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 *

17 − 7 =

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

SSMS 18.xx: Creating your Azure Data Factory SSIS IR directly in SSMS

Creating your Azure Data Factory(ADF) SSIS IR in SSMS Since  version 18.0 we could see our Integration Catalog on Azure Instances directly. Yesterday I wrote an article how to Schedule your SSIS Packages in ADF, during writing that article I found out that you can...

Azure Purview Costs in Public Preview explained

Why are you potentially charged for Azure Purview during the public preview? Since I published my post that Azure Purview started billing as of January 21th, I got a lot of questions how billing was working. UPDATE 27th of February: We are extending the Azure Purview...

SSMS 18.1: Schedule your SSIS Packages in Azure Data Factory

Schedule your SSIS Packages with SSMS in Azure Data Factory(ADF) This week SQL Server Management Studio version 18.1 was released, which can be downloaded from here. In version 18.1 the Database diagrams are back and from now on we can also schedule SSIS Packages in...

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 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...

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...

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? ...

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...

Service Healths in Azure

Creating Service Health Alerts in AzureAzure Portal In the Azure Portal go to Monitor – Service Health – Health alerts If you have created alerts before you will see them over here. Assuming you haven’t created an Alert before, we will start to create an Alert.1...

Azure Data Factory Naming Conventions

Naming Conventions More and more projects are using Azure Data Factory, the more important it is to apply a correct naming convention. When using naming conventions you create recognizable results across different projects, but you also create clarity for your...