忙しい人のためのMS SQL Serverデータベースの信頼できるメンテナンス

データベースのメンテナンスは、バックアップの作成、整合性のチェック、インデックスの管理、統計などの複雑な手順であることをご存知でしょう。 ネットワークのオープンスペース（およびHabréを含む）では、この主題について多くの記事と推奨事項が書かれています。 ただし、「1C：Enterprise」を実装する場合、データベースのメンテナンスが誤って、または非常に単純化されたスキームで構成されているという事実に直面することがよくあります。 たとえば、トランザクションログの管理に煩わされないように、「戦闘」データベース用のシンプルリカバリモデルがインストールされます。 そしてこれは、数時間での情報の損失がすでに会社にとって重要であるという事実にもかかわらずです。 データベースファイルを圧縮するタスクが定期的なメンテナンスに含まれている場合（「ショブは成長しませんでした」）、またはインデックスを更新した後、統計が破壊されたり、その他の同様の間違いが発生する場合があります。 これは、多くの場合、企業には経験豊富なデータベース管理者がいないため、IT部門の誰か、「非自発的」データベース管理者（ DBA ）がメンテナンスを行う必要があるためです。 さらに、このようなDBAは、すべてのリスクとそれに割り当てられた責任を常に認識しているわけではありません。







データベースのメンテナンスのために、MicrosoftはSQL Server Management Studio（SSMS）でメンテナンスプランを提供しています。 ただし、実践が示すように、品質と信頼性の高いサービスプランを作成および構成できるのは、経験豊富なDBAだけです。 信頼性の高いサービスは可能な限り自動化されており、管理者による定期的な手動監視を必要とせず、障害が発生した場合にデータを復元できることにも注意してください。



市場で入手でき、生活を楽にすることができるサードパーティのプログラムは、主にバックアップの作成を自動化します。 そのようなプログラムの選択は非常に広いです。 FTP / GoogleDrive / Amazonなどに圧縮および暗号化されたバックアップを作成できます。 ここでのバックアップは、フォレスト・ガンプの写真でババが話したエビと比較できます：「 ...彼らは、揚げ、調理、焼き、煮込み、エビ海老、クレオール海老、ご飯と揚げたガンボ海老を調理できます... 」







ただし、前述したように、バックアップの設定はすべてとはほど遠いため、このようなプログラムは問題の一部のみを対象としています。



その結果、「非自発的な」DBAは記事を読み、SSMSに対処し、バックアップ戦略を開発し、スクリプトを探し、通知を構成する必要があります。 それには多くの時間がかかりますが、常に誓う何かがあります...しかし、私は平和に生きたいです！ かつてそうし忘れていた。



この記事では、Microsoft SQL Serverのデータベースメンテナンスを簡単かつ迅速にセットアップするのに役立つQuick Maintenance＆Backup （ QMB ）プログラムを確認します。 間違いなく、大規模で負荷の高いデータベースでは、経験豊富なDBAと個々のパフォーマンスチューニングなしでは実行できませんが、多くの小さなデータベース（通常は最大50〜80 GB）を処理する必要がある場合、このユーティリティは初心者と上級者の両方に役立ちますユーザーに。





以下の短いビデオは、QMBを使用してサービスをセットアップするエンドツーエンドの例を示しています。 以下の説明は、ビデオを補足するものであり、プログラムの機能の一部について説明しています。

コンセプト：初心者にアクセスしやすく、専門家に便利

一方では、初心者がプログラムにアクセスできるようにし、最も一般的なサービスシナリオを実装しようとしました。 一方、プログラムを上級ユーザーにとって便利にし、Transact SQLを使用したデータベースメンテナンスとアプリケーションの他のルーチンプロシージャの組み合わせなど、さまざまなシナリオの構成を支援したいと考えました。 たとえば、QMBでは、最初にデータを「1C：Enterprise」にアップロードしてから、バックアップを作成し、残りのサービスを実行するスクリプトを作成できます。 結果は、T-SQLスクリプトとバッチファイルを実行するための独自のフレームワークを提供するスケジューラーです（実行結果を保存する機能を備えています）。

建築

