Creación y administración de trabajos elásticos mediante T-SQL

Se aplica a: Azure SQL Database

En este artículo se proporciona un tutorial y ejemplos para comenzar a trabajar con trabajos elásticos mediante T-SQL. Los trabajos elásticos habilitan la ejecución de uno o más scripts de Transact-SQL (T-SQL) en paralelo en varias bases de datos.

En los ejemplos de este artículo se utilizan los procedimientos almacenados y las vistas disponibles en la base de datos de trabajo.

En este tutorial completo, aprenderá los pasos necesarios para ejecutar una consulta en múltiples bases de datos:

Creación del agente de trabajos elásticos

Transact-SQL (T-SQL) se puede utilizar para crear, configurar, ejecutar y administrar los trabajos.

La creación del agente de trabajos elásticos no se admite en T-SQL, por lo que debe crear primero un agente de trabajos elásticos mediante el portal de Azure o crear un agente de trabajos elásticos mediante PowerShell.

Creación de la autenticación del trabajo

El agente de trabajos elásticos debe poder autenticarse en cada servidor o base de datos de destino. Como se describe en Creación de la autenticación del agente de trabajo, el enfoque recomendado es usar la autenticación de Microsoft Entra (anteriormente Azure Active Directory) con una identidad administrada asignada por el usuario (UMI). Anteriormente, las credenciales de ámbito de base de datos eran la única opción.

Uso de la autenticación de Microsoft Entra con una UMI para la ejecución del trabajo

Para usar el método recomendado de autenticación de Microsoft Entra (anteriormente Azure Active Directory) en una identidad administrada asignada por el usuario (UMI), siga estos pasos. El agente de trabajo elástico se conecta a los servidores lógicos o bases de datos objetivo deseados a través de la autenticación de Microsoft Entra.

Además del inicio de sesión y los usuarios de la base de datos, tenga en cuenta la adición de los comandos GRANT en el siguiente script. Estos permisos son necesarios para el script que hemos elegido para este trabajo de ejemplo. Los trabajos pueden requerir permisos diferentes. Dado que en el ejemplo se crea una nueva tabla en las bases de datos de destino, el usuario de cada una de estas bases de datos necesita los permisos adecuados para ejecutarse correctamente.

En cada uno de los servidores o bases de datos de destino, cree un usuario independiente asignado a la UMI.

• Si el trabajo elástico tiene destinos de grupo o servidor lógico, debe crear el usuario independiente asignado a la UMI en la base de datos master del servidor lógico objetivo.
• Por ejemplo, para crear un inicio de sesión de base de datos independiente en la base de datos master y un usuario de la base de datos de usuario, en función de la identidad administrada asignada por el usuario (UMI) denominada 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;

• Para crear un usuario de base de datos independiente si no se necesita un inicio de sesión en el servidor lógico:

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

Uso de una credencial con ámbito de base de datos para la ejecución del trabajo

Se usa una credencial de ámbito de base de datos para conectarse a las bases de datos objetivo para la ejecución del script. La credencial necesita permisos adecuados en las bases de datos especificadas por el grupo de destino para ejecutar correctamente el script. Cuando se utiliza un servidor SQL lógico o un miembro del grupo de destino del grupo, se recomienda crear una credencial para actualizar la credencial antes de la expansión del grupo o del servidor en el momento de ejecución del trabajo. La credencial de ámbito de base de datos se crea en la base de datos del agente de trabajo.

La misma credencial debe usarse para crear un inicio de sesión y crear un usuario desde el inicio de sesión para conceder los permisos de base de datos de inicio de sesión en todas las bases de datos objetivo.

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

A continuación, cree inicios de sesión en los servidores de destino o usuarios de bases de datos independientes en las bases de datos objetivo.

Cree un inicio de sesión en la base de datos master del servidor SQL lógico y los usuarios de cada base de datos de usuario.

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

Cree un usuario de base de datos independiente si no se necesita un inicio de sesión en el servidor lógico. Normalmente, solo lo haría si tiene una base de datos única para administrar con este agente de trabajo elástico.

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

Definición de servidores y bases de datos de destino

En el ejemplo siguiente se muestra cómo ejecutar un trabajo en todas las bases de datos de un servidor.

Conectar a job_database y ejecute el siguiente comando para agregar un grupo de destino y un miembro objetivo:

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

Exclusión de una base de datos individual

En el ejemplo siguiente se muestra cómo ejecutar un trabajo en todas las bases de datos de un servidor, excepto en la denominada MappingDB.

Al usar la autenticación de Microsoft Entra (anteriormente Azure Active Directory), omita el parámetro @refresh_credential_name, que solo se debe proporcionar al usar credenciales de ámbito de base de datos. En los ejemplos siguientes, se comenta el parámetro @refresh_credential_name.

Conéctese a la job_database y ejecute el siguiente comando:

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

Creación de un grupo de destino (grupos)

En el ejemplo siguiente se muestra cómo establecer como destino todas las bases de datos de uno o varios grupos elásticos.

Al usar la autenticación de Microsoft Entra (anteriormente Azure Active Directory), omita el parámetro @refresh_credential_name, que solo se debe proporcionar al usar credenciales de ámbito de base de datos. En los ejemplos siguientes, se comenta el parámetro @refresh_credential_name.

