はじめに
SSISDBデータベース（別名 Integration Services カタログ）は、SQL Server 2012 で MSDB データベースが整理され、社内ログおよびレポートのインフラストラクチャを提供するために導入されたものです。 SSISDB は、一言で言えば、SSIS フレームワークで、SQL Server Integration Services をより堅牢でエンタープライズ フレンドリーなものにするためのものです。
- データベースのバックアップ
- データベースの暗号化
- 環境のサポート
- プロジェクトおよびパッケージパラメーターのサポート
- パッケージ バージョン管理
- SSMS に組み込まれた顧客向け SSRS パフォーマンスレポート
- SSDT から直接デプロイメント
SSISDB フレームワークは SSIS をより高機能にしたが、その一方で。 それは、いくつかのマイクロソフトの仮定(a.s.)を伴っていた。通称デフォルト)が付いてきます。 この記事では、デフォルト、デフォルトが最適でない理由、およびデフォルトの変更方法を説明します。
問題
SSISDB Catalog には、操作とプロジェクトのバージョン管理をクリーンアップするための組み込みプロセスが搭載されています。 このクリーンアップ プロセスは、変更しないと SSIS を動作不能にする可能性のある SSISDB のデフォルトに依存します。
事実上すべての新しい SSIS プロジェクトは、豊富な空きディスク領域があるときに、わずかなパッケージを構築して実行することから開始します。 3～6ヶ月先に進むと、多くのSSISプロジェクト、数十または数百のパッケージがあり、レポートや分析のために新鮮なデータを必要とすることもあります（読み：常に24時間パッケージを実行する）。 これらの SSIS パッケージは、Microsoft のクリーンアップのデフォルトをはるかに超えるバージョン データ (あまり問題ではない) とログ データ (もっと問題) を蓄積するかもしれず、SSISDB データベース サイズが膨らんで予想外に大きくなる (利用できるディスク スペースをすべて消費する) 原因になるかもしれません。 この設定は、最終的に SSISDB データベースのサイズを管理し、最終的にディスク領域を占有するものです。 とはいえ、SSIS カタログ (図 1) には、SSISDB データベースのサイズ/ディスク領域を制御するための他のオプションがあります。
- Server-wide Default Logging Level
- Clean-up defaults:
- Clean Logs Periodically = True
- Periodically Remove Old Versions = True
- Retention Period (days) = 365
- Maximum Number of Versions per Projects = 10
Fig.SSISDB のデフォルト 一方、ディスク スペースを回復するためにサーバー全体のデフォルト ログ レベルを基本からなしに変更することは非常に非現実的で、トラブルシューティングを悪夢のように苦しめるでしょう。 プロジェクトごとの最大バージョン数や保持期間（日）などの他の SSISDB カタログ設定の変更は、SSISDB データベース/ディスクスペースを制御したまま、トラブルシューティングに最小限の影響を与えることができます。
これらのデフォルトはすべて、クリーンアップ プロセス (別名、SSIS サーバー メンテナンス ジョブ) を駆動しています。 Clean Logs Periodically = True および Periodically Remove Old Versions = True の両方はほぼ常に完璧ですが、Max Number of Versions per Projects = 10 および Retention Period (days) = 365 の両方の設定は楽観的に過ぎるかもしれません。 その理由は次のとおりです。
Clean Logs Periodically = True および Periodically Remove Old Versions = True の両方のデフォルトが完璧である主な理由は、これらのデフォルトがない場合 (または両方とも False に変更すると事実上)、データを削除せずにログおよびバージョン管理データを蓄積するのみで SSISDB が無限に成長しディスク容量が不足するためです。
Maximum Number of Versions per Projects = 10 と Retaining logging data for 365 days の両方が楽観的すぎるかもしれない主な理由は、サイズではなく期間に基づいてデータを保持するという Microsoft の決定です。 つまり、固定期間の数値は、どれだけのデータが蓄積されるかを考慮していないのです。 プロジェクトごとの最大バージョン数は、数千のパッケージがあれば問題になるかもしれませんが、保持期間（日数）は、数分ごとに実行されるパッケージがあれば大きな問題になるでしょう。 Microsoft は、1 日に 1440 回 (1分ごとに実行) 実行されるログ データが蓄積され、365 日以上の頻度でクリーンアップが必要になるか、ディスク容量が不足するとは予想していなかったと思われます。 パッケージを毎分実行し (e-commerce プラットフォームの要件)、最大 10 プロジェクトを持つ私の個人的な経験に基づいて、保持期間 (日) を 365 (デフォルト) から 7 日に減らし、プロジェクトごとの最大バージョン数を 10 (デフォルト) から 5 に減らすと、デバッグおよびトラブルシューティングの目的で情報を十分に保持しながら SSISDB/ディスク領域を制御するのに十分となるはずです。
この変更を行うには 2 つの方法があります。
- SQL Server Management Studio (別名 “SSMS”) を使用してデフォルトを変更するには、Integration Services を展開して SSISDB を右クリックし、プロパティ メニュー項目をクリックします (図 2 – SSMS)。
Fig 2 – SSMS - ssisdb.catalog.catalog_properties テーブルのレコードを直接更新するには、次の T-SQL を使用してデフォルトを変更します。
ソースコード 
UPDATE ssisdb.CATALOG.catalog_propertiesSET property_value = 5WHERE property_name = 'MAX_PROJECT_VERSIONS' UPDATE ssisdb.CATALOG.CTRL_VERSIONS.CATRO_VERSIONS テーブルのレコードを直接更新します。catalog_propertiesSET property_value = 7WHERE property_name = 'RETENTION_WINDOW'
一旦変更が完了すると、その変更は行われません。 以下の T-SQL クエリを実行することで、変更を確認できます (図 3 – SSISDB カタログ)
ソースコード SELECT * FROM ssisdb.SSISDB.SSISDB (図 3)
SSISDB (図 3) T-SQLクエリCATALOG.catalog_properties 
Fig 3 - SSIS カタログ デフォルトを変更すると、今後の問題は解決するかもしれませんが、既存の SSISDB ディスク容量の問題はすぐに解決しないかもしれません。 ssisdb.internal.cleanup_server_retention_window ストアド プロシージャを変更することにより、コードを修正し、@delete_batch_size を 1000 から 10000 のように変更することでクリーンアップ プロセスを高速化できます (図 4 - Retention Procedure).
Fig 4- Retention Procedure Summary
この記事では、ディスク容量不足につながる SSISDB カタログのデフォルトに関する潜在的問題を取り上げ、SSISDB に少ない情報を保持させる解決法を提供しました。