Get-AzureSqlDatabaseCopy not returning expected value when copying database using Azure Automation

Question

I wrote a PowerShell script to automate the copying of a database using Azure Automation. It had worked fine for a couple of months but there has been some strange behavior related to the Get-AzureSqlDatabaseCopy command. I was using the Get-AzureSqlDatabaseCopy to check whether the database copy has completed before moving onto the next step in the process. If the database copy hasn't completed in time then it will cause an error.

$SqlServer = 'servername.database.windows.net'
$DatabaseSource = 'Database1'
$DatabaseDestination = 'Database2'

Write-Output "Starting database copy - $DatabaseDestination"
Start-AzureSqlDatabaseCopy -ServerName $SqlServer -DatabaseName $DatabaseSource -PartnerServer $SqlServer -PartnerDatabase $DatabaseDestination    

$i = 0
$secs = 0
do
{
    $check = Get-AzureSqlDatabaseCopy -ServerName $SqlServer -DatabaseName $DatabaseDestination
    $i = $check.PercentComplete
    Write-Output "Database Copy ($DatabaseDestination) not complete in $secs seconds"

    $secs += 10
    Start-Sleep -s 10
}
while($i -ne $null -and $secs -lt 600)

This code has worked fine in the past and the database copy normally takes 30-40 seconds to complete. There looks to be a problem with

$check = Get-AzureSqlDatabaseCopy -ServerName $SqlServer -DatabaseName $DatabaseDestination

$check does not look like it is being set to anything so $i is null and the loop does not work. This is something that only started happening in the last week. I went through some of the job records and it had been working fine until Thursday. I ran some test database copies today and $check is consistently not being set.

Does anyone know a reason for this change in behavior? Is there a better way to monitor a database copy using PowerShell in Azure Automation?

It appears Get-AzureSqlDatabaseCopy is returning null. Does the same command return null when run locally in the PowerShell ISE? This will help determine whether the issue is with Azure Automation or with the cmdlet itself. Also, do the Azure Automation jobs complete successfully, and do they have any error records if you look on the job's history / streams page? — Joe
– Joe, Commented Aug 11, 2015 at 19:56
I ran the same script in PowerShell ISE and it had the same problem so it is something to do with the cmdlet. I've looked through the history of the jobs and I haven't seen anything unusual except that Get_AzureSqlDatabaseCopy is returning null when — Tom
– Tom, Commented Aug 12, 2015 at 16:31
I ran the same script in PowerShell ISE and it had the same problem so it is something to do with the cmdlet. I've looked through the history of the jobs and I haven't seen anything unusual except that Get_AzureSqlDatabaseCopy is returning null when I was expecting a value. I removed the -DatabaseName parameter and it does work. I checked the documentation for Get_AzureSqlDatabaseCopy and I can't see a reason why this should be returning a null value. It is expecting a string value and I've checked that it is correct. — Tom
– Tom, Commented Aug 12, 2015 at 16:42

MihaelaBlendea · Accepted Answer · 2015-08-14 00:20:42Z

2

Below check for existence of the copy is done on the target side:

$check = Get-AzureSqlDatabaseCopy -ServerName $SqlServer -DatabaseName $DatabaseDestination

The target side copy object is not guaranteed to be created before Start-AzureSqlDtaabaseCopy returns, so $check becomes null prematurely, meaning that the copy did not actually complete.

To fix this, we recommend to check the existence of the copy on the source side:

$check = Get-AzureSqlDatabaseCopy -ServerName $SqlServer -DatabaseName $DatabaseSource -PartnerDatabase $DatabaseDestination

answered Aug 14, 2015 at 0:20

MihaelaBlendea

2181 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get-AzureSqlDatabaseCopy not returning expected value when copying database using Azure Automation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related