Erstellen und Verwalten von elastischen Aufträgen mithilfe von T-SQL

Gilt für:: Azure SQL-Datenbank

Dieser Artikel enthält ein Tutorial und Beispiele für die ersten Schritte bei der Arbeit mit elastischen Aufträgen mithilfe von T-SQL. Mit elastischen Aufträgen können einzelne oder mehrere T-SQL-Skripts (Transact-SQL) für mehrere Datenbanken gleichzeitig ausgeführt werden.

In den in diesem Artikel genannten Beispielen werden die gespeicherten Prozeduren und Ansichten verwendet, die in der Auftragsdatenbank verfügbar sind.

In diesem End-to-End-Tutorial erfahren Sie, wie Sie eine datenbankübergreifende Abfrage ausführen:

Erstellen des Agents für elastische Aufträge

Transact-SQL (T-SQL) kann zum Erstellen, Konfigurieren, Ausführen und Verwalten von Aufträgen dienen.

Die Erstellung des Agents für elastische Aufträge wird nicht in T-SQL unterstützt. Daher müssen Sie mithilfe des Azure-Portals oder PowerShel zuerst einen Agent für elastische Aufträge Datenbank erstellen.

Erstellen der Auftragsauthentifizierung

Der Agent für elastische Aufträge muss in der Lage sein, sich bei jedem Zielserver oder jeder Zieldatenbank zu authentifizieren. Wie unter Authentifizierung des Auftrags-Agenten beschrieben, wird empfohlen, die Microsoft Entra-Authentifizierung (ehemals Azure Active Directory) mit einer benutzerseitig zugewiesenen verwalteten Identität (UMI) zu verwenden. Zuvor waren gezielte Anmeldeinformationen für die Datenbank die einzige Option.

Verwenden Sie für die Auftragsausführung die Microsoft Entra-Authentifizierung mit einer UMI

Um die empfohlene Methode der Authentifizierung mit Microsoft Entra (früher Azure Active Directory) für eine benutzerseitig zugewiesene verwaltete Identität (UMI) zu verwenden, führen Sie die folgenden Schritte aus. Der Agent für elastische Aufträge stellt über die Microsoft Entra-Authentifizierung eine Verbindung mit dem gewünschten logischen Zielserver/datenbanken(n) bereit.

Beachten Sie, dass zusätzlich zu den Anmelde- und Datenbankbenutzern die GRANT-Befehle im folgenden Skript hinzugefügt werden müssen. Diese Berechtigungen werden für das Skript benötigt, das wir für diesen Beispielauftrag ausgewählt haben. Für Ihre Aufträge sind möglicherweise unterschiedliche Berechtigungen erforderlich. Da im Beispiel eine neue Tabelle in den Zieldatenbanken erstellt wird, benötigt der Datenbankbenutzer in jeder Zieldatenbank die richtigen Berechtigungen, um das Beispiel erfolgreich auszuführen.

Erstellen Sie für den/die einzelnen Zielserver/-datenbank(en) einen enthaltenen Benutzer, der der UMI zugeordnet ist.

• Wenn der elastische Auftrag logische Server- oder Poolziele hat, müssen Sie den eigenständigen Benutzer, der der UMI zugeordnet ist, in der Datenbank master des logischen Zielservers erstellen.
• Zum Beispiel, um ein Login für eine eigenständige Datenbank in der master-Datenbank und einen Benutzer in der Benutzerdatenbank zu erstellen, basierend auf der benutzerseitig zugewiesenen verwalteten Identität (UMI) namens 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;

• So erstellen Sie einen Benutzer für eine eigenständige Datenbank, wenn keine Anmeldung auf dem logischen Server erforderlich ist:

--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;

Verwenden von Anmeldeinformationen mit Datenbankbereich für die Auftragsausführung

Die datenbankspezifischen Anmeldeinformationen werden verwendet, um eine Verbindung mit Ihren Zieldatenbanken für die Skriptausführung herzustellen. Für die Anmeldeinformationen müssen entsprechende Berechtigungen für die von der Zielgruppe angegebenen Datenbanken vorliegen, damit das Skript erfolgreich ausgeführt werden kann. Wenn Sie einen logischen SQL-Server bzw. ein Zielgruppenmitglied eines Pools einsetzen, sollten Sie auf jeden Fall Anmeldeinformationen erstellen, die zum Aktualisieren der Anmeldeinformationen verwendet werden, bevor Sie den Server oder den Pool bei der Auftragsausführung erweitern. Die datenbankweit gültigen Anmeldeinformationen werden für die Agent-Datenbank für Aufträge erstellt.

