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.
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!
This Runbook runs the SQL command in the variable $Query
String name of the SQL Server to connect to, imported from the variable object in the Automation Account
String name of the SQL Server database to connect to, imported from the variable object in the Automation Account
PSCredential containing a username and password with access to the SQL Server, imported from the credential store in the Automation Account
AUTHOR: Cor den Boer
#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!