Schedule Data Sync with Azure SQL Database use an Automation account

[Mohamed_Baioumy_MSFT]

First published on MSDN on Mar 04, 2018
Azure Data Sync (in-preview) , is one of the common Azure SQL DB migration/replication features introduced with Azure SQL DB, one of the common question received regarding Azure Data Sync is about running a Sync based on a schedule, is there any option to setup a customizable schedule can run according to start/end time user can specify?

At the moment, by design Azure Data Sync can be setup with 2 types of schedules, were the first one is automatic schedule, the automatic schedule works based on time intervals, the lowest value is 5 minutes. The second type is manual schedule, and in this type it left for the user to run the sync whenever they need through the Azure Portal or via PowerShell.

From the above we figured the need to build this PowerShell to automate the sync based on a start and end time defined by the user.

The idea behind this PowerShell workflow script is to trigger a one time sync manually, if it succeeds, switch to automatic sync and set Sync time intervals according to your preferences, in the PowerShell workflow script set it to run based on 300 seconds intervals. Then wait until Sync is competed successfully and set Sync back to Off and stop Sync after wait for a XX time you specified, (in our test we set the time to 10 minutes).

Prerequisites:

To complete this exercise, you should have the following resources in place:

Azure SQL DB, and Data Sync group created, either to sync between on-premises database and/or Azure SQL DB, for more reference how to create Azure SQL DB from here , to create a new Data Sync group from here

To create an automation account:

1) Go to https://portal.azure.com

2) Select All services and type “ automation ”



Figure 1 – search for automation account

3) Click “+ Add ” to create a new Automation Account

4) Fill in the required details for your automation account, and make sure “Create Azure Run As account” set to Yes



Figure 2 – create a new automation account

5) Click “ create ”. The new automation created, and configuration blade will be opened

6) First, make sure " AzureClassicRunAsConnection " and " AzureRunAsConnection ” connections created automatically during Automation account creation. Note: (" AzureRunAsConnection " connection will be used in our PowerShell workflow script later to authenticate to Azure subscription)



Figure 3 – confirm connections are created

7) Second, verify that your Automation account has latest PowerShell modules imported and are up to date ( AzureRM.Automation - AzureRM.Profile - AzureRM.Sql ) (tested available Version: 3.4.0 ) and Azure to Version: 5.1.1 .



Figure 4 – confirm modules are up to date

8) Import PowerShell script Update-ModulesInAutomationToLatestVersion from runbook gallery and make sure it is published

Note: (without this PowerShell script the Export PowerShell workflow will not work)!

To import Update-ModulesInAutomationToLatestVersion PowerShell script from runbook Gallery, follow below steps:

a. Go to automation account

b. Select runbooks from middle blade

c. From top bar select Browse gallery

d. Set the filter Gallery Source to PowerShell Gallery

e. Search for Update-ModulesInAutomationToLatestVersion PowerShell script

f. Select this script and import it

g. Revert to runbooks again

h. Select recently imported PowerShell script

i. From top bar select Edit, then Publish

j. Verify that this script in Published state

Now we will import the PowerShell workflow script!

1) From Automation account overview dashboard

2) Select runbooks

3) Click Add runbook --> Create a new runbook (make sure you select PowerShell Workflow!)

4) Click ‘create”. Once new runbook created, it will present in runbooks in new state!

5) Select Schedule-DataSync and from top bar click Edit

6) Copy and Past the PowerShell script to the new runbook and Save, then publish

7) Click Edit, and before click “ Test Pane ” make sure you update required parameters (Subscription ID – Resource Group Name – Server name – Database name – Sync Group name – Time Interval in seconds)

8) You can review completed jobs from runbooks jobs

To schedule follow below steps:

1) Click on schedule



Figure 5 – schedules

2) Click on “ Create a new schedule ” and fill in the required information



Figure 6 – Create new schedule

Monitor the jobs status form here



Figure 7 – Automation account Monitoring

