Skapa och hantera elastiska jobb med hjälp av T-SQL (förhandsversion)

Gäller för:Azure SQL Database

Den här artikeln innehåller en självstudie och exempel för att komma igång med elastiska jobb med T-SQL. Elastiska jobb aktiverar körning av ett eller flera Transact-SQL-skript (T-SQL) parallellt över flera databaser.

Exemplen i den här artikeln använder lagrade procedurer och vyer som är tillgängliga i jobbdatabasen.

I den här självstudien från slutpunkt till slutpunkt får du lära dig de steg som krävs för att köra en fråga i flera databaser:

• Skapa en elastisk jobbagent
• Skapa autentiseringsuppgifter för jobbet så att det kan köra skript på sina mål
• Definiera de mål (servrar, elastiska pooler, databaser) som du vill köra jobbet mot
• Skapa databasomfattande autentiseringsuppgifter i måldatabaserna så att agenten ansluter och kör jobb
• Skapa ett jobb
• Lägg till jobbsteg i ett jobb
• Starta körningen av ett jobb
• Övervaka ett jobb

Kommentar

Elastiska jobb finns i förhandsversion. Funktioner som för närvarande är i förhandsversion är tillgängliga under kompletterande användningsvillkor, granska för juridiska villkor som gäller för Azure-funktioner som är i förhandsversion. Azure SQL Database innehåller förhandsversioner som ger dig möjlighet att utvärdera och dela feedback med produktgruppen om funktioner innan de blir allmänt tillgängliga (GA).

Skapa den elastiska jobbagenten

Transact-SQL (T-SQL) kan användas för att skapa, konfigurera, köra och hantera jobb.

Det går inte att skapa den elastiska jobbagenten i T-SQL, så du måste först skapa en elastisk jobbagent med hjälp av Azure-portalen eller skapa en elastisk jobbagent med hjälp av PowerShell.

Skapa jobbautentiseringen

Den elastiska jobbagenten måste kunna autentisera till varje målserver eller databas. Som beskrivs i Skapa jobbagentautentisering rekommenderar vi att du använder Microsoft Entra-autentisering (tidigare Azure Active Directory) med en användartilldelad hanterad identitet (UMI). Tidigare var databasomfattande autentiseringsuppgifter det enda alternativet.

Använda Microsoft Entra-autentisering med en UMI för jobbkörning

Följ dessa steg om du vill använda den rekommenderade metoden för Microsoft Entra-autentisering (tidigare Azure Active Directory) för en användartilldelad hanterad identitet (UMI). Den elastiska jobbagenten ansluter till önskad logisk målserver/databaser via Microsoft Entra-autentisering.

Förutom inloggnings- och databasanvändarna noterar du tillägget av GRANT kommandona i följande skript. Dessa behörigheter krävs för skriptet vi valde för det här exempeljobbet. Dina jobb kan kräva olika behörigheter. Eftersom exemplet skapar en ny tabell i måldatabaserna behöver databasanvändaren i varje måldatabas rätt behörighet för att kunna köras.

I var och en av målservrarna/databaserna skapar du en innesluten användare som mappats till UMI.

• Om det elastiska jobbet har logiska server- eller poolmål måste du skapa den inneslutna användaren som mappas till UMI i master databasen för den logiska målservern.
• Om du till exempel vill skapa en innesluten master databasinloggning i databasen och en användare i användardatabasen, baserat på den användartilldelade hanterade identiteten (UMI) med namnet job-agent-UMI:

--Create a login on the master database mapped to a user-assigned managed identity (UMI)
CREATE LOGIN [job-agent-UMI] FROM EXTERNAL PROVIDER; 

--Create a user on a user database mapped to a login.
CREATE USER [job-agent-UMI] FROM LOGIN [job-agent-UMI];

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO jobuser;
GRANT CREATE TABLE TO jobuser;

• Så här skapar du en innesluten databasanvändare om en inloggning inte behövs på den logiska servern:

--Create a contained database user on a user database mapped to a user-assigned managed identity (UMI)
CREATE USER [job-agent-UMI] FROM EXTERNAL PROVIDER; 

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO jobuser;
GRANT CREATE TABLE TO jobuser;