Es müssen die gleichen Anmeldeinformationen zum Erstellen eines Anmeldenamens und zum Erstellen eines Benutzers bei der Anmeldung für die Erteilung der Datenbankanmeldeberechtigungen für alle Zieldatenbanken verwendet werden.

--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

Erstellen Sie dann Logins auf den Zielservern oder eigenständige Datenbankbenutzer auf den Zieldatenbanken.

Erstellen Sie ein Login in der master-Datenbank des logischen SQL-Servers und Benutzer in jeder Benutzerdatenbank.

--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;

Erstellen Sie einen eigenständigen Datenbankbenutzer, wenn keine Anmeldung auf dem logischen Server erforderlich ist. Normalerweise würden Sie dies nur tun, wenn Sie eine einzige Datenbank mit diesem Agent für elastische Aufträge zu verwalten haben.

--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;

Definieren von Zielservern und Datenbanken

Im folgenden Beispiel wird gezeigt, wie ein Auftrag für alle Datenbanken auf einem Server ausgeführt wird.

Verbinden Sie sich mit dem job_database und führen Sie den folgenden Befehl aus, um eine Zielgruppe und ein Zielmitglied hinzuzufügen:

-- 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';

Ausschließen einer einzelnen Datenbank

Im folgenden Beispiel wird gezeigt, wie ein Auftrag für alle Datenbanken auf einem Server ausgeführt wird, ausgenommen für die Datenbank mit dem Namen MappingDB.

Wenn Sie die Microsoft Entra-Authentifizierung (ehemals Azure Active Directory) verwenden, lassen Sie den @refresh_credential_name-Parameter weg. Er sollte nur angegeben werden, wenn Sie datenbankgestützte Anmeldeinformationen verwenden. In den folgenden Beispielen ist der @refresh_credential_name-Parameter auskommentiert.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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';

Erstellen einer Zielgruppe (Pools)

Das folgende Beispiel zeigt, wie Zielgruppen für alle Datenbanken in einem oder in mehreren Pools für elastische Datenbanken erstellt werden.

Wenn Sie die Microsoft Entra-Authentifizierung (ehemals Azure Active Directory) verwenden, lassen Sie den @refresh_credential_name-Parameter weg. Er sollte nur angegeben werden, wenn Sie datenbankgestützte Anmeldeinformationen verwenden. In den folgenden Beispielen ist der @refresh_credential_name-Parameter auskommentiert.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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';

Erstellen eines Auftrags und von Schritten

Erstellen Sie mit T-SQL Aufträge mithilfe von im System gespeicherten Prozeduren in der Auftragsdatenbank: jobs.sp_add_job und jobs.sp_add_jobstep. Die Syntax der T-SQL-Befehle ähnelt den Schritten, die zur Erstellung von SQL Agent-Aufträgen und Auftragsschritten in SQL Server erforderlich sind.

Sie dürfen keine internen Katalogansichten in der Auftragsdatenbank aktualisieren. Das manuelle Ändern dieser Katalogansichten kann die Auftragsdatenbank beschädigen und zu einem Fehler führen. Diese Ansichten dienen nur für schreibgeschützte Abfragen. Sie können die gespeicherten Prozeduren im Schema jobs in Ihrer Auftragsdatenbank verwenden.

• Wenn Sie die Microsoft Entra-Authentifizierung für eine Microsoft Entra ID oder eine benutzerseitig zugewiesene verwaltete Identität verwenden, um sich bei Zielserver(n)/Datenbank(en) zu authentifizieren, sollte das Argument @credential_name nicht für sp_add_jobstep oder sp_update_jobstep angegeben werden. Ebenso lassen Sie die optionalen Argumente @output_credential_name und @refresh_credential_name weg.
• Bei der Verwendung von datenbankübergreifenden Anmeldeinformationen zur Authentifizierung bei Zielserver(n)/Datenbank(en) ist der Parameter @credential_name für sp_add_jobstep und sp_update_jobstep erforderlich.
  • Beispiel: @credential_name = 'job_credential'.