Test pane sample completed script run view



Figure 8 – Test pane completed run script



Figure 9 – monitor jobs output

[code language="PowerShell"]
workflow Schedule-DataSync {

inlineScript
{
$connectionName = "AzureRunAsConnection"
try {

# Get the connection "AzureRunAsConnection "
$servicePrincipalConnection = Get-AutomationConnection -Name $connectionName

"Login to Azure"

Add-AzureRmAccount `
-ServicePrincipal `
-TenantId $servicePrincipalConnection.TenantId `
-ApplicationId $servicePrincipalConnection.ApplicationId `
-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
}

catch {
if (!$servicePrincipalConnection) {
$ErrorMessage = "Connection $connectionName not found."
throw $ErrorMessage
}
else {
Write-Error -Message $_.Exception
throw $_.Exception
}
}

# Sync Group to start
$SubscriptionId = "xxxxx-xxxxx-xxxxx-xxxxxx-xxxxxx"
$ResourceGroupName = "ResourceGroup"
$ServerName = "ServerName"
$DatabaseName = "DatabaseName"
$SyncGroupName = "SyncGroup"
$IntervalInSeconds = "300"

# Trigger sync manually
Write-Output "Trigger sync manually"
$SyncLogStartTime = Get-Date
Start-AzureRmSqlSyncGroupSync -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $DatabaseName `
-SyncGroupName $SyncGroupName

# Check the sync log and wait until the first sync succeeded
Write-Output "Check the sync log"
$IsSucceeded = $false
For ($i = 0; ($i -lt 300) -and (-not $IsSucceeded); $i = $i + 10)
{
Start-Sleep -s 10
$SyncLogEndTime = Get-Date
$SyncLogList = Get-AzureRmSqlSyncGroupLog -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $DatabaseName `
-SyncGroupName $SyncGroupName `
-StartTime $SyncLogStartTime.ToUniversalTime() `
-EndTime $SyncLogEndTime.ToUniversalTime()
if ($SynclogList.Length -gt 0)
{
foreach ($SyncLog in $SyncLogList)
{
if ($SyncLog.Details.Contains("Sync completed successfully"))
{
Write-Host $SyncLog.TimeStamp : $SyncLog.Details
$IsSucceeded = $true
}
}
}
}

if ($IsSucceeded)
{
# Enable scheduled sync
Write-Output "Enable the scheduled sync with 300 seconds interval"
Update-AzureRmSqlSyncGroup -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $DatabaseName `
-Name $SyncGroupName `
-IntervalInSeconds $IntervalInSeconds
}
else
{
# Output all log if sync doesn't succeed in 300 seconds
$SyncLogEndTime = Get-Date
$SyncLogList = Get-AzureRmSqlSyncGroupLog -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $DatabaseName `
-SyncGroupName $SyncGroupName `
-StartTime $SyncLogStartTime.ToUniversalTime() `
-EndTime $SyncLogEndTime.ToUniversalTime()
if ($SynclogList.Length -gt 0)
{
foreach ($SyncLog in $SyncLogList)
{
Write-Host $SyncLog.TimeStamp : $SyncLog.Details
}
}
}
Write-Output "This Script will disable auto sync and stop sync in 10 minutes"
Start-Sleep -s 600
# Stop sync manually
Write-Output "Set automatic sync OFF"
Update-AzureRmSqlSyncGroup -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $DatabaseName `
-Name $SyncGroupName `
-IntervalInSeconds "-1"

# Stop sync
Write-Output "Stop sync"
Stop-AzureRmSqlSyncGroupSync -ResourceGroupName $ResourceGroupName `
-ServerName $ServerName `
-DatabaseName $DatabaseName `
-SyncGroupName $SyncGroupName
}
}[/code]

I hope you enjoy run this runbook PowerShell script, please share any thoughts here in post comments.

More references:
Getting Started with Azure Automation
Create a standalone Azure Automation account