ERWIN & BUSINESS ANALYTICS

Using Azure Automation to generate data in your WideWorldImporters database

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 *

three × two =

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

Azure Data Factory: How to assign a Customer Managed Key

Customer key With this new functionality you can add extra security to your Azure Data Factory environment. Where the data was first encrypted with a randomly generated key from Microsoft, you can now use the customer-managed key feature. With this Bring Your Own Key...

Azure Data Factory: Generate Pipeline from the new Template Gallery

Last week I mentioned that we could save a Pipeline to GIT. But today I found out that you can also create a Pipeline from a predefined Solution Template.Template Gallery These template will make it easier to start with Azure Data Factory and it will reduce...

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

Change your Action Group in Azure Monitoring

Change a Action GroupPrevious Article In my previous artcile I wrote about how to create Service Helath Alerts. In this article you will learn how to change the Action Group to add, change or Remove members(Action Group Type Email/SMS/Push/Voice) Azure Portal In the...

Azure DevOps and Azure Feature Pack for Integration Services

Azure Feature Pack for Integration ServicesAzure Blob Storage A great addition for SSIS is using extra connectors like  Azure Blob Storage or Azure Data Lake Store which are added by the Azure Feature Pack. This Pack needs to be installed on your local machine. Are...

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

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 SQL Data Warehouse: How to setup Reserved Capacity

Purchase your Azure SQL Datawarehouse Reservation   Since a few weeks you can buy Reserved Capacity for an Azure SQL Datawarehouse (SQLDW). This Reservation can save you up to 65% on the normal Pay as You go rates with a 3 year pre-commit. A pre-commit of 1 year...

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