SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scheduling jobs in Azure SQL database - Azure automation or Elastic jobs for EXPORT and IMPORT of a...


Scheduling jobs in Azure SQL database - Azure automation or Elastic jobs for EXPORT and IMPORT of a table?

Author
Message
Keistad
Keistad
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 355
Have recently created a database in Azure SQL database and I'm quiet new to it.

Requirement - Have to export a table from on premises server and import it on the virtual database hosted in Azure SQL database and automate this process.

What I have done so far - I have managed to complete the above process manually - Did BCP out to local server for export (.dat file), used Azure copy to copy the .dat file to Azure blob storage and did an import with the help of using EXTERNAL DATA SOURCE and BULK insert command.

Question - The challenge for me now is to automate this process every weekend. I am exploring different options and I can see there are numerous ways to do it - Use SQL agent of any on-premises server, Azure automation, Elastic jobs etc. I would like to use newer methods and I'm confused between Azure automation vs Elastic jobs.

I am thinking of exploring Elastic jobs and use it for the above purpose. Can anyone please help me and confirm if Elastic jobs is the right option over Azure automation for my requirement?

Note: We will gradually increase the number of tables to be used for this data migration. Additionally, I am also thinking to add few job steps like to truncate table or purge table before importing the data. Hope I can achieve this using elastic jobs?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339682 Visits: 33994
Unless you've set up and are using Azure Active directory, you're going to have a hard time automating this process from Azure. This is because it won't be able to see into your network to pull the files up. Instead, I'd schedule it using whatever you use locally for scheduling (SQL Agent?) and then use the tools you already outlined along with PowerShell to make the whole thing happen.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Keistad
Keistad
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 355
@Grant Fritchey - Thanks for your reply. We kind of have the AZure active directory set-up. I am planning to do BCP out to local server for export (.dat file), use Azure copy to copy the .dat file to Azure blob storage and do an import with the help of using EXTERNAL DATA SOURCE and BULK insert commands to Azure SQL database. Thinking of creating 3 different SPROC's for the above operations and use Elastic jobs for scheduling it(with powershell). Will this approach not work? We can use Azcopy to copy the files.. Am I thinking in the rite direction?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339682 Visits: 33994
Yeah, you're on the right track. I've used AZCopy for exactly this type of operation with Azure SQL Data Warehouse. I used Azure Automation instead of Elastic Jobs. I'm not sure what the difference is there, but if the one is working for you, stick with it.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search