normalian blog

Let's talk about Microsoft Azure, ASP.NET and Java!

Setup tips for SQL DB auto export PowerShell scripts

SQL Database offered to backup SQL Database instances with their build-in features, but it was expired now. You can choose below options.

In this post, I will introduce setup tips for the scripts. Please read README of "Automate export PowerShell script with Azure Automation" to setup this script at first.

Add SQL DB instances into single script

You can add other databases to add them into “$databaseServerPairs” in below code.

And please use other credentials if you use other SQL Database servers.

Export error when SQL DB instances so large

Please read this section when you get below error.
f:id:waritohutsu:20180218102309p:plain

The error message is caused by below line.
- https://github.com/Microsoft/sql-server-samples/blob/master/samples/manage/azure-automation-automated-export/AutoExport.ps1#L115

The error is caused by below, so it seems to take too long time to copy DB data.

  if((-not $? -and $global:retryLimit -ile $dbObj.RetryCount) -or ($currentTime - $dbObj.OperationStartTime).TotalMinutes -gt $global:waitInMinutes)

Please change variable “$waitInMinutes = 30;” from 30 minutes to long time.

In order to execute the runbook do I need to have the automation account to have the ability to “Run As account”?

“Azure Run As account” is needed, because we can't execute Runbook scripts without this. It needs to enable Azure Active Directory to register applications.
https://docs.microsoft.com/en-us/azure/automation/automation-create-aduser-account#create-an-automation-account-in-the-azure-portal

" 429 Too many requests" error in Runbook Job log when exporting large SQL Database instances

You will get below error when you execute long jobs.

Get-AzureSqlDatabaseImportExportStatus : A task was canceled.
At line:181 char:11
+ ...    $check = Get-AzureSqlDatabaseImportExportStatus -Request $dbObj.Ex ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-AzureSqlDatabaseImportExportStatus], TaskCanceledException
    + FullyQualifiedErrorId : 
Microsoft.WindowsAzure.Commands.SqlDatabase.Database.Cmdlet.GetAzureSqlDatabaseImportExportStatus

The error is caused by frequent requests using “Get-AzureSqlDatabaseImportExportStatus”, so it need to insert “Start-Sleep” in the script to reduce Azure Management API calls internally.