[Note (7/30/2024): The script in this blog has been edited to contain TargetSubscriptionId parameter, which should be used for cross-subscription copy and move operations. This parameter is supported in Az.Sql module since version 5.1.0 version.]
Introduction
In this blog I will show how you can automate database copy between two instances, for a set of databases. I will also talk about some typical use cases for database copy and move, and details of how this functionality works and should be used.
What is database copy?
Database copy and database move operations for Azure SQL Managed Instance are very convenient in various situations when you want to copy or move database from one managed instance to another in an online way. What does online mean in this context? It means that the database on destination managed instance will be identical to the source database at the moment when operation is explicitly completed by user action. Copying a database is a size of data operation, and you can expect copy will take some time. What is important and convenient with database copy - you will get a target database as exact copy of source database at the moment when the operation is explicitly completed. So, this differs from point in-time restore, because with PITR you get a database in state from chosen fixed point in time in the past.
Typical use cases
So, what are situations when you typically use database copy or move?
For example, you’ve noticed you have a performance issue each Monday morning. You can start database copy on Sunday from production to test managed instance and complete the copy on Monday morning. This gives you an opportunity to investigate the issue and run performance tests on the database that’s identical to your Monday morning production database.
Another useful scenario for database copy or move is the following. You have several managed instances, hosting several databases. After a while you’ve noticed that all managed instances are underutilized, and you could probably consolidate all databases and their workloads on a single managed instance. With database move you could move databases one by one, or few in a batch. During this process you would typically monitor the load on the target managed instance and continue with the process until resource utilization on target managed instance reaches a reasonable threshold.
Consolidate multiple databases to one managed instance
If you’re hosting multiple databases on your managed instances, another useful scenario could be moving a very busy database from one managed instance to another one with more spare resources, to solve noisy neighborhood situations.
Whatever the motivation for using database copy and move is, with management REST API, Azure PowerShell and CLI you can automate your operations. With automation you can very precisely choose when you want database operation (copy or move) to start, and even more important you can program very precisely when you want the operation to end. In scripts that perform this you can check for different conditions before proceeding with any step, and you can coordinate other relevant processes. And when moving a database, you'd probably want also to update the connection string used by your application, so you can have a smooth database transition from one managed instance to another one.
How to automate database copy for a group of databases?
Database copy (and move) is used like this: you start copy operation, then poll its status until is ready to be completed, and finally you complete the operation explicitly. The diagram below shows this.
Database copy usage flow
And according to the usage pattern, I'll automate database copy for a group of databases in the script shown below. Databases specified in $dbNames list will be copied from source to target managed instance. All Copy-AzSqlInstanceDatabase commands will be issued with parameter -AsJob. This means that each copy process will be started, and the command will not wait for the copy to complete but will progress with the rest of the script. This will allow us to start all individual database copy operations in parallel.
As soon as the script starts database copy operations, it will wait in a loop for the status of all operations to become “succeeded”. As already mentioned, database copy and move are size of data operations, so this part of the process may take some time. Once the Copy-AzSqlInstanceDatabase command succeeds, a database has been successfully seeded to the target managed instance, and from that moment on all changes happening on source databases are replicated to target, until we explicitly complete the copy operation.
In this script, as soon as we see that all copy operations have succeeded, the script will proceed with completing all of them, but have in mind that’s not necessarily what you need to do. You can wait longer, maybe for some other conditions to be met or for optimal timing. One limitation here is – you need to complete the operation within 24h since the copy operation success, otherwise the operation will be automatically cancelled.
Note: Of course, you don’t necessarily need to complete the database copy process – you can also stop it with
Stop-AzSqlInstanceDatabaseCopy.
Back to our script - after issuing all Complete-AzSqlInstanceDatabaseCopy commands (again -AsJob), the script will wait in loop to see that all of them succeeded. Completing database copy is fairly quick operation and it runs only for a few seconds. Have in mind, only once the copy operation has completed, your target database will become online and ready for read and write workloads.
In the end, once all complete operations have succeeded, the script prints a message saying that all databases have been copied.
## Check if Az.Sql version is >= 5.1
##
if (($module = Get-InstalledModule -Name Az.Sql) -and ($module.Version -ge [Version]"5.1.0"))
{ "All good! The installed version of Az.Sql ($($module.Version)) is greater than or equal to 5.1." }
else
{ "Az.Sql module is not installed or is less than 5.1. Install version 5.1 or newer to use latest Azure SQL Managed Instance database copy and move scripts.";
exit;
}
## Specify source and target subscription id.
## Target Subscription id is optional if it's identical to source subscription id,
## or in other words, if database copy or move is happening within one subscription.
$subscription = '00000000-0000-0000-0000-000000000000'
$targetSubscription = '11111111-1111-1111-1111-111111111111'
$dbNames = @("my_db_01", "my_db_02", "my_db_03", "my_db_04")
$sourceMi = "sqlmi1"
$sourceRg = "rg1"
$targetMi = "sqlmi2"
$targetRg = "rg2"
$timeout = $false
Connect-AzAccount -Subscription $subscription
Select-AzSubscription -SubscriptionId $subscription
$expectedDuration = New-TimeSpan -Days 0 -Hours 1 -Minutes 30
$startTime = Get-Date
Write-Host "Starting copy operation for databases: "
for ( $i = 0; $i -lt $dbNames.count; $i++) { Write-Host $dbNames[$i] " "}
## Start copy for all databases in the list.
##
for ( $i = 0; $i -lt $dbNames.count; $i++)
{
## Parameter TargetSubscriptionId is optional is source and target subscription ids are the same.
##
Copy-AzSqlInstanceDatabase -DatabaseName $dbNames[$i] -InstanceName $sourceMi -ResourceGroupName $sourceRg -TargetInstanceName $targetMi -TargetResourceGroupName $targetRg -TargetSubscriptionId $targetSubscription -AsJob | Format-Table -Property Name, PSBeginTime, State
}
$allCopiesSucceeded = $true
while($true)
{
if ((Get-Date) -gt $startTime + $expectedDuration)
{
Write-Host "Script timeout. Terminating the script."
$timeout = $true
break
}
Write-Host "Getting the status of operations..."
Start-Sleep -Seconds 10
$operations = [System.Collections.ArrayList]@()
## Get status of all copy operations.
##
for ( $i = 0; $i -lt $dbNames.count; $i++)
{
$operations += Get-AzSqlInstanceDatabaseCopyOperation -DatabaseName $dbNames[$i] -InstanceName $sourceMi -ResourceGroupName $sourceRg -TargetInstanceName $targetMi -TargetResourceGroupName $targetRg -OnlyLatestPerDatabase
}
try
{
$allCopiesSucceeded = $true;
for ( $i = 0; $i -lt $dbNames.count; $i++)
{
## Call with -AsJob, because this is long running operation (size of data),
## and we want to parallelize calls for multiple databases.
##
Write-Host "Status of copy for " $dbNames[$i] " is " $operations[$i].State
if (!($operations[$i].State -eq "Succeeded")) { $allCopiesSucceeded = $false; }
}
}
catch { $allCopiesSucceeded = $false; Write-Host "Oops, something went wrong. Retrying..." }
if ($allCopiesSucceeded)
{
Write-Host "All operations ready for completion!"
break
}
else { Write-Host "Copying in progress..." }
Write-Host "Waiting a bit..."
Start-Sleep -Seconds 5
}
if ($allCopiesSucceeded -and !$timeout)
{
## Let's complete all database copy operations.
##
Write-Host "Completing all database copy operations..."
for ( $i = 0; $i -lt $dbNames.count; $i++)
{
## Call with -AsJob, because this is not super quick operation
## and we want to parallelize calls for multiple databases.
## Parameter TargetSubscriptionId is optional is source and target subscription ids are the same.
##
Complete-AzSqlInstanceDatabaseCopy -DatabaseName $dbNames[$i] -InstanceName $sourceMi -ResourceGroupName $sourceRg -TargetInstanceName $targetMi -TargetResourceGroupName $targetRg -TargetSubscriptionId $targetSubscription -AsJob | Format-Table -Property Name, PSBeginTime, State
}
$completeDone = $true
while($true)
{
Write-Host "Getting the status of copy complete operations..."
$completeOperations = [System.Collections.ArrayList]@()
for ( $i = 0; $i -lt $dbNames.count; $i++)
{
$completeOperations += Get-AzSqlInstanceDatabaseCopyOperation -DatabaseName $dbNames[$i] -InstanceName $sourceMi -ResourceGroupName $sourceRg -TargetInstanceName $targetMi -TargetResourceGroupName $targetRg -OnlyLatestPerDatabase
}
try
{
$completeDone = $true
for ( $i = 0; $i -lt $dbNames.count; $i++)
{
Write-Host "Status of copy completion for " $dbNames[$i] " is " $completeOperations[$i].State
if (!($completeOperations[$i].State -eq "Succeeded")) { $completeDone = $false; }
}
}
catch { Write-Host "Oops, something went wrong. Retrying..." }
if ($completeDone)
{
Write-Host "Database copy succeeded for databases: "
for ( $i = 0; $i -lt $dbNames.count; $i++) { Write-Host $dbNames[$i] " "}
break
}
else { Write-Host "Complete in progress..." }
Write-Host "Waiting a bit..."
Start-Sleep -Seconds 5
}
}
Note that the script shown above is just a description of how the feature can be used, and you will probably need to modify and adjust it to your specific needs. It would be fair to say that the script is mostly focused on a happy path and is not handling potential failures that might happen.
Automating database move operation
In scenarios when you want to move databases from one managed instance to another, instead of using Copy-AzSqlInstanceDatabase, you can use Move-AzSqlInstanceDatabase commands. Database move operation enables you to move database from one managed instance to another and be sure that no transaction is lost in the process. Once you chose to complete the move operation, source database will become read-only and will no longer accept new changes. Then, all the changes in the transaction log will be applied to the target database, source database will be dropped, and target database will become online and available for read and write workload. This design ensures there is no data loss in the database move process. As you automate database move, as part of the automation you will probably want to modify the connection string your application is using, to be sure that after the move, the application starts connecting to the target managed instance and target database.
Conclusion
In this blog I've explained some typical use cases for database copy and move operations and shown an example of a script that automates database copy for a group of databases.
For more information on Azure SQL Managed Instance database copy and move, see:
- Documentation: https://learn.microsoft.com/azure/azure-sql/managed-instance/database-copy-move-how-to
- Public preview blog post: https://techcommunity.microsoft.com/t5/azure-sql-blog/database-copy-and-move-for-azure-sql-managed-instance/ba-p/3676845
- Database copy and move demo: https://youtu.be/i910bvO9_Jk
- Using database copy with TDE:
As always, if you’d like Azure SQL Managed Instance to get new features, you can propose your ideas here (or upvote the existing ones) https://aka.ms/sqlmi-feedback.
Updated Jul 30, 2024
Version 12.0sasapopo
Microsoft
Joined August 04, 2020
Azure SQL Blog
Follow this blog board to get notified when there's new activity