How to automate managed instance database copy and database move operations

Introduction

In this blog I will show how you can automate database copy 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, but what is important and convenient, unlike point in-time restore where database is in state from some point in time in the past, with database copy you get database in state as it was when the operation was completed.

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, and then on the test instance you can investigate state of things 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 by few), and during this process monitor the load on the target managed instance and continue with the process until resource utilization on target managed instance reaches a reasonable threshold and you get good utilization of your resources.

Consolidate multiple databases to one managed instance

If you’re hosting multiple databases on your managed instances, another useful scenario would 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. For example, when moving database, you 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.

Please note that the script shown here 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 scripts in this blog are mostly focused on a happy path and are not handling potential failures that might happen.

How to automate database copy for a set 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 set 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 finish but will progress with the rest of the script. This will allow us to start all database copies in parallel.

As soon as we start database copy operations, we will wait in a loop for all copy operations’ statuses to be “succeeded”. As already mentioned, database copy and move are size of data operations, so this part of the process may take some time.

In this script, as soon as we see that all copy operations succeeded, we 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), we will wait in loop to see that all of them succeeded. Completing database copy is metadata operation that is fairly quick and executes in constant time (not size of data operation). Have in mind, only once the copy operation is completed, your target database will become online and ready for read and write workload.

In the end, once we see all complete operations succeeded, we print a message saying that all databases have been copied.

Connect-AzAccount

Select-AzSubscription -SubscriptionId 0000000-0000-0000-0000-000000000000

$dbNames = @("my_db_01", "my_db_02", "my_db_03", "my_db_04")

$sourceMi = "sqlmi1"

$sourceRg = "rg1"

$targetMi = "sqlmi2"

$targetRg = "rg2"

$timeout = $false

$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++)

{

Copy-AzSqlInstanceDatabase

-DatabaseName $dbNames[$i]

-InstanceName $sourceMi

-ResourceGroupName $sourceRg

-TargetInstanceName $targetMi

-TargetResourceGroupName $targetRg

-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.

##

Complete-AzSqlInstanceDatabaseCopy -DatabaseName $dbNames[$i] -InstanceName $sourceMi -ResourceGroupName $sourceRg -TargetInstanceName $targetMi -TargetResourceGroupName $targetRg -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

}

}

Automating database move operation

In scenarios when you want to move databases from one managed instance to another, instead of using a set of Copy-AzSqlInstanceDatabase commands, you will use set of 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 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 we’ve explained some typical use cases for database copy and move operations, and we’ve shown an example of script that automates database copy for a set of user databases.

For more information on Azure SQL Managed Instance database copy and move, see:

• Documentation: Copy or move a database (preview) - Azure SQL Managed Instance
  
• Public preview blog post: Azure SQL Blog
  
• Database copy and move demo:
  
  
  
   
  
• 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 existing ones) Community.

Continue reading...