Tag Archives: PowerShell

PowerShell with a strong focus on Azure SDK / Automation.

Azure SQL Elastic Pool – Moving databases across pools using PowerShell

I’ve written a bit about Azure SQL Elastic Pool lately: an overview, about ARM template and about database size.

One of the many great features of Azure SQL Elastic Pool is that like Azure SQL Database (standalone), we can change the eDTU capacity of the pool “on the fly”, without downtime.

Unlike its standalone cousin though, we can’t change the edition of the pool. The edition is either Basic, Standard or Premium. It is set at creation and is immutable after that.

If we want to change the edition of a pool, the obvious way is to create another pool, move the databases there, delete the original, recreate it with a different edition and move the databases back.

This article shows how to do that using PowerShell.

You might want to move databases around for other reasons, typically to optimize the density and performance of pools. You would then use a very similar script.

Look at the pool

Let’s start with the pools we established with the sample ARM template of a previous article.

From there we can look at the pool Pool-A using the following PowerShell command:


$old = Get-AzureRmSqlElasticPool -ResourceGroupName DBs -ElasticPoolName Pool-A -ServerName pooldemoserver

$old

We can see the pool current edition is Standard while its Data Transaction Unit (DTU) count is 200.

Create a temporary pool

We’ll create a temporary pool, aptly named temporary, attached to the same server:


$temp = New-AzureRmSqlElasticPool -ResourceGroupName DBs -ElasticPoolName Temporary -ServerName pooldemoserver -Edition $old.Edition -Dtu $old.Dtu

$temp

It’s important to create a pool that will allow the databases to be moved into. The maximum size of a database is dependent of the edition and number of DTU of the elastic pool. The easiest way is to create a pool with the same edition / DTU and this is what we do here by referencing the $old variable.

Move databases across

First, let’s grab the databases in the original pool:


$dbs = Get-AzureRmSqlDatabase -ResourceGroupName DBs -ServerName pooldemoserver | where {$_.ElasticPoolName -eq $old.ElasticPoolName}

$dbs | select DatabaseName

ElasticPoolName is a property of a database. We’ll simply change it by setting each database:


$dbs | foreach {Set-AzureRmSqlDatabase -ResourceGroupName DBs -ServerName pooldemoserver -DatabaseName $_.DatabaseName -ElasticPoolName $temp.ElasticPoolName}

That command takes longer to run as the databases have to move from one compute to another.

Delete / Recreate pool

We can now delete the original pool. It’s important to note that we wouldn’t have been able to delete a pool with databases in it.


Remove-AzureRmSqlElasticPool -ResourceGroupName DBs -ElasticPoolName $old.ElasticPoolName -ServerName pooldemoserver

$new = New-AzureRmSqlElasticPool -ResourceGroupName DBs -ElasticPoolName $old.ElasticPoolName -ServerName pooldemoserver -Edition Premium -Dtu 250

The second line recreates it with Premium edition. We could keep the original DTU, but it’s not always possible since different editions support different DTU values. In this case, for instance, it wasn’t possible since 200 DTUs isn’t supported for Premium pools.

If you execute those two commands without pausing in between, you will likely receive an error. It is one of those cases where the Azure REST API returns and the resource you asked to be removed seems to be removed but you can’t really recreate it back yet. An easy work around consist in pausing or retrying.

Move databases back

We can then move the databases back to the new pool:


$dbs | foreach {Set-AzureRmSqlDatabase -ResourceGroupName DBs -ServerName pooldemoserver -DatabaseName $_.DatabaseName -ElasticPoolName $new.ElasticPoolName}

Remove-AzureRmSqlElasticPool -ResourceGroupName DBs -ElasticPoolName $temp.ElasticPoolName -ServerName pooldemoserver

In the second line we delete the temporary pool. Again, this takes a little longer to execute since databases must be moved from one compute to another.

Summary

We showed how to move databases from a pool to another.

The pretext was a change in elastic pool edition but we might want to move databases around for other reasons.

In practice you might not want to move your databases twice to avoid the duration of the operation and might be happy to have a different pool name. In the demo we did, the move took less than a minute because we had two empty databases. With many databases totaling a lot of storage it would take much more time to move those.

Finding ARM template ApiVersion

Login

As usual, please do login in PowerShell Integrated Scripting Environment (ISE) with the usual command Login-AzureRmAccount.

Find your provider

First you need to find the provider for the resource you want to create.

For that, use Get-AzureRmResourceProvider. This will return the list of all available providers.

For me, since I’m looking for the backup vault, the Microsoft.Backup provider seems promissing.

The following will give you all the resource types under the provider (in this case only one):


(Get-AzureRmResourceProvider -ProviderNamespace "Microsoft.Backup").ResourceTypes

You see already that we get very valuable information here. We get the API versions I was looking for but also the Azure regions where the resources are available.

ARM Template

Once you know the Api Version it is much easier to create the arm template. In the case of my backup vault:

{
      "name": "AdvVault-cp",
      "type": "Microsoft.Backup/BackupVault",
      "apiVersion": "2015-03-15",
      "location": "[resourceGroup().location]",
      "tags": { },
      "properties": {
        "sku": {
          "name": "[parameters('skuName')]"
        }
      }
}

Conclusion

There really is a wealth of information you can undig by using just a few Azure PowerShell cmdlets.

One of the tricky part when you reverse engineer an ARM template starting from the Resource Explorer in the portal is to find the API version which you can get with a few cmd lets as demonstrated here.

Listing Resources under Resource Group with Azure PowerShell

Login

Login-AzureRmAccount

This will prompt you to enter an ID + credentials. Once you enter them, you should be in your subscription.

… if you have more than one subscription? Ok, that’s a little more complicated.

You need to find the subscription ID you are interested in. Either go to https://account.windowsazure.com/Subscriptions to find it in the UI or… use more PowerShell scripts:

Get-AzureRmSubscription

Once you have your subscription ID, simply grab the ID and pass it to:

Add-AzureRmAccount –SubscriptionId <your subscription ID here>

This should prompt you again and afterwards, you’ll be in the context of the right subscription.

List resources under resource group

Now that you’re within the right subscription, let’s list the resource groups within that subscription:

Get-AzureRmResourceGroup

This will give you the list of resources under that resource group. Grab the resource group name and then you can list the resources underneath:

$res = Get-AzureRmResource | Where–Object {$_.ResourceGroupName –eq <You resource group name>}

Conclusion

That’s it! I just wanted to get this ceremony out of the way.

You can build on that and use all different kind of cmdlets to query and manipulate your subscriptions.

Move Azure Resources between Resource Groups using Powershell

Vincent-Philippe Lauzon's blog

Azure, Machine Learning & Productivity

Tag Archives: PowerShell

Azure SQL Elastic Pool – Moving databases across pools using PowerShell

Look at the pool

Create a temporary pool

Move databases across

Delete / Recreate pool

Move databases back

Summary

Finding ARM template ApiVersion

Login

Find your provider

ARM Template

Conclusion

Listing Resources under Resource Group with Azure PowerShell

Login

List resources under resource group

Conclusion

Move Azure Resources between Resource Groups using Powershell