Execute a SQL script against a set of azure PaaS databases listed in an excel file. ( Azure AD authentication)

One of my customer has varieties of applications hosted in hybrid environment( on-prem and cloud) predominantly on azure platform and a significant effort is been put to move the back end platform to azure where ever its viable . This pushed me to develop quite a few automation scripts which helped us to get the things done with minimal manual work.

Recently I was pulled into develop a script which should convert a PaaS database tier from DTU to vCore for whatever the business reasons they found . Let me tell you I am talking about 5000+ databases what they are targeting and spread across prod to non-prod . So it’s impossible to accomplish this task using azure portal / UI . Hence I may have to use the script and scale up the execution in multiple databases .

We had brainstorming hours to get the requirements as well as to make our road map vivid , of course we decided to go for PowerShell without much hesitation !!!

  1. Its easily do-able using the DDL “”alter database <database_name>  modify(edition = ‘GeneralPurpose’ ,service_objective = ‘<target_tier>’)” . Let’s see how can I execute this in a loop .
  2. Need a brief downtime during this activity ( during the process when a database gets migrated from DTU to vCore) , so the duration of execution may depends on region , off-business hours etc..(how busy your database is when you migrate)
  3. One approach I considered for developing script was to get the databases names using “sys.databases” from master database and pass it in a loop to execute the migration script. But this is not possible due to point number “2” as they have a mixed collection of databases hosted in a single logical server.
  4. Customer can provide me an excel with server names , database names and corresponding vCore tier model what we are targeting ( followed a rule of thumb, every 100 DTUs in the standard tier require at least 1 vCore, and every 125 DTUs in the premium tier require at least 1 vCore). We picked this approach as we have more control in the activity .
Final script :

# Defined this function to build the connection string using ADO.Net client # reason being Microsoft has already disclosed that there is no plan        # dropping support for System.Data.SqlClient any time soon .
# I want to use more secured AAD account for authentication 
# Note : invoke-sqlcmd won’t support Azure Active Directory authentication as of 3/16/2020 .

function Get-QueryExecutionOutput ($SQLconnectionstring, $SQLquery) {
$Conn = New-Object System.Data.SqlClient.SqlConnection($SQLconnectionstring)
$Conn.open()
$read = New-Object System.Data.SqlClient.SqlCommand($SQLquery,$Conn)
$read.CommandTimeout = "300"
$da = new-object System.Data.SqlClient.SqlDataAdapter($read)
$dt = New-Object system.Data.datatable
[void]$da.fill($dt)
$Conn.close()
return $dt
}

# My source excel path , change appropriately with yours
$excelfile = "C:\Array\vCore.xlsx"
$sheetgiven = "Sheet1"


# Create Excel object
$excelCOM = New-Object -com Excel.Application
#$excelCOM.Visible = $false

#opening the source file and reading the data from the sheet name assigned  variable
$workbook = $excelCOM.workbooks.open($excelfile)
$sheet = $workbook.Worksheets.Item($sheetgiven)

#select total rows
$maxrows = ($sheet.UsedRange.Rows).Count

#assign new powershell-object with values like ServerName , DatabaseName and Tergetmodel
$powershellobject = New-Object -TypeName psobject
$powershellobject | Add-Member -MemberType NoteProperty -Name ServerName -Value $null
$powershellobject | Add-Member -MemberType NoteProperty -Name DatabaseName -Value $null
$powershellobject | Add-Member -MemberType NoteProperty -Name Targetmodel -Value $null
$outputarray = @()
#$i =2 , because im skipping column names
#for loop  will iterate till we reach max row count
for ($i = 2; $i -le $maxrows; $i++)
{
    $adhocobject = $powershellobject | Select-Object *
    $adhocobject.ServerName = $sheet.Cells.Item($i,1).Text
    $adhocobject.DatabaseName = $sheet.Cells.Item($i,2).Text
    $adhocobject.Targetmodel = $sheet.Cells.Item($i,3).Text
    $servername = $adhocobject.ServerName
    $Databasename = $adhocobject.DatabaseName
    $Targetmodel = $adhocobject.Targetmodel
    $SQLquery = "alter database $Databasename  modify(edition = 'GeneralPurpose' ,service_objective = '$Targetmodel')"
    $SQLconnectionstring = "Data Source=tcp:$servername;Initial Catalog=master;Authentication=Active Directory Password;User id=<>;Password=<>;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;"

# function call to execute the query against the dynamically built connection string .
Get-QueryExecutionOutput "$SQLconnectionstring" $SQLquery 
#creates a new array with the same elements as old one + the new item, and this new larger array replaces the old one in the $outputarray variable         
$outputarray += $adhocobject
}
$excelCOM.quit()
get-process *excel* | stop-process -force

Expectations from DBA :

  • Install required Azure PowerShell modules .
  • $file = “C:\Array\vCore.xlsx” your excel file .
  • Should have only 3 columns ( ServerName , DatabaseName ,Targetmodel ) should be in same order . You are expected to fill servername , DatabaseName and Target model .
  • No null values are accepted in the excel sheet .
  • You may have to filter out elastic pool databases from the list.
  • If you want a detailed output then you may define a variable to get the output from “Get-QueryExecutionOutput” function . Then this variable content can be written to a text file using “Out-File” and -append . ( within for-each loop)