Azure Automation: クラウド内の SQL エージェント

このポストは、6 月 26 日に投稿した Azure Automation: Your SQL Agent in the Cloud の翻訳です。

定期的なメンテナンスや管理ジョブをスケジュール設定できると、1 つまたは複数のデータベースを管理する担当者は非常に楽になります。オンプレミスまたは Azure Virtual Machines で SQL Server を使用している場合は、SQL Server エージェントによりこの機能が提供されます。しかし、PaaS の Azure SQL Database を使用する場合、SQL Server エージェントでこの機能を利用することはできません。このため、さまざまなフォーラムでこの機能へのご要望をいただいています (例 1、例 2、例 3、いずれも英語)。

先日パブリック プレビュー サービスとして導入された (英語) Azure Automation では、ニーズの高かった、強力な PowerShell ワークフロー実行サービスが Azure プラットフォームで実現しました (サービスについてはこちら、ドキュメントはこちら (英語) から参照できます)。これにより、面倒なメンテナンス作業を自動化できるようになります。また、通常の Azure ポータルのエクスペリエンスにカプセル化できるようになり、便利になりました。使用方法は簡単で、PowerShell ワークフロー (Azure Automation では “Runbook” と呼ばれます) をオーサリングするだけで、クラウドへのアップロード、および Runbook 実行のスケジュール設定を行えます。

ここまでをお読みになると、「Azure Automation は Azure SQL DB における SQL Server エージェントの役割を果たすのか」という疑問をお持ちになるかと思います。簡単に答えると、そのとおりです。この記事では、Azure Automation サービスを活用して、特定の SQL Database がサイズ制限に達した場合にテーブルを切り捨てるというお客様からご要望いただいたシナリオ (英語) を実現する方法について説明します。これは特定のシナリオを想定していますが、ここで紹介するサンプルは、通常 SQL Server エージェントの実行が必要となるさまざまな Azure SQL DB のシナリオを実行するための基盤となるものです。

Azure Automation には複数の入門ガイド (ドキュメント、TechNet 記事、TechNet 記事、いずれも英語) が用意されていますので、詳細は省略します。Azure Automation の使用に関して不明な点がある場合は、フォーラムを参照してください。

下記に Runbook の例を紹介します。この例では、特定の論理サーバーの SQL Database に対して反復処理を行い、データベース名とそれぞれの現在のサイズの配列を取得します (Runbook はこちらのページ (英語) からダウンロードできます)。その後、各 SQL Database に対して最大サイズを取得するクエリを発行し、SQL Database のサイズが最大サイズの一定範囲以内である場合は、指定されたテーブルを切り捨てます。

workflow Remove-DataFromSqlDbTable

