How to Schedule a SQL Job in Microsoft Azure SQL Database

How do Jobs in Azure Sql Server Databse?

You need to use Azure Automation to schedule the execution of a stored procedure. For instance, You can use Azure Automation to schedule index maintenance tasks.

Below are steps :

1. Provision an Automation Account if you don’t have any, by going to https://portal.azure.com and select New > Management > Automation Account

2. After creating the Automation Account, open the details and now click on Runbooks > Browse Gallery

Type on the search box the word “indexes” and the runbook “Indexes tables in an Azure database if they have a high fragmentation” appears:

4. Note that the author of the runbook is the SC Automation Product Team at Microsoft. Click on Import:

5. After importing the runbook, now let’s add the database credentials to the assets. Click on Assets > Credentials and then on “Add a credential…” button.

6. Set a Credential name (that will be used later on the runbook), the database user name and password:

7. Now click again on Runbooks and then select the “Update-SQLIndexRunbook” from the list, and click on the “Edit…” button. You will be able to see the PowerShell script that will be executed:

8. If you want to test the script, just click on the “Test Pane” button, and the test window opens. Introduce the required parameters and click on Start to execute the index rebuild. If any error occurs, the error is logged on the results window. Note that depending on the database and the other parameters, this can take a long time to complete:

9. Now go back to the editor, and click on the “Publish” button enable the runbook. If we click on “Start”, a window appears asking for the parameters. But as we want to schedule this task, we will click on the “Schedule” button instead:

10. Click on the Schedule link to create a new Schedule for the runbook. I have specified once a week, but that will depend on your workload and how your indexes increase their fragmentation over time. You will need to tweak the schedule based on your needs and by executing the initial queries between executions:

11. Now introduce the parameters and run settings:

NOTE: you can play with having different schedules with different settings, i.e. having a specific schedule for a specific table.

With that, you have finished. Remember to change the Logging settings as desired:

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

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:

Azure - Schedule SQL Job

If you are using an Azure SQL Database (which I assume you are), then you don't have the SQL Server Agent.

You do however have 2 possible options for your needs:

1. Using Azure Automation, you can use PowerShell to do your scheduled tasks. More info and a sample here. Azure Automation is the surrogate for the SQL Agent if you ask me.
2. The other option is Azure WebJobs. I think this is the way to go, as you are already using Azure WebSites and this comes as a feature of it. Scott Hanselman has an excellent blog post about it.

Difference between scheduling Jobs in SSMS in Managed Instance and Data Factory

For your first question "Can the SQL Agent Job Scheduler run Jobs that have an SSIS package in a step or do the packages strictly have to be scheduled to run strictly in Data Factory with SSIS IR?

Yes, there many tutorials talked about this, you could ref this official document:

1. Microsoft: SQL Server Agent Jobs for Packages: You can automate and schedule the execution of SQL Server Integration Services packages by using SQL Server Agent. You can schedule packages that are deployed to the Integration Services server, and are stored in SQL Server, the SSIS Package Store, and the file system.

Azure SQL managed instance support SQL Agent job and can schedule run the SSIS packages, Data Factory also could achieve that with SSIS-IR.

Second question, what is the best way to run a job that uses both T-SQL scripts and SSIS Packages in a Managed Instance?

Azure SQL managed instance has almost the same feature with on-premise SQL Server. So you could create the SQL Agent job to execute the T-SQL scripts or SSIS Packages like on-premise.

HTH.

Can I replace SQL Jobs by Azure Scheduler?

My current investigation says SQL Scheduler is not right candidate for replacement of SQL Agents which runs on always ON configuration

Here are links which will provide in-dept details on finding PRIMARY nodes in Always ON and setting up SQL Agents

http://sqlschool.gr/blog/running-sql-server-agent-job-on-alwayson-availability-groups-blog-post-1046.aspx

https://jaredzagelbaum.wordpress.com/2015/05/05/synchronizing-read-write-sql-agent-jobs-across-alwayson-replicas/

How to launch SQL user defined procedure exclusively, without transaction, using external launcher.exe?

Azure SQL database has two auto jobs for us SQL agent for Azure MI and Elastic jobs for Azure SQL database You can use the Elastic Jobs to execute your stored procedures.

Ref this document: Automate management tasks using elastic jobs:

• You can create and schedule elastic jobs that could be periodically
  executed against one or many Azure SQL databases to run Transact-SQL
  (T-SQL) queries and perform maintenance tasks.

The Elastic Job agent is free. The job database is billed at the same rate as any database in Azure SQL Database.

HTH.

---

Related Topics