Använda en databasomfattande autentiseringsuppgift för jobbkörning

En databasomfattande autentiseringsuppgift används för att ansluta till dina måldatabaser för skriptkörning. Autentiseringsuppgifterna behöver lämpliga behörigheter för de databaser som anges av målgruppen för att skriptet ska kunna köras. När du använder en logisk SQL-server och/eller poolmålgruppmedlem rekommenderar vi att du skapar en autentiseringsuppgift som ska användas för att uppdatera autentiseringsuppgifterna innan servern och/eller poolen expanderas vid tidpunkten för jobbkörningen. Databasens omfångsbegränsade autentiseringsuppgifter skapas i jobbagentdatabasen.

Samma autentiseringsuppgifter måste användas för att skapa en inloggning och skapa en användare från inloggning för att bevilja inloggningsdatabasbehörigheter för alla måldatabaser.

--Connect to the new job database specified when creating the elastic job agent

-- Create a database master key if one does not already exist, using your own password.  
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';  

-- Create two database-scoped credentials.  
-- The credential to connect to the Azure SQL logical server, to execute jobs
CREATE DATABASE SCOPED CREDENTIAL job_credential WITH IDENTITY = 'job_credential',
    SECRET = '<EnterStrongPasswordHere>';
GO
-- The credential to connect to the Azure SQL logical server, to refresh the database metadata in server
CREATE DATABASE SCOPED CREDENTIAL refresh_credential WITH IDENTITY = 'refresh_credential',
    SECRET = '<EnterStrongPasswordHere>';
GO

Skapa sedan inloggningar på målservrarna eller inneslutna databasanvändare i måldatabaser.

Viktigt!

Inloggningen/användaren på varje målserver/databas måste ha samma namn som identiteten för den databasomfattande autentiseringsuppgiften för jobbanvändaren och samma lösenord som jobbanvändarens databasomfattande autentiseringsuppgifter.

Skapa en inloggning i databasen för master den logiska SQL-servern och användare i varje användardatabas.

--Create a login on the master database
CREATE LOGIN job_credential WITH PASSWORD='<Enter_same_StrongPassword_as_database_scoped_credential>';

--Create a user on a user database mapped to a login.
CREATE USER [job_credential] FROM LOGIN [job_credential];

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO job_credential;
GRANT CREATE TABLE TO job_credential;

Skapa en innesluten databasanvändare om en inloggning inte behövs på den logiska servern. Vanligtvis gör du bara detta om du har en enda databas att hantera med den här elastiska jobbagenten.

--Create a contained database user on a user database mapped to a Microsoft Entra account
CREATE USER [job_credential] WITH PASSWORD='<Enter_same_StrongPassword_as_database_scoped_credential>';

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO job_credential;
GRANT CREATE TABLE TO job_credential;

Definiera målservrar och databaser

I följande exempel visas hur du kör ett jobb mot alla databaser på en server.

Anslut till job_database och kör följande kommando för att lägga till en målgrupp och målmedlem:

-- Connect to the job database specified when creating the job agent

-- Add a target group containing server(s)
EXEC jobs.sp_add_target_group 'ServerGroup1';

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'ServerGroup1',
@target_type = 'SqlServer',
@server_name = 'server1.database.windows.net';

--View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name='ServerGroup1';
SELECT * FROM jobs.target_group_members WHERE target_group_name='ServerGroup1';

Exkludera en enskild databas

I följande exempel visas hur du kör ett jobb mot alla databaser på en server, förutom databasen med namnet MappingDB.

När du använder Microsoft Entra-autentisering (tidigare Azure Active Directory) utelämnar du parametern @refresh_credential_name , som endast ska anges när du använder databasomfattande autentiseringsuppgifter. I följande exempel kommenteras parametern @refresh_credential_name ut.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

-- Add a target group containing server(s)
EXEC [jobs].sp_add_target_group N'ServerGroup';
GO

-- Add a server target member
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@target_type = N'SqlServer',
--@refresh_credential_name = N'refresh_credential', --credential required to refresh the databases in a server
@server_name = N'London.database.windows.net';
GO

