CODEWITHSUNDEEP
sqlsql-serverazure
Adam Andersson
Adam Andersson

Reputation: 113

Schedule a SQL-query to move data from one table to another with Azure SQL-db

I have a simple query that takes old data from a table and inserts the data into another table for archiving.

DELETE FROM Events
OUTPUT DELETED.*
INTO ArchiveEvents
WHERE GETDATE()-90 > Events.event_time

I want this query to run daily.

As i currently understand, there is no SQL Server Agent when using Azure SQL-db. Thus SQL Server agent does not seem like the solution here.

What is the easiest/best solution to this using Azure SQL-db?

Upvotes: 0

Views: 759

Answers (3)

Joseph Xu
Joseph Xu

Reputation: 6043

You can use Azure data factory, create a pipeline to execute SQL query and trigger it run every day. Azure data factory is used to move and transform data from Azure SQL or other storage.

Upvotes: 1

Katherine Smith
Katherine Smith

Reputation: 11

You asked in part for a comparison of Elastic Jobs to Runbooks.

  • Elastic Jobs will also run a pre-determined SQL script against a
    target set of servers/databases.
    -Elastic jobs were built internally for Azure SQL by Azure SQL engineers, so the technology is supported at the same level of Azure SQL.
  • Elastic jobs can be defined and managed entirely through PowerShell scripts. However, they also support setup/configuration through TSQL.
  • Elastic Jobs are handy if you want to target many databases, as you set up the job one time, and set the targets and it will run everywhere at once. If you have many databases on a given server that would be good targets, you only need to specify the target
    server, and all of the databases on the server are automatically targeted.
  • If you are adding/removing databases from a given server, and want to have the job dynamically adjust to this change, elastic jobs is designed to do this seamlessly. You just have to configure the job to the server, and every time it is run it will target all (non excluded) databases on the server.

For reference, I am a Microsoft Employee who works in this space.

I have written a walkthrough and fuller explanation of elastic jobs in a blog series. Here is a link to the entry point of the series:https://techcommunity.microsoft.com/t5/azure-sql/elastic-jobs-in-azure-sql-database-what-and-why/ba-p/1177902

Upvotes: 1

AnuragSharma-MSFT
AnuragSharma-MSFT

Reputation: 692

There are multiple ways to run automated scripts on Azure SQL Database as below:

  1. Using Automation Account Runbooks.
  2. Using Elastic Database Jobs in Azure
  3. Using Azure Data factory.

As you are running just one script, I would suggest you to take a look into Automation Account Runbooks. As an example below, a PowerShell Runbook to execute the statement.

$database = @{
'ServerInstance' = 'servername.database.windows.net'
'Database' = 'databasename' 
'Username' = 'uname'
'Password' = 'password'
'Query' = 'DELETE FROM Events OUTPUT DELETED.* INTO archieveevents'    
}
Invoke -Sqlcmd @database

Then, it can be scheduled as needed:

enter image description here

Upvotes: 3

Related Questions