こんばんは。今日は、Azure Automationを触ってAzure SQL Databaseを操作できないか試してみましたので、その手順をまとめておきたいと思います。
それではまいります。
Contents
Azure SQL Databaseの操作自動化にAzure Automationを使うのはあり？
と、そもそもAzure SQL Databaseの管理操作を自動化する方法はAzure Functions、Logic Apps、Data Factoryなどいろいろあるかと思いますが、Azure Automationも適切なオプションなのでしょうか。
結論、公式Docでちゃんと紹介されているので”Yes”かと思います。
Azure Automation では、Azure PowerShell ツールで利用可能な PowerShell コマンドレットを使用することにより、 Azure SQL Database 内のデータベースを管理できます。 Azure Automation には、このような Azure SQL Database PowerShell コマンドレットがあらかじめ用意されており、サービス内ですべての SQL Database 管理タスクを実行することができます。 Azure Automation 内のこれらのコマンドレットと別の Azure サービスのコマンドレットを組み合わせて、Azure サービスとサード パーティ システム全体の複雑なタスクを自動化することもできます。
さらに、Azure Automation には、PowerShell を使用して SQL コマンドを発行することにより、SQL サーバーと直接通信する機能もあります。
https://docs.microsoft.com/ja-jp/azure/azure-sql/database/automation-manage
Azure Automationを使ったSQL Database操作の自動化
それでは、順を追って実装していきます。
Azure Automationアカウントの作成
Azure Automationを利用するには、まずAzure Automationアカウントリソースの作成が必要です。
Automationアカウントの作成にあたっては、特段込み入った設定はありません。今回は規定値のまま作成します。ネットワーク設定では、プライベートエンドポイント経由のアクセスを構成することができますので、エンタープライズでの利用にあたってはプライベートアクセスのみを許可しておく必要があるでしょう。
リソースの作成は時間かからず終わります。
新しいRunbookを作成する
Azure AutomationではRunbookというリソースの中に管理タスクを記述したコードを記述して、このRunbookに対して実行スケジュールを指定することでプロセスの自動化を実現します。
というわけで、新規Runbookを作成します。Runbookの作成にあたっての自由度は、Runbookの種類とランタイムバージョンのみです。
Runbookの種類では、大きくPyhonかPowershellか、Powershellの場合はさらにPowershellワークフローやグラフィカルPowershellなどのオプションが選べます。
今回は、シンプルにPowershellコードをひたすらかくだけの”Powershell”オプションを選択して作成しました。
ランタイムバージョンは、Powershellのバージョン（現時点では7.x or 5.x）を選択可能です。
Runbookを作成したら開いてみます。こんな感じで、全くの白紙です。ここにひたすらPowershellコードを書いていきます。
Powershellによるコーディング
さて、いよいよコーディングです。以下のQiita記事に助けていただいてサンプルコードをすぐに組むことができました！ありがたやです・・・
https://qiita.com/shingo_kawahara/items/608fd110f1203453e3c9
今回は、SQL Databaseの管理操作自動化とはいいましたが、超シンプルな実装として、上のQiitaのようにテーブルの件数を取得する処理の実装を目指します。
以下がコードです。
# Define the connection to the SQL Database
$Conn = New-Object System.Data.SqlClient.SqlConnection("★SQLDBの接続文字列★")
# Open the SQL connection
$Conn.Open()
# Define the SQL command to run. In this case we are getting the number of rows in the table
$Cmd=new-object system.Data.SqlClient.SqlCommand("SELECT COUNT(*) from ★テーブル名★", $Conn)
$Cmd.CommandTimeout=120
# Execute the SQL command
$Ds=New-Object system.Data.DataSet
$Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
[void]$Da.fill($Ds)
$html = $Ds.Tables[0].Rows[0][0]
# Output Result to Stdout
Write-Output($html)
# Close the SQL connection
$Conn.Close()
これをテストしてみます。
すると、ちゃんとテーブル件数が返ってきました！
超シンプルですが、これでSQL Databaseに対して操作を発行できるようになりましたので、あとは所望の操作に変更してみていただければと思います。
なお、上のコードではsystem.Data.SqlClientのコマンドなどが自由に実行できましたが、本来であればモジュールをインストールしている必要があるはずです。その疑問は、Automationアカウントのモジュールメニューを見ると解決します。
以下のように、Automationアカウントを作成した際に、あらかじめ必要になりそうなモジュールは一通りインストール済の状態にしてくれていたから実行できたのですね。もちろん、追加のモジュールが必要であればここからインストール可能です。モジュール管理も簡単ですね。
コードの改善：資格情報のハードコードをやめる
さて、上のコードはお試しで書くには良いですが、実運用上ではイマイチです。データベースの接続文字列（特にユーザ名とパスワード）がハードコードされているためです。
回避策の一つは、資格情報をAzure Automation側で管理することです。この機能については以下に詳しい説明があります。
Azure Automation でセキュリティ保護される資産としては、資格情報、証明書、接続、暗号化された変数などがあります。 これらの資産は、各 Automation アカウント用に生成された一意のキーを使って暗号化され、Azure Automation に保存されます。 Azure Automation では、キーはシステムによって管理される Key Vault に格納されます。 セキュリティで保護された資産を保存する前に、Automation によって Key Vault からキーが読み込まれ、それを使用して資産が暗号化されます。
https://docs.microsoft.com/ja-jp/azure/automation/shared-resources/credentials?tabs=azure-powershell
試してみましょう。Automationアカウント＞資格情報から、資格情報の追加を行います。
ここで、SQL DBの資格情報を登録しておきます。
続けて、コードの一部を以下の通り変更してみます。
# Define the connection to the SQL Database
# 新規追加-----------
$myCredential = Get-AutomationPSCredential -Name '★資格情報名★（私の例だとSQLDB）'
$SqlUsername = $myCredential.UserName
$SqlPass = $myCredential.GetNetworkCredential().Password
# ------------------
# コード変更---------
# Define the connection to the SQL Database
$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=xxxx....,User ID=$using:SqlUsername;Password=$using:SqlPass,.....")
# ------------------これで実行すると・・・ありゃ、エラーがでていまいました。
InvalidOperation: C:\Temp\svp4crj2.20o\a6f3fced-e3c5-4b22-a48d-a0f59cc15bca.ps1:8
Line |
8 | $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:sq …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| A Using variable cannot be retrieved. A Using variable can be used only
| with Invoke-Command, Start-Job, or InlineScript in the script workflow.
| When it is used with Invoke-Command, the Using variable is valid only if
| the script block is invoked on a remote computer.エラー内容を見ると、どうやら接続文字列の中に仕込んだUsing句が使えなかった模様。この問題はPowershell Workflowを使うと解決できたのですが、普通のPowershell Runbookで回避策があるかは、ちょっと当方Powershellの知識が疎くすぐみつけられなかったのでまた分かったら更新します。
ちなみに、ここまで書いておいてなんですが、ベストな方法はマネージドIDを使う方法かと思います。マネージドIDを利用することで、SQL DBの接続文字列に資格情報を含める必要自体がなくなるためです。
今回は実装しませんが、以下を参考にしてみてください。
SQL DB側の事前設定がいる想定です。
Runbookを公開する
さて、コードの改善で予想外にてこずってしまいましたが、最後にRunbookを公開します。これでRunbookが実行可能になります。
スケジュールを設定する
公開したら、最後にスケジュールを設定します。
Runbook > スケジュールから設定できます。
こんな感じで、Runbookを定時実行するように設定できます。
以上、Azure Automationを使ってSQL Databaseの操作を自動化する手順のメモでした！Powershellに慣れていればすらすらかけるのかもしれませんが、Powershell初心者な私にとっては、コードを書くので一苦労でした笑 が、そこさえクリアすれば簡単に運用できて便利そうですね。
今後の自動化の選択肢に加えておきたいと思います。
少しでも参考になりましたら幸いです。
おしまい
コメントを残す