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.
-
Create an Azure Automation Account
-
Add module to Azure Automation Account
-
Adding a SQL Server Credential
-
Create a Runbook
-
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
Fill in the details and select the correct subscription and Resource Group.
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.
Search for sqlserver Created by matteot_msft.
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.
Add a Credential name AzureSQLDBAutomationAccount and define a password.
If this account is not created in your Azure SQL Database, then you need to add this account as well:
[sql]
--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'
[/sql]
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.
Name = “AzureSQLDB_Run_StoredProcedure”
Runbook Type =”Powershell”
[ps]
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
[/ps]
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
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.
Define a Name, Schedule Start Time and the Recurrence for the Schedule and click on Create
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.