-- Add a server target member
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@target_type = N'SqlServer',
--@refresh_credential_name = N'refresh_credential', --credential required to refresh the databases in a server
@server_name = 'server2.database.windows.net';
GO

--Exclude a database target member from the server target group
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@membership_type = N'Exclude',
@target_type = N'SqlDatabase',
@server_name = N'server1.database.windows.net',
@database_name = N'MappingDB';
GO

--View the recently created target group and target group members
SELECT * FROM [jobs].target_groups WHERE target_group_name = N'ServerGroup';
SELECT * FROM [jobs].target_group_members WHERE target_group_name = N'ServerGroup';

Skapa en målgrupp (pooler)

I följande exempel visas hur du riktar in dig på alla databaser i en eller flera elastiska pooler.

När du använder Microsoft Entra-autentisering (tidigare Azure Active Directory) utelämnar du parametern @refresh_credential_name , som endast ska anges när du använder databasomfattande autentiseringsuppgifter. I följande exempel kommenteras parametern @refresh_credential_name ut.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

-- Add a target group containing pool(s)
EXEC jobs.sp_add_target_group 'PoolGroup';

-- Add an elastic pool(s) target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'PoolGroup',
@target_type = 'SqlElasticPool',
--@refresh_credential_name = 'refresh_credential', --credential required to refresh the databases in a server
@server_name = 'server1.database.windows.net',
@elastic_pool_name = 'ElasticPool-1';

-- View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name = N'PoolGroup';
SELECT * FROM jobs.target_group_members WHERE target_group_name = N'PoolGroup';

Skapa ett jobb och steg

Med T-SQL skapar du jobb med hjälp av system lagrade procedurer i jobbdatabasen: jobs.sp_add_job och jobs.sp_add_jobstep. T-SQL-kommandona är syntaxen liknar de steg som krävs för att skapa SQL Agent-jobb och jobbsteg i SQL Server.

Du bör inte uppdatera interna katalogvyer i jobbdatabasen. Om du ändrar katalogvyerna manuellt kan jobbdatabasen skadas och orsaka fel. Dessa vyer är endast skrivskyddade för frågor. Du kan använda de lagrade procedurerna i schemat i jobbdatabasen jobs.

• När du använder Microsoft Entra-autentisering för ett Microsoft Entra-ID eller en användartilldelad hanterad identitet för att autentisera mot målservrar/databaser ska argumentet @credential_name inte anges för sp_add_jobstep eller sp_update_jobstep. På samma sätt utelämnar du de valfria argumenten @output_credential_name och @refresh_credential_name .
• När du använder databasomfattande autentiseringsuppgifter för att autentisera mot målservrar/databaser krävs parametern @credential_name för sp_add_jobstep och sp_update_jobstep.
  • Exempel: @credential_name = 'job_credential'

I följande exempel finns guider för att skapa jobb- och jobbsteg med hjälp av T-SQL för att utföra vanliga uppgifter med elastiska jobb.

Exempel

Distribuera nytt schema till många databaser

I följande exempel visas hur du distribuerar ett nytt schema till alla databaser.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

--Add job for create table
EXEC jobs.sp_add_job @job_name = 'CreateTableTest', @description = 'Create Table Test';

-- Add job step for create table
EXEC jobs.sp_add_jobstep @job_name = 'CreateTableTest',
@command = N'IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(''Test''))
CREATE TABLE [dbo].[Test]([TestId] [int] NOT NULL);',
@target_group_name = 'PoolGroup';

Datainsamling med hjälp av inbyggda parametrar

I många datainsamlingsscenarier kan det vara användbart att inkludera några av dessa skriptvariabler för att hjälpa till att bearbeta resultatet av jobbet efter processen.

• $(job_name)
• $(job_id)
• $(job_version)
• $(step_id)
• $(step_name)
• $(job_execution_id)
• $(job_execution_create_time)
• $(target_group_name)

Om du till exempel vill gruppera alla resultat från samma jobbkörning tillsammans använder du $(job_execution_id) det som visas i följande kommando:

@command= N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());'

Kommentar