Die folgenden Beispiele bieten Anleitungen zum Erstellen von Auträgen und Auftragsschritten mit T-SQL, um gängige Aufgaben mit elastischen Aufträgen zu erledigen.

Beispiele

Bereitstellen eines neuen Schemas für eine Vielzahl von Datenbanken

Das folgende Beispiel zeigt, wie ein neues Schema für alle Datenbanken bereitgestellt wird.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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';

Datensammlung mithilfe von integrierten Parametern

In vielen Datensammlungsszenarien kann es sinnvoll sein, einige dieser Skriptvariablen einzuschließen, um die Ergebnisse des Auftrags nachzuverarbeiten.

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

Um beispielsweise alle Ergebnisse aus derselben Auftragsausführung zusammen zu gruppieren, verwenden Sie $(job_execution_id) wie im folgenden Befehl gezeigt wird:

@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());'

Überwachen der Datenbankleistung

Im folgenden Beispiel wird ein neuer Auftrag zum Sammeln von Leistungsdaten von mehreren Datenbanken erstellt.

Der Auftrags-Agent erstellt die Ausgabetabelle standardmäßig zum Speichern der zurückgegebenen Ergebnisse. Aus diesem Grund muss der Datenbankprinzipal, der mit den Anmeldeinformationen für die Ausgabe verknüpft ist, mindestens über die folgenden Berechtigungen verfügen: CREATE TABLE für die Datenbank, ALTER, SELECT, INSERT und DELETE für die Ausgabetabelle oder ihr Schema und SELECT für die sys.indexes-Katalogsicht.

Wenn Sie die Tabelle vorher manuell erstellen möchten, muss sie die folgenden Eigenschaften aufweisen:

1. Spalten mit den richtigen Namen und Datentypen für das Resultset.
2. Zusätzliche Spalte für internal_execution_id mit dem Datentyp uniqueidentifier.
3. Ein nicht gruppierter Index, der in der internal_execution_id-Spalte als IX_<TableName>_Internal_Execution_ID benannt wird.
4. Alle oben aufgeführten Berechtigungen mit Ausnahme der CREATE TABLE-Berechtigung für die Datenbank.

Stellen Sie eine Verbindung mit der Auftragsdatenbank her, und führen Sie die folgenden Befehle aus:

--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>';

Ausführung des Auftrags

Das folgende Beispiel zeigt, wie Sie einen Auftrag sofort als manuelle, ungeplante Aktion starten können.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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;

Planen der Auftragsausführung

Das folgende Beispiel zeigt, wie Sie eine zukünftige Ausführung eines Auftrags auf einer wiederkehrenden Basis alle 15 Minuten planen.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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;

Anzeigen von Auftragsdefinitionen

Das folgende Beispiel zeigt, wie aktuelle Auftragsdefinitionen angezeigt werden.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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;

Überwachen des Status einer Auftragsausführung

Das folgende Beispiel veranschaulicht, wie Ausführungsstatusdetails für alle Aufträge angezeigt werden.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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;

Abbrechen eines Auftrags

Das folgende Beispiel zeigt, wie Sie eine Auftragsausführungs-ID abrufen und dann die Ausführung eines Auftrags abbrechen.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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';

Löschen eines alten Auftragsverlaufs

Das folgende Beispiel zeigt, wie der Auftragsverlauf bis zu einem bestimmten Datum gelöscht wird.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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

Löschen eines Auftrags sowie des zugehörigen Auftragsverlaufs

Das folgende Beispiel zeigt, wie ein Auftrag und alle zugehörigen Auftragsverläufe gelöscht werden.

Stellen Sie eine Verbindung mit der job_database her, und führen Sie den folgenden Befehl aus:

--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

In der Auftragsdatenbank gespeicherte Prozeduren

Die folgenden gespeicherten Prozeduren sind in der Auftragsdatenbank enthalten. Sie sind ähnlich benannt, unterscheiden sich jedoch von den gespeicherten Systemprozeduren, die für den SQL Server-Agent-Dienst verwendet werden.

Auftragsansichten

Die folgenden Ansichten sind in der Auftragsdatenbank verfügbar.

Nächster Schritt