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

適用対象: Azure SQL Managed Instance

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

どのようなときに 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、Azure SQL Database、または SQL Server の他のデータベースにレプリケートできます。 詳しくは、Azure SQL Managed Instance にレプリケーションを構成する方法に関する記事をご覧ください。

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

ジョブ スケジュール

スケジュールによって、ジョブが実行されるタイミングが指定されます。 同じスケジュールで複数のジョブを実行できるほか、同じジョブに複数のスケジュールを適用することもできます。

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

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

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

ジョブの通知

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 を使用してメールを送信することはできません。

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

-- 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 とは
• Azure SQL Managed Instance の新機能
• Azure SQL Managed Instance と SQL Server の T-SQL の相違点
• 機能の比較:Azure SQL Database と Azure SQL Managed Instance

次のステップ

• データベース メールを構成する
• Azure でのアウトバウンド SMTP 接続に関する問題のトラブルシューティング