未加星标

Azure SQL Elastic Pool Moving databases across pools using PowerShell

字体大小 | |
[系统(windows) 所属分类 系统(windows) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

Azure SQL Elastic Pool   Moving databases across pools using PowerShell

I’ve written a bit about Azure SQL Elastic Pool lately: anoverview, aboutARM template and aboutdatabase 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.


Azure SQL Elastic Pool   Moving databases across pools using PowerShell
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.

本文系统(windows)相关术语:三级网络技术 计算机三级网络技术 网络技术基础 计算机网络技术

主题: SQLPowerShellREST
分页:12
转载请注明
本文标题:Azure SQL Elastic Pool Moving databases across pools using PowerShell
本站链接:http://www.codesec.net/view/523058.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 系统(windows) | 评论(0) | 阅读(49)