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).
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
, to create a new Data Sync group from here
3) Click “+
” 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 “
”. The new automation created, and configuration blade will be opened
6) First, make sure "
" and "
” connections created automatically during Automation account creation. Note: ("
" 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 (
) (tested available Version:
) and Azure to Version:
Figure 4 – confirm modules are up to date
8) Import PowerShell script
from runbook gallery and make sure it is published
Note: (without this PowerShell script the Export PowerShell workflow will not work)!
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
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!
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 “
” 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