How cool would it be to automate your daily SQL tasks using Azure Automation? Well, really cool off course! So lets start using Azure Automation! So go ahead, if you don’t have an automation account yet, create one by going to Automation Accounts.
Give your automation account an 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 my manual on 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 an schedule.
Create a new schedule based on your requirements/needs.
Click create to finalize the process. Now just wait and enjoy your automated task!