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?