Schedule a DML operation on EXTERNAL TABLEs in Azure SQL

Question

I'm new to AzureSQL.
I want to migrate data from Azure SQL periodically based on timestamp.

use DB1 GO
delete DB2.dbo.Orders where OrderDate < GETDATE()-90

The database DB1 and DB2 are in the same server.
As I currently understand, there is no SQL Server Agent when using AzureSQL.

Thanks

Answer 1

1. We can use exec sp_execute_remote to execute delete operation on EXTERNAL TABLEs.

2. As you are running just one script, I think you can use 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' = 'exec sp_execute_remote N''<Your-remote-datasource-name>'', N''delete DB2.dbo.Orders where OrderDate < GETDATE()-90''''   
    }
    Invoke -Sqlcmd @database

Then, it can be scheduled.