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 !!!
- 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 .
- 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)
- 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.
- 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)