Azure SQL Managed Instance で SQL Agent ジョブを使用して管理タスクを自動化する

[アーティクル]
04/26/2023

SQL Server および SQL Managed Instance で SQL Server エージェントを使用すると、1 つまたは多数のデータベースに対して定期的に実行されるジョブを作成してスケジュールし、Transact-SQL (T-SQL) クエリを実行してメンテナンスタスクを行うことができます。この記事では、SQL Managed Instance 用の SQL Agent の使用について説明します。

注意

SQL Agent は、Azure SQL Database または Azure Synapse Analytics では使用できません。代わりに、エラスティックジョブを使用したジョブの自動化をお勧めします。

どのようなときに SQL Agent ジョブを使用するか

SQL Agent ジョブを使用できるシナリオをいくつか次に示します。

管理タスクの自動化とスケジュール (平日に毎日、業務時間後に実行するなど)
- スキーマの変更、資格情報の管理、パフォーマンスデータの収集、テナント (顧客) テレメトリの収集をデプロイします。
- 参照データ (全データベースに共通の情報) を更新したり、Azure Blob Storage からデータを読み込んだりします。 Microsoft では、Azure BLOB ストレージに対する認証に SHARED ACCESS SIGNATURE 認証を使用することをお勧めします。
- データの整合性を確保するための DBCC CHECKDB などの一般的なメンテナンスタスクや、クエリのパフォーマンスを向上させるためのインデックスのメンテナンス。データベースコレクションを対象として (ピーク外の時間などに) 定期的に実行するジョブを構成します。
- データベースセットのクエリ結果をリアルタイムで中央のテーブルに収集します。パフォーマンスクエリを継続的に実行して、その他のタスクの実行をトリガーするように構成できます。
レポート用データの収集
- データベースのコレクションから 1 つのテーブルにデータを集約します。
- 顧客の製品利用統計情報収集など、大規模なデータセット全体に対して、時間がかかるデータ処理クエリを実行します。結果は 1 つの対象テーブルに収集され、分析に使用されます。
データの移動
- お客様のデータベースで行われた変更を他のデータベースにレプリケートしたり、リモートデータベースに加えられた更新を収集してデータベースに変更を適用したりするジョブを作成します。
- SQL Server Integration Services (SSIS) を使用してお客様のデータベースとの間で双方向にデータを読み込むジョブを作成します。

SQL Managed Instance 内での SQL Agent ジョブ

SQL Agent ジョブは、SQL Server と SQL Managed Instance でタスクの自動化に引き続き使用される SQL エージェントサービスによって実行されます。

SQL エージェントジョブは、お客様のデータベースに対して指定される一連の T-SQL スクリプトです。ジョブを使用し、1 回または複数回実行してその成否を監視できる管理タスクを定義します。

ジョブは、1 つのローカルサーバーで実行することも、複数のリモートサーバーで実行することもできます。 SQL Agent ジョブは、SQL Managed Instance サービス内で実行されるデータベースエンジンの内部コンポーネントです。

SQL エージェントジョブには、いくつかの重要な概念があります。

ジョブステップ: ジョブ内で実行されるべき 1 つまたは複数のステップのセットです。ジョブステップが成功または失敗した場合に実行されるアクションと再試行方法をジョブステップごとに定義することができます。
スケジュール: ジョブを実行すべきタイミングを定義します。
通知: ジョブが完了したときにメール経由でオペレーターに通知するために使用されるルールを定義できます。

ジョブステップ

SQL エージェントのジョブステップは、SQL エージェントが実行する必要のある一連のアクションです。それぞれのステップには、それが成功または失敗した場合に実行される後続のステップ (失敗した場合には再試行回数) があります。

SQL Agent を使用すると、さまざまな種類のジョブステップを作成できます。たとえば、データベースに対して単一の Transact-SQL バッチを実行する Transact-SQL ジョブステップ、カスタム OS スクリプトを実行できる OS コマンドまたは PowerShell のステップ、SSIS ランタイムを使用してデータを読み込むことができる SSIS ジョブステップ、お客様のデータベースから他のデータベースに変更を発行できるレプリケーションステップなどがあります。

注意

SQL Managed Instance によってホストされている SSISDB を使用して Azure SSIS Integration Runtime を利用する方法の詳細については、「Azure Data Factory 内で SQL Server Integration Services (SSIS) と共に Azure SQL Managed Instance を使用する」を参照してください。

トランザクションレプリケーションを使用すると、テーブルの変更を、SQL Managed Instance、Azure SQL Database、または SQL Server の他のデータベースにレプリケートできます。詳しくは、Azure SQL Managed Instance にレプリケーションを構成する方法に関する記事をご覧ください。

現在、マージレプリケーションやキューリーダーなど、SQL Managed Instance では、その他の種類のジョブステップはサポートされていません。

ジョブスケジュール

スケジュールでは、ジョブを実行する時期を指定します。同じスケジュールで複数のジョブを実行できるほか、同じジョブに複数のスケジュールを適用することもできます。

ジョブが実行されるタイミングに関して、次の条件をスケジュールで定義できます。

SQL Server エージェントが開始されるたび。ジョブは、フェールオーバー後に毎回アクティブ化されます。
特定の日時に 1 回。これは、なんらかのジョブを遅延実行する際に便利です。
定期的なスケジュール。

SQL Agent ジョブのスケジュールについて詳しくは、「Schedule a Job」をご覧ください。

注意

現在、Azure SQL Managed Instance を使用して、CPU がアイドル状態のときにジョブを開始することはできません。