{

    param

    (

        # Azure DB サーバーの完全修飾名

        [parameter(Mandatory=$true)]

        [string] $SqlServerName,

 

        # $SqlServerName の認証情報を Azure Automation の資格情報アセットとして保存

        # Azure Automation の UI で使用する場合、"Credential" パラメーターに資格情報アセットの名前を入力する

        [parameter(Mandatory=$true)]

        [PSCredential] $Credential

    )

 

    inlinescript

    {

 

        # 資格情報を設定

        $ServerName = $Using:SqlServerName

        $UserId = $Using:Credential.UserName

        $Password = ($Using:Credential).GetNetworkCredential().Password

 

        # しきい値を DB サイズの最大値に対する割合で指定する

        $Threshold = 0.8

 

        # マスター DB への接続を作成

        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection

        $MasterDatabaseConnection.ConnectionString = "Server = $ServerName; Database = Master; User ID = $UserId; Password = $Password;"

        $MasterDatabaseConnection.Open();

 

        # アクティブなデータベースの現在のサイズを確認するクエリを $ServerName に対して発行するコマンドを作成

        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand

        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection

        $MasterDatabaseCommand.CommandText =

            "

                SELECT

                       database_name,

                       storage_in_megabytes [SizeMB]

                FROM

                       [sys].[databases] as db

                INNER JOIN

                       [sys].[resource_usage] as rs

                ON

                       rs.database_name = db.name

                WHERE

                       [time] = (SELECT Max([time]) FROM [sys].[resource_usage] WHERE database_name = db.name)

                GROUP BY

                       database_name, storage_in_megabytes

            "

        # リーダーを実行し、その結果のタプルを返す <database_name、SizeMB>

        $MasterDbResult = $MasterDatabaseCommand.ExecuteReader()

 

        # データベースが 1 つ以上存在する場合は続行

        if ($MasterDbResult.HasRows)

        {

            # 各データベースの接続を作成

            $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection

            $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand

 

            # $ServerName の各データベースに反復適用

            while($MasterDbResult.Read())

            {

                $DbName = $MasterDbResult[0]

                $DbSize = $MasterDbResult[1]

 

                # ユーザーのデータベースを判断する条件を適用 (例: マスター DB ではない、など)

                if($DbName -ne "Master")

                {

                    # $DbName への接続文字列を設定

                    $DatabaseConnection.ConnectionString = "Server=$ServerName; Database=$DbName; User ID=$UserId; Password=$Password;"

                    $DatabaseConnection.Open();

 

                    # 指定したデータベース $DbName に対するコマンドを作成

                    $DatabaseCommand.Connection = $DatabaseConnection

                    $DatabaseCommand.CommandText = "SELECT DATABASEPROPERTYEX ('$DbName','MaxSizeInBytes')"

 

                    # クエリを実行し、単一のスカラー値の結果を返す

                    $DbResultBytes = $DatabaseCommand.ExecuteScalar()

                    $MaxDbSizeMB = $DbResultBytes/(1Mb)

 

                    # $TargetDbSize の演算を実行

                    $TargetDbSize = $MaxDbSizeMB * $Threshold

 

                    # 現在の $DbSize が、$MaxDbSizeMB に一定の割合 ($Threshold) を乗じた値よりも大きい場合

                    # 指定した処理を実行 (この例の場合は該当するデータベースのテーブルを切り捨てる)

                    if($DbSize -gt $TargetDbSize)

                    {

                        Write-Output "Perform action on $DbName ($DbSize MB > $TargetDbSize MB)"

 

                        # ExampleTable は重要性が低く失ってもよい大規模なデータを格納するテーブルを示すプレース ホルダー

                        # これを切り捨ててデータベース空間を確保する

 

                        $DatabaseCommand.CommandText = "TRUNCATE TABLE [dbo].[ExampleTable]"

                        $NonQueryResult = $DatabaseCommand.ExecuteNonQuery()

                    }

                    else

                    {

                        Write-Output "Do not perform action on $DbName ($DbSize MB <= $TargetDbSize MB)"

                    }

 

                    # $DbName への接続を切断

                    $DatabaseConnection.Close()       

                }

            }

        }

 

        # Master DB への接続を切断

        $MasterDatabaseConnection.Close()

    }   

}

Runbook は、一度 Azure Automation にアップロードした後は、変更、テスト、オンデマンドでの実行、スケジュールへのリンクが可能です。実際には、Runbook 全体の作成は Azure ポータルで行えます。Azure Automation では、IntelliSense、構文の色、テキスト検索機能などを備えた使いやすいオーサリング エクスペリエンスを利用できます。次に示すスクリーンショットは、前述の Runbook を毎晩午前 0 時に実行するようにスケジュールを設定した場合の画面の様子です。

ここまでに説明したように、Azure Automation では非常に拡張性の高い PowerShell ワークフローの実行エンジンとジョブ スケジューラを提供します。今回説明した機能の他にも、より高レベルな SQL DB タスクを Azure Automation Runbook から実行する Azure PowerShell コマンドレットを Azure Automation に搭載し、新規 SQL Database や SQL Server のプロビジョニングなどを行うことができます。SQL Database のプロビジョニングだけでなく、容量管理、インデックスのメンテナンス、SQL Database のパフォーマンス レベルの上げ下げといったことも可能です。Azure Automation を使用すれば、クラウドの開発と運用において、Azure SQL DB の管理およびメンテナンスのタスクを自動化するために必要な基本要素が利用できます。

Azure Automation をまだご利用でない方は、プレビュー版にサインアップしお試しください、また入門ガイド (英語) をご覧ください。