SE の雑記

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

RDS for SQL Server の Multi-AZ の仕組みを調べてみる

leave a comment

先日、RDS の SQL Server で Multi-AZ の機能がサポートされました。
【AWS発表】Amazon RDS for SQL ServerでもMulti-AZ機能を利用可能に
Amazon RDS for Microsoft SQL Server
Multi-AZ 配置

構築方法などは、【速報】Amazon RDS for SQL ServerがMulti-AZをサポートしたのでやってみた が参考になります。

どのような仕組みで実装されているのかが気になったので試してみました。

■Mult-AZ の設定


Multi-AZ を使用した SQL Server の作成は簡単で、[Multi-AZ Deployment] を [Yes (Mirroring)] にして、RDS のインスタンスを作成するだけです。
この投稿を書いている時点では、[US East (N. Virginia)] [US West (Oregon)] [EU (Ireland)] でのみ使用することができます。
image

image

インスタンスの作成まで 1 時間程度かかりますので気長に待ちます。
image

作成が完了すると以下のような Multi-AZ が設定された SQL Server のインスタンスが作成されます。
image

 

■構成を確認してみる


それでは構成を確認してみたいと思います。

Multi-AZ を設定した SQL Server では接続のポイントは単一となるようです。
ポータルで確認をしたエンドポイントの情報をしてインスタンスに接続をすると、プリンシパルのサーバーに接続がされます。

RDS の Multi-AZ はデータベースミラーリングを使用して構築されています。
データベースミラーリングですが、Standard / Enterprise Edition で使用できる機能となります。
# BI Edition でも使用できますが今回は省略で。

ミラーリングには、

  • 同期モード
  • 非同期モード

があり、Multi-AZ では同期モードが使用されているようです。
# Standard では 同期モードしか使用できない というのもありますが。

また、ミラーリング用の監視サーバー (Witness) も使用されているようで、自動フェールオーバー可能な同期モードで構築が行われているようです。

それでは構成を sys.database_mirroring から確認してみたいと思います。

select
	@@servername AS server_name
	, DB_NAME(database_id) AS db_name
	, mirroring_state_desc
	, mirroring_role_desc
	, mirroring_safety_level_desc
	, mirroring_partner_name
	, mirroring_partner_instance
	, mirroring_witness_name
from 
	sys.database_mirroring

 

image

partner と、witness が設定されており、ミラーリングの保護レベルとしては FULL (同期モード) が使用されていることが確認できます。

ミラーリングポートとしては 1120 が使用されているようですね。

つぎに、ミラーリングで使用しているエンドポイントの設定を sys.database_mirroring_endpoints から確認してみます。

select 
	name
	, connection_auth_desc
	, encryption_algorithm_desc 
from 
	sys.database_mirroring_endpoints

 

image

ミラーリングのエンドポイントでは証明書が使用されているのが確認できます。

RDS for SQL Server では SQL Server のサービスはドメインアカウントではなく、ローカルアカウントを使用して起動しているようです。

そのため、エンドポイント間の接続にはユーザーアカウントではなく証明書を使用した接続を利用しているようです。

インスタンスのフェールオーバーですが [Instance Action] の [Reboot] から実行することができます。

Multi-AZ を設定しているインスタンスでは、[Reboot With Failover] を設定することができ、これによりプリンシパルとミラーを手動で切り替えることができます。

image

データベースミラーリングを設定しているサーバーに接続をしている場合、接続文字列にフェールオーバーパートナーを設定し、障害が発生した場合に接続先を自動的に切り替えるのが一般的かと思います。

SQL Server のデータベース ミラーリング

RDS for SQL Server の接続には、エンドポイントの情報のみが公開されており、プリンシパルとミラーを意識せずに接続をすることになり、フェールオーバーをすると自動的に接続先が切り替わります。

この仕組みですが、DNS を用いて実施しているようです。

現状の RDS のエンドポイントを NSLOOKUP した結果が、以下になります。

image

EC2 のインスタンスとして、[ec2-54-213-108-114.us-west-2.compute.amazonaws.com] が使用されていることが確認できます。

それでは、フェールオーバーをしてみたいと思います。

フェールオーバーをすることで、DNS の CNAME が [ec2-54-201-30-115.us-west-2.compute.amazonaws.com] に切り替わったことが確認できます。

image

フェールオーバーをすることで、今までミラーだったサーバーがプリンシパルに昇格し、そのタイミングで DNS の CNAME を切り替えて接続先を DNS ベースで透過的に切り替えているようです。

DNS のキャッシュや、既に接続しているコネクションが正常に切り替わっているかの検証はきちんと実施したほうが良さそうですね。

ec2 ~ の DNS 名がわかっている場合には、直接接続することもできてしまうようです。