このプログラムには、クライアントGUI、QMBサービス、およびデータを保存するファイルデータベースの3つのコンポーネントがあります。 QMBをインストールすると、3つのプログラムコンポーネントがすべてインストールされます。 サービスプランは作成されないため、SQL Serverエージェントサービスは必要ありません。 アーキテクチャの詳細についてはこちらをご覧ください 。

サービスポリシー、シナリオ、およびタスク

上記のように、QMBはSQL Serverでサービスプランを作成しません。 代わりに、 サービスポリシーが作成され、ローカルストレージ（ファイルデータベース）に保存されます。 本質的に、ポリシーとは、同じルールによって提供される同様のプロパティを持つデータベースのグループです。 このポリシーには、データベースのリスト、バックアップの保存とコピーの設定が含まれています。 ポリシーには、1つ以上のサービスシナリオが含まれます 。 このスクリプトには、各（ポリシーに含まれる）データベースに対して順番に実行される一連のタスクが含まれています。 メンテナンスプランから類推すると、ネストされたメンテナンスプランとシナリオを比較できます。











QMBのタスクには、次の5つのタイプのいずれかを含めることができます。

• T-SQLスクリプト
• バックアップコピーの作成（T-SQLスクリプト）
• アーカイブの復元（T-SQLダイナミックスクリプト）
• カスタムスクリプト（T-SQLではない）
• アーカイブコピーのコピー（同じ名前のシステムタスクで使用）

このプログラムには、2セットの組み込みタスクがあります。 タスクの最初のセットは、オープンソースから取得し、QMB開発者によって作成されたT-SQLスクリプトに基づいています。 2番目のセットは、データベースを提供するための3つの一般的なストアドプロシージャを開発したOla Halengren（スウェーデンのデータベース管理者）によるスクリプトに基づいています。 Olaプロシージャは、テンプレートからポリシーを作成するときに、 マスターシステムデータベースに自動的にインストールされます。

大小のデータベースを提供します。 ポリシーテンプレート

サービスポリシーは、テンプレートから作成することも、ゼロから手動で作成することもできます。 プログラムの現在のバージョンには、主に異なる7つのテンプレートが含まれています。

• データベース復旧モデル。 完全復旧モデルを備えた5つのポリシーと単純復旧モデルを備えた2つのポリシー。
  
  
  
  
• インデックスのメンテナンス手順。 小規模なデータベースの場合、インデックスの最適化は毎晩実行され、更新された統計は終日更新されます。 大規模なデータベースの場合、インデックスの最適化は週に1回実行されます。
  
  
  
  
• スクリプトで使用される一連のタスク。 メンテナンスには、Ola HalengrenまたはQMBのタスク/スクリプトが使用されます。
  
  

毎日の運用情報入力（ OLTPデータベース）がある作業データベースの場合、 フルリカバリモデルのポリシーを選択することをお勧めします。たとえば、1Cの場合：データが毎日入力されるエンタープライズデータベース。 このモデルを使用すると、データベースを現在または任意の時点に復元できます。



アーカイブデータベースとテストデータベース、およびときどきデータを読み込むさまざまなストレージには、単純な復旧モデルを使用することをお勧めします。



テンプレートからポリシーを作成した後、そのパラメーター（スクリプトとスケジュール、タスク、通知順序）を変更できます。 将来、作成されたポリシーは、プログラムに登録されている他のサーバーにコピーできます。



テンプレートの違いの詳細については、 ヘルプを参照してください。

タスク

上記のように、QMBには5種類のタスクがあります-その機能のいくつかを以下に説明します。

スクリプト実行

ほとんどのシステムタスクはT-SQLスクリプトです。 スクリプト自体は、タスクの形式で表示できます。











スクリプトテキスト（T-SQL、CMD、VBS、PowerShellなど）には、実行前に対応する値に置き換えられるマーカーが含まれる場合があります。 たとえば、マーカー？DataBaseName？ はデータベース名に置き換えられ、 ？BackupDirectory？ -ポリシーで指定されたアーカイブコピーディレクトリへのパス。 マーカーの完全なリストはヘルプにあります 。



サービスウィンドウの最適化

