Scale Azure SQL Database with PowerShell

In the pursuit to move to Azure we have had the need to scale the SQL databases. Your company may only have high or any traffic during certain times or even certain season. We need the best solution during working hours so this is what I have based it off.

The program will take the database you want to scale and then you can set it to be whichever setting you require. So lets begin…

The first step is to install the necessary programs and plugins. These are

• Install Azure Power Shell
• Install Command-Line tools

These can be downloaded from The Azure Downloads Section.

Now we get into the PowerShell scripting part. The first section is to get the credentials for the database server. There are two method to this that I have found.

The simplest and securist method is to use ‘Get-Credential’. This will promote you for the username and the password for the database, that will be used later for the authentication.
#Varibles
$DBname = "protech"
$creds = Get-Credential
The other method is less secure as you will have your username and password stored in the file unencrypted. Though this is the best method to then have it automated with out the need for user interaction.
#Varibles
$DBname = "database"
$username = "username"
$password = "password"


#Credentials
$secstr = New-Object -TypeName System.Security.SecureString
$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
$creds = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr

This section then gets the connection to the database. This is where it will use your ‘creds’ variable and also the full name to the database. You can get this by following the step guide on How to find the Fully Qualified Azure SQL Database Name. In the example below I have put ‘DBname.database.windows.net’
$serverContext = New-AzureSqlDatabaseServerContext -Credential $creds -FullyQualifiedServerName DBname.database.windows.net
Other methods to get the Server Context are on the Microsoft Website. https://msdn.microsoft.com/en-us/library/dn546736.aspx

Finally you can connect to the database using you ‘serverContext’ and the chosen ‘DBname’.
$db = Get-AzureSqlDatabase $serverContext –DatabaseName $DBname
Below are how to change to the different Azure SQL Database Tiers. I have added how to change to each one, so you will just have to chosen which you would like.

---

Scale Azure SQL Database to Basic

$b = Get-AzureSqlDatabaseServiceObjective $serverContext -ServiceObjectiveName "Basic"
Set-AzureSqlDatabase $serverContext –Database $db –ServiceObjective $b –Edition Basic

---

Scale Azure SQL Database to tandard 1

$S1 = Get-AzureSqlDatabaseServiceObjective $serverContext -ServiceObjectiveName "S1"
Set-AzureSqlDatabase $serverContext –Database $db –ServiceObjective $S1 –Edition Standard

---

Scale Azure SQL Database to Standard 2

$S2 = Get-AzureSqlDatabaseServiceObjective $serverContext -ServiceObjectiveName "S2"
Set-AzureSqlDatabase $serverContext –Database $db –ServiceObjective $S2 –Edition Standard

---

Scale Azure SQL Database to Standard 3

$S3 = Get-AzureSqlDatabaseServiceObjective $serverContext -ServiceObjectiveName "S3"
Set-AzureSqlDatabase $serverContext –Database $db –ServiceObjective $S3 –Edition Standard

---

Scale Azure SQL Database to Premium 1

$P1= Get-AzureSqlDatabaseServiceObjective $serverContext -ServiceObjectiveName "P1"
Set-AzureSqlDatabase $serverContext –Database $db –ServiceObjective $P1 –Edition Premium

---

Scale Azure SQL Database to Premium 2

$P2= Get-AzureSqlDatabaseServiceObjective $serverContext -ServiceObjectiveName "P2"
Set-AzureSqlDatabase $serverContext –Database $db –ServiceObjective $P2 –Edition Premium

---

Scale Azure SQL Database to Premium 3

$P3= Get-AzureSqlDatabaseServiceObjective $serverContext -ServiceObjectiveName "P3"
Set-AzureSqlDatabase $serverContext –Database $db –ServiceObjective $P3 –Edition Premium

---

An issue I found was the once I had saved the PowerShell file it wouldn’t run. There would be permissions issue to run the program, so to get round this if you run the below script first it will give you the permissions to run the code.

Error:

Set-ExecutionPolicy : Access to the registry key
‘HKEY_LOCAL_MACHINESOFTWAREMicrosoftPowerShell1ShellIdsMicrosoft.PowerShell’
is denied.

Solution:

Set-ExecutionPolicy Unrestricted