Mr.Tです、こんにちは。
とりあえず、Googleで、網羅的に転がっている情報を見てみる。
検索語：SQLServer 読み取り専用
あきこさんのBlogが一番にHIT。http://blogs.sqlpassj.org/akiko/archive/2006/08/23/18453.aspx
まずは、ここで読んでみると、Oracleとの比較うんちゃらと書いてある。
で、まずは基本の部分がわからん場合は、そこからが出発点とする。
ファイルグループの指定が、必要だというが、ファイルグループとはなんぞということであれば、それを調べる。
そこで、きちんと原点に返る。Ｇｏｏｇｌｅで調べても同じソースにであえることもあるが、知識がない場合は
それが正しいものなのかどうかすら、判断できない。だから、「より正しいだろう原点」を覚えておくことがまず必要だ。
SQLServer 2005 ヘルプをF1キーで起動。
検索語：Alter DataBase
http://msdn2.microsoft.com/ja-JP/library/ms189563.aspx
ファイルグループってのは、
各データベースには、1 つのプライマリ ファイル グループがあります。プライマリ ファイル グループには、プライマリ データ ファイル、および他のファイル グループに配置されていないセカンダリ ファイルが含まれます。ユーザー定義のファイル グループを作成して、データベースの管理、データの割り当て、および配置をしやすくするために、データ ファイルをグループ化できます。
通常、GUIでつくるときは、プライマリだけになっているが、セカンダリという形で、複数のデータファイル(ｎｄｆファイル)を
作れる。
更に読むと、Blogにあるようにファイルグループに対して読み取り専用とすることができることがわかる。
ここで、重要なのは、参照という項目にある、関連項目のチェックだ。
ここにある情報では、知識をすべて補完できるわけではなく、他の情報にまたがっている場合が多い。
なので、そこのリンク先から派生していろいろと見てみること。
で、その中の、「ファイル グループのデザイン」
http://msdn2.microsoft.com/ja-JP/library/ms189126.aspx
ここにくると、「読み取り専用のファイル グループと圧縮」というのがある。かなり、近い情報と思われるので、ここをクリック。
はいはい、セカンダリグループでないと読み取り専用にできないってことがわかった。
で、最初に戻って、Alter DataBaseで読み取り専用の指定場所をしらべると
<filegroup_updatability_option>::=
ファイル グループに読み取り専用、または読み取り/書き込みのプロパティを設定します。
- READ_ONLY | READONLY
-
ファイル グループが読み取り専用であることを指定します。この中のオブジェクトを更新することはできません。プライマリ ファイル グループを読み取り専用にすることはできません。この状態を変更するには、データベースに対する排他的アクセスが必要になります。詳細については、SINGLE_USER 句を参照してください。
読み取り専用データベースのデータを変更することはできないため、次のようになります。
- システム起動時に自動復旧がスキップされます。
- データベースの縮小が不可能になります。
- 読み取り専用データベースでは、ロックは発生しません。これにより、クエリのパフォーマンスが向上することがあります。
.gif)
メモ :
キーワード READONLY は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では READONLY の使用は避け、現在 READONLY を使用しているアプリケーションは修正するようにしてください。代わりに、READ_ONLY を使用してください。
で、プライマリファイルグループでは読み取り専用にできない、とその前に書いてあったけど、排他的アクセスができれば、可能って
書いてある。というので、Single_User句を調べる。
<db_user_access_option> ::=
データベースへのユーザー アクセスを制御します。
- SINGLE_USER
-
一度に 1 人のユーザーだけがデータベースにアクセスできます。SINGLE_USER が指定されており、他のユーザーがデータベースに接続している場合には、指定したデータベースからすべてのユーザーが接続解除するまで、ALTER DATABASE ステートメントはブロックされます。この動作を無効にする場合は、WITH <termination> 句を参照してください。
このオプションを設定したユーザーがログオフしても、データベースは SINGLE_USER モードのままです。そのユーザーがログオフした時点で、他のユーザーが 1 人だけデータベースに接続できます。
データベースを SINGLE_USER に設定する前に、AUTO_UPDATE_STATISTICS_ASYNC オプションが OFF に設定されていることを確認します。ON に設定されている場合、統計の更新に使用されるバックグラウンド スレッドによってデータベースへの接続が使用されるため、シングル ユーザー モードでデータベースにアクセスできなくなります。このオプションの状態を表示するには、sys.databases カタログ ビューの is_auto_update_stats_async_on 列にクエリを実行します。このオプションが ON に設定されている場合、次の作業を行います。
- AUTO_UPDATE_STATISTICS_ASYNC を OFF に設定します。
- sys.dm_exec_background_job_queue 動的管理ビューにクエリを実行することにより、アクティブな非同期の統計ジョブがあるかどうかを確認します。
- アクティブなジョブがある場合、それらのジョブが完了するまで待機するか、KILL STATS JOB を使用して手動でジョブを終了します。
- RESTRICTED_USER
-
RESTRICTED_USER モードでは、db_owner 固定データベース ロールと、dbcreator 固定サーバー ロールおよび sysadmin 固定サーバー ロールのメンバだけが、データベースに接続できます。ただし、接続ユーザー数に制限はありません。データベースに対するすべての接続は、ALTER DATABASE ステートメントの終了句で指定したタイムフレーム内に接続解除されます。データベースが RESTRICTED_USER 状態に移行すると、許可されていないユーザーによる接続の試行は拒否されます。
- MULTI_USER
-
データベースに接続するための適切な権限を持つすべてのユーザーが許可されます。
このオプションの状態を確認するには、sys.databases カタログ ビューの user_access 列、または DATABASEPROPERTYEX 関数の UserAccess プロパティを調べてください。
さらに、SingleUserにするには、With句が必要らしいので、
WITH <termination>::=
ある状態から別の状態にデータベースが遷移する場合に、未完了のトランザクションがいつロールバックされるかを指定します。データベースにロックが存在した場合に終了句を省略すると、ALTER DATABASE ステートメントが無限に待機します。指定できる終了句は 1 つだけで、SET 句の後に指定します。
メモ :
すべてのデータベース オプションで WITH <termination> 句が使用できるわけではありません。詳細については、「解説」の「オプションの設定」にある表を参照してください。
- ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
-
指定した秒数の後、または直ちにロールバックするかどうかを指定します。
- NO_WAIT
-
要求されたデータベースの状態またはオプションの変更がすぐに完了しない場合、トランザクションがコミットまたはロールバックするのを待機せずに、要求が失敗します。
ちょっとくじけそうになるが、プライマリファイルグループを読み取り専用にするなら、色々としなくてはならないらしい。
SingleUserモードにするには、
１）AUTO_UPDATE_STATISTICS_ASYNC オプションがOFFかどうか調べる
Use master
go
select is_auto_update_stats_async_on from sys.databases
しかし、これで返ってくるのは、ON,OFFと思いきや0という数値。あわてず騒がす、sys.databasesをHELPで検索。
http://msdn2.microsoft.com/ja-jp/library/ms178534.aspx
ここで、
1 = AUTO_UPDATE_STATISTICS_ASYNC は ON です。
0 = AUTO_UPDATE_STATISTICS_ASYNC は OFF です。
というくだりを発見できるので、OKとの判断がくだせる。
さらに、WITH句を指定しないといけないのだが、トランザクションが仮にあった場合に、どうするかということなので、
すぐにロールバックしてOKなら、IMMEDIATEを利用すればいいみたいだ。
さらに、Single_Userモードにしたら、元の状態にもどさないといけないので、その元がなんなのかを調べる。
select user_access from sys.databases
ユーザー アクセス設定です。
0 = MULTI_USER が指定されています。
1 = SINGLE_USER が指定されています。
2 = RESTRICTED_USER が指定されています。
これで、MULTI_USERが指定されていることがわかったので、ようやく
alter database データベース名
set SINGLE_USER
with rollback IMMEDIATE
go
alter database データベース名
set READ_ONLY
with IMMEDIATE
go
alter database データベース名
set MULTI_USER
go
ということで、ようやくデータベースを読み取り専用にできたわけだ。
実は、alter databaseのドキュメントには、サンプルがのってあって、きちんと読み取り専用にするケースがかいてあった。
#それにしても、このT-SQLコマンドのドキュメントにある文法記述って、なんだかわかりにくいなぁと思うのは私だけ？
#サンプルみないと、うまく理解できません。