在 Azure SQL 受控執行個體中使用 SQL Agent 作業的自動化管理工作

發行項
05/04/2023

使用 SQL Server 與 SQL 受控執行個體中的 SQL Server Agent，您可以建立及排程可針對一或多個資料庫定期執行的作業，以便執行 Transact-SQL (T-SQL) 查詢及執行維護工作。本文說明如何使用 SQL 受控執行個體的 SQL Agent。

注意

Azure SQL Database 或 Azure Synapse Analytics 不提供 SQL Agent。相反地，我們建議使用彈性作業進行工作自動化。

使用 SQL Agent 作業的時機

您可以使用 SQL Agent 作業的案例有數個：

將管理工作自動化，並將這些工作排定為在每個工作天、下班時間等時段執行。
- 部署結構描述變更、認證管理、效能資料收集或租用戶 (客戶) 遙測收集。
- 更新參考資料 (所有資料庫通用的資訊)，並從 Azure Blob 儲存體載入資料。 Microsoft 建議使用共用存取簽章驗證來驗證 Azure Blob 儲存體。
- 一般維護工作，包括透過 DBCC CHECKDB 確保資料完整性或索引維護，以改善查詢效能。將作業設定為以週期性基礎跨資料庫的集合執行，例如在離峰時段。
- 以持續執行的基礎從一組資料庫將查詢結果收集至中央資料表。效能查詢可以持續執行，並設定為觸發要執行的其他作業。
收集資料以供報告
- 將資料庫集合中的資料彙總到單一目的地資料表中。
- 跨大型資料庫集合執行較長的執行資料處理查詢，例如客戶遙測的集合。結果會收集到單一目的地資料表做進一步的分析。
資料移動
- 建立一些作業，以將在您資料庫中所做的變更複寫到其他資料庫，或收集在遠端資料庫中進行的更新，並在資料庫中套用變更。
- 建立一些作業，以使用 SQL Server Integration Services (SSIS) 在您的資料庫中載入資料。

SQL 受控執行個體中的 SQL Agent 作業

SQL Agent 作業是由 SQL Agent 服務執行，且會持續用於 SQL Server 和 SQL 受控執行個體中的工作自動化。

SQL Agent 作業是針對您的資料庫而指定的 T-SQL 指令碼系列。使用作業來定義可執行一或多次並監視成功或失敗的系統管理工作。

一個作業可以在一部本機伺服器或在多部遠端伺服器上執行。 SQL Agent 作業是在 SQL受控執行個體服務中執行的內部資料庫引擎元件。

SQL Agent 作業中有數個重要概念：

作業步驟可設定應在作業內執行的一或多個步驟。針對每個作業步驟，您可以定義重試策略，以及在作業步驟成功或失敗時應發生的動作。
排程可定義應執行作業的時間。
通知可讓您定義一些規則，這些規則將在作業完成後用於透過電子郵件通知操作員。

作業步驟

SQL Agent 作業步驟是 SQL Agent 應該執行的動作序列。每個步驟都有在步驟成功或失敗時所應執行的後續步驟，以及在失敗時的重試次數。

SQL Agent 可讓您建立不同類型的作業步驟；例如，針對資料庫執行單一 Transact-SQL 批次的 Transact-SQL 作業步驟、可執行自訂 OS 指令碼的 OS 命令/PowerShell 步驟、可讓您使用 SSIS 執行階段載入資料的 SSIS 作業步驟，或是可將您資料庫中的變更發佈至其他資料庫的複寫步驟。

注意

如需運用 Azure SSIS Integration Runtime 搭配 SQL 受控執行個體所裝載 SSISDB 相關的詳細資訊，請參閱使用 Azure SQL 受控執行個體搭配 Azure Data Factory 中的 SQL Server Integration Services (SSIS)。

異動複寫可以將您資料表中的變更複寫到 SQL 受控執行個體、Azure SQL Database 或 SQL Server 中的其他資料庫。如需詳細資訊，請參閱在 Azure SQL 受控執行個體中設定複寫。

SQL 受控執行個體目前不支援其他類型的作業步驟，例如合併式複寫和佇列讀取器。

作業排程

排程可指定執行作業的時間。您可以依照相同的排程執行一項以上的作業，而且可以將一個以上的排程套用至相同的作業。

排程可以針對作業的執行時間定義下列條件：

SQL Server Agent 啟動時。作業會在每次容錯移轉後啟動。
在特定日期和時間執行一次，這適合用於某項作業延遲執行時。
依照週期性排程。

如需排程 SQL Agent 作業的詳細資訊，請參閱排程作業。

注意

Azure SQL 受控執行個體目前無法讓您在 CPU 閒置時啟動作業。

作業通知

SQL Agent 作業可讓您在作業順利完成或失敗時取得通知。您可以透過電子郵件接收通知。

