Azure Automation, RunBooks and T-SQL Query output directly to Azure Blob Storage

We often perform tasks to export data using custom T-SQL queries against a SQL Server (either in cloud or on-premises) and store it on a local/network shared drive/Cloud Storage for the data analytics team or for other purposes. The data export process can either be done manually or using an automated scripting process, and in a variety of ways using SQL Agent job, PowerShell with Windows Task or a third-party tool.

When we are in the Microsoft Azure Cloud, it is a good idea to choose a capable automation technique. Azure Automation is the best option and can be considered for all automation needs who loves PowerShell scripting language.

Now let’s say there is a requirement to extract data from Azure SQL (either SaaS, PaaS or IaaS environment) and store it in CSV format on a regular basis. As the SaaS offering (Azure SQL Database) does not support SQL Server Agent, Azure Automation using PowerShell is the most efficient way to perform this task. We can easily leverage all of our existing code with only some minor tweaking required.

Azure Automation - PowerShell:
For this example, we have an Azure SQL server “shb-srv-db-01.database.windows.net” and a database “WorkForceDB”. We need to extract data from this database using one or more T-SQL queries every night and dump the data directly into an Azure blob storage. Following are some required steps to create an Azure Automation Runbook:

1. Create an Azure Automation Account.
2. Create Runbook, specify PowerShell as scripting language.
3. Add the PowerShell Script.
4. Publish the Runbook.
5. Create and configure a Schedule.
6. Link the schedule to the Runbook.

How does the PowerShell Script work?
This PowerShell Script can be run on-premises as well as in Azure Cloud. In both cases we need to import the Az.Storage module. We can execute this script manually in PowerShell ISE/VS Code as well as using Windows Task Scheduler:

1. Connects to a SQL Server.
2. Execute one or more queries.
3. Stage the output in $Env:temp location.
4. Connect to the Azure Storage Account
5. Copy the CSV file to the blob storage.

PowerShell Script:

#Variable declaration

# Connect to an on-premise SQL Server 
# $SQLServer = "tcp:SHBMAIN\SQL2019,52225"  

# Connect to Cloud based SQL Server
$SQLServer = "tcp:shb-srv-db-01.database.windows.net,1433"  

$DatabaseName = "WorkForceDB"  
$UserName ="sqladmin"  
$Password = "Winter20"   
$delimiter = ","

# fine name and staging area to told query output
$FileName1 = $env:temp +"/AzureQueryOutput1_$(get-date -f yyyyMMdd-hhmmtt).csv"
# second file name of the second query output
$FileName2 = $env:temp +"/AzureQueryOutput2_$(get-date -f yyyyMMdd-hhmmtt).csv"

# SQL Query and connection to the database server
$SqlQuery = "SELECT * FROM dbo.tbl01;SELECT * FROM dbo.tbl02;"  
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection  
$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$DatabaseName;Integrated Security=False;UID=$UserName;PWD=$Password"
$SqlConnection.Open()
  
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
$SqlCmd.CommandText = $SqlQuery  
$SqlCmd.Connection = $SqlConnection  
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
$SqlAdapter.SelectCommand = $SqlCmd   

#export the query result  
$DataSet = New-Object System.Data.DataSet  
$SqlAdapter.Fill($DataSet)  
$DataSet.Tables[0] | export-csv -Delimiter $delimiter -Path $FileName1 -NoTypeInformation 
$DataSet.Tables[1] | export-csv -Delimiter $delimiter -Path $FileName2 -NoTypeInformation 
$SqlConnection.Close()

# storage account information
$StorageAccountName = "storesqloutput"
$StorageAccountKey = "gMdFZwzNoB8DPGVi2cwTexqUt9gp6ET07rwEdgqrPHnc68cZz4Dmm6rasrIw7D5KSETaC7q+lF5d7RXsThUbPQ=="
$ContainerName = "sqloutput"

$Context = New-AzStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
$Container = Get-AzStorageContainer -Container $ContainerName -Context $Context 

# export the query result to blob storage
$Container | Set-AzStorageBlobContent -File $FileName1 -BlobType Block -Force -Verbose
$Container | Set-AzStorageBlobContent -File $FileName2 -BlobType Block -Force -Verbose

# end of script

Azure RunBook:

Test run of the PowerShell RunBook:

PowerShell output to Azure Blob Storge:

Executing same script in PowerShell ISE:

Further Reading:

1. Azure Automation: Your SQL Agent in the Cloud: https://azure.microsoft.com/is-is/blog/azure-automation-your-sql-agent-in-the-cloud/
2. Azure Automation documentation: https://docs.microsoft.com/en-us/azure/automation/