Schedule Jobs in Azure SQL Database with Azure Functions
You don’t get SQL Server Agent in Azure SQL Database. If you’ve been a DBA for any length of time, that sentence probably made you twitch. Agent is the workhorse that runs your index maintenance, collects performance baselines, purges old data, and generally keeps the lights on while you sleep. Azure SQL Database doesn’t have it, and Microsoft’s answer – Elastic Jobs – requires a separate database and comes with its own licensing.
There’s a simpler option. An Azure Function running on a timer can execute T-SQL on a schedule, costs almost nothing on a Consumption plan, and takes about fifteen minutes to set up. This post walks through building one, with both PowerShell and C# examples.
The Problem: Transient Data Disappears
The most common reason you need a scheduled job in Azure SQL Database is to capture data that doesn’t persist. DMV data like sys.dm_db_index_usage_stats resets whenever the service restarts – and Azure can restart your database at any time for patching, scaling, or resource rebalancing. If you’re trying to identify unused indexes, you need periodic snapshots of that data before it vanishes.
The same applies to wait statistics, query store gaps, tempdb usage patterns, and any home-grown monitoring you’ve built on top of DMVs.
The Architecture
The solution has three parts:
- A job table in your Azure SQL Database that stores the T-SQL commands to run, along with scheduling metadata.
- An Azure Function with a timer trigger that wakes up on a schedule, reads the job table, and executes each enabled job.
- A log table that records what ran, when, and whether it succeeded.
The Azure Function authenticates using a Managed Identity, which means no connection strings with passwords stored anywhere. The function’s identity is granted access to the database directly, just like a Windows login in on-premises SQL Server.
The Job and Log Tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE TABLE [dbo].[ScheduledJob] ( [job_id] int NOT NULL IDENTITY(1, 1) , [job_name] nvarchar(128) NOT NULL , [sql_command] nvarchar(MAX) NOT NULL , [is_enabled] bit NOT NULL DEFAULT (1) , [run_interval_minutes] int NOT NULL DEFAULT (60) , [last_run_utc] datetime2(3) NULL , [last_status] nvarchar(20) NULL , [created_utc] datetime2(3) NOT NULL DEFAULT (SYSUTCDATETIME()) , CONSTRAINT [PK_ScheduledJob] PRIMARY KEY CLUSTERED ([job_id]) ); CREATE TABLE [dbo].[ScheduledJobLog] ( [log_id] bigint NOT NULL IDENTITY(1, 1) , [job_id] int NOT NULL , [started_utc] datetime2(3) NOT NULL , [completed_utc] datetime2(3) NULL , [status] nvarchar(20) NOT NULL , [error_message] nvarchar(4000) NULL , [rows_affected] int NULL , CONSTRAINT [PK_ScheduledJobLog] PRIMARY KEY CLUSTERED ([log_id]) , CONSTRAINT [FK_ScheduledJobLog_Job] FOREIGN KEY ([job_id]) REFERENCES [dbo].[ScheduledJob] ([job_id]) ); CREATE NONCLUSTERED INDEX [IX_ScheduledJobLog_JobId_StartedUtc] ON [dbo].[ScheduledJobLog] ([job_id], [started_utc] DESC); |
The run_interval_minutes column controls how often each job runs. The Azure Function fires every five minutes (a lightweight check), but only executes jobs whose last_run_utc is older than their configured interval. This lets you have some jobs run hourly and others daily, all from the same function.
Setting Up the Azure Function
I won’t walk through every portal click since Azure’s UI changes frequently. Here’s what you need:
- Create a Function App in the Azure Portal. Choose the Consumption plan (pay per execution, essentially free for a few hundred runs per day). Select either PowerShell Core or .NET as the runtime stack depending on your preference.
- Enable System-Assigned Managed Identity on the Function App (under Identity in the left menu).
- Grant the identity access to your Azure SQL Database by running the T-SQL below while connected as an admin.
- Create a timer-triggered function with a cron expression of
0 */5 * * * *(every 5 minutes).
Granting the Managed Identity Access
Connect to your Azure SQL Database and run:
|
1 2 3 4 |
CREATE USER [your-function-app-name] FROM EXTERNAL PROVIDER; ALTER ROLE [db_datareader] ADD MEMBER [your-function-app-name]; ALTER ROLE [db_datawriter] ADD MEMBER [your-function-app-name]; GRANT EXECUTE TO [your-function-app-name]; |
Replace your-function-app-name with the actual name of your Function App. The identity name matches the Function App name. The roles above give it read/write access and permission to execute stored procedures. Adjust based on what your jobs actually need – principle of least privilege applies here just like it does on-premises.
Application Settings
Add one application setting to your Function App:
|
1 2 |
Name: SqlConnectionString Value: Server=tcp:your-server.database.windows.net,1433;Database=your-database;Authentication=Active Directory Managed Identity;Encrypt=True;TrustServerCertificate=False; |
No username or password. The Authentication=Active Directory Managed Identity parameter tells the SQL client library to use the function’s managed identity automatically.
The Azure Function: PowerShell Version
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
param($Timer) $connectionString = $env:SqlConnectionString $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString) try { $connection.AccessToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token $connection.Open() # Find jobs that are due to run $query = @" SELECT [job_id], [job_name], [sql_command] FROM [dbo].[ScheduledJob] WHERE [is_enabled] = 1 AND ( [last_run_utc] IS NULL OR DATEDIFF(MINUTE, [last_run_utc], SYSUTCDATETIME()) >= [run_interval_minutes] ); "@ $cmd = New-Object System.Data.SqlClient.SqlCommand($query, $connection) $reader = $cmd.ExecuteReader() $jobs = @() while ($reader.Read()) { $jobs += @{ job_id = $reader["job_id"] job_name = $reader["job_name"] sql_command = $reader["sql_command"] } } $reader.Close() foreach ($job in $jobs) { $startedUtc = (Get-Date).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.fff") $status = "Success" $errorMsg = $null $rowsAffected = 0 try { $jobCmd = New-Object System.Data.SqlClient.SqlCommand($job.sql_command, $connection) $jobCmd.CommandTimeout = 300 $rowsAffected = $jobCmd.ExecuteNonQuery() } catch { $status = "Failed" $errorMsg = $_.Exception.Message.Substring(0, [Math]::Min($_.Exception.Message.Length, 4000)) } # Log the result and update last_run $logSql = @" INSERT INTO [dbo].[ScheduledJobLog] ([job_id], [started_utc], [completed_utc], [status], [error_message], [rows_affected]) VALUES ($($job.job_id), '$startedUtc', SYSUTCDATETIME(), '$status', $(if ($errorMsg) { "N'$($errorMsg.Replace("'","''"))'" } else { 'NULL' }), $rowsAffected); UPDATE [dbo].[ScheduledJob] SET [last_run_utc] = SYSUTCDATETIME() , [last_status] = '$status' WHERE [job_id] = $($job.job_id); "@ $logCmd = New-Object System.Data.SqlClient.SqlCommand($logSql, $connection) $logCmd.ExecuteNonQuery() | Out-Null Write-Host "Job '$($job.job_name)' completed with status: $status" } if ($jobs.Count -eq 0) { Write-Host "No jobs due to run." } } finally { $connection.Close() } |
The Azure Function: C# Version
For the C# version, create a .NET 8 (or later) isolated worker function. You’ll need the Microsoft.Data.SqlClient and Azure.Identity NuGet packages.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
using System; using System.Collections.Generic; using System.Threading.Tasks; using Azure.Identity; using Microsoft.Azure.Functions.Worker; using Microsoft.Data.SqlClient; using Microsoft.Extensions.Logging; public class ScheduledJobRunner { private readonly ILogger _logger; public ScheduledJobRunner(ILoggerFactory loggerFactory) { _logger = loggerFactory.CreateLogger<ScheduledJobRunner>(); } [Function("ScheduledJobRunner")] public async Task Run( [TimerTrigger("0 */5 * * * *")] TimerInfo timer) { var connectionString = Environment.GetEnvironmentVariable("SqlConnectionString"); await using var connection = new SqlConnection(connectionString); var credential = new DefaultAzureCredential(); var token = await credential.GetTokenAsync( new Azure.Core.TokenRequestContext( new[] { "https://database.windows.net/.default" })); connection.AccessToken = token.Token; await connection.OpenAsync(); // Find jobs that are due to run var dueJobsSql = @" SELECT [job_id], [job_name], [sql_command] FROM [dbo].[ScheduledJob] WHERE [is_enabled] = 1 AND ( [last_run_utc] IS NULL OR DATEDIFF(MINUTE, [last_run_utc], SYSUTCDATETIME()) >= [run_interval_minutes] );"; var jobs = new List<(int Id, string Name, string Sql)>(); await using (var cmd = new SqlCommand(dueJobsSql, connection)) await using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { jobs.Add(( reader.GetInt32(0), reader.GetString(1), reader.GetString(2))); } } foreach (var job in jobs) { var startedUtc = DateTime.UtcNow; var status = "Success"; string errorMessage = null; var rowsAffected = 0; try { await using var jobCmd = new SqlCommand(job.Sql, connection); jobCmd.CommandTimeout = 300; rowsAffected = await jobCmd.ExecuteNonQueryAsync(); } catch (Exception ex) { status = "Failed"; errorMessage = ex.Message.Length > 4000 ? ex.Message[..4000] : ex.Message; } // Log the result and update last_run var logSql = @" INSERT INTO [dbo].[ScheduledJobLog] ([job_id], [started_utc], [completed_utc], [status], [error_message], [rows_affected]) VALUES (@jobId, @started, SYSUTCDATETIME(), @status, @error, @rows); UPDATE [dbo].[ScheduledJob] SET [last_run_utc] = SYSUTCDATETIME(), [last_status] = @status WHERE [job_id] = @jobId;"; await using var logCmd = new SqlCommand(logSql, connection); logCmd.Parameters.AddWithValue("@jobId", job.Id); logCmd.Parameters.AddWithValue("@started", startedUtc); logCmd.Parameters.AddWithValue("@status", status); logCmd.Parameters.AddWithValue("@error", (object)errorMessage ?? DBNull.Value); logCmd.Parameters.AddWithValue("@rows", rowsAffected); await logCmd.ExecuteNonQueryAsync(); _logger.LogInformation( "Job '{Name}' completed: {Status}", job.Name, status); } if (jobs.Count == 0) { _logger.LogInformation("No jobs due to run."); } } } |
The C# version uses parameterized queries for the logging insert, which avoids SQL injection if a job’s error message contains single quotes. The PowerShell version uses string escaping for simplicity, but you could adapt it to use parameters as well.
Example Job: Index Usage Stats Collector
Here’s the T-SQL to insert a job that snapshots sys.dm_db_index_usage_stats every hour. First, create the history table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE [dbo].[IndexUsageStatsHistory] ( [snapshot_id] bigint NOT NULL IDENTITY(1, 1) , [captured_utc] datetime2(3) NOT NULL DEFAULT (SYSUTCDATETIME()) , [database_id] smallint NOT NULL , [object_id] int NOT NULL , [index_id] int NOT NULL , [user_seeks] bigint NOT NULL , [user_scans] bigint NOT NULL , [user_lookups] bigint NOT NULL , [user_updates] bigint NOT NULL , [last_user_seek] datetime2(3) NULL , [last_user_scan] datetime2(3) NULL , [last_user_lookup] datetime2(3) NULL , [last_user_update] datetime2(3) NULL , CONSTRAINT [PK_IndexUsageStatsHistory] PRIMARY KEY CLUSTERED ([snapshot_id]) ); CREATE NONCLUSTERED INDEX [IX_IndexUsageStatsHistory_ObjectId] ON [dbo].[IndexUsageStatsHistory] ([object_id], [index_id], [captured_utc] DESC); |
Then register the collection job:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
INSERT INTO [dbo].[ScheduledJob] ([job_name], [sql_command], [run_interval_minutes]) VALUES ( N'Collect Index Usage Stats' , N'INSERT INTO [dbo].[IndexUsageStatsHistory] ([database_id], [object_id], [index_id], [user_seeks], [user_scans], [user_lookups], [user_updates], [last_user_seek], [last_user_scan], [last_user_lookup], [last_user_update]) SELECT [database_id], [object_id], [index_id], [user_seeks], [user_scans], [user_lookups], [user_updates], [last_user_seek], [last_user_scan], [last_user_lookup], [last_user_update] FROM sys.[dm_db_index_usage_stats] WHERE [database_id] = DB_ID();' , 60 ); |
After a few weeks of hourly snapshots, you can query the history to find genuinely unused indexes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT OBJECT_SCHEMA_NAME([h].[object_id]) AS [schema_name] , OBJECT_NAME([h].[object_id]) AS [table_name] , [i].[name] AS [index_name] , MAX([h].[user_seeks]) AS [max_seeks] , MAX([h].[user_scans]) AS [max_scans] , MAX([h].[user_lookups]) AS [max_lookups] , MAX([h].[user_updates]) AS [max_updates] , COUNT(1) AS [snapshots] , MIN([h].[captured_utc]) AS [earliest_snapshot] , MAX([h].[captured_utc]) AS [latest_snapshot] FROM [dbo].[IndexUsageStatsHistory] AS [h] INNER JOIN sys.[indexes] AS [i] ON [h].[object_id] = [i].[object_id] AND [h].[index_id] = [i].[index_id] WHERE [i].[is_primary_key] = 0 AND [i].[is_unique_constraint] = 0 GROUP BY [h].[object_id] , [i].[name] HAVING MAX([h].[user_seeks]) = 0 AND MAX([h].[user_scans]) = 0 AND MAX([h].[user_lookups]) = 0 ORDER BY MAX([h].[user_updates]) DESC; |
The HAVING clause filters to indexes with zero seeks, scans, and lookups across all snapshots. The ORDER BY on user_updates shows you the most expensive unused indexes first – these are costing you write performance for zero read benefit.
Operational Considerations
Cost. A Consumption-plan Azure Function that runs every five minutes and executes a few queries costs pennies per month. You’ll pay for the Function App storage account (a few cents) and the executions themselves. For this workload, you’re unlikely to exceed the free grant of 1 million executions per month.
Timeouts. Consumption plan functions have a 10-minute timeout by default (configurable up to 10 minutes). If you have long-running maintenance jobs, consider the Flex Consumption or App Service plan, which support longer timeouts. The 300-second CommandTimeout in the code above is a safety net for individual queries.
Monitoring. Azure Functions integrate with Application Insights out of the box. Enable it when creating the Function App and you get execution logs, failure alerts, and performance metrics without any extra code.
Error handling. The function logs failures to the ScheduledJobLog table and continues to the next job. A single failing job won’t block the others. Set up an alert in Application Insights for failed executions if you want email notifications.
When to use Elastic Jobs instead. If you need to run the same job across multiple databases (fan-out), or if you need built-in retry policies, job dependencies, or the ability to run jobs against databases in different servers, Elastic Jobs is the right tool. The Azure Function approach is better for single-database scheduling where you want simplicity and low cost.
Wrapping Up
The combination of an Azure Function, a Managed Identity, and a simple job table gives you a lightweight, secure, and nearly free replacement for SQL Server Agent’s scheduling capability in Azure SQL Database. The five-minute polling interval is more than sufficient for most DBA maintenance tasks, and you can tune it per-job through the run_interval_minutes column.
The index usage stats collector is just one example. Other good candidates for scheduled collection: wait statistics snapshots, query store forced plan validation, data retention cleanup, and statistics maintenance on tables where auto-stats isn’t keeping up.
Have questions or a use case I didn’t cover? Find me on Bluesky or LinkedIn.