# 管理を考慮すると、EC2 インスタンス名も把握しておいたほうが良さそうな気がするのですがどうなのでしょう。

以下は、直接接続をした場合の SSMS の表示内容ですが、各インスタンスがプリンシパルとミラーの関係になっていることが確認できます。

image

データベースミラーリングでは、master/model/msdb/tempdb といったシステムデータベースはミラーされません。

そこで、通常運用で使用する以下のオブジェクトがミラーに連携されるかを確認してみました。

ログイン ミラーに連携される
SQL Server Agent ジョブ 連携されない

 

RDS for SQL Server には、いくつかのトリガーが設定されています。

image

データベースとログインの作成に関してはトリガーで制御 (おそらく、ミラーへの情報同期の機構) されているようですが、SQL Server Agent ジョブについては同期が行われないようですので、定期的なジョブ実行をしたい場合には注意したほうが良さそうですね。

データベースについては作成するとミラーリング対象のデータベースとして設定が行われます。

ただし、設定がされるまではタイムラグがあるようです。

以下は作成直後の状態ですが、この状態ではミラーリングは行われていません。

image

少し時間を置くと同期が完了します。

image

RDS では構成の維持管理のために、 [RdsAdminService] というサービスがバックグラウンドで動作しているようなのですが、ミラーリングの設定はこのサービスが実行しているようです。

データベースを作成すると以下のようなクエリがこのサービスから実行されています。

BACKUP DATABASE [RDSTEST2] TO DISK = 'D:RDSDBDATABACKUPb5d954b4-aaa9-4537-996b-0b5f82635d4d.1402357569.database_backup' WITH FORMAT

DECLARE @after_backup_epoch BIGINT
SET @after_backup_epoch = CAST('1402357570' AS BIGINT)
BEGIN TRANSACTION
INSERT INTO [rdsadmin].[dbo].[createdb_activity]([family_guid], [database_name], [properly_created_time]) 
SELECT 'b5d954b4-aaa9-4537-996b-0b5f82635d4d', N'RDSTEST2', DATEADD(second, @after_backup_epoch, '1970-01-01 00:00:00') AS properly_created
INSERT INTO [rdsadmin].[dbo].[done_initial_db_backups]([family_guid]) 
VALUES ('b5d954b4-aaa9-4537-996b-0b5f82635d4d')
INSERT INTO rdsadmin.dbo.db_backups (family_guid, before_backup_epoch, after_backup_epoch, database_name, database_backup) 
  SELECT 'b5d954b4-aaa9-4537-996b-0b5f82635d4d', '1402357569', '1402357570', N'RDSTEST2', bulkcolumn FROM OPENROWSET(BULK 'D:RDSDBDATABACKUPb5d954b4-aaa9-4537-996b-0b5f82635d4d.1402357569.database_backup', SINGLE_BLOB) AS new_backup;
COMMIT TRANSACTION

declare @p1 int
set @p1=104
exec sp_prepexec @p1 output,N'@mirroring_login_0 nvarchar(45),@mirroring_login_1 nvarchar(45),@mirroring_login_2 nvarchar(45)',N'SELECT 
            CHECKSUM_AGG(
                CONVERT(INT,
                    HASHBYTES(''MD5'', 
                        CONVERT(varchar(11),
                            BINARY_CHECKSUM(
                                p.name, p.sid, p.default_database_name, p.default_language_name, p.is_disabled, 
                                p.is_policy_checked, p.is_expiration_checked, p.password_hash, perms.permission_name, 
                                perms.state_desc, grantors.name
                            )
                        )
                    )
                )
            )  
            FROM sys.sql_logins p 
            LEFT OUTER JOIN sys.server_permissions perms ON p.principal_id = perms.grantee_principal_id 
            LEFT OUTER JOIN sys.server_principals grantors ON perms.grantor_principal_id = grantors.principal_id
            WHERE p.type = ''S'' AND p.name NOT IN (''##MS_PolicyEventProcessingLogin##'', ''##MS_PolicyTsqlExecutionLogin##'',
            @mirroring_login_0, @mirroring_login_1, @mirroring_login_2)
            UNION ALL
            SELECT
            CHECKSUM_AGG(
                CONVERT(INT,
                    HASHBYTES(''MD5'', 
                        CONVERT(varchar(11),
                            BINARY_CHECKSUM(
                                p.name, role_principal.name
                            )
                        )
                    )
                )
            ) 
            FROM sys.sql_logins p
            left outer join sys.server_role_members roles on p.principal_id = roles.member_principal_id
            left outer join sys.server_principals role_principal on roles.role_principal_id = role_principal.principal_id
            where p.type = ''S'' and role_principal.name is not null',@mirroring_login_0=N'AMAZONAGHLRIKB_login',@mirroring_login_1=N'AMAZONACHOVKR1_login',@mirroring_login_2=N'AMAZONA24V00NU_login'
