Creare e gestire processi elastici usando T-SQL

Articolo
04/03/2024

Questo articolo fornisce un’esercitazione e alcuni esempi per iniziare a usare i processi elastici tramite T-SQL. I processi elastici permettono l'esecuzione di uno o più script di Transact-SQL (T-SQL) in parallelo tra molti database.

Gli esempi illustrati in questo articolo impiegano le stored procedure e le viste disponibili nel database dei processi.

Questa esercitazione end-to-end illustra tutte le fasi necessarie per l'esecuzione di una query tra più database:

Creare un agente di processi elastici
Creare le credenziali di processo in modo che i processi possano eseguire script nelle relative destinazioni
Definire le destinazioni (server, pool elastici, database) in cui eseguire i processi
Creare credenziali con ambito database all'interno dei database di destinazione in modo che l'agente possa connettersi ed eseguire i processi
Creare un processo
Aggiungere passaggi del processo a un processo
Avviare l'esecuzione di un processo
Monitorare un processo

Creare l'agente dei processi elastici

Transact-SQL (T-SQL) può essere usato per creare, configurare, eseguire e gestire i processi.

La creazione dell'agente di processo elastico non è supportata in T-SQL, pertanto è innanzitutto necessario creare un agente di processo elastico usando il portale di Azure ocreare un agente di processo elastico usando PowerShell.

Creare l'autenticazione del processo

L'operatore di processi elastici deve essere in grado di autenticarsi in ogni server o database di destinazione. Come descritto nel paragrafo Creare l'autenticazione dell'agente di processo, si consiglia di usare l'autenticazione di Microsoft Entra (in precedenza Azure Active Directory) con un'identità gestita assegnata dall'utente (UMI). In precedenza, l'unica opzione eranole credenziali con ambito database.

Usare l'autenticazione di Microsoft Entra con un'identità gestita assegnata dall'utente per l'esecuzione del processo

Per usare il metodo consigliato per l'autenticazione di Microsoft Entra (in precedenza Azure Active Directory) con un'identità gestita assegnata dall'utente, procedere come segue. L'agente di processo elastico si connette al/ai server logico/database di destinazione desiderati attraverso l'autenticazione Microsoft Entra.

Oltre all’accesso e agli utenti database, prendere nota dei GRANT comandi aggiuntivi nello script seguente. Queste autorizzazioni sono necessarie per lo script che è stato scelto per il processo di esempio. I processi potrebbero richiedere autorizzazioni diverse. Poiché nell'esempio viene creata una nuova tabella nei database di destinazione, l’utente database di ogni database di destinazione necessita di autorizzazioni appropriate per la corretta esecuzione.

In ciascuno del/dei server/database di destinazione, creare un utente indipendente mappato dall'identità gestita assegnata dall'utente.

Se il processo elastico ha come destinazioni server o pool logici, è necessario creare l'utente indipendente mappato dall'identità gestita assegnata dall'utente nelmaster database del server logico di destinazione.
Ad esempio, per creare un account di accesso master al database indipendente nel database e un utente nel database utente, in base all'identità gestita assegnata dall'utente denominata 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;

Per creare un utente database indipendente nel caso in cui non sia necessario un account di accesso nel server logico:

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

Usare credenziali con ambito database per l'esecuzione del processo

Le credenziali con ambito database vengono usate per connettersi ai database di destinazione per l'esecuzione dello script. Per la corretta esecuzione dello script, le credenziali devono disporre delle autorizzazioni appropriate per i database specificati dal gruppo di destinazione. Quando si usa un SQL Server logico e/o un membro del gruppo di destinazione del pool, si raccomanda di creare credenziali da usare per aggiornare le credenziali prima dell'espansione del server e/o del pool al momento dell'esecuzione del processo. Le credenziali con ambito database vengono create nel database dell'agente di processo.

È necessario usare le stesse credenziali per creare un account di accesso e creare un utente dall'account di accesso per concedere le autorizzazioni per il database dell'account di accesso in tutti i database di destinazione.

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

Creare quindi account di accesso nei server di destinazione o utenti database indipendenti nei database di destinazione.

Importante

L'account di accesso/utente in ogni server/database di destinazione deve essere denominato allo stesso modo dell'identità delle credenziali nell'ambito del database per l'utente del processo, nonché possedere la stessa password delle credenziali nell'ambito del database per l'utente del processo.

Creare un account di accesso nel master database del server SQL logico e gli utenti in ogni database utente.

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

Creare un utente database indipendente nei casi in cui non è necessario un account di accesso al server logico. Questa operazione si esegue generalmente solo se si dispone di un singolo database da gestire con un agente di processo elastico.

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

Definire server e database di destinazione

L'esempio seguente mostra come eseguire un processo in tutti i database in un server.

Connettersi al database job_database ed eseguire il comando seguente per aggiungere un gruppo di destinazione e un membro di destinazione:

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

Escludere un singolo database

L'esempio seguente mostra come eseguire un processo in tutti i database in un server, ad eccezione del database denominatoMappingDB.

Quando si usa l'autenticazione di Microsoft Entra (in precedenza Azure Active Directory), omettere il @refresh_credential_name parametro , che dovrà essere fornito solo se si usano credenziali con ambito database. Negli esempi seguenti il @refresh_credential_name parametro è impostato come commento.

Connettersi al job_database database ed eseguire il comando seguente:

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

Creare un gruppo di destinazione (pool)

L'esempio seguente mostra come specificare come destinazione tutti i database in uno o più pool elastici.

Connettersi al job_database database ed eseguire il comando seguente:

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

Creare un processo e i passaggi

Con T-SQL, creare processi usando stored procedure di sistema nel database dei processi: jobs.sp_add_job e jobs.sp_add_jobstep. I comandi T-SQL sono sintassi simili ai passaggi necessari per creare processi e passaggi di processo di SQL Agent in SQL Server.

Si consiglia di non aggiornare le viste del catalogo interne nel database del processo. La modifica manuale delle viste del catalogo può danneggiare il database dei processi e provocare un errore. Queste viste sono destinate solo all'esecuzione di query di sola lettura. È possibile usare le stored procedure nello jobs schema nel database del processo.

Quando si usa l'autenticazione Microsoft Entra per un ID Microsoft Entra o un'identità gestita assegnata dall'utente per l'autenticazione nel/i server/database di destinazione, l'argomento @credential_name non deve essere fornito per sp_add_jobstep o sp_update_jobstep. Analogamente, omettere gli argomenti facoltativi @output_credential_name e @refresh_credential_name.
Quando si usano credenziali con ambito database per eseguire l'autenticazione nel/i server/database di destinazione, è necessario il parametro @credential_name per sp_add_jobstep e sp_update_jobstep.
- Ad esempio: @credential_name = 'job_credential'.

Gli esempi seguenti forniscono una guida su come creare processi e passaggi di processo usando T-SQL, per eseguire attività comuni con processi elastici.

Esempi

Distribuire un nuovo schema a più database

L'esempio seguente mostra come distribuire un nuovo schema a tutti i database.

Connettersi al job_database database ed eseguire il comando seguente:

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

Raccolta dati con i parametri predefiniti

In molti scenari di raccolta dati può essere utile includere alcune di queste variabili di scripting per facilitare la post-elaborazione dei risultati del processo.

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

Ad esempio, per raggruppare tutti i risultati relativi alla stessa esecuzione del processo, usare $(job_execution_id) come illustrato nel comando seguente:

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

Nota

Tutti gli orari nei processi elastici sono espressi nel fuso orario UTC.

Monitorare le prestazioni del database

Nell'esempio seguente viene creato un nuovo processo per raccogliere i dati sulle prestazioni da più database.

Per impostazione predefinita l'agente processo creerà la tabella di output in cui archiviare i risultati. Pertanto, l'entità di sicurezza del database associata alle credenziali di output deve avere almeno le autorizzazioni seguenti: CREATE TABLE nel database, ALTER, SELECT, INSERTDELETE nella tabella di output o nel relativo schema e SELECT nella vista del catalogo sys. indexes.

Se si vuole creare manualmente la tabella in anticipo, è necessario che disponga delle proprietà seguenti:

Colonne con il nome e tipi di dati corretti per il set di risultati.
Colonna aggiuntiva per internal_execution_id con il tipo di dati uniqueidentifier.
Indice non cluster denominato IX_<TableName>_Internal_Execution_ID sulla internal_execution_id colonna.
Tutte le autorizzazioni elencate in precedenza, ad eccezione CREATE TABLE dell’autorizzazione nel database.

Connettersi al database dei processi ed eseguire i comandi seguenti:

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

Eseguire il processo

L'esempio seguente mostra come avviare immediatamente un processo come azione manuale e non pianificata.