AlwaysOn 可用性グループと TDE (透過的なデータ暗号化) の組み合わせ方法について少しまとめてみたいと思います。
BOL としては 暗号化されたデータベースと AlwaysOn 可用性グループ (SQL Server) に記載されているのですが、これだと少しわかりずらいので。
データベースが暗号化されているか、データベース暗号化キー (DEK) を含んでいる場合、新しい可用性グループ ウィザード または 可用性グループへのデータベース追加ウィザード を使用してそのデータベースを可用性グループに追加することはできません。
とありますが、これは可用性グループのウィザードを使用して設定ができないということを表しており、AlwaysOn 可用性グループと TDE を組み合わせることは可能です。
細かな手順については Encrypting Your SQL Server 2012 AlwaysOn Availability Databases がとても参考になります。
手順としては以下のようになります。
- プライマリサーバーでデータベースマスターキーを作成
CREATE MASTER KEY (Transact-SQL) - 作成したデータベースマスターキーをバックアップ
BACKUP MASTER KEY (Transact-SQL) - 暗号化用の証明書を作成
CREATE CERTIFICATE (Transact-SQL) - 作成した暗号化用の証明書+プライベートキーのバックアップを作成
(この証明書をセカンダリサーバーでも使用する)
BACKUP CERTIFICATE (Transact-SQL) - データベース暗号化キーを作成
CREATE DATABASE ENCRYPTION KEY (Transact-SQL) - 可用性グループに含めるデータベースを暗号化
- セカンダリサーバーでデータベースマスターキーを作成
- 作成したデータベースマスターキーをバックアップ
- プライマリサーバーでバックアップを取得した暗号化用の証明書を使用して証明書をインポート
CREATE CERTIFICATE を FROM FILE で実行 - AlwaysOn 可用性グループをスクリプト (T-SQL) ベースで作成
基本的な流れについては 別の SQL Server への TDE で保護されたデータベースの移動 に近いと思います。
それでは各操作のクエリを見ていきたいと思います。
Contents
■プライマリサーバーの作業
まずは TDE を使用するため、データベースマスターキーを作成します。
データベースマスターキーを作成したあとでないと証明書を作成することができません。
作成したデータベースマスターキーはバックアップを取得して保存しておきます。
USE master GO CREATE MASTER KEY ENCRYPTION BY Password='PrimaryP@ss' GO BACKUP MASTER KEY TO FILE='C:tempDEK_Primary_MasterKey' ENCRYPTION BY Password = 'Primary_DEKP@ss' GO
データベースマスターキーを作成したら、証明書を作成し、バックアップを取得します。
バックアップ時には証明書 (cer) とプライベートキー (pvk) の両方を取得します。
USE master GO CREATE CERTIFICATE DEKCert WITH Subject ='DEK Certificate' GO BACKUP CERTIFICATE DEKCert TO FILE= 'C:tempDEK_Cert.cer' WITH PRIVATE KEY ( FILE = 'C:tempDEK_PrivateKey.pvk', ENCRYPTION BY PASSWORD= 'CERTP@ss' )
これで、TDE のよる暗号化の準備が整いましたので、データベースを暗号化します。
今回は SyncDB01 というデータベースを暗号化しています。
USE SyncDB01 GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE DEKCert GO ALTER DATABASE SyncDB01 SET ENCRYPTION ON GO
これでプライマリサーバーでの暗号化の準備は終了です。
次にセカンダリサーバーの作業を行います。
■セカンダリサーバーの作業
最初にセカンダリサーバーでもデータベースマスターキーを作成し、バックアップを取得します。
USE master GO CREATE MASTER KEY ENCRYPTION BY Password='SecondaryP@ss' GO BACKUP MASTER KEY TO FILE='C:tempDEK_Secondary_MasterKey' ENCRYPTION BY Password = 'Secondary_DEKP@ss' GO
次にプライマリでバックアップした cer/pvk を使用して証明書を作成します。
# セカンダリサーバーからアクセスできる場所にファイルを配置しておく必要があります。
USE master GO CREATE CERTIFICATE DEKCert FROM FILE= 'C:tempDEK_Cert.cer' WITH PRIVATE KEY ( FILE = 'C:tempDEK_PrivateKey.pvk', DECRYPTION BY PASSWORD= 'CERTP@ss' )
以上で、プライマリ / セカンダリでの TDE の設定準備は完了です。
この後に AlwaysOn 可用性グループの設定をスクリプトベースで実施します。
なお、この状態で AlwaysOn 可用性グループをウィザードで設定しようとすると「データベース暗号がキーが含まれています」となり、ウィザードでは設定をすることができません。。
TDE を使用しない状態で、AlwaysOn 可用性グループのウィザードを実行して、スクリプトを作成しておき、そのスクリプトをベースにして、TDE 設定後に可用性グループを構築するのが楽かと思います。
AlwaysOn 可用性グループのウィザードでは以下のようなスクリプトを作成することができます。
# クエリエディタを SQLCMD モードにして実行する必要があります。
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. :Connect ALWAYSON-SQL-01 IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0 BEGIN ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED END GO use [master] GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ALWAYSONSQL2014-Service-User] GO :Connect ALWAYSON-SQL-02 IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0 BEGIN ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED END GO use [master] GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ALWAYSONSQL2014-Service-User] GO :Connect ALWAYSON-SQL-01 IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health') BEGIN ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON); END IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health') BEGIN ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START; END GO :Connect ALWAYSON-SQL-02 IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health') BEGIN ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON); END IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health') BEGIN ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START; END GO :Connect ALWAYSON-SQL-01 USE [master] GO CREATE AVAILABILITY GROUP [AlwaysOn-AG] WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY) FOR DATABASE [SyncDB01] REPLICA ON N'ALWAYSON-SQL-01' WITH (ENDPOINT_URL = N'TCP://AlwaysOn-SQL-01.AlwaysOn.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)), N'ALWAYSON-SQL-02' WITH (ENDPOINT_URL = N'TCP://AlwaysOn-SQL-02.AlwaysOn.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)); GO :Connect ALWAYSON-SQL-01 USE [master] GO ALTER AVAILABILITY GROUP [AlwaysOn-AG] ADD LISTENER N'AlwaysOn-LN' ( WITH DHCP ON (N'10.0.0.0', N'255.0.0.0' ) , PORT=1433); GO :Connect ALWAYSON-SQL-02 ALTER AVAILABILITY GROUP [AlwaysOn-AG] JOIN; GO :Connect ALWAYSON-SQL-01 BACKUP DATABASE [SyncDB01] TO DISK = N'\ALWAYSON-AD-01QuorumSyncDB01.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5 GO :Connect ALWAYSON-SQL-02 RESTORE DATABASE [SyncDB01] FROM DISK = N'\ALWAYSON-AD-01QuorumSyncDB01.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5 GO :Connect ALWAYSON-SQL-01 BACKUP LOG [SyncDB01] TO DISK = N'\ALWAYSON-AD-01QuorumSyncDB01_20141222054852.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5 GO :Connect ALWAYSON-SQL-02 RESTORE LOG [SyncDB01] FROM DISK = N'\ALWAYSON-AD-01QuorumSyncDB01_20141222054852.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5 GO :Connect ALWAYSON-SQL-02 -- Wait for the replica to start communicating begin try declare @conn bit declare @count int declare @replica_id uniqueidentifier declare @group_id uniqueidentifier set @conn = 0 set @count = 30 -- wait for 5 minutes if (serverproperty('IsHadrEnabled') = 1) and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0) and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0) begin select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'AlwaysOn-AG' select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id while @conn <> 1 and @count > 0 begin set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1) if @conn = 1 begin -- exit loop when the replica is connected, or if the query cannot find the replica status break end waitfor delay '00:00:10' set @count = @count - 1 end end end try begin catch -- If the wait loop fails, do not stop execution of the alter database statement end catch ALTER DATABASE [SyncDB01] SET HADR AVAILABILITY GROUP = [AlwaysOn-AG]; GO GO
以上で、可用性グループと TDE を組み合わせた環境を作成することができます。
- プライマリとセカンダリで同一の証明書を使用する
- AlwaysOn 可用性グループの設定はウィザードではなくスクリプトで行う
この 2 点を押さえておけばよさそうですね。