Conéctese a la job_database y ejecute el siguiente comando:

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

Creación de un trabajo y pasos

Con T-SQL, cree trabajos mediante procedimientos almacenados del sistema en la base de datos de trabajos: jobs.sp_add_job y jobs.sp_add_jobstep. Los comandos T-SQL son sintaxis similares a los pasos necesarios para crear trabajos y pasos de trabajo del Agente SQL en SQL Server.

No debe actualizar las vistas de catálogo internas en la base de datos de trabajos. Cambiar manualmente estas vistas de catálogo puede dañar la base de datos de trabajo y provocar un error. Estas vistas son solo para consultas de solo lectura. Puede usar los procedimientos almacenados en el esquema jobs de la base de datos de trabajo.

• Al usar la autenticación de Microsoft Entra para un identificador de Microsoft Entra o una identidad administrada asignada por el usuario para autenticarse en servidores o bases de datos objetivo, no se debe proporcionar el argumento @credential_name para sp_add_jobstep o sp_update_jobstep. De forma similar, omita los argumentos opcionales @output_credential_name y @refresh_credential_name.
• Cuando se usan credenciales de ámbito de base de datos para autenticarse en servidores de destino o bases de datos, se requiere el parámetro @credential_name para sp_add_jobstep y sp_update_jobstep.
  • Por ejemplo, @credential_name = 'job_credential'.

En los ejemplos siguientes se proporcionan guías para crear pasos de trabajo y trabajos mediante T-SQL para realizar tareas comunes con trabajos elásticos.

Ejemplos

Implementación del nuevo esquema en muchas bases de datos

En el ejemplo siguiente se muestra cómo implementar el nuevo esquema en todas las bases de datos.

Conéctese a la job_database y ejecute el siguiente comando:

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

Recopilación de datos mediante los parámetros integrados

En muchos escenarios de recopilación de datos puede ser útil incluir algunas de estas variables de scripting para ayudar al posprocesamiento de los resultados del trabajo.

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

Por ejemplo, para agrupar todos los resultados de la misma ejecución del trabajo, use $(job_execution_id) tal como se muestra en el siguiente comando:

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

Supervisión del rendimiento de una base de datos

En el ejemplo siguiente se crea un nuevo trabajo para recopilar datos de rendimiento de varias bases de datos.

De forma predeterminada, el agente de trabajo crea la tabla de salida para almacenar los resultados devueltos. Por lo tanto, la entidad de seguridad de base de datos asociada con la credencial de salida debe tener, como mínimo, los siguientes permisos: CREATE TABLE en la base de datos, ALTER, SELECT, INSERT y DELETE en la tabla de salida o su esquema y SELECT en la vista de catálogo sys.indexes.

Si desea crear manualmente la tabla antes de tiempo, debe tener las siguientes propiedades:

1. Columnas con el nombre y los tipos de datos correctos para el conjunto de resultados.
2. Columna adicional para internal_execution_id con el tipo de datos de uniqueidentifier.
3. Índice no agrupado denominado IX_<TableName>_Internal_Execution_ID en la columna internal_execution_id.
4. Todos los permisos enumerados anteriormente, salvo el permiso CREATE TABLE en la base de datos.

Conéctese a la base de datos de trabajo y ejecute los siguientes comandos:

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

Ejecutar el trabajo

En el ejemplo siguiente se muestra cómo iniciar un trabajo inmediatamente como una acción manual y no planificada.

Conéctese a la job_database y ejecute el siguiente comando:

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

Programación de la ejecución de un trabajo

El ejemplo siguiente muestra cómo programar un trabajo para una ejecución futura de manera recurrente cada 15 minutos.

Conéctese a la job_database y ejecute el siguiente comando:

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

Visualización de definiciones de trabajos

En el ejemplo siguiente se muestra cómo ver las definiciones de trabajos actuales.

Conéctese a la job_database y ejecute el siguiente comando:

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

Supervisión del estado de ejecución de trabajos

En el ejemplo siguiente se muestra cómo ver los detalles del estado de ejecución de todos los trabajos.

Conéctese a la job_database y ejecute el siguiente comando:

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

Cancelación de un trabajo

En el ejemplo siguiente se muestra cómo recuperar un identificador de ejecución de trabajo y, a continuación, cancelar una ejecución de trabajo.

Conéctese a la job_database y ejecute el siguiente comando:

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

Eliminación del historial de trabajos antiguos

En el ejemplo siguiente se muestra cómo eliminar el historial de trabajos anteriores a una fecha concreta.

Conéctese a la job_database y ejecute el siguiente comando:

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

Eliminación de un trabajo y de todo el historial de trabajos

En el ejemplo siguiente se muestra cómo eliminar un trabajo y el historial de todos los trabajos relacionados.

Conéctese a la job_database y ejecute el siguiente comando:

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

Procedimientos almacenados de trabajos

Los siguientes procedimientos almacenados se encuentran en la base de datos de trabajos. Se denominan de forma similar, pero son diferentes de los procedimientos almacenados del sistema que se usan para el servicio Agente SQL Server.

Vistas de trabajos

Las siguientes vistas están disponibles en la base de datos de trabajos.

Paso siguiente