SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Amazon RDS for SQL Server の構成をしらべてみる

without comments

今までの仕事ではオンプレミスの環境ばかり触っていたのですが、最近はクラウド環境を使う機会も出てきました。
Azure の SQL Database は少しは触っていたのですが、AWS の RDS (Microsoft SQL Server 用 Amazon RDS) は触ったことがなかったので、少しずつ触るようにしています。
SQL Server のエンジニアとしては RDS でどのような環境で SQL Server が構成されているのか気になったので調べてみました。
今回は Micro インスタンスを無償枠で使用していますのでインスタンスのサイズによっては構成が違うかもしれません。
細かな構成などは Working with Microsoft SQL Server on Amazon RDS を見るとよいかと思います。
また、RDS と似たようなサービスとしては Azure の SQL Database があります。
SQL Database と構成を比較してみたいときは
Windows Azure SQL データベースにおける高可用性と災害復旧
Windows Azure SQL データベースにおけるビジネス継続性
Windows Azure SQL Database Performance and Elasticity Guide
が参考になります。
Windows Azure SQL Database Performance and Elasticity Guide は SQL Database のハードウェアスペックに関することが一文書かれてる貴重な文書だと思います。

■SQL Server の実行環境


Windows Azure の SQL Database は以下のような構成になっています。
image
RDS はどうなっているかというと以下のような構成になっていそうです。
image
SQL Database は物理ハードウェア上にインストールされていた SQL Server に複数のユーザーのデータベースを作成するという方式を使用しています。
RDS では Hypervisor 上にインストールされた Windows 上に SQL Server をインストールして、インスタンス単位でユーザーに割り当てるという方式を採用しているようです。
この確認方法ですが RDS では SQL Server の ERRORLOG を AWS 管理コンソール (Management Console) から確認することができます。
image
このログに以下の記述があります。

??2013-05-24 23:59:00.23 spid51 Microsoft SQL Server 2012 – 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
2013-05-24 23:59:00.23 spid51 (c) Microsoft Corporation.
2013-05-24 23:59:00.23 spid51 All rights reserved.
2013-05-24 23:59:00.23 spid51 Server process ID is 1696.
2013-05-24 23:59:00.27 spid51 System Manufacturer: ‘Xen‘, System Model: ‘HVM domU’.

ここから、Xen 上にインストールされた Windows Server 2008 R2 SP1 で SQL Server が実行されていることが確認できます。
EC2 上のインスタンスとして RDS for SQL Server が展開され、SQL Server 部分にのみ接続が可能というような環境となっているのかもしれないですね。
nslookup をすると RDS で使用されている [~.rds.amazonaws.com] は [~.compute.amazonaws.com] の CNAME となっているようですので。
なお、SQL Server のサーバー名は [RDSIMAGE] となっているようです。
 

■可用性の構成


可用性の構成ですが、SQL Database は物理的に 3 台に冗長化された構成がとられています。
# 3 台中 2 台でコミットできたらトランザクションを正常に終了されたと認識される仕組みだったかと。
image
RDS for SQL Server では、WSFC / Replication / Log Ship / AlwaysOn というような構成をとることができません。
# EC2 であれば可能なはずですが。
クラウドサービスとして環境の冗長構成はとられていると思いますのでこの辺は Xen の機能かストレージの機能で実装されているのかもしれないですね。
# ハードウェア障害を利用者が意識する必要はない構成になっていると思います。
 

■初期のデータベースの構成


RDS の初期のデータベースの構成ですが以下のようになっています。
image
RDS は通常の SQL Server インスタンスですので、システムデータベースの構成は変わらないですね。
特徴的なのは [rdsadmin] というユーザーデータベースでしょうか。
このデータベースは以下のようなテーブルで構成されています。
image
このデータベースにはバックアップについての管理情報や SQL Server の構成情報を変更するためのテーブルが含まれているようです。
権限がないのでこのデータベースのテーブルは内容を確認することはできません。
また、ストアドプロシージャがいくつか作成されているのですが、これらのストアドプロシージャは暗号化されており内容を確認することはできません。
tempdb に関してはデータファイルと同じドライブ上に配置がされているようですが設定は変更することができあいようですので、初期サイズやデータファイルの分割といったことはできないようです。

■SQL Server の設定


RDS では作成したログインから SQL Server の設定は権限が不足しており変更することができません。
image
変更をする場合には、AWS の管理コンソール側で実施ます。
新規に DB Parameter Group を作成し、そこで設定を変更します。
image
image
DB Parameter グループを変更することで、SQL Server の設定として反映がされるようになっています。
RDS では一部のトレースフラグの設定有無を変更できるようになっています。
管理コンソールから変更できるトレースフラグは

  • 1204
  • 1211
  • 1222
  • 1224
  • 2528
  • 4199
  • 4616
  • 6527

となっています。
RDS ではほかにも以下のようなトレースフラグが意識されているようです。