Alla tider i elastiska jobb finns i UTC-tidszonen.

Övervaka databasprestanda

I följande exempel skapas ett nytt jobb för att samla in prestandadata från flera databaser.

Som standard skapar jobbagenten utdatatabellen för att lagra returnerade resultat. Därför måste databasobjektet som är associerat med utdataautentiseringsuppgifterna minst ha följande behörigheter: CREATE TABLE i databasen, , ALTERSELECT, INSERTpå DELETE utdatatabellen eller dess schema och SELECT i katalogvyn sys.indexes.

Om du vill skapa tabellen manuellt i förväg måste den ha följande egenskaper:

1. Kolumner med rätt namn och datatyper för resultatuppsättningen.
2. Ytterligare kolumn för internal_execution_id med datatypen unikidentifierare.
3. Ett icke-grupperat index med namnet IX_<TableName>_Internal_Execution_ID i internal_execution_id kolumnen.
4. Alla tidigare angivna behörigheter förutom CREATE TABLE behörighet för databasen.

Anslut till jobbdatabasen och kör följande kommandon:

--Connect to the job database specified when creating the job agent

-- Add a job to collect perf results
EXEC jobs.sp_add_job @job_name ='ResultsJob', @description='Collection Performance data from all customers'

-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name = 'ResultsJob',
@command = N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());',
@target_group_name = 'PoolGroup',
@output_type = 'SqlDatabase',
@output_server_name = 'server1.database.windows.net',
@output_database_name = '<resultsdb>',
@output_table_name = '<output_table_name>';

--Create a job to monitor pool performance

--Connect to the job database specified when creating the job agent

-- Add a target group containing elastic job database
EXEC jobs.sp_add_target_group 'ElasticJobGroup';

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'ElasticJobGroup',
@target_type = 'SqlDatabase',
@server_name = 'server1.database.windows.net',
@database_name = 'master';

-- Add a job to collect perf results
EXEC jobs.sp_add_job
@job_name = 'ResultsPoolsJob',
@description = 'Demo: Collection Performance data from all pools',
@schedule_interval_type = 'Minutes',
@schedule_interval_count = 15;

-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name='ResultsPoolsJob',
@command=N'declare @now datetime
DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @poolLagMinutes datetime
DECLARE @poolStartTime datetime
DECLARE @poolEndTime datetime
SELECT @now = getutcdate ()
SELECT @startTime = dateadd(minute, -15, @now)
SELECT @endTime = @now
SELECT @poolStartTime = dateadd(minute, -30, @startTime)
SELECT @poolEndTime = dateadd(minute, -30, @endTime)

SELECT elastic_pool_name , end_time, elastic_pool_dtu_limit, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent, max_session_percent,
        avg_storage_percent, elastic_pool_storage_limit_mb FROM sys.elastic_pool_resource_stats
        WHERE end_time > @poolStartTime and end_time <= @poolEndTime;
',
@target_group_name = 'ElasticJobGroup',
@output_type = 'SqlDatabase',
@output_server_name = 'server1.database.windows.net',
@output_database_name = 'resultsdb',
@output_table_name = '<output_table_name>';

Kör jobbet

I följande exempel visas hur du startar ett jobb omedelbart som en manuell, oplanerad åtgärd.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

-- Execute the latest version of a job
EXEC jobs.sp_start_job 'CreateTableTest';

-- Execute the latest version of a job and receive the execution ID
declare @je uniqueidentifier;
exec jobs.sp_start_job 'CreateTableTest', @job_execution_id = @je output;
select @je;

-- Monitor progress
SELECT * FROM jobs.job_executions WHERE job_execution_id = @je;

Schemalägg körning av ett jobb

I följande exempel visas hur du schemalägger ett jobb för framtida körning regelbundet var 15:e minut.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

EXEC jobs.sp_update_job
@job_name = 'ResultsJob',
@enabled=1,
@schedule_interval_type = 'Minutes',
@schedule_interval_count = 15;

Visa jobbdefinitioner

I följande exempel visas hur du visar aktuella jobbdefinitioner.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

-- View all jobs
SELECT * FROM jobs.jobs;

