概要
DBのリプレースなどでデータを別DBに移行する場合、様々な戦略が考えられます。
もっともシンプルな戦略としては、データのバックアップの取得・復元や、データのエクスポート・インポート機能によって移行する方法ですが、DBに対するあらゆる更新処理を停止してデータの静止点を作る必要があります。
その場合なんらかの方法で確実にデータが更新されない状況を作る必要があります。単にアプリケーションの処理を停止するだけでは止め忘れなど意図しない更新がかかってしまうリスクは拭えません。
SQLServerの機能としてRead Only(読み取り専用)モードがあります。
これを利用することで、SQLServerの機能として確実に静止点を取ることができ、安心してデータ移行やメンテナンス作業を行うことができます。
読み取り専用モードへの変更方法
SQLServer公式のGUIツールであるSSMSまたはSQLコマンドから実行することができますが、SSMSのGUIから操作することをお勧めします。
読み取り専用に切り替えるためには、そのDBに対してすべてのセッションを一時的に全てkillする必要があり、１つでもクエリが実行中で時間がかかっていると、ロックが取れず読み取り専用に切り替えることができません。
しかしGUIから操作する場合、内部的にセッションをすべて切ってくれるため確実に切り替えることができます。
GUIから行う場合
データベース > プロパティ > オプション から「読み取り専用データベース」
FalseからTrueに切り替え
OKを押すと接続を切るかの確認が出るので「はい」を押すと開始します。
コマンドで行う場合
SSMS上での操作をスクリプト生成しましたが、これにはセッションのKILLは含まれていません。
-- To Read Only
use [master];
GO
USE [master]
GO
ALTER DATABASE [******] SET READ_ONLY WITH NO_WAIT
GO
GO
ある程度アクセスのあるDBであれば以下のようにエラーになってしまいます
SET LOCK_TIMEOUTを付けることでロック解放待ち時間を制限できるかと思いましたが、
ALTER DATABASEでは指定できないので使えません。
CREATE DATABASE、ALTER DATABASE、および DROP DATABASE ステートメントでは、SET LOCK_TIMEOUT の設定は無視されます。
注意点：切り替え時のダウンタイムについて
モードを切り替える際にDBに対してダウンタイムが一時的に発生します。
ダウンタイムが発生すると、参照処理に影響が出るため、このダウンタイムは注意しなければなりません。
あるDBリプレース時、検証環境で5~7秒程度瞬断することを確認していましたが、本番環境の作業では3分ほど長期間のダウンタイムが発生してしまいました。
原因は、内部のバッファキャッシュのサイズの違いによるものでした。
読み取り専用モードに切り替える際、対象DBのバッファキャッシュをクリアする処理が内部的に実行されます。
本番DBなど検証環境よりも大量のメモリを確保しており、数百GB単位でバッファプールにデータが乗っている場合注意が必要です。
また、サーバーの負荷によっても所要時間が変わりそうなので注意してください。
まとめ
SQL Serverの読み取り専用モードは便利な機能ですが、以下の注意点に気をつけましょう。
- 全てのセッションを確実に切るためSSMSから行う
- ダウンタイムが発生する
- ダウンタイム時間はバッファキャッシュのクリア時間に比例する
検証環境では本番同等のデータ量で検証していましたが、メモリ量や実際のワークロード下での検証はできていませんでした。
ダウンタイムが数秒でも発生する作業は、例え数秒だとしても影響があるとないでは大きく違います。
検証環境では数秒程度の影響でも、その所要時間は何によって決まるのか、原因は何かを事前に確認しておき本番環境でもその時間で終わることを確認できるとよかったです。あらためて良い教訓になりました。
リプレースやメンテナンス時には非常に便利な機能なので、皆さんも読み取り専用機能を使ってみてください！