ジョブの通知

SQL Agent ジョブでは、ジョブが正常に完了したとき、または失敗したときに通知を受け取ることができます。通知はメール経由で受け取ることができます。

まだ有効になっていない場合は、最初に、SQL Managed Instance 上でデータベースメール機能を構成する必要があります。

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

たとえば、メール通知の送信に使用されるメールアカウントを設定します。 AzureManagedInstance_dbmail_profile という名前のメールプロファイルにアカウントを割り当てます。 SQL Managed Instance で SQL Agent ジョブを使用して電子メールを送信するには、AzureManagedInstance_dbmail_profile という名前のプロファイルが存在する必要があります。そうしないと、SQL Managed Instance で SQL Agent を使用してメールを送信することはできません。

注意

メールサーバーの場合は、認証済みの SMTP リレーサービスを使用して電子メールを送信することをお勧めします。通常、これらのリレーサービスは、TLS 経由の接続の場合は TCP ポート 25 または 587、SSL 接続の場合はポート 465 を介して接続しますが、データベースメールは任意のポートを使用するように構成できます。これらのポートには、マネージドインスタンスのネットワークセキュリティグループに新しいアウトバウンド規則が必要です。これらのサービスは、IP とドメインの評判を維持するために使用され、外部ドメインがメッセージを拒否したり、スパムフォルダーに置く可能性を最小限にします。オンプレミスサーバーに既に認証された SMTP リレーサービスを検討してください。 Azureでは、SendGridは、このような SMTP リレーサービスの 1 つですが、他にもあります。

次のサンプルスクリプトを使用して、データベースメールアカウントとプロファイルを作成し、それらを関連付けます。

-- 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 でデータベースメールの構成をテストします。

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 エージェントジョブに関する出来事をオペレーターに通知できます。 SQL Managed Instance の 1 つまたは複数のインスタンスを保守するメンテナンス担当者の連絡先情報は、オペレーターが定義します。オペレーターの責任が 1 人の担当者に割り当てられることもあります。

SQL Managed Instance または 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 のデータベースメールログを使用して、メールの成功または失敗を確認します。

その後、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 Agent のプロパティは基になるレジストリ値に格納されているため、現在、SQL Managed Instance でそれを変更することはできません。これは、ジョブ履歴レコードの Agent 保持ポリシーを調整するためのオプションは、既定値である合計 1000 レコードと、ジョブあたり最大 100 履歴レコードに固定されていることを意味します。

詳しくは、SQL Agent のジョブ履歴の表示に関する記事をご覧ください。

固定データベースロールのメンバーシップ

sysadmin 以外のログインにリンクされているユーザーが、msdb システムデータベース内の 3 つの SQL Agent 固定データベースロールのいずれかに追加された場合、明示的な EXECUTE 権限を、master データベース内の 3 つのシステムストアドプロシージャに付与する必要がある、という問題があります。この問題が発生した場合は、エラーメッセージ The EXECUTE permission was denied on the object <object_name> (Microsoft SQL Server, Error: 229) が表示されます。

msdb の SQL Agent 固定データベースロール (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 Managed Instance 内での SQL Agent ジョブの制限事項

SQL Server で使用できる SQL Agent と SQL Managed Instance の一部としての SQL Agent の違いに注意してください。 SQL Server と SQL Managed Instance でサポートされる機能の違いの詳細については、Azure SQL Managed Instance と SQL Server の T-SQL の相違点に関するページを参照してください。

SQL Server で利用できる SQL Agent の機能の一部は、SQL Managed Instance ではサポートされていません。

SQL エージェントの設定は読み取り専用です。
- システムストアドプロシージャ sp_set_agent_properties はサポートされていません。
現在 SQL Agent の有効化と無効化はサポートされていません。 SQL エージェントは常に実行されています。
通知は部分的にサポートされていますが、以下はサポートされていません。
- ポケットベルはサポートされていません。
- NetSend はサポートされていません。
- アラートはサポートされていません。
プロキシはサポートされていません。
Eventlog はサポートされていません。
アイドル状態の CPU に基づくジョブスケジュールトリガーはサポートされていません。
マージレプリケーションジョブステップはサポートされていません。
キューリーダーはサポートされていません。
Analysis Services はサポートされていません。
ディスク上のファイルとして保存されているスクリプトの実行はサポートされていません。
dbatools や dbachecks などの外部モジュールのインポートはサポートされていません。
PowerShell Core はサポートされていません。

Azure SQL Managed Instance で SQL Agent ジョブを使用して管理タスクを自動化する

どのようなときに SQL Agent ジョブを使用するか

SQL Managed Instance 内での SQL Agent ジョブ

ジョブステップ

ジョブスケジュール

ジョブの通知

ジョブ履歴

固定データベースロールのメンバーシップ

SQL Managed Instance 内での SQL Agent ジョブの制限事項

詳細情報

次のステップ

フィードバック

フィードバック

その他のリソース

Azure SQL Managed Instance で SQL Agent ジョブを使用して管理タスクを自動化する

どのようなときに SQL Agent ジョブを使用するか

SQL Managed Instance 内での SQL Agent ジョブ

ジョブ ステップ

ジョブ スケジュール

ジョブの通知

ジョブ履歴

固定データベース ロールのメンバーシップ

SQL Managed Instance 内での SQL Agent ジョブの制限事項

詳細情報

次のステップ

フィードバック

フィードバック

その他のリソース

ジョブステップ

ジョブスケジュール

固定データベースロールのメンバーシップ