Elastic Agent Jobs

elastic_agent_jobs.sql

-- Elastic Agent Jobs
-- Official doc: https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tsql-create-manage?view=azuresql
-- Unofficial doc: https://sqlkitty.com/elastic-jobs-azure-sql-db/

-- Add target group
EXEC jobs.sp_add_target_group 'AzureSQLDBs';

-- Add single database (or server/elastic pool) to target group
EXEC jobs.sp_add_target_group_member
@target_group_name = 'AzureSQLDBs',
@target_type = 'SqlDatabase',
@server_name = '<SERVER_NAME>.database.windows.net',
@database_name = '<DB_NAME>';

-- Add job
EXEC jobs.sp_add_job 
@job_name = 'IndexMaintenance', 
@description = 'Run stored procedure dbo.IndexOptimize'; 

-- Add job step
EXEC jobs.sp_add_jobstep
@job_name = 'IndexMaintenance',
@step_name = 'Run IndexOptimize',
@command = N'EXECUTE [dbo].[IndexOptimize]
@Databases = ''USER_DATABASES'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ''Y'',
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y'',
@LogToTable = ''Y'',
@Execute = ''N'';',
@target_group_name = 'AzureSQLDBs';

-- Update job step
EXEC jobs.sp_update_jobstep
@job_name = 'IndexMaintenance',
@step_name = 'Run IndexOptimize',
@command = N'EXECUTE [dbo].[IndexOptimize]
@Databases = ''USER_DATABASES'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ''Y'',
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y'',
@LogToTable = ''Y'',
@Execute = ''Y'';',
@target_group_name = 'AzureSQLDBs';

-- Add 2nd job step
EXEC jobs.sp_add_jobstep 
@job_name = 'IndexMaintenance',
@step_name = 'Clean CommandLog',
@command = N'DELETE FROM [dbo].[CommandLog]
WHERE StartTime <= DATEADD(DAY, -30, GETDATE());',
@target_group_name = 'AzureSQLDBs';

-- Start job
EXEC jobs.sp_start_job 'IndexMaintenance';

-- Set job schedule
EXEC jobs.sp_update_job
@job_name = 'IndexMaintenance',
@enabled=1,
@schedule_interval_type = 'Days',
@schedule_interval_count = 1, 
@schedule_start_time = '2024-01-01 00:00:00'; 

