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

Advertisements

Leave a message please

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s