SQL Server のアップグレード / マイグレーションを実施する際には、SQL Server の互換性を考慮する必要があります。
SQL Server の互換性についてのドキュメントについては次のような情報があります。
SQL Server のドキュメントは 2016 以降とそれより前のバージョンでドキュメントが分かれており、バージョンによって記載されている互換性レベルのバージョンが異なっているため、いくつかのバージョンのドキュメントを組み合わせる必要があります。(SQL Server 2019 のドキュメントには、SQL Server 2016 以降の情報が含まれています)
- 互換性レベル
- SQL Server 2008 : ALTER DATABASE 互換性レベル (Transact-SQL)
- SQL Server 2012 : ALTER DATABASE 互換性レベル (Transact-SQL)
- SQL Server 2014 : ALTER DATABASE 互換性レベル (Transact-SQL)
- SQL Server 2019 : ALTER DATABASE (Transact-SQL) 互換性レベル
- 機能互換性
- SQL Server 2008 : SQL Server データベース エンジンの旧バージョンとの互換性
- SQL Server 2012 : SQL Server データベース エンジンの旧バージョンとの互換性
- SQL Server 2014 : SQL Server データベース エンジンの旧バージョンとの互換性
- SQL Server 2019 : SQL Server で廃止されたデータベース エンジンの機能
現時点で非推奨とさている機能を使用しているかについては、パフォーマンスモニターのオブジェクトとしても情報が提供されており、次のカウンターの情報から使用状況を把握することもできるようになっています。
様々な情報が公開されていますが、「自分が使用している環境でどのような非互換となる情報が該当するか」を一つ一つチェックして影響度を把握することは工数を積んで人海戦術で対応をしようとしても、抜けなく各項目の対応状況を網羅することは現実的には難しいのではないでしょうか？
アップグレード / マイグレーションによる影響を確認するためのツールとして、SQL Server では、Data Migration Assistant (DMA) というツールが提供されています。このツールは SQL Server Upgrade Advisor の後継となり、現在の SQL Server 環境のアップグレード / マイグレーションについては、DMA が使用されます。
このツールをアップグレード対象の SQL Server に対して実行することで、環境を変更することの影響度を評価 (アセスメント) することができ、上述した各種ドキュメントで記載されている内容に対して、どの項目が該当する可能性があるかをツールの実行で機械的に判断することができ、環境の移行に伴う問題の評価を容易に実施することができます。
バージョンの変更 / 互換性レベルの変更についての影響度を評価することができ、新しい環境の SQL Server を使用する際の問題の把握 / 設定の変更の影響度を確認するためには、DMA の利用が推奨されています。
本投稿では、DMA を使用した評価の応用方法についてまとめたいと思います。
Contents
DMA で評価される対象
DMA で評価される対象については「SQL Server 内に存在しているデータベース内のオブジェクト」となります。
これは、
- インスタンスの設定
- データベースの設定
- データベース内のオブジェクト (テーブル / 関数 / ストアドプロシージャ等)
- ログイン
というようなオブジェクトが該当し、評価対象については、機能 で記載されています。
DMA ではデータベース内のオブジェクトのみが評価されるため「アプリケーションで生成されているアドホック / パラメーター化クエリについては評価が行われない」ということを意識しておく必要があります。
すべてのデータアクセスがストアドプロシージャ化されているということは、基本的にないかと思いますので、DMA の基本機能だけでは、アプリケーション内で組み立てられているクエリについては評価対象となっていません。
DMA ではアプリケーションから実行されているクエリを評価対象とするための機能も提供されており、この機能を使用することで、データベース外で組み立てられているクエリについても評価対象とすることができます。
アプリケーションで組み立てているクエリを評価対象とする
DMA では、アプリケーションで組み立てている (生成している) クエリを評価対象とするために二種類の方法が提供されています。
一つ目が、評価するデータベースと拡張イベント トレースを追加する となります。
拡張イベントで実行されているクエリの情報をキャプチャして、評価の入力とする方法となり、「sqlserver.sql_batch_completed」のイベントを取得して、DMA のインプット情報として使用することができます。
二つ目が、データ Migration Assistant によるアプリのデータアクセス層の評価 となり、これは、Data Access Migration Toolkit という Visual Studio Code の拡張機能 (Extension) を利用した方法となります。(Using Data Migration Assistant to assess an application’s data access layer でも利用方法が解説されています。)
Data Access Migration Toolkit は Visual Studio Code の拡張機能として、コマンドパレットから利用することができ、Visual Studio Code で開いているフォルダーや、ファイルのコードの中でデータベースにアクセスしている箇所を検出し、クエリの情報を抽出するというものになります。
実際に抽出した結果が次の画像となります。ソースコードのどの部分で SQL を生成しており、どのようなクエリが実行されているかを抽出することができます。
この情報は JSON で保存することができ、DMA の次の画面で、インプット情報として使用することができます。 
これらの方法を使用することで、データベース外で生成されているクエリを DMA の評価対象とすることができるようになります。
拡張イベントと Data Access Migration Toolkit の特徴
拡張イベントと Data Access Migration Toolkit のどちらもアプリケーションで生成されているクエリを DMA のインプットとして使用することができますが、それぞれの方法にはどのような特徴があるでしょうか？
拡張イベントの特徴
拡張イベントを使用すると、 SQL Server に実行されたクエリの情報を取得することができますが、「sqlserver.sql_batch_completed」を取得するため、SQL Server に対しての負荷が発生します。(フィルターを設定しても、フィルターが動作するのはイベントが発生した後になりますので、イベントを取得することによる負荷の軽減にはつながりません)
また、クエリが実行されたタイミングで情報が取得されますので、実行頻度が低いクエリ / 取得期間中に実行されていないクエリなどについては拡張イベントに情報が出力されません。
大量にクエリが実行されている環境では、拡張イベントのログのサイズについても考慮が必要となります。
Data Access Migration Toolkit の特徴
Data Access Migration Toolkit はソースコードからデータベースのアクセス箇所 / クエリを検出しますが、
- データベースアクセスがソースコード内に記載された SQL ではなく OR マッパーのような形で抽象化されている
- 文字列を連結してベースクエリを生成している場合は、クエリ全体を抽出できない
というような特徴があり、こちらでもすべてのクエリが検出できるわけではありません。
拡張イベントは実行されたクエリの情報を取得しますので、データベースアクセスが抽象化されていてもクエリの情報を取得することができますが、取得することによる負荷が発生します。
Data Access Migration Toolkit はソースコードを解析するので、SQL Server には負荷は発生しませんが、ソースコードから取得できるクエリについては限界があり、クエリ全体を取得できていない可能性があります。
どちらの方法も一長一短がありますね。
Data Access Migration Toolkit のファイル内容の評価
この内容が以降が、本投稿の主題となります。
Data Access Migration Toolkit のサポート対象のファイルタイプは次のようになっています。
C# / Java のソースコード以外に、XML / JSON / SQL ファイルについても対象とすることができます。
「.sql ファイル」については、SQL ファイルが存在するかの確認となっており、現時点では「.sql ファイル」内のクエリについては、DMA で解析対象とするクエリ情報として活用することはできません。(.SQL ファイルは、新しいデータースプラットフォーム上で動作するかをレビューする必要がありますという情報のみ出力され、ファイル内に記載されているクエリは結果として出力されず、DMA でも検知されたファイル内にはアクセスされていません)
しかし、JSON や XML についてはファイル内に含まれているクエリ情報を DMA の入力情報として使用することができます。
次のようなファイルが Data Access Migration Toolkit で評価を行うディレクトリ内に含まれていた場合、
評価結果として次のように検出を行うことができます。
つまり「JSON」「XML」のようなファイル内に記載されているクエリについては、クエリ内容を Data Access Migration Toolkit は DMA の連携内容として生成ができるということになります。
本来の用途とは異なりますが、このファイル内の記載内容の評価により、拡張イベント / Data Access Migration Toolkit で手が届かなった範囲を保管することができます。
XML にキャッシュされたクエリの情報を記載することで DMA で評価対象となるクエリを補完
JSON を使用してもよいとは思うのですが、一部の記号のエスケープの考慮が必要となると思いますので、XML の CDATA セクションを使用したファイルを使用します。
ファイルはクエリ単位で分けても、次のように一つの XML ファイルの中に複数の CDATA セクションを記述してもどちらの方法でも構いません。
<xml>
<![CDATA[
SELECT * FROM sys.objects ORDER BY 1
]]>
<![CDATA[
SELECT * FROM sys.objects, sys.columns WHERE sys.objects.object_id *= sys.columns.object_id
]]>
</xml>
このようなファイルを用意し、Data Access Migration Toolkit の入力として使用することで、DMA に対して任意のクエリを入力情報として使用することができるようになります。
「拡張イベント」はログを取得する際の負荷への懸念がありましたが、「クエリのキャッシュ情報からクエリテキストを取得する」のであれば、拡張イベントと比較して負荷を抑えてクエリの情報を取得することができます。
- SQL Server のキャッシュからクエリテキストを取得
- 取得したテキストを XML に出力
- Data Access Migration Toolkit で出力した XML または XML を含むディレクトリを評価
- 評価結果の JSON を保存し、DMA で評価
というような手順を行うことで、DMA に対してアプリケーションで生成されているクエリを評価させることができるようになります。
「メモリ上にキャッシュされている情報」をベースとしているため、すべてのクエリを評価できるわけではありませんが、実行頻度の高いクエリについては、評価対象になる可能性が高く、拡張イベントを設定する手間もありませんので、敷居は低いのではないでしょうか。
適当ですが、次のようなクエリでテキストを取得することができます。
ストアドプロシージャ等のオブジェクト ID を持つクエリについては、DMA の通常の評価結果の中に含まれていますので、キャッシュの取得対象からは除外しています。そのほかには、sys スキーマに対してのクエリについても一般的なクエリではない可能性が高いので除外をしています。
select top 10 qt.text from sys.dm_exec_query_stats AS qs outer apply sys.dm_exec_query_plan(qs.plan_handle) AS qp cross apply sys.dm_exec_sql_text(qs.sql_handle) AS qt where qp.objectid IS NULL and qt.text NOT LIKE '%sys.%' option (MAXDOP 1)
これを上述の XML 内に記述することで DMA にキャッシュされたクエリを入力とすることができるようになります。
XML の生成については、SSMS で実行した結果をコピペして加工や、バッチを作成して動的に生成する等お好みで。
パラメーター化クエリに対しての考慮
上記のクエリの結果にも含まれていますが、パラメーター化クエリについては DMA のインプットとして使用する場合には考慮が必要となります。
上記の実行結果であれば、次のようなパラメーター化クエリが含まれています。
(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 int)with FKTable as ( 以降省略
DMA のクエリ評価については、シンタックスエラーの評価も行われており、上記のクエリをそのままインプットとして使用するとシンタックスエラーとして評価されてしまいます。
クエリ内で使用しているパラメーターの変数宣言 (DECLARE) が含まれていないため、構文エラーとなってしまうので、上記のクエリであれば次のようなクエリに修正をして XML 内に記載する必要があります。
DECLARE @0 varchar(8000);DECLARE @1 varchar(8000);DECLARE @2 varchar(8000); DECLARE @3 int; with FKTable as ( 以降省略
この辺の変換も考慮すると XML への出力はプログラム化したほうが良いかと思います。
Data Access Migration Toolkit で想定している通り XML の内容が解析できると、Data Access Migration Toolkit の評価結果を JSON として保存した際に、次のように JSON 内にクエリのテキストが含まれるようになります。含まれているテキストについては、DMA で評価を行うことができます。
DMA で Data Access Migration Toolkit の出力内容を評価
Data Access Migration Toolkit で出力した JSON については、DMA の次の画面でインプットとして使用することができます。
実際に実行されるわけではなく、構文の解析となりますので、選択する DB については適当なもので問題ないと思います。
JSON に含まれていたクエリについては Type が「Ad hoc query」として、データベース内に含まれていないオブジェクトのクエリとして評価が行われます。
昨今、データベースに対して実行されるクエリはアドホック / パラメーター化クエリが多いかと思いますので、DMA で任意のクエリを評価させる方法は利便性が高いのではないでしょうか。
まとめ
DMA では、データベース外で生成されているクエリを評価対象とする方法が提供されていますが、本番環境での取得 / ソースコードに記載されているクエリの抽出が難しいクエリも多々あるかと思います。
SQL Upgrade Advisor であれば、キャッシュかに含まれるクエリ/ 任意のクエリを評価する方法が提供されていたのですが、DMA については現時点ではこれらの情報をインプットとして使用することはできません。
しかし、Visual Studio Code の Data Access Migration Toolkit で出力される JSON を活用することで、DMA で拡張イベントを使用することなく任意のクエリを評価対象にすることができます。
今後、機能が拡張されていくと、任意のクエリ / キャッシュ / クエリストアをインプットとして使用することができるかもしれませんが、現時点では、DMA の応用方法として本投稿のような方法を使用して、任意のクエリをインプットとして使用する方法を覚えておくと、アップグレード / マイグレーションを実施する際の評価対象となるクエリを拡充することができるのではないでしょうか。