select @p1



DECLARE @num_days_to_retain INT
DECLARE @earliest_retain_datetime DATETIME
DECLARE @earliest_retain_epoch BIGINT

SET @num_days_to_retain = CAST('36' AS INT)
IF @num_days_to_retain < 1 BEGIN
    RAISERROR('NumDaysToRetain is %d but must be at least 1', 16, 0, @num_days_to_retain) WITH LOG
END

SET @earliest_retain_datetime = DATEADD(day, 0 - @num_days_to_retain, SYSUTCDATETIME())
SET @earliest_retain_epoch = DATEDIFF(second, '1970-01-01 00:00:00', @earliest_retain_datetime)
DELETE FROM rdsadmin.dbo.log_backup_manifest
WHERE file_epoch < @earliest_retain_epoch
DELETE FROM rdsadmin.dbo.createdb_activity
WHERE properly_created_time < @earliest_retain_datetime
DELETE FROM rdsadmin.dbo.db_backups
WHERE family_guid != 'ccfa0f19-df9b-4f12-9ce2-8d6f3408add2'
  and after_backup_epoch < @earliest_retain_epoch
exec msdb..sp_delete_backuphistory @earliest_retain_datetime
DELETE FROM rdsadmin..db_mappings WHERE family_guid NOT IN
    (SELECT family_guid FROM sys.databases sysdb 
    JOIN rdsadmin..db_mappings mappings 
    ON sysdb.name = mappings.database_name)
AND created_time < GETDATE() - 15;


declare @request_action as varchar(50) = 'SET_WITNESS_SERVER';
IF @request_action = 'SET_PARTNER_SERVER'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER = 'TCP://rdswitness:1120';
        IF (select mirroring_state from sys.database_mirroring where database_id = (select database_id from sys.databases where name = 'RDSTEST2')) IS NOT NULL
        BEGIN
            ALTER DATABASE [RDSTEST2] SET PARTNER TIMEOUT 30;
        END
    END
ELSE IF @request_action = 'SET_PARTNER_FAILOVER'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER FAILOVER;
    END
ELSE IF @request_action = 'SET_PARTNER_FORCE_SERVICE_ALLOW_DATA_LOSS'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
    END
ELSE IF @request_action = 'SET_PARTNER_OFF'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER OFF;
    END
ELSE IF @request_action = 'SET_PARTNER_RESUME'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER RESUME;
    END
ELSE IF @request_action = 'SET_PARTNER_SAFETY_FULL'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER SAFETY FULL;
    END
ELSE IF @request_action = 'SET_PARTNER_SAFETY_OFF'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER SAFETY OFF;
    END
ELSE IF @request_action = 'SET_PARTNER_SUSPEND'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER SUSPEND;
    END
ELSE IF @request_action = 'SET_PARTNER_TIMEOUT'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER TIMEOUT 30;
    END
ELSE IF @request_action = 'SET_WITNESS_SERVER'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET WITNESS = 'TCP://rdswitness:1120';
    END
ELSE IF @request_action = 'SET_WITNESS_OFF'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET WITNESS OFF;
    END
ELSE
    BEGIN
        RAISERROR ('Error - unknown request action %s', 16, 1, @request_action);
    END

以下のような

  1. CREATE DATABASE されたタイミングでイベントをアクティビティとして登録
  2. バックアップを取得
  3. ミラーサーバーにリストア
  4. 作成したデータベースをミラーリング対象として設定

一連の処理を実行しているのかと思います。

ミラーリングを設定したデータベースは削除しようとすると以下のようなエラーとなります。

image

一度ミラーを以下のようなクエリで解除し、同期を外した状態にすることで削除が可能となります。

# PARTNER OFF にして、少し待っていると再度同期設定がされてしまったので、ALTER → DROP を一連の流れで実施する必要がありそうですね。API とかあるのでしょうか??

ALTER DATABASE RDSTEST2 SET PARTNER OFF
DROP DATABASE RDSTEST2

image

DROP DATABASE もトリガーでキャッチされているようで、DROP 直後はミラーでもデータベースは残っていますが、少し待っていると削除が行われます。

imageimage

ドキュメントをきちんと読めていないので手探りで検証してみましたが、なかなか面白い作りですね。

ざっくりとした構成は以下のようになるようです。

image

SQL Server の情報としては同期モードのミラーリングについての情報を参考にするとよさそうですね。

Written by masayuki.ozawa

6月 10th, 2014 at 6:21 am

Posted in AWS,SQL Server

Tagged with , ,

Leave a Reply

*