-- Generate stop job commands
SELECT 
'EXEC jobs.sp_stop_job '''+ CAST(job_execution_id AS CHAR(36)) +''';' AS [command],
*
FROM jobs.job_executions
WHERE job_name = 'IndexMaintenance' and is_active = 1
ORDER BY start_time DESC;

-- Target group details
SELECT * FROM jobs.target_group_members;

-- Job details
SELECT * FROM jobs.jobs;

-- Job step details
SELECT js.*
FROM jobs.jobsteps js
JOIN jobs.jobs j
ON j.job_id = js.job_id AND j.job_version = js.job_version;

-- Job run history
 SELECT 
job_name,
step_name,
target_server_name,
target_database_name,
target_type, 
last_message,
start_time,
end_time,
is_active,
lifecycle,
current_attempts
FROM jobs.job_executions 
ORDER BY start_time DESC;

elastic_agent_jobs_cross_server_data.md

Azure SQL Elastic Jobs: Cross-Server Data Copy Quickstart

Copy data between Azure SQL Databases on different servers using Elastic Job Agent with managed identity - no external data sources or credentials required.

Table of Contents

• Overview
• Architecture
• Prerequisites
• Quick Start
• Complete Example
• Monitoring
• Best Practices
• Troubleshooting
• When to Use This Pattern

---

Overview

This guide shows how to copy data between Azure SQL Databases on different servers using an Elastic Job Agent with the job database as an intermediate staging area, authenticating via user-assigned managed identity. No external data sources or credential management required.

Key Features

✅ Managed identity authentication - No password management ✅ No credentials required - Uses Microsoft Entra ID (Azure AD) ✅ No external data sources - Simple cross-server setup ✅ Works across servers and subscriptions ✅ Automatic table creation ✅ Built-in execution tracking

How It Works

Step 1: Source DB → Job Database (via @output_* parameters + managed identity)
Step 2: Job Database → Destination DB (via @output_* parameters + managed identity)
Step 3: Cleanup staging data

Authentication Model

The Elastic Job Agent uses its user-assigned managed identity (UMI) to authenticate to all databases. No credentials (sp_add_credential) needed when using managed identity.

💡 Key Difference from SQL Authentication:

• ❌ Old Way (SQL Auth): Create credentials → Store passwords → Rotate regularly → Manage secrets
• ✅ New Way (Managed Identity): Create users FROM EXTERNAL PROVIDER → Done! Azure handles everything

Requirements:

• Microsoft Entra ID (Azure AD) authentication enabled on all SQL Servers
• User-assigned managed identity created and assigned to job agent
• Database users created FROM EXTERNAL PROVIDER (not FROM LOGIN)

---

Architecture

graph LR
    A[Source Database<br/>Server A] -->|Step 1: Extract<br/>Managed Identity Auth| B[Job Database<br/>Staging Table]
    B -->|Step 2: Load<br/>Managed Identity Auth| C[Destination Database<br/>Server B]
    B -->|Step 3: Cleanup| B
    D[User-Assigned<br/>Managed Identity] -.->|Authenticates to| A
    D -.->|Authenticates to| B
    D -.->|Authenticates to| C

Loading

Key Mechanism: The @output_* parameters in sp_add_jobstep automatically write query results to tables across servers, authenticated via the job agent's managed identity.

---

Prerequisites

1. Elastic Job Agent with Managed Identity

Deploy the following Azure resources:

• ✅ User-assigned managed identity - Create via Azure Portal, Azure CLI, PowerShell, or IaC tools
• ✅ Elastic Job Agent - Deploy using Azure Portal, Azure CLI (az sql elastic-job-agent), or IaC
• ✅ Job database - Azure SQL Database (S1 or higher recommended)

Important: Assign the user-assigned managed identity to the elastic job agent during or after creation.

Deployment Methods:

• Azure Portal: SQL Server → Elastic Jobs → Create job agent → Assign managed identity
• Azure CLI: az sql elastic-job-agent create with --identity-type UserAssigned
• PowerShell: New-AzSqlElasticJobAgent with -IdentityType UserAssigned
• Terraform: Use azurerm_mssql_job_agent with identity block
• ARM/Bicep: Define Microsoft.Sql/servers/jobAgents with identity property

2. Microsoft Entra ID Authentication

Ensure Microsoft Entra ID (Azure AD) authentication is enabled on:

• ✅ Source SQL Server
• ✅ Job database SQL Server
• ✅ Destination SQL Server

3. Database Users and Permissions

Get the managed identity name:

# Find your managed identity name in Azure Portal:
# Azure Portal → Managed Identities → [Your Identity] → Copy the name
# Or via Azure CLI:
az identity list --query "[].{Name:name, ResourceGroup:resourceGroup}" -o table

# Example identity name: "ElasticJobAgent-Prod" or "umi-elastic-jobs-prod"

On Job Database:

-- Create user from managed identity
CREATE USER [ElasticJobAgent-Prod] FROM EXTERNAL PROVIDER;

-- Grant permissions
GRANT CREATE TABLE TO [ElasticJobAgent-Prod];
CREATE SCHEMA staging;
GRANT ALTER ON SCHEMA::staging TO [ElasticJobAgent-Prod];
GRANT INSERT, SELECT, DELETE ON SCHEMA::staging TO [ElasticJobAgent-Prod];

On Source Database (master database if targeting server/pool):

-- Create user from managed identity
CREATE USER [ElasticJobAgent-Prod] FROM EXTERNAL PROVIDER;

-- Grant SELECT permissions
GRANT SELECT ON SCHEMA::dbo TO [ElasticJobAgent-Prod];

On Destination Database (master database if targeting server/pool):

-- Create user from managed identity
CREATE USER [ElasticJobAgent-Prod] FROM EXTERNAL PROVIDER;

-- Grant permissions for auto-creating tables
GRANT CREATE TABLE TO [ElasticJobAgent-Prod];
GRANT INSERT, ALTER ON SCHEMA::dbo TO [ElasticJobAgent-Prod];

4. Network Connectivity

• Source → Job Database Server
• Job Database Server → Destination
• Configure firewall rules to allow Azure services or use private endpoints

---

Quick Start

Note: When using managed identity, NO sp_add_credential calls are needed. The job agent automatically uses its managed identity to authenticate to all targets.

1. Verify Managed Identity Setup

-- Connect to job database
USE [JobDatabase];
GO

-- Verify the managed identity user exists
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE name = 'ElasticJobAgent-Prod';  -- Replace with your identity name

-- Expected output: type_desc = 'EXTERNAL_USER', authentication_type_desc = 'EXTERNAL'

2. Create Staging Schema

-- Run on job database (if not already created)
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'staging')
BEGIN
    CREATE SCHEMA staging;
END
GO

3. Create Target Groups

-- Source database target group
EXEC jobs.sp_add_target_group @target_group_name = 'SourceGroup';
EXEC jobs.sp_add_target_group_member
    @target_group_name = 'SourceGroup',
    @target_type = 'SqlDatabase',
    @server_name = 'source-server.database.windows.net',
    @database_name = 'SourceDB';
    -- No @credential_name needed - uses managed identity!

-- Destination database target group
EXEC jobs.sp_add_target_group @target_group_name = 'DestGroup';
EXEC jobs.sp_add_target_group_member
    @target_group_name = 'DestGroup',
    @target_type = 'SqlDatabase',
    @server_name = 'dest-server.database.windows.net',
    @database_name = 'DestDB';
    -- No @credential_name needed - uses managed identity!

-- Job database target group (IMPORTANT - points to itself!)
EXEC jobs.sp_add_target_group @target_group_name = 'JobDBGroup';
EXEC jobs.sp_add_target_group_member
    @target_group_name = 'JobDBGroup',
    @target_type = 'SqlDatabase',
    @server_name = 'job-server.database.windows.net',
    @database_name = 'JobDatabase';
    -- No @credential_name needed - uses managed identity!

4. Create Job

EXEC jobs.sp_add_job
    @job_name = 'CrossServerCopy',
    @description = 'Copy data between servers via staging',
    @enabled = 1;

5. Add Job Steps

Step 1: Extract from Source

EXEC jobs.sp_add_jobstep
    @job_name = 'CrossServerCopy',
    @step_id = 1,
    @step_name = 'Extract',
    @command = N'
        SELECT
            $(job_execution_id) as JobExecutionId,
            CustomerID,
            OrderID,
            OrderDate,
            Amount
        FROM dbo.Orders
        WHERE ModifiedDate >= DATEADD(hour, -1, GETDATE())
    ',
    @target_group_name = 'SourceGroup',
    @output_type = 'SqlDatabase',
    -- No @output_credential_name - uses managed identity automatically!
    @output_server_name = 'job-server.database.windows.net',
    @output_database_name = 'JobDatabase',
    @output_schema_name = 'staging',
    @output_table_name = 'Orders_Staging';

Step 2: Load to Destination

EXEC jobs.sp_add_jobstep
    @job_name = 'CrossServerCopy',
    @step_id = 2,
    @step_name = 'Load',
    @command = N'
        SELECT
            CustomerID,
            OrderID,
            OrderDate,
            Amount
        FROM staging.Orders_Staging
        WHERE JobExecutionId = $(job_execution_id)
    ',
    @target_group_name = 'JobDBGroup',  -- Runs ON job database
    @output_type = 'SqlDatabase',
    -- No @output_credential_name - uses managed identity automatically!
    @output_server_name = 'dest-server.database.windows.net',
    @output_database_name = 'DestDB',
    @output_schema_name = 'dbo',
    @output_table_name = 'Orders_Replica';

Step 3: Cleanup

EXEC jobs.sp_add_jobstep
    @job_name = 'CrossServerCopy',
    @step_id = 3,
    @step_name = 'Cleanup',
    @command = N'
        DELETE FROM staging.Orders_Staging
        WHERE JobExecutionId = $(job_execution_id);
    ',
    @target_group_name = 'JobDBGroup';
    -- No @credential_name - uses managed identity automatically!

6. Execute Job

-- Start job immediately
EXEC jobs.sp_start_job 'CrossServerCopy';

-- Or schedule it
EXEC jobs.sp_add_schedule
    @schedule_name = 'HourlySchedule',
    @enabled = 1,
    @frequency_type = 4,  -- Daily
    @frequency_interval = 1,
    @frequency_subday_type = 8,  -- Hours
    @frequency_subday_interval = 1;

EXEC jobs.sp_attach_schedule
    @job_name = 'CrossServerCopy',
    @schedule_name = 'HourlySchedule';

---

Complete Example

Scenario: Copy Customer Orders from Production to Analytics

-- ===============================================
-- COMPLETE WORKING EXAMPLE - USING MANAGED IDENTITY
-- ===============================================

-- 1. NO CREDENTIALS NEEDED!
-- The job agent uses its managed identity automatically
-- Just verify the managed identity user exists in each database:

/*
-- Run on each target database (Production, Analytics, JobDB):
CREATE USER [ElasticJobAgent-Prod] FROM EXTERNAL PROVIDER;
GRANT SELECT ON SCHEMA::dbo TO [ElasticJobAgent-Prod];        -- Production
GRANT INSERT, CREATE TABLE ON SCHEMA::dbo TO [ElasticJobAgent-Prod];  -- Analytics
GRANT CREATE TABLE, INSERT, DELETE ON SCHEMA::staging TO [ElasticJobAgent-Prod];  -- JobDB
*/

-- 2. CREATE TARGET GROUPS
-- Production database
EXEC jobs.sp_add_target_group @target_group_name = 'ProductionDB';
EXEC jobs.sp_add_target_group_member
    @target_group_name = 'ProductionDB',
    @target_type = 'SqlDatabase',
    @server_name = 'prod-sql-server.database.windows.net',
    @database_name = 'ProductionDB';
    -- No credentials needed - uses managed identity!

-- Analytics database
EXEC jobs.sp_add_target_group @target_group_name = 'AnalyticsDB';
EXEC jobs.sp_add_target_group_member
    @target_group_name = 'AnalyticsDB',
    @target_type = 'SqlDatabase',
    @server_name = 'analytics-sql-server.database.windows.net',
    @database_name = 'AnalyticsDB';
    -- No credentials needed - uses managed identity!

-- Job database (self-reference)
EXEC jobs.sp_add_target_group @target_group_name = 'JobDatabase';
EXEC jobs.sp_add_target_group_member
    @target_group_name = 'JobDatabase',
    @target_type = 'SqlDatabase',
    @server_name = 'elastic-job-sql-server.database.windows.net',
    @database_name = 'ElasticJobDB';
    -- No credentials needed - uses managed identity!

-- 3. CREATE JOB
EXEC jobs.sp_add_job
    @job_name = 'HourlyOrdersSync',
    @description = 'Sync orders from Production to Analytics every hour',
    @enabled = 1;

-- 4. STEP 1: Extract from Production
EXEC jobs.sp_add_jobstep
    @job_name = 'HourlyOrdersSync',
    @step_id = 1,
    @step_name = 'ExtractFromProduction',
    @command = N'
        SELECT
            $(job_execution_id) AS JobExecutionId,
            $(job_name) AS JobName,
            DB_NAME() AS SourceDatabase,
            @@SERVERNAME AS SourceServer,
            GETDATE() AS ExtractedAt,
            o.OrderID,
            o.CustomerID,
            o.OrderDate,
            o.TotalAmount,
            o.Status,
            c.CustomerName,
            c.Email
        FROM dbo.Orders o
        INNER JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
        WHERE o.OrderDate >= DATEADD(hour, -1, GETDATE())
          AND o.Status != ''Cancelled''
    ',
    @target_group_name = 'ProductionDB',
    @output_type = 'SqlDatabase',
    -- No @output_credential_name - uses managed identity!
    @output_server_name = 'elastic-job-sql-server.database.windows.net',
    @output_database_name = 'ElasticJobDB',
    @output_schema_name = 'staging',
    @output_table_name = 'Orders_Staging';

-- 5. STEP 2: Transform and Load to Analytics
EXEC jobs.sp_add_jobstep
    @job_name = 'HourlyOrdersSync',
    @step_id = 2,
    @step_name = 'LoadToAnalytics',
    @command = N'
        SELECT
            OrderID,
            CustomerID,
            OrderDate,
            TotalAmount,
            Status,
            CustomerName,
            Email,
            SourceDatabase,
            SourceServer,
            ExtractedAt,
            GETDATE() AS LoadedAt,
            YEAR(OrderDate) AS OrderYear,
            MONTH(OrderDate) AS OrderMonth,
            DATEPART(QUARTER, OrderDate) AS OrderQuarter
        FROM staging.Orders_Staging
        WHERE JobExecutionId = $(job_execution_id)
    ',
    @target_group_name = 'JobDatabase',
    @output_type = 'SqlDatabase',
    -- No @output_credential_name - uses managed identity!
    @output_server_name = 'analytics-sql-server.database.windows.net',
    @output_database_name = 'AnalyticsDB',
    @output_schema_name = 'dbo',
    @output_table_name = 'OrdersFact';

-- 6. STEP 3: Cleanup Staging
EXEC jobs.sp_add_jobstep
    @job_name = 'HourlyOrdersSync',
    @step_id = 3,
    @step_name = 'CleanupStaging',
    @command = N'
        -- Delete current execution data
        DELETE FROM staging.Orders_Staging
        WHERE JobExecutionId = $(job_execution_id);

        -- Archive old staging data (optional)
        DELETE FROM staging.Orders_Staging
        WHERE ExtractedAt < DATEADD(day, -7, GETDATE());
    ',
    @target_group_name = 'JobDatabase';
    -- No @credential_name - uses managed identity!

-- 7. CREATE SCHEDULE (Hourly)
EXEC jobs.sp_add_schedule
    @schedule_name = 'EveryHour',
    @enabled = 1,
    @frequency_type = 4,          -- Daily
    @frequency_interval = 1,      -- Every 1 day
    @frequency_subday_type = 8,   -- Hours
    @frequency_subday_interval = 1, -- Every 1 hour
    @active_start_time = 0;       -- Start at midnight

EXEC jobs.sp_attach_schedule
    @job_name = 'HourlyOrdersSync',
    @schedule_name = 'EveryHour';

-- 8. START JOB (optional - will run on schedule)
EXEC jobs.sp_start_job 'HourlyOrdersSync';

---

Monitoring

View Job Execution History

-- Recent executions
SELECT
    je.job_execution_id,
    j.job_name,
    je.lifecycle,
    je.start_time,
    je.end_time,
    DATEDIFF(SECOND, je.start_time, ISNULL(je.end_time, GETDATE())) AS duration_seconds,
    je.message
FROM jobs.job_executions je
INNER JOIN jobs.jobs j ON je.job_id = j.job_id
WHERE j.job_name = 'HourlyOrdersSync'
ORDER BY je.start_time DESC;

View Step-Level Details

-- Step execution details
SELECT
    je.job_execution_id,
    js.step_name,
    jse.lifecycle,
    jse.start_time,
    jse.end_time,
    DATEDIFF(SECOND, jse.start_time, ISNULL(jse.end_time, GETDATE())) AS duration_seconds,
    jse.message,
    jse.target_server_name,
    jse.target_database_name
FROM jobs.job_executions je
INNER JOIN jobs.jobsteps js ON je.job_id = js.job_id
INNER JOIN jobs.jobstep_executions jse ON je.job_execution_id = jse.job_execution_id AND js.step_id = jse.step_id
WHERE je.job_execution_id = '<your-execution-id>'
ORDER BY js.step_id;

Check Staging Data

-- View current staging data
SELECT
    JobExecutionId,
    COUNT(*) AS RowCount,
    MIN(ExtractedAt) AS FirstExtract,
    MAX(ExtractedAt) AS LastExtract
FROM staging.Orders_Staging
GROUP BY JobExecutionId
ORDER BY JobExecutionId DESC;

-- Total staging size
SELECT
    COUNT(*) AS TotalRows,
    SUM(CAST(DATALENGTH(*) AS BIGINT)) / 1024 / 1024 AS TotalSizeMB
FROM staging.Orders_Staging;

Verify Destination Data

-- Connect to destination database
-- Check recent loads
SELECT
    COUNT(*) AS RowCount,
    MIN(LoadedAt) AS FirstLoad,
    MAX(LoadedAt) AS LastLoad,
    SourceDatabase,
    SourceServer
FROM dbo.OrdersFact
WHERE LoadedAt >= DATEADD(hour, -24, GETDATE())
GROUP BY SourceDatabase, SourceServer;

---

Best Practices

1. Data Volume Management

-- Use incremental loads with date filters
WHERE ModifiedDate >= DATEADD(hour, -1, GETDATE())
  AND ModifiedDate < GETDATE()

-- Add indexes on filter columns in source
CREATE INDEX IX_Orders_ModifiedDate ON dbo.Orders(ModifiedDate);

2. Error Handling

-- Add retry policy to job steps
EXEC jobs.sp_update_jobstep
    @job_name = 'HourlyOrdersSync',
    @step_id = 1,
    @retry_attempts = 3,
    @retry_interval_minutes = 5;

-- Add error logging step
EXEC jobs.sp_add_jobstep
    @job_name = 'HourlyOrdersSync',
    @step_id = 99,
    @step_name = 'LogError',
    @command = N'
        INSERT INTO logs.JobErrors (JobName, ExecutionId, ErrorMessage, ErrorTime)
        VALUES (''$(job_name)'', $(job_execution_id), ''Step failed'', GETDATE())
    ',
    @on_failure_action = 0;  -- Quit reporting failure

3. Execution Tracking

-- Use execution ID in all queries
WHERE JobExecutionId = $(job_execution_id)

-- Include metadata in extracts
SELECT
    $(job_execution_id) AS JobExecutionId,
    $(job_name) AS JobName,
    DB_NAME() AS SourceDatabase,
    GETDATE() AS ExtractedAt,
    *
FROM SourceTable

4. Staging Cleanup Strategy

-- Option A: Immediate cleanup (in job step 3)
DELETE FROM staging.Orders_Staging
WHERE JobExecutionId = $(job_execution_id);

-- Option B: Time-based retention
DELETE FROM staging.Orders_Staging
WHERE ExtractedAt < DATEADD(day, -7, GETDATE());

-- Option C: Separate cleanup job (daily)
CREATE PROCEDURE staging.CleanupOldData
AS
BEGIN
    -- Keep last 100 executions
    DELETE FROM staging.Orders_Staging
    WHERE JobExecutionId NOT IN (
        SELECT TOP 100 JobExecutionId
        FROM staging.Orders_Staging
        GROUP BY JobExecutionId
        ORDER BY MAX(ExtractedAt) DESC
    );
END

5. Performance Optimization

-- Job database: Upgrade to higher tier if needed
-- S1: Light workloads (< 100 MB/hour)
-- S2/S3: Moderate workloads (100 MB - 1 GB/hour)
-- P1+: Heavy workloads (> 1 GB/hour)

-- Add indexes to staging tables (for large staging datasets)
CREATE INDEX IX_Staging_JobExecId ON staging.Orders_Staging(JobExecutionId);

-- Use columnstore for analytics destination
CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrdersFact ON dbo.OrdersFact;

6. Security Best Practices

-- ✅ NO CREDENTIALS TO MANAGE!
-- Managed identity provides secure, password-less authentication

-- Principle of least privilege - grant only required permissions:

-- Source databases: SELECT only
GRANT SELECT ON SCHEMA::dbo TO [ElasticJobAgent-Prod];

-- Destination databases: INSERT and CREATE TABLE only (no UPDATE/DELETE)
GRANT INSERT ON SCHEMA::dbo TO [ElasticJobAgent-Prod];
GRANT CREATE TABLE TO [ElasticJobAgent-Prod];

-- Job database: CREATE TABLE, INSERT, DELETE on staging schema only
GRANT CREATE TABLE TO [ElasticJobAgent-Prod];
GRANT INSERT, DELETE ON SCHEMA::staging TO [ElasticJobAgent-Prod];

-- ✅ No credential rotation needed - managed identity handles this automatically
-- ✅ No secrets to store in Azure Key Vault
-- ✅ Integrated with Microsoft Entra ID for centralized access management

Additional Security Tips:

• Use Azure Private Endpoints for database connectivity (no public internet exposure)
• Enable Microsoft Defender for SQL on all databases
• Monitor job execution logs for anomalies using Azure Monitor
• Use separate managed identities for different environments (dev/test/prod)

---

Troubleshooting

Common Issues

1. Job Fails - "Cannot create table"

Cause: Job agent lacks CREATE TABLE permission Fix:

-- On job database and destination
GRANT CREATE TABLE TO [<JobAgentIdentity>];

2. Step 2 Fails - "Invalid object name 'staging.Orders_Staging'"

Cause: Staging table not created or cleanup happened too early Fix:

-- Ensure Step 1 completed successfully
-- Check step execution order
-- Add explicit ON SUCCESS dependency
EXEC jobs.sp_update_jobstep
    @job_name = 'CrossServerCopy',
    @step_id = 2,
    @on_success_action = 3,  -- Go to next step
    @on_success_step_id = 3;

3. "Login failed for user" or Authentication Errors

Cause: Managed identity not configured properly or Entra ID auth not enabled Fix:

-- Step 1: Verify managed identity user exists in target database
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE name = 'ElasticJobAgent-Prod';
-- Should return: type_desc = 'EXTERNAL_USER', authentication_type_desc = 'EXTERNAL'

-- Step 2: If not exists, create it
CREATE USER [ElasticJobAgent-Prod] FROM EXTERNAL PROVIDER;
GRANT SELECT ON SCHEMA::dbo TO [ElasticJobAgent-Prod];  -- Adjust permissions as needed

-- Step 3: Verify Entra ID authentication is enabled on SQL Server
-- Check in Azure Portal: SQL Server > Settings > Azure Active Directory
-- Must have an admin configured

-- Step 4: Check firewall rules
-- Add "Allow Azure services and resources to access this server" or
-- Add specific firewall rules for job agent's outbound IPs

Additional checks:

# Verify the managed identity is assigned to the job agent
az sql elastic-pool show --name <elastic-job-agent-name> \
  --resource-group <rg-name> --server <server-name> \
  --query identity

4. Staging Table Growing Too Large

Cause: Cleanup not running or insufficient Fix:

-- Check staging size
SELECT
    t.name,
    SUM(p.rows) AS row_count,
    SUM(a.total_pages) * 8 / 1024 AS total_size_mb
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.schema_id = SCHEMA_ID('staging')
GROUP BY t.name;

-- Manual cleanup if needed
TRUNCATE TABLE staging.Orders_Staging;

-- Improve cleanup step
DELETE FROM staging.Orders_Staging
WHERE ExtractedAt < DATEADD(day, -1, GETDATE());  -- More aggressive

5. Job Runs Slow

Cause: Large data volume or job database under-provisioned Fix:

-- Check execution duration
SELECT
    AVG(DATEDIFF(SECOND, start_time, end_time)) AS avg_duration_sec,
    MAX(DATEDIFF(SECOND, start_time, end_time)) AS max_duration_sec
FROM jobs.job_executions
WHERE job_name = 'HourlyOrdersSync'
  AND lifecycle = 'Succeeded';

-- Scale up job database if duration increasing
-- Consider splitting large extracts:
WHERE OrderDate >= DATEADD(hour, -1, GETDATE())
  AND OrderDate < DATEADD(minute, -30, GETDATE())  -- First 30 min
-- AND second job for next 30 min

6. Duplicate Data in Destination

Cause: Job re-run or concurrent executions Fix:

-- Add unique constraint to destination
ALTER TABLE dbo.OrdersFact
ADD CONSTRAINT UQ_OrderID_LoadedAt UNIQUE (OrderID, LoadedAt);

-- Or filter duplicates in Step 2
SELECT DISTINCT ...

-- Prevent concurrent runs
EXEC jobs.sp_update_job
    @job_name = 'HourlyOrdersSync',
    @max_parallelism = 1;

7. "The server principal '' is not able to access the database"

Cause: Managed identity user created in wrong database (user vs. master) Fix:

-- For database-level targets: Create user in the target database
USE [TargetDatabase];
CREATE USER [ElasticJobAgent-Prod] FROM EXTERNAL PROVIDER;

-- For server-level or pool targets: ALSO create user in master database
USE [master];
CREATE USER [ElasticJobAgent-Prod] FROM EXTERNAL PROVIDER;

-- Verify both exist
SELECT DB_NAME() AS DatabaseName, name
FROM sys.database_principals
WHERE name = 'ElasticJobAgent-Prod';

8. "Cannot mix Entra authentication and database-scoped credentials"

Cause: Trying to use credentials when managed identity is configured Fix:

-- Remove any @credential_name or @output_credential_name parameters from job steps
-- Managed identity is used automatically when assigned to the job agent

-- If you need to use credentials instead, you must NOT use managed identity
-- This is an all-or-nothing choice

Debug Queries

-- Get last error message
SELECT TOP 1
    job_execution_id,
    message,
    lifecycle
FROM jobs.job_executions
WHERE job_name = 'HourlyOrdersSync'
  AND lifecycle = 'Failed'
ORDER BY start_time DESC;

-- Get failed step details
SELECT
    js.step_name,
    jse.message,
    jse.target_server_name,
    jse.target_database_name
FROM jobs.jobstep_executions jse
INNER JOIN jobs.jobsteps js ON jse.step_id = js.step_id AND jse.job_id = js.job_id
WHERE jse.job_execution_id = '<failed-execution-id>'
  AND jse.lifecycle = 'Failed';

-- Verify managed identity permissions on target database
EXECUTE AS USER = 'ElasticJobAgent-Prod';
SELECT HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id), 'SCHEMA', 'SELECT') AS HasSelect,
       HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id), 'SCHEMA', 'INSERT') AS HasInsert,
       HAS_PERMS_BY_NAME(NULL, NULL, 'CREATE TABLE') AS HasCreateTable,
       SCHEMA_NAME(schema_id) AS SchemaName
FROM sys.schemas WHERE schema_id < 5;
REVERT;

---

When to Use This Pattern

✅ Good Use Cases

Scenario	Why It Works
Incremental ETL	Hourly/daily delta loads (< 1 GB per run)
Cross-subscription copy	No private endpoints needed between source/dest
Audit requirements	Staging data provides paper trail
Simple aggregations	Can transform in Step 2 query
Multiple sources → one dest	Centralized staging consolidation
Can't modify source DBs	No external data sources required

❌ Avoid For

Scenario	Why It Doesn't Work	Alternative
Large bulk transfers (> 10 GB)	Double network hop, staging overhead	Azure Data Factory, blob staging
Real-time replication	Job execution latency (minutes)	Transactional replication, Change Data Capture
Complex MERGE logic	Output only supports INSERT	External data sources with MERGE
High-frequency (< 5 min)	Job scheduling overhead	Continuous sync, Azure Synapse Link
Very large staging	Job DB becomes bottleneck	Use blob storage staging

Comparison with Alternatives

Method	Setup	Cross-Server	No Credentials	No Ext. DS	Large Data	Real-time
This Pattern (Managed Identity)	⭐⭐ Low	✅	✅	✅	❌	❌
Elastic Jobs (SQL Auth)	⭐⭐ Low	✅	❌	✅	❌	❌
External Data Sources	⭐⭐⭐ Medium	✅	⚠️	❌	⚠️	❌
Azure Data Factory	⭐⭐⭐⭐ High	✅	✅	✅	✅	❌
Blob Storage Staging	⭐⭐⭐ Medium	✅	⚠️	✅	✅	❌
Transactional Replication	⭐⭐⭐⭐⭐ Very High	✅	⚠️	✅	✅	✅

Legend:

• No Credentials: Managed identity / password-less authentication
• No Ext. DS: No external data sources required
• ⚠️ = Partial support or depends on configuration

---

Additional Resources

• Azure SQL Elastic Jobs Overview
• Create Elastic Jobs with T-SQL
• sp_add_jobstep Reference
• Managed Identities for Azure Resources
• Azure CLI: az sql elastic-job-agent

---

Summary

This pattern provides a secure, credential-free, no-external-data-source solution for copying data between Azure SQL Databases on different servers using managed identity:

Key Benefits

1. ✅ No Credentials to Manage - Uses user-assigned managed identity (UMI)
2. ✅ No Password Rotation - Microsoft Entra ID handles authentication
3. ✅ No External Data Sources - Simple @output_* parameter approach
4. ✅ Works Across Servers & Subscriptions - Seamless cross-boundary data copy

How It Works

1. Step 1: Extract from source using @output_* → writes to job database staging (managed identity auth)
2. Step 2: Transform and load from job database using @output_* → writes to destination (managed identity auth)
3. Step 3: Cleanup staging data

When to Use

✅ Perfect for:

• Incremental ETL with managed identity security
• Cross-subscription scenarios without credential sharing
• Moderate data volumes (< 1 GB/hour)
• Organizations requiring password-less authentication

❌ Avoid for:

• Large bulk transfers (> 10 GB)
• Real-time replication
• Complex MERGE operations
• Environments without Microsoft Entra ID

Security Advantage

This approach eliminates credential management entirely - no passwords to store, rotate, or secure in Azure Key Vault. The managed identity is automatically managed by Azure and integrated with your organization's Microsoft Entra ID.

---

Questions or Issues?

• Check the Troubleshooting section above
• Review Azure SQL Elastic Jobs documentation
• Post questions on Microsoft Q&A with tag azure-sql-database
• Open issues on Azure SQL Database feedback

elastic_agent_jobs_logging.sql

EXEC jobs.sp_update_jobstep 
@job_name = 'DBCopy',
@step_name = 'Copy DB',
@command = N'
SET NOCOUNT ON;

DECLARE 
@print_only BIT = 1,
@job NVARCHAR(100) = $(job_name),
@job_step NVARCHAR(100) = $(step_name)

DECLARE @temp_cmd
TABLE (
    row_num INT IDENTITY (1, 1) Primary key NOT NULL,
    job NVARCHAR(100) NOT NULL,
    job_step NVARCHAR(100) NOT NULL,
    cmd NVARCHAR(MAX) NULL,
    error NVARCHAR(MAX) NULL,
    runtime DATETIME NULL
)

BEGIN TRY  
    IF (@print_only <> 0)
    BEGIN
        PRINT ''DB COPY DISABLED'';
    END
    ELSE
    BEGIN
        DROP DATABASE IF EXISTS [MyDBDeID]
        CREATE DATABASE [MyDBDeID] AS COPY OF [MyDB]
        --(EDITION = ''Hyperscale'', SERVICE_OBJECTIVE = ''HS_S_Gen5_80'')
        (EDITION = ''Premium'', SERVICE_OBJECTIVE = ''P15'')
        WITH (BACKUP_STORAGE_REDUNDANCY = ''LOCAL'');

        WAITFOR DELAY ''00:05''

        DECLARE @msg NVARCHAR(1000)
        WHILE EXISTS(Select 1 from sys.databases where  Name like ''MyDBDeID'' and state_desc <> ''Online'')
        BEGIN
            SELECT @msg =  ''Database MyDBDeID  has status '' + state_desc  from sys.databases  where  Name like ''MyDBDeID'' and state_desc <> ''Online''
            RAISERROR(@msg, 0, 1) WITH NOWAIT
            WAITFOR DELAY ''00:05''
        END
    END
END TRY  
BEGIN CATCH
    INSERT INTO @temp_cmd (job, job_step, error, runtime)
    SELECT
        @job,
        @job_step,
        ''ERROR_NUMBER: '' + ISNULL(TRY_CAST(ERROR_NUMBER() AS NVARCHAR), '''') +
        ''|ERROR_SEVERITY: '' + ISNULL(TRY_CAST(ERROR_SEVERITY() AS NVARCHAR), '''') +
        ''|ERROR_STATE: '' + ISNULL(TRY_CAST(ERROR_STATE() AS NVARCHAR), '''') +
        ''|ERROR_PROCEDURE: '' + ISNULL(TRY_CAST(ERROR_PROCEDURE() AS NVARCHAR), '''') +
        ''|ERROR_LINE: '' + ISNULL(TRY_CAST(ERROR_LINE() AS NVARCHAR), '''') +
        ''|ERROR_MESSAGE: '' + ISNULL(TRY_CAST(ERROR_MESSAGE() AS NVARCHAR), '''') AS [error],
        GETUTCDATE() AS [runtime]
END CATCH;  

SELECT
job,
job_step,
cmd,
error,
runtime
FROM @temp_cmd
',
@target_group_name = 'master_MyServer',
@step_timeout_seconds = 10800,
@output_type = 'SqlDatabase',
@output_server_name = 'MyServer.database.windows.net',
@output_database_name = 'MyAdminDB',
@output_schema_name = 'dbo',
@output_table_name = 'jobstep_results';

EXEC jobs.sp_update_jobstep 
@job_name = 'DBCopy - MyDB',
@step_name = 'DeID DB',
@command = N'
SET NOCOUNT ON;

DECLARE 
@print_only BIT = 1,
@job NVARCHAR(100) = $(job_name),
@job_step NVARCHAR(100) = $(step_name)

DECLARE @temp_cmd
TABLE (
    row_num INT IDENTITY (1, 1) Primary key NOT NULL,
    job NVARCHAR(100) NOT NULL,
    job_step NVARCHAR(100) NOT NULL,
    cmd NVARCHAR(MAX) NULL,
    error NVARCHAR(MAX) NULL,
    runtime DATETIME NULL
)

BEGIN TRY  
    DECLARE @loop_num INT = 1

    INSERT INTO @temp_cmd (job, job_step, cmd)
    SELECT
        @job,
        @job_step,
        ''BEGIN TRAN; UPDATE '' + [table] + '' SET '' + STRING_AGG([column] + ''='''''' + [mask] + '''''''', '', '') + ''; COMMIT;'' AS [cmd]
    FROM (
        SELECT
        QUOTENAME(schema_name(O.schema_id)) + ''.'' + QUOTENAME(O.NAME) AS [table],
        QUOTENAME(C.NAME) AS [column],
        t.name AS [type],
        sc.label,
        CASE t.name
            WHEN ''date'' THEN ''2000-01-01''
            ELSE sc.label
        END AS [mask]
        FROM sys.sensitivity_classifications sc
            JOIN sys.objects O ON  sc.major_id = O.object_id
	        JOIN sys.columns C ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id
	        JOIN sys.types AS [t] ON (c.user_type_id = t.user_type_id)
        WHERE sc.label <> ''NO_DEID''
    ) a
    GROUP BY [table]

    WHILE (@loop_num <= (SELECT MAX(row_num) FROM @temp_cmd))
    BEGIN
        DECLARE @cmd NVARCHAR(MAX) = (SELECT [cmd] FROM @temp_cmd WHERE row_num = @loop_num)
        UPDATE @temp_cmd SET runtime = GETUTCDATE() WHERE row_num = @loop_num
        IF (@print_only = 0)
            EXEC sp_executesql @cmd
        SET @loop_num+=1
        WAITFOR DELAY ''00:00:00:10''
    END

END TRY  
BEGIN CATCH
    INSERT INTO @temp_cmd (job, job_step, error, runtime)
    SELECT
        @job,
        @job_step,
        ''ERROR_NUMBER: '' + ISNULL(TRY_CAST(ERROR_NUMBER() AS NVARCHAR), '''') +
        ''|ERROR_SEVERITY: '' + ISNULL(TRY_CAST(ERROR_SEVERITY() AS NVARCHAR), '''') +
        ''|ERROR_STATE: '' + ISNULL(TRY_CAST(ERROR_STATE() AS NVARCHAR), '''') +
        ''|ERROR_PROCEDURE: '' + ISNULL(TRY_CAST(ERROR_PROCEDURE() AS NVARCHAR), '''') +
        ''|ERROR_LINE: '' + ISNULL(TRY_CAST(ERROR_LINE() AS NVARCHAR), '''') +
        ''|ERROR_MESSAGE: '' + ISNULL(TRY_CAST(ERROR_MESSAGE() AS NVARCHAR), '''') AS [error],
        GETUTCDATE() AS [runtime]
END CATCH;  

SELECT
job,
job_step,
cmd,
error,
runtime
FROM @temp_cmd
',
@target_group_name = 'MyDBDeID',
@step_timeout_seconds = 86400,
@output_type = 'SqlDatabase',
@output_server_name = 'MyServer.database.windows.net',
@output_database_name = 'MyAdminDB',
@output_schema_name = 'dbo',
@output_table_name = 'jobstep_results';

elastic_agent_provision.tf

/*
--Assign as identity to elastic job agent
--master db
CREATE USER ElasticJobsIdentity FROM EXTERNAL PROVIDER;
--user db
CREATE USER ElasticJobsIdentity FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER ElasticJobsIdentity;
*/
resource "azurerm_user_assigned_identity" "elastic_jobs" {
  location            = azurerm_resource_group.default.location
  name                = "ElasticJobsIdentity"
  resource_group_name = azurerm_resource_group.default.name
}
resource "azurerm_mssql_job_agent" "elastic_jobs" {
  name        = "ElasticJobsAgent"
  location    = azurerm_resource_group.default.location
  database_id = azurerm_mssql_database.elastic_jobs.id
}
resource "azurerm_mssql_database" "elastic_jobs" {
  name      = "ElasticJobsDB"
  server_id = azurerm_mssql_server.default.id
}