Automatizzare le attività di gestione usando i processi di SQL Agent in Istanza gestita di SQL di Azure

  • Articolo

Si applica a:Istanza gestita di SQL di Azure

Utilizzando SQL Server Agent in SQL Server e Istanza gestita di SQL è possibile creare e pianificare processi eseguibili periodicamente su uno o più database per eseguire query T-SQL (Transact-SQL) e attività di manutenzione. Questo articolo illustra l'uso di SQL Agent per Istanza gestita di SQL.

Nota

SQL Agente non è disponibile nel database SQL di Azure o in Azure Synapse Analytics. È invece consigliabile l'automazione dei processi con processi elastici.

Quando usare i processi di SQL Agent

I processi SQL Agente possono essere usati in diversi scenari:

  • Automatizzare le attività di gestione e quindi pianificare l'esecuzione in ogni giorno feriale, fuori orario lavorativo e così via.
    • Distribuire le modifiche dello schema, la gestione delle credenziali, la raccolta dei dati sulle prestazioni o la raccolta dei dati di telemetria del tenant (cliente).
    • Aggiornare i dati di riferimento (ossia le informazioni comuni tra tutti i database) e caricare dati dall'archivio BLOB di Azure. Microsoft consiglia di usare l'autenticazione SHARED ACCESS SIGNATURE per eseguire l'autenticazione nell'archiviazione BLOB di Azure.
    • Attività di manutenzione comuni, tra cui DBCC CHECKDB per garantire l'integrità dei dati o la manutenzione degli indici per migliorare le prestazioni delle query. Configurare i processi per l'esecuzione in una raccolta di database su base periodica, ad esempio durante le fasce orarie non di punta.
    • Raccogliere i risultati di query da un set di database in una tabella centrale su base costante. Le query di prestazione possono essere eseguite continuamente e configurate per l'esecuzione di attività aggiuntive di trigger.
  • Raccogliere i dati per i report
    • Aggregare i dati di una raccolta di database in una singola tabella di destinazione.
    • Eseguire query di elaborazione dei dati più lunghe per una vasta serie di database, ad esempio la raccolta della telemetria del cliente. I risultati vengono raccolti in una tabella di destinazione singola per ulteriori analisi.
  • Spostare dati
    • Creare processi che replicano le modifiche apportate ai database in altri database o raccolgono gli aggiornamenti effettuati in database remoti e applicano le modifiche nel database.
    • Creare processi che caricano dati da o verso i database usando SQL Server Integration Services (SSIS).

Processi SQL Agent in Istanza gestita di SQL

I processi di SQL Agent vengono eseguiti dal servizio SQL Agent che continua a essere usato per l'automazione delle attività in SQL Server e Istanza gestita di SQL.

I processi di SQL Agent sono una serie specificata di script T-SQL sul database. Usare i processi per definire un'attività amministrativa eseguibile una o più volte e monitorabile per verificarne l'esito positivo o negativo.

Un processo può essere eseguito in un server locale o in più server remoti. I processi di SQL Agent sono un componente interno del motore di database che viene eseguito nel servizio Istanza gestita di SQL.

Di seguito sono riportati i concetti chiave dei processi di SQL Agent:

  • I passaggi di processo sono set di uno o più passaggi da eseguire all'interno del processo. Per ogni passaggio di processo è possibile definire la strategia di ripetizione dei tentativi e l'azione da eseguire in caso di esito positivo o negativo del passaggio.
  • Le pianificazioni definiscono quando deve essere eseguito il processo.
  • Le notifiche consentono di definire le regole da usare per inviare notifiche agli operatori tramite posta elettronica al termine del processo.

Passaggi di processo

I passaggi di processo di SQL Agent sono sequenze di azioni che devono essere eseguite da SQL Agent. Ogni passaggio include il passaggio successivo da eseguire in caso di esito positivo o negativo e il numero di tentativi in caso di errore.

SQL Agent consente di creare tipi diversi di passaggi di processo, ad esempio passaggi di processi Transact-SQL per l'esecuzione di un singolo batch Transact-SQL sul database, passaggi i PowerShell o di comandi del sistema operativo per l'esecuzione di uno script personalizzato del sistema operativo, passaggi di processi SSIS che consentono di caricare dati con il runtime SSIS o passaggi di replica per la pubblicazione di modifiche da un database ad altri.

Nota

Per altre informazioni sull'uso di Azure SSIS Integration Runtime con SSISDB ospitato da Istanza gestita di SQL, vedere Usare Istanza gestita di SQL di Azure con SQL Server Integration Services (SSIS) in Azure Data Factory.

La replica transazionale può replicare le modifiche dalle tabelle in altri database in Istanza gestita di SQL, database SQL di Azure o SQL Server. Per informazioni, vedere Configurare la replica in un database di Istanza gestita di SQL di Azure.

Altri tipi di passaggi di processo non sono attualmente supportati in Istanza gestita di SQL, ad esempio la replica di tipo merge e il lettore di accodamento.

Pianificazioni dei processi

Una pianificazione specifica quando un processo viene eseguito. La stessa pianificazione può includere l'esecuzione di più processi e allo stesso processo possono essere applicate più pianificazioni.

Per quanto riguarda quando un processo deve essere eseguito, una pianificazione può definire le condizioni seguenti:

  • A ogni avvio di SQL Server Agent. Il processo viene attivato dopo ogni failover.
  • Una sola volta in una data e un'ora specifiche. Questa opzione è utile per l'esecuzione posticipata di alcuni processi.
  • In base a una pianificazione ricorrente.

Per altre informazioni sulla pianificazione di un processo di SQL Agent, vedere Pianificare un processo.

Nota

Attualmente Istanza gestita di SQL di Azure non consente di avviare un processo quando la CPU è "inattiva".

Notifiche dei processi

I processi di SQL Agent consentono di ricevere notifiche quando il processo viene completato o non riesce. È possibile ricevere le notifiche tramite posta elettronica.

Se non è già abilitata, è prima necessario configurare la funzionalità di Posta elettronica database in Istanza gestita di SQL:

GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE

Come esercizio di esempio, configurare l'account di posta elettronica che verrà usato per inviare le notifiche tramite posta elettronica. Assegnare l'account al profilo di posta elettronica denominato AzureManagedInstance_dbmail_profile. Per inviare messaggi di posta elettronica usando i processi di SQL Agent in Istanza gestita di SQL, deve essere presente un profilo che deve essere chiamato AzureManagedInstance_dbmail_profile. In caso contrario, Istanza gestita di SQL non sarà in grado di inviare messaggi di posta elettronica tramite SQL Agent.

Nota

Per il server di posta, è consigliabile usare i servizi di inoltro SMTP autenticati per inviare messaggi di posta elettronica. Questi servizi di inoltro si connettono in genere tramite le porte TCP 25 o 587 per le connessioni tramite TLS o la porta 465 per le connessioni SSL, tuttavia Posta elettronica database possono essere configurati per l'uso di qualsiasi porta. Queste porte richiedono una nuova regola in uscita nel gruppo di sicurezza di rete dell'istanza gestita. Questi servizi vengono usati per mantenere la reputazione dell'IP e del dominio per ridurre al minimo la possibilità che i domini esterni rifiutino i messaggi o li inviino alla cartella SPAM. Considerare un servizio di inoltro SMTP autenticato nei server locali. In Azure, SendGrid è un servizio di inoltro SMTP, ma ne esistono altri.

Usare lo script di esempio seguente per creare un account e un profilo Posta elettronica database, quindi associarli insieme:

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL Agent Account',
    @description = 'Mail account for Azure SQL Managed Instance SQL Agent system.',
    @email_address = '$(loginEmail)',
    @display_name = 'SQL Agent Account',
    @mailserver_name = '$(mailserver)' ,
    @username = '$(loginEmail)' ,
    @password = '$(password)';

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @description = 'E-mail profile used for messages sent by Managed Instance SQL Agent.';

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @account_name = 'SQL Agent Account',
    @sequence_number = 1;

Testare la configurazione Posta elettronica database tramite T-SQL usando la stored procedure di sistema sp_send_db_mail:

DECLARE @body VARCHAR(4000) = 'The email is sent from ' + @@SERVERNAME;
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'AzureManagedInstance_dbmail_profile',  
    @recipients = 'ADD YOUR EMAIL HERE',  
    @body = 'Add some text',  
    @subject = 'Azure SQL Instance - test email';

È possibile notificare all'operatore che si sono verificati determinati eventi nei processi di SQL Agent. Un operatore definisce le informazioni di contatto per la persona responsabile della manutenzione di una o più istanze in Istanza gestita di SQL. Le responsabilità di operatore vengono talvolta assegnate a una sola persona.

Nei sistemi con più istanze in Istanza gestita di SQL o in SQL Server, possono essere condivise da diverse persone. Un operatore non include informazioni di sicurezza e non definisce un'entità di sicurezza. Idealmente, un operatore non è un individuo le cui responsabilità possono cambiare, ma un gruppo di distribuzione di posta elettronica.

È possibile creare gli operatori usando SQL Server Management Studio (SSMS) o lo script Transact-SQL illustrato nell'esempio seguente:

EXEC msdb.dbo.sp_add_operator
    @name=N'AzureSQLTeam',
    @enabled=1,
    @email_address=N'AzureSQLTeamn@contoso.com';

Verificare l'esito positivo o negativo del messaggio di posta elettronica tramite il Posta elettronica database Log in SSMS.

È possibile modificare qualsiasi processo di SQL Agent e assegnare gli operatori che riceveranno una notifica tramite posta elettronica in caso di completamento, esito negativo o esito positivo del processo usando SSMS o lo script T-SQL seguente:

EXEC msdb.dbo.sp_update_job @job_name=N'Load data using SSIS',
    @notify_level_email=3, -- Options are: 1 on succeed, 2 on failure, 3 on complete
    @notify_email_operator_name=N'AzureSQLTeam';

Cronologia dei processi

Istanza gestita di SQL attualmente non consente di modificare le proprietà di SQL Agent perché sono archiviate nei valori del Registro di sistema sottostanti. Ciò significa che le opzioni per la modifica dei criteri di conservazione dell'agente per i record della cronologia dei processi vengono corrette al valore predefinito di 1000 record totali e al massimo 100 record di cronologia per processo.

Per ulteriori informazioni, vedere Visualizzare la cronologia processo di SQL Agent.

Adesione al ruolo predefinito del database

Se gli utenti collegati agli accessi non amministratore di sistema vengono aggiunti a uno dei ruoli predefiniti del database di SQL Agent nel database di sistema msdb, si verifica un problema per cui le autorizzazioni EXECUTE esplicite devono essere concesse alle tre stored procedure di sistema nel database master. Se si verifica questo problema, verrà visualizzato il messaggio di errore The EXECUTE permission was denied on the object <object_name> (Microsoft SQL Server, Error: 229).

Dopo aver aggiunto utenti a un ruolo predefinito del database di SQL Agente (SQLAgentUserRole, SQLAgentReaderRole o SQLAgentOperatorRole) in msdb, per ognuno degli accessi aggiunti a questi ruoli, eseguire lo script T-SQL seguente per concedere esplicitamente le autorizzazioni EXECUTE alle stored procedure di sistema elencate. In questo esempio si presuppone che il nome utente e il nome di accesso siano uguali:

USE [master]
GO
CREATE USER [login_name] FOR LOGIN [login_name];
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [login_name];
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [login_name];
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [login_name];

Limitazioni al processo di SQL Agent in Istanza gestita di SQL

Vale la pena notare le differenze tra SQL Agent disponibile in SQL Server e come parte di Istanza gestita di SQL. Per le differenze di sintassi e comportamento tra Istanza gestita di SQL e SQL Server, vedere Differenze T-SQL Istanza gestita di SQL di Azure rispetto a SQL Server.

Alcune delle funzionalità di SQL Agent disponibili in SQL Server non sono supportate in Istanza gestita di SQL:

  • Le impostazioni dell'agente SQL sono di sola lettura.
    • La stored procedure di sistema sp_set_agent_properties non è supportata.
  • L'abilitazione/disabilitazione di SQL Agent non è attualmente supportata. SQL Agent è sempre in esecuzione.
  • Anche se le notifiche sono parzialmente supportate, le opzioni seguenti non sono supportate:
    • Il cercapersone non è supportato.
    • NetSend non è supportato.
    • Gli avvisi non sono supportati.
  • I proxy non sono supportati.
  • EventLog non è supportato.
  • Il trigger di pianificazione del processo in base a una CPU inattiva non è supportato.
  • Il passaggio del processo di replica di tipo merge non è supportato.
  • La lettura coda non è supportata.
  • Analysis Services non è supportato.
  • L'esecuzione di uno script archiviato come file su disco non è supportata.
  • L'importazione di moduli esterni, ad esempio dbatools e dbachecks, non è supportata.
  • PowerShell Core non è supportato.

Altre informazioni

Passaggi successivi