dbcc traceoff(4616, -1)
dbcc traceoff(4199, -1)
dbcc traceoff(1204, -1)
dbcc traceoff(3205, -1)
dbcc traceoff(1224, -1)
dbcc traceoff(1211, -1)
dbcc traceoff(2528, -1)
dbcc traceon(3226, -1)
dbcc traceoff(1222, -1)
dbcc traceon(7806, -1)
dbcc traceoff(6527, -1)
dbcc traceoff(3625, -1)

デフォルトの起動オプションは以下のようになっているようです。
いくつかのトレースフラグは規定で有効かされるようになっているようですね。

-d D:RDSDBDATADATAmaster.mdf
-e D:RDSDBDATALogERROR
-l D:RDSDBDATADATAmastlog.ldf
-k 20.000000
-T 3226
-T 7806

 
珍しいものとしては -k でしょうか。
このオプションですが、チェックポイントプロセスの調整をするためのスタートアップオプションになります。
FIX、I/O サブシステムが SQL Server 2005 内の IO 要求を維持するために十分に高速でない場合は、チェックポイント プロセスによって生成される I/O 要求の I/O ボトルネックがあります。
データベース チェックポイント (SQL Server)
なお、-T3226 は バックアップのログのエントリを抑制するためのオプションとなります。
 

■サーバーレベルの権限


RDS は SMMS やクエリで直接サーバーレベルの設定を変更することができません。
デフォルトで作成するログインの権限ですが以下のようなテーブルで確認することができます。

select * from sys.server_principals
select * from sys.server_permissions
select * from sys.server_role_members

 
サーバーレベルで設定されている権限としては

  • ALTER ANY CONNECTION
  • ALTER ANY LOGIN
  • ALTER ANY LINKED SERVER
  • ALTER ANY SERVER ROLE
  • ALTER SERVER STATE
  • ALTER TRACE
  • CONNECT SQL
  • CREATE ANY DATABASE
  • VIEW ANY DEFINITION
  • VIEW ANY DATABASE
  • VIEW SERVER STATE
  • setupadmin
  • processadmin

というような権限になります。
ALTER TRACE が許可されていますので RDS に SQL Server Profiler で接続することは可能です。
インスタンスに対しての sysadmin の権限は付与されていませんので、インスタンスに対して実行できる操作については制限がされています。
なお、以下の権限に関しては明示的に禁止されています。

  • ADMINISTER BULK OPERATIONS
  • ALTER ANY SERVER AUDIT
  • ALTER ANY AVAILABILITY GROUP
  • ALTER ANY CREDENTIAL
  • ALTER ANY EVENT NOTIFICATION
  • ALTER RESOURCES
  • ALTER SETTINGS
  • AUTHENTICATE SERVER
  • CREATE AVAILABILITY GROUP
  • CREATE DDL EVENT NOTIFICATION
  • CREATE ENDPOINT
  • CREATE TRACE EVENT NOTIFICATION
  • SHUTDOWN
  • EXTERNAL ACCESS ASSEMBLY
  • UNSAFE ASSEMBLY

 

■システムデータベースの権限


RDS では SQL Server エージェント (SQL Server Agent) によるジョブ実行を使用することができます。
SQL Server エージェントを使用するために msdb のデータベースロールとして、

  • SQLAgentUserRole

のロールに初期ユーザーが登録されています。
また、SSMS からデータベースを作成するために以下のストアドプロシージャに対して EXECUTE 権限が付与されています。

  • sp_purge_jobhistory
  • sp_delete_database_backuphistory

 

■ユーザーデータベースの作成


RDS ではディスクの上限内で自由にデータベースを作成することができます。
データベースのファイル数は任意に設定できますので、ファイルグループやデータファイル/ログファイルの設定は柔軟にできます。
ただし、データベース作成時にはいくつかの制限がかかっています。
RDS のインスタンスには、[rds_create_database_trigger] という [CREATE_DATABASE] 時に実行されるトリガーが設定されています。
image
このトリガーでは、データベースのオーナーの設定や、特定のフォルダ (D:rdsdbdataDATA) 以外にデータベースのファイルが配置できないように制限がされています。
# ALTER DATABASE に関してはトリガーがかかっていないので、実は DB を作成した後であれば、データファイルを別のフォルダに配置できたりするのですが。
作成したデータベースに対しては db_owner の権限が付与されていますのでデータベースレベルの設定は変更することができます。
瞬時初期化に関しては有効になっていないようですので DB 作成時の作成時間はそれなりに時間がかかるかと思います。
なおデータベースに関しては以下の権限が拒否 (DENY) されています。

  • BACKUP DATABASE
  • BACKUP LOG

バックアップに関しては RDS 側で定期的に取られているバックアップのチェーンを崩さないために、ユーザーからバックアップができないように制限されているようです。
 

■バックアップの方法