-- View the steps of the current version of all jobs
SELECT js.* FROM jobs.jobsteps js
JOIN jobs.jobs j
  ON j.job_id = js.job_id AND j.job_version = js.job_version;

-- View the steps of all versions of all jobs
SELECT * FROM jobs.jobsteps;

Övervaka jobbkörningsstatus

I följande exempel visas hur du visar körningsstatusinformation för alla jobb.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

--View top-level execution status for the job named 'ResultsPoolJob'
SELECT * FROM jobs.job_executions
WHERE job_name = 'ResultsPoolsJob' and step_id IS NULL
ORDER BY start_time DESC;

--View all top-level execution status for all jobs
SELECT * FROM jobs.job_executions WHERE step_id IS NULL
ORDER BY start_time DESC;

--View all execution statuses for job named 'ResultsPoolsJob'
SELECT * FROM jobs.job_executions
WHERE job_name = 'ResultsPoolsJob'
ORDER BY start_time DESC;

-- View all active executions
SELECT * FROM jobs.job_executions
WHERE is_active = 1
ORDER BY start_time DESC;

Avbryt ett jobb

I följande exempel visas hur du hämtar ett jobbkörnings-ID och sedan avbryter en jobbkörning.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

-- View all active executions to determine job execution ID
SELECT * FROM jobs.job_executions
WHERE is_active = 1 AND job_name = 'ResultPoolsJob'
ORDER BY start_time DESC;
GO

-- Cancel job execution with the specified job execution ID
EXEC jobs.sp_stop_job '01234567-89ab-cdef-0123-456789abcdef';

Ta bort gammal jobbhistorik

I följande exempel visas hur du tar bort jobbhistoriken före ett visst datum.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

-- Delete history of a specific job's executions older than the specified date
EXEC jobs.sp_purge_jobhistory @job_name='ResultPoolsJob', @oldest_date='2016-07-01 00:00:00';

--Note: job history is automatically deleted if it is >45 days old

Ta bort ett jobb och all dess jobbhistorik

I följande exempel visas hur du tar bort ett jobb och all relaterad jobbhistorik.

Anslut till job_database och kör följande kommando:

--Connect to the job database specified when creating the job agent

EXEC jobs.sp_delete_job @job_name='ResultsPoolsJob';
EXEC jobs.sp_purge_jobhistory @job_name='ResultsPoolsJob';

--Note: job history is automatically deleted if it is >45 days old

Jobb lagrade procedurer

Följande lagrade procedurer finns i jobbdatabasen. De namnges på samma sätt men skiljer sig tydligt från de system lagrade procedurerna som används för SQL Server Agent-tjänsten.

Lagrad procedur	beskrivning
sp_add_job	Lägger till ett nytt jobb.
sp_update_job	Uppdateringar ett befintligt jobb.
sp_delete_job	Tar bort ett befintligt jobb.
sp_add_jobstep	Lägger till ett steg i ett jobb.
sp_update_jobstep	Uppdateringar ett jobbsteg.
sp_delete_jobstep	Tar bort ett jobbsteg.
sp_start_job	Börjar köra ett jobb.
sp_stop_job	Stoppar en jobbkörning.
sp_add_target_group	Lägger till en målgrupp.
sp_delete_target_group	Tar bort en målgrupp.
sp_add_target_group_member	Lägger till en databas eller grupp med databaser i en målgrupp.
sp_delete_target_group_member	Tar bort en målgruppsmedlem från en målgrupp.
sp_purge_jobhistory	Tar bort historikposterna för ett jobb.

Jobbvyer

Följande vyer är tillgängliga i jobbdatabasen.

Visning	beskrivning
job_executions	Visar jobbkörningshistorik.
Jobb	Visar alla jobb.
job_versions	Visar alla jobbversioner.
jobsteps	Visar alla steg i den aktuella versionen av varje jobb.
jobstep_versions	Visar alla steg i alla versioner av varje jobb.
target_groups	Visar alla målgrupper.
target_group_members	Visar alla medlemmar i alla målgrupper.

Gå vidare

Skapa och hantera elastiska jobb med hjälp av PowerShell (förhandsversion)