如果尚未啟用，則您必須先在 SQL 受控執行個體上設定 Database Mail 功能：

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

在範例練習中，設定將用來傳送電子郵件通知的電子郵件帳戶。將帳戶指派給稱為 AzureManagedInstance_dbmail_profile 的電子郵件設定檔。若要在 SQL 受控執行個體中使用 SQL Agent 作業傳送電子郵件，則應有必須呼叫的設定檔 AzureManagedInstance_dbmail_profile。否則，SQL 受控執行個體將無法透過 SQL Agent 傳送電子郵件。

注意

針對郵件伺服器，我們建議您使用驗證 SMTP 轉送服務來傳送電子郵件。這些轉送服務通常會透過 TCP 通訊埠 25 或 587 進行 TLS 連線，或是透過通訊埠 465 進行 SSL 連線，但 Database Mail 可設定使用任何通訊埠。這些通訊埠在您的受控執行個體網路安全性群組中，需要新的輸出規則。這些服務是用來維護 IP 和網域信譽，以將外部網域拒絕您的郵件或將其放到垃圾郵件資料夾的可能性降至最低。請考慮已在內部部署伺服器中驗證的 SMTP 轉送服務。在 Azure 中，SendGrid 是此類 SMTP 轉送服務的其中一種，但還有其他這類服務。

使用下列範例指令碼來建立 Database Mail 帳戶和設定檔，並將其進行關聯：

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

使用 sp_send_db_mail 系統預存程序，透過 T-SQL 測試 Database 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';

您可以通知操作員您的 SQL Agent 作業有狀況發生。操作員會為負責維護 SQL 受控執行個體中一或多個執行個體的個人，定義連絡人資訊。操作員責任有時會指派給某一個人。

在具有 SQL 受控執行個體或 SQL Server 中多個執行個體的系統中，可能會有多個人分擔操作員責任。操作員不包含安全性資訊，並不會定義安全性主體。在理想情況下，操作員不是責任可能變更的個人，而是電子郵件通訊群組。

您可以使用 SQL Server Management Studio (SSMS) 或 Transact-SQL 指令碼來建立操作員，如下列範例所示：

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

透過 SSMS 中的 Database Mail 記錄確認電子郵件成功或失敗。

接著，您可以使用 SSMS 或下列 T-SQL 指令碼來修改任何 SQL Agent 作業並指派操作員，該操作員會在作業完成、失敗或成功時透過電子郵件收到通知：

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

作業歷程記錄

SQL 受控執行個體目前不允許您變更任何 SQL Agent 屬性，因為這些屬性儲存在基礎登錄值中。這表示調整作業記錄的代理程式保留原則選項，會固定為預設1,000 筆記錄總計，以及每項作業的最大 100 個歷程記錄。

如需詳細資訊，請參閱檢視 SQL Agent 作業記錄。

個定資料庫角色成員資格

如果連結至非系統管理員登入的使用者新增至 msdb 系統資料庫中三個 SQL Agent 固定資料庫角色的其中之一，則存在一個問題，就是必須將明確的 EXECUTE 權限授與 master 資料庫中的三個系統預存程序。如果遇到此問題，會顯示以下錯誤訊息：The EXECUTE permission was denied on the object <object_name> (Microsoft SQL Server, Error: 229)。

您將使用者新增至 msdb 中的 SQL Agent 固定資料庫角色 (SQL SQLAgentUserRole、SQLAgentReaderRole 或 SQLAgentOperatorRole) 後，針對新增至這些角色的每個使用者，會執行下列 T-SQL 指令碼，以明確地將 EXECUTE 權限授與所列的系統預存程序。此範例假設使用者名稱和登入名稱相同：

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

SQL 受控執行個體中的 SQL Agent 作業限制

值得注意的是，SQL Server 與一部分 SQL 受控執行個體之間 SQL Agent 可用情況的差異。如需 SQL Server 與 SQL 受控執行個體之間所支援功能差異的詳細資訊，請參閱 SQL Server 中 Azure SQL 受控執行個體 T-SQL 的差異。

SQL 受控執行個體不支援有些適用於 SQL Server 的 SQL Agent 功能：

SQL 代理程式設定是唯讀狀態。
- 不支援系統預存程序 sp_set_agent_properties。
目前不支援啟用/停用 SQL Agent。 SQL 代理程式一律會處於正在執行的狀態。
雖然支援一部分通知，但不支援下列項目：
- 不支援呼叫器。
- 不支援 NetSend。
- 不支援警示。
不支援 Proxy。
不支援 Eventlog。
不支援以閒置 CPU 為基礎的工作排程觸發程序。
不支援合併複寫作業步驟。
不支援佇列讀取器。
不支援 Analysis Services。
不支援執行儲存為磁片上檔案的指令碼。
不支援匯入外部模組，例如 dbatools 和 dbachecks。
不支援 PowerShell Core。