Automate daily copy to different Azure server

Category: azure sqlazure


JRStern on Mon, 14 Nov 2016 21:27:07

Another newbie question.

We want to start doing a nightly copy of our production database over to our Azure development environment.  I'm scanning several articles that address this.  It's easy enough to execute the TSQL "create database ... as copy", but there's no SQL Agent to schedule it.  Another article suggests it's possible to use an Azure scheduled backup facility to do this copy - but I can't quite see how.

And then once it's copied, we want to automatically run a job that masks the data.  Not sure how to schedule that, either.

I miss the SQL Agent capability!

So what are people doing?  Building and deploying Azure apps to do these things, seems excessive.  Is it best just to use an external machine to run these commands?  Hey, can SQL Agent on my workstation be set to log into Azure and run commands?




JRStern on Tue, 15 Nov 2016 18:54:37


Thanks, this is probably an answer, but I welcome additional links.

So, maybe Sync can do what I want: create a 100% copy every day. I'll have go find some clearer instructions though.  These blog articles are nice, but isn't there something like a BOL that is a complete and current reference?

(anyone want to recommend a good Azure book that might cover this?)

I know that I can create the copy with a "create database as copy", though it also requires setting some stuff up, so if that Sync can do it, it might be easier.

And there are at least three ways people can schedule jobs on Azure:

1. Mobile services / job scheduler, job can be in JS or C# (dotnet), basically to call a stored procedure.

2. Azure Automation, job must be in powershell, and again all it really has to do is call an SP.

3. Use SQL Agent on another system to call SqlCmd to run a script against Azure, that can call an SP.


Debra Dove - MSFT on Fri, 18 Nov 2016 22:19:17

SQL Agent for Azure SQL Database is name Elastic jobs. While there is a public preview version of Elastic jobs available today, it is a customer-hosted flavor and requires other Azure services for installation nd configuration.

We now have a 'new' Elastic jobs which has been re-architected and integrated directly as a feature of Azure SQL Database in Private Preview. I would love to on board you to try the new Elastic jobs for scheduling all your T-SQL scripts for SQL Database. Please contact me directly ddove(at)Microsoft(dot)com.

Elastic job Private Preview currently has a T-SQL (sprocs: sp_add_job, sp_add_jobstep,... and views) for creating and monitoring jobs. Soon, we will also expose PowerShell cmdlets and Portal for managing and monitoring jobs.