限られた時間枠では、SQL Serverツールを使用したデータベースのメンテナンスだけでなく、アプリケーションの他のスケジュールされた操作の実行も合わせる必要があります。 たとえば、1Cデータベースのテストと修正、1Cを使用したアンロード：エンタープライズプラットフォーム、交換の実施など。 通常、これにはWindowsタスクスケジューラまたは1C：Enterprise Schedulerが使用されます。 ただし、この場合、適切なマージンで時間内に手順を配布する必要があります。これにより、手順が重複しないことが保証されます。 その結果、タスクが既存の時間枠に収まらない場合があります。



QMBを使用すると、VBS、JavaScript、CMD、PowerShellなどのT-SQLスクリプトとバッチファイルの実行をスクリプトで組み合わせて、メンテナンスウィンドウをできるだけ効率的に使用できます。 以下は、Robocopyユーティリティを使用した代替バックアップバックアップタスクの簡単な例です。











バッチファイルは、プログラムがインストールされているマシンとSQL Server側の両方で実行できることに注意してください。 これにより、SQL Server側でバックアップファイルを処理できます。 たとえば、最後のバックアップをアーカイブしてクラウドストレージにアップロードするスクリプトを記述したり、独自のコピーアルゴリズムを実装したりできます。 次の記事では、この機能について詳しく説明し、「1C：Enterprise 8」データベースを操作するためのスクリプトを提供する予定です。



メッセージ出力とサービスログ

スクリプトの実行中に表示されるすべてのメッセージは、プログラムメンテナンスログにリダイレクトされます。 これは、T-SQLスクリプトのprint、raiserrorコマンドによって出力されるメッセージ、および他のCMDスクリプトとバッチファイルのechoコマンドによってコンソールに出力されるメッセージに適用されます。 そしてそれは素晴らしいことです！ 読み取り可能で理解可能なログは時間を大幅に節約するため、ボーナスとして、エラーテキストは電子メール通知で送信されます。

リカバリーによる自動バックアップ検証

バックアップの存在は、障害が発生した場合にデータを回復できることを意味するものではありません。さまざまな理由でエラーが発生して回復が失敗する可能性があります。 たとえば、アーカイブコピーのチェーンが中断され、データを復元しようとするまでそれを知ることさえできない場合があります。 そのため、ベストプラクティスでは、優れたDBAはバックアップされたバックアップを定期的にチェックし、そこから復元する必要があるとしています。 他に100％の方法はありません。 また、すべてのバックアップを少なくとも1回テストすることをお勧めします 。 SSMSには自動回復のタスクはありません。また、毎日手動でバックアップを確認したい人はあまりいません。



QMBには、各ポリシーデータベースのバックアップチェーン全体を順番に復元する特別なタスクがあります。フルバックアップ->差分バックアップ->トランザクションログバックアップ。 回復は一時的なテストベースで実行され、整合性の確認後に削除されます。



たとえば、当社の仮想SQL Serverには、合計容量100 GBの小さなデータベースが約60個あります。 QMBは、すべてのデータベースが復元できることを毎晩チェックします。 検証には約1時間半かかります。これにより、すべてのバックアップが検証されることが保証されます。 バックアップチェーンが中断されると、次のエラーのような通知が表示されます。

1.タスク 'アーチからの復元。 一時データベースへのコピーとそれに続く整合性チェック '（データベース：Buh_Oazis）

メッセージ：4305、レベル：16、状態：1、行：21

このバックアップデータセットのログは、LSN 5235000000291100001で始まります。これはまだデータベースに適用できません。 LSN 5228000000281600001を含む以前のログバックアップを復元できます。



メッセージ：3013、レベル：16、状態：1、行：21

RESTORE LOGはエラーで中止されました。



メッセージ：50000、レベル：16、状態：1、行：119

回復プロセス中にエラーが発生しました。

通常、不注意または従業員の知識不足が原因で、このようなエラーが発生することはほとんどありません。 この場合、追加の完全バックアップを行うだけです。



同様のチェックを設定したい人のためのヒント：

1. 回復操作はリソースを大量に消費するため、数時間後に実行されるスクリプトに含める必要があります。
   
   
   
   
2. 一時的なテストデータベースを作成してバックアップを復元するには、少なくともポリシー内の最大のデータベース+ボリュームの10％に等しいディスク領域の予約が必要です。
   
   
   
   
3. 大規模なデータベースの回復にはかなりの時間がかかる場合があります。 指定されたサービスウィンドウで操作が完了するかどうか不明な場合は、検証タスクを有効にしないでください。
   
   
   
   
4. スクリプトにタスクを正しく配置します。 リカバリは現在の時点で実行されることに注意してください。 タスクの実行時。 たとえば、フルバックアップを作成した直後にバックアップをチェックするタスクを配置すると、最後のバックアップのみがテストされます。 現在の時点でベースを復元するのに十分です。
   
   
   
   
5. すべてのポリシーデータベースのバックアップをチェックするのに十分なサービスウィンドウがない場合、特定のデータベースのみのバックアップをチェックできます。 または、曜日ごとにタスクを配布します。 たとえば、今夜は拠点AとBのバックアップをチェックし、明日は拠点CとDのバックアップをチェックします。
   
   
   
   
6. ネットワークフォルダーにバックアップすることはお勧めしません。 リカバリ中に、ネットワーク経由でバックアップファイルを「ドラッグ」する必要があり、リカバリ時間が大幅に増加します。 ネットワークフォルダーへの毎日のコピーを使用して、ローカルドライブへのバックアップの作成を構成する方が適切です。

最新のデータベースコピーの自動サポート

このプログラムを使用すると、データベースのコピーを最新の状態に保つことができます。 たとえば、1C開発者の場合、テストベースを毎晩更新できます。 これを行うには、組み込みの「アーチから復元」に似たタスクを作成します。 一時データベースにコピーします。」 タスクでは、バックアップのソースデータベースと、リカバリが実行されるベースを指定する必要があります。 そして、タスクを夜間シナリオに配置します。 次の図は、AccountingデータベースのバックアップをAccountingCopyデータベースに復元するタスクを示しています。 また、SQL ServerにAccountingCopyデータベースがない場合は、自動的に作成されます。











回復手順中、AccountingCopyデータベースはすべてのユーザー接続が切断されたシングルユーザーモードになります。

バックアップファイルをコピーする

ビデオでは、プログラムがネットワークまたはローカルフォルダーへのバックアップの追加コピーを構成する方法を示しました。 バックアップをコピーすると、ファイル、ディスク、またはサーバー全体の損傷をある程度保証できます。 仮想SQL Serverの場合、バックアップを実際の物理ディスクにコピーすると、仮想マシン全体の回復を待たずに、1つ以上のデータベースをすばやく復元できます。



以下では、QMBを使用してバックアップをコピーするいくつかの機能に焦点を当てたいと思います。

1. コピーの頻度は、「アーカイブコピーのコピー」タスクを含むスクリプトのスケジュールによって決まります。 タスクは、1つ以上のシナリオに配置できます。
   
   
   
   
2. 新規および変更されたバックアップファイルのみがコピーされます-これにより、ネットワークの負荷が軽減され、頻繁なコピーが可能になります。 たとえば、トランザクションログの新しいバックアップを作成した後、毎回コピーできます。
   
   
   
   
3. ネットワークフォルダの場合、ファイルの保存期間を設定できます。 したがって、バックアップは、たとえば1週間はローカルのSQL Serverディスクに、1か月はネットワークフォルダーに保存できます。
   
   
   
   
4. 選択したポリシーデータベースに対してのみバックアップコピーを構成することができます。

データベース回復

標準のSSMSコンソールでデータベースを復元できます。 ただし、QMBには、よりシンプルな設定のアナログがあります。







[アーカイブコピーから復元]コマンドを使用すると、次のことができます。

• バックアップチェーンの自動選択を使用して、指定した時点でバックアップからデータベースを復元します（バックアップが同じSQL Serverで作成された場合）。
  
  
  
  
• 完全バックアップを使用して、ファイルからデータベースを復元します。
  
  
  
  
• あるデータベースのバックアップを、新しいデータベースを含む別のデータベースに復元します。
  
  
  
  
• 復元後、データベースの整合性チェックを実行します。
  
  

メールアラート

SSMSで電子メールアラートを使用したことがある場合、データベースメールコンポーネントからのメッセージには最小限の情報が含まれていることをご存じでしょう。 たとえば、エラーの場合、同様のメッセージが送信されます。

実行されたタスク：

「作業データベース。ネストされたPlan_1」は2015年5月19日17:00:00に開始しました

期間：

0時間0分5秒

条件：

エラー

メッセージ：

タスクを完了できませんでした。 タスクは、スケジュール9（MaintenancePlan）によって開始されました。 実行された最後のステップはステップ1（トランザクションログバックアップ）でした。

このようなメッセージにより、エラーが発生したことを理解できますが、どのエラーを理解する（およびその重大度を評価する）ために、サーバーログを調べる必要があります。 さらに、データベースメールはエラーが発生するたびに通知を送信します。メールに同じ種類のメッセージが何百もある場合、状況は排除されません。



データベースメールとは異なり、QMBは通知でエラーテキストの最初の15行を送信します。 通常、これは理由を理解し、必要なアクションを実行するのに十分です。 完全なログは、プログラムメンテナンスログで確認できます。 エラーメッセージの例：

シナリオ「中規模OLTPデータベースのリソース集中型タスク（毎晩）」は、サーバー「Srv05」でエラーが発生して実行されました。



スクリプト開始：06/06/2015 1:00

作業終了：2015年6月6日1:29

期間：00:29:28



合計タスク：7

完了したタスク：7

エラーあり：1



1.タスク 'アーチからの復元。 一時データベースへのコピーとそれに続く整合性チェック '（データベース：IPGor）

メッセージ：4305、レベル：16、状態：1、行：21

このバックアップデータセットのログは、LSN 5235000000291100001で始まります。これはまだデータベースに適用できません。 LSN 5228000000281600001を含む以前のログバックアップを復元できます。



メッセージ：3013、レベル：16、状態：1、行：21

RESTORE LOGはエラーで中止されました。



メッセージ：50000、レベル：16、状態：1、行：119

回復プロセス中にエラーが発生しました。

また、たとえばエラーが定期的に繰り返される場合など、大量の同一の文字を送信しないようにするメカニズムもあります。

ライセンスポリシーとコスト

プログラムのフルバージョンは、 当社のWebサイトからダウンロードできます。 試用期間（SQL Serverの最初の登録日から30日）があります。その後、各SQL Serverのライセンスを購入する必要があります。 ただし、QMBを使用すると、 SQL Expressを使用してデータベースを（いくつかの制限付きで） 無料で保守できます 。 また、SQL Expressには、1,560ルーブルの安価な商用ライセンスがあります。 現在、ロシア企業のプロフェッショナルライセンスの費用は7,100ルーブルです。 仕様と価格はこちらをご覧ください 。



ライセンスは永久的であり、時間に制限はありません。 必要に応じて、ライセンスをサーバー間で簡単に転送できます。



サポート

商用ライセンスを購入した瞬間から、プログラムの更新を1年間ロールアップできます;以降の更新をインストールするには、サポートを延長する必要があります。

おわりに

サードパーティのSQL Serverプログラムは排他的に「クランチ」と見なされるという意見に出会うことがあります。 おそらく、そのようなプログラムを使用して構成されたバックアップまたはサービスは、定義上、通常のSQL Serverエージェントを使用するよりも悪いことです。 この場合、SQL ServerはTransact SQLステートメントのみを理解し、ステートメントの送信者（SQL Serverエージェントまたは別のプログラム）を気にしないことを説明する必要があります。 たとえば、データベースの整合性をチェックするには、 DBCC CHECKDBコマンドを送信し、バックアップ-BACKUP DATABASEを作成する必要があります。 明らかに、このコマンドの送信者に関係なく、結果は常に同じになります。



このレビューがお役に立てば幸いです。 パフォーマンスの低下やSQL Serverの突然のシャットダウンはITサービス全体の評判を傷つけますが、ほとんどの場合、データの損失はさらに深刻な結果につながります。 サービスプランを作成したが、その信頼性がわからない場合は、時限爆弾に座っています。その結果を解くよりも、事前に緊急事態を防ぐことを強くお勧めします。



ご清聴ありがとうございました。コメントでご質問にお答えします。