Using Cloud Shell To Run SQL Scripts On Multiple Azure SQL Databases
If you have many databases (maybe hundreds or even thousands), you need efficient ways to manage them. Using CloudShell (in the Azure Portal) you can easily automate maintenance tasks. In this post I will show how to run SQL scripts on multiple databases.
Azure Cloud Shell
Azure Cloud Shell is an interactive shell built into the Azure Portal. You can use it from any web browser and it supports PowerShell scripts as well as Bash. It also comes with Azure CLI pre-installed.
You access it either by going to https://shell.azure.com or by clicking the Cloud Shell icon in the Azure Portal:
The first time you start Cloud Shell, it goes through some setup. It needs a storage account and will help you create one if necessary.
Install the Azure Az PowerShell module
You are going to need the Az PowerShell module. Currently (as of March 2022) it is not preinstalled in Cloud Shell.
Start Cloud Shell and make sure you have selected PowerShell:
To install the Az PowerShell module, run this script:
1Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force
The installation should take a couple of minutes. Don't worry if it seems to be stuck for a few minutes at first.
Preparing the scripts
There is an icon for uploading/downloading files to Cloud Shell.
For demo purposes, I have chosen to run Ola Hallengren's maintenance scripts (download). I'm using the CommandLog.sql, CommandExecute.sql and IndexMaintenance.sql scripts.
Also I created a file with my own script CloudShellDemoScript.ps1. You can open an editor by running code.
This is the full script (which you can copy/paste into code):
1<#
2.Synopsis
3A Cloud Shell (PowerShell) script to run SQL scripts on multiple Azure SQL databases.
4
5.DESCRIPTION
6This is a demo script on how to run SQL scripts on all Azure SQL databases belonging to a server.
7
8.Notes
9Created : 2022-03-27
10Version : 1.0
11Author : Johan Åhlén
12Twitter : @how2codeinfo
13Blog : https://www.how2code.info
14Disclaimer: This script is provided "AS IS" with no warranties.
15#>
16
17Import-Module SQLServer
18Import-Module Az.Accounts -MinimumVersion 2.2.0
19
20$resourcegroup = "rg-sql"
21$servername = "sql-johanlab1"
22
23$databases = Get-AzSqlDatabase -ResourceGroupName $resourcegroup -ServerName $servername
24| Where-Object {$_.Edition -ne "System"}
25| Select-Object -Property ResourceId,DatabaseName,ElasticPoolName
26
27# Get an access token for the current user so we can login to the Azure SQL Databases
28$dbaccesstoken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
29
30# List databases and which elastic pool they belong to
31Write-Output ($databases | Select-Object -Property DatabaseName,ElasticPoolName | Format-Table)
32
33foreach ($database in $databases)
34{
35 # Display progress
36 Write-Output "Now processing database $($database.DatabaseName)"
37
38 # Run scripts
39 Invoke-Sqlcmd -InputFile "CommandLog.sql" -ServerInstance "$($servername).database.windows.net" -Database $database.DatabaseName -AccessToken $dbaccesstoken
40 Invoke-Sqlcmd -InputFile "CommandExecute.sql" -ServerInstance "$($servername).database.windows.net" -Database $database.DatabaseName -AccessToken $dbaccesstoken
41 Invoke-Sqlcmd -InputFile "IndexOptimize.sql" -ServerInstance "$($servername).database.windows.net" -Database $database.DatabaseName -AccessToken $dbaccesstoken
42
43 # If you wish to run a simple statement instead of a script file, this is how...
44 # Invoke-Sqlcmd -ServerInstance "$($servername).database.windows.net" -Database $database.DatabaseName -AccessToken $dbaccesstoken -Query "SELECT 1"
45}
Press Ctrl+S in code to save and Ctrl+Q to quit.
Running the script
Now you should be able to see your files:
You can execute your script by typing something like:
1./CloudShellDemoScript.ps1
And you should get an output like this:
P.S. This script can be run locally in PowerShell as well (provided you have installed the Azure Az Powershell module).
If your databases are accessible only within VNETs
If you have disabled public network access to your databases and disallowed Azure service to connect, you will have to connect Cloud Shell to the database VNET.
To change connection settings, you first need to dismount your Cloud Shell drive:
Then you can setup Cloud Shell again and select your VNET.
Note that you will need a Network Profile and a Relay to connect to your VNET. See this article: Deploy Cloud Shell into an Azure virtual network.
Good luck!