RDS はバックアップウィンドウを設定することが可能です。
image
また、DB Snapshot を取得することも可能です。
バックアップウィンドウはデータベースの完全バックアップと 5 分間隔で取得されるトランザクションログのバックアップの組み合わせについての世代数や、完全バックアップの開始時間やメンテナンスウィンドウの範囲を決めるものになります。RDS は 最短で 5 分前までのデータの状態にリストアできるように 5 分間隔でトランザクションログのバックアップが取得されています。
データベースを作成した後には自動的に以下のような形でバックアップが取得されます。
取得したバックアップの正当性を確認する方法としては、RESTORE HEADERONLY でヘッダ部の読み込みができるかで確認をしているようです。
# 初回のバックアップはローカルドライブに取得されているようです。

BACKUP DATABASE [TEST3] TO DISK = ‘D:RDSDBDATABACKUP{GUID}database_backup’ WITH FORMAT
BACKUP LOG [TEST3] TO DISK = ‘D:RDSDBDATABACKUP{GUID}.extracted_txn_log’ WITH INIT, RETAINDAYS = 36, NOSKIP, NOFORMAT
RESTORE HEADERONLY FROM DISK = ‘D:RDSDBDATABACKUP{GUID}.extracted_txn_log’

以降は 5 分間隔で BACKUP LOG / RESTORE HEADERONLY が実行されます。
このバックアップを取得しているアプリケーションですが、[RdsAdminService] となっています。
SQL Server Agent やタスクスケジューラーではなく内部で管理用のサービスが独自に動作しており、そのかんりようのサービスがバックアップも取得しているようです。
# このサービスは定期的に SELECT 1 を実行して監視か接続の確認、SQL Server の構成の変更といった動作も行っているようです。
バックアップウィンドウを有効にすると以下のようにテーブルの初期化が行われています。
rdsadmin の以下のテーブルはバックアップの制御をするためのもののようですね。

? IF EXISTS (SELECT * FROM rdsadmin.sys.tables WHERE name = ‘db_backups’)
BEGIN
delete from rdsadmin..db_backups
END

? IF EXISTS (SELECT * FROM rdsadmin.sys.tables WHERE name = ‘createdb_activity’)
BEGIN
delete from rdsadmin..createdb_activity
END

? IF EXISTS (SELECT * FROM rdsadmin.sys.tables WHERE name = ‘log_backup_manifest’)
BEGIN
delete from rdsadmin..log_backup_manifest
END

? IF EXISTS (SELECT * FROM rdsadmin.sys.tables WHERE name = ‘log_truncation_activity’)
BEGIN
delete from rdsadmin..log_truncation_activity
END

バックアップウィンドウのタイミングでは以下のようなクエリでバックアップが取得されています。

BACKUP DATABASE [test] TO VIRTUAL_DEVICE='{GUID}4′ WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024
select rtrim(physical_name),rtrim(type_desc),rtrim(state_desc), is_name_reserved from sys.master_files where DB_ID(N’test’) = database_id
RESTORE VERIFYONLY FROM VIRTUAL_DEVICE='{GUID}’ WITH SNAPSHOT,COMMIT_DIFFERENTIAL_BASE,BUFFERCOUNT=1,BLOCKSIZE=1024

RDS では最大で 1TB のデータベースを作ることができるはずです。
このような大容量のデータベースのバックアップを通常の方法で取得していると完了まではかなりの時間がかかります。
そのため、RDS では VSS Writer を使用した VSS ベースのバックアップを取得しているようです。
# バックアップウィンドウで取得されるバックアップはアプリケーション名が [Microsoft SQL Server VSS Writer] となっています。
BACKUP (Transact-SQL) の情報を見ても WITH SNAPSHOT というオプションについては記載されていません。
このオプションについてはストレージベンダーの技術情報を探す必要がありそうです。
ストレージのスナップショットと連携してデータベースのバックアップを取得するためのオプションとなりそうですね。
この辺りは大容量のデータベースをバックアップをするための工夫ですかね。
NAS環境でのSQL Server SnapShot取得について
Always On ホワイトペーパー 富士通ETERNUSストレージシステム
SQL Server Snapshot backup and restores – Part 1
SQL Server Snapshot backup and restores – Part 2
DB Snapshot でも同じ仕組みでバックアップがされているようです。
RDS では SQL Server 2008 R2 → SQL Server 2012 へのメジャーバージョンアップが行えますが、バージョンアップを実行する前にもスナップショットのバックアップが取得されるようです。
このバージョンアップですが、既存のインスタンスのインプレースアップグレードで実施されているようですね。

■性能情報の取得


管理コンソールから RDS のインスタンスの性能情報を取得することができます。
image
[sys.dm_os_wait_stats] や [sys.dm_os_performance_counters] といような DMV も使用することができますので、独自に情報を取得することもできるかと。
 
RDS for SQL Server は通常の SQL Server のインスタンスとして? SSMS で接続ができ、SQL Server Profiler による接続も可能ですので、構成や挙動を調べるのは SQL Database より楽に対応できそうですね。

Written by Masayuki.Ozawa

5月 25th, 2013 at 6:26 pm

Posted in AWS,SQL Server

Tagged with ,

Leave a Reply