Azure Automation: Run SQL command on Azure SQL (Manual)

Posted On September 17, 2020

How cool would it be to automate your daily SQL tasks using Azure Automation? Well, really cool off course! Lets start using Azure Automation! Go ahead, if you don’t have an automation account yet, create one by going to Automation Accounts.

Give your automation account a name, choose a subscription, resource group and a location and hit the create button!

STEP 1: Now go to Modules in your Automation Account, click on Browse gallery

Now from the Gallery search for SqlServer by matteot_msft, and click on the module

Now run the import of the SqlServer PowerShell module.

STEP 2: Next step is to create a credential to login to SQL Database. Create an Azure SQL Database user with the correct permissions using the manual creating and setting SQL users and permissions.

Go to Credentials, and click Add a credential.

Create the credential with the credentials from the Azure Service account

STEP 3: In the next step we are going to create the variable required by the script. Create the following variables:

• Database (fill in the database name)
• SqlServer (fill in the full server url, example: 2azuredemo.database.windows.net)

STEP 4: Go to runbooks, and create a new runbook!

Give your runbook a name, as type select PowerShell!

In the new opened window copy and paste the code from below. Adjust the SQL query with your own.

Use the menu to Save your runbook, use the Test pane to review the output of your PowerShell script. When ready Publish your runbook!

<#

DESCRIPTION
	This Runbook runs the SQL command in the variable $Query

PARAMETER SqlServer
    String name of the SQL Server to connect to, imported from the variable object in the Automation Account

PARAMETER Database
    String name of the SQL Server database to connect to, imported from the variable object in the Automation Account

PARAMETER SqlCredential
    PSCredential containing a username and password with access to the SQL Server, imported from the credential store in the Automation Account

NOTES
    AUTHOR: Cor den Boer
    LASTEDIT: 17-09-2020
#>

#Import your Credential object from the Automation Account
 $SQLServerCred = Get-AutomationPSCredential -Name "SqlCredential"
 #Import the SQL Server Name from the Automation variable.
 $SQL_Server_Name = Get-AutomationVariable -Name "SqlServer"
 #Import the SQL DB from the Automation variable.
 $SQL_DB_Name = Get-AutomationVariable -Name "Database"


# Query to execute
 $Query = "select * from Table"

# Execute query
 "----- Running SQL Command "
 invoke-sqlcmd -ServerInstance "$SQL_Server_Name" -Database "$SQL_DB_Name" -Credential $SQLServerCred -Query "$Query" -Encrypt
 "`n ----- END SQL Command"

STEP 5: Last step is to create a schedule. From your workbook go to Schedules, and Add a schedule.

Create a new schedule based on your requirements/needs.

Click create to finalize the process. Now just wait and enjoy your automated task!