SE の雑記

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

AlwaysOn 可用性グループと TDE の組み合わせについて

leave a comment

AlwaysOn 可用性グループと TDE (透過的なデータ暗号化) の組み合わせ方法について少しまとめてみたいと思います。
BOL としては 暗号化されたデータベースと AlwaysOn 可用性グループ (SQL Server) に記載されているのですが、これだと少しわかりずらいので。

データベースが暗号化されているか、データベース暗号化キー (DEK) を含んでいる場合、新しい可用性グループ ウィザード または 可用性グループへのデータベース追加ウィザード を使用してそのデータベースを可用性グループに追加することはできません。

とありますが、これは可用性グループのウィザードを使用して設定ができないということを表しており、AlwaysOn 可用性グループと TDE を組み合わせることは可能です。

細かな手順については Encrypting Your SQL Server 2012 AlwaysOn Availability Databases がとても参考になります。

手順としては以下のようになります。

  1. プライマリサーバーでデータベースマスターキーを作成
    CREATE MASTER KEY (Transact-SQL)
  2. 作成したデータベースマスターキーをバックアップ
    BACKUP MASTER KEY (Transact-SQL)
  3. 暗号化用の証明書を作成
    CREATE CERTIFICATE (Transact-SQL)
  4. 作成した暗号化用の証明書+プライベートキーのバックアップを作成
    (この証明書をセカンダリサーバーでも使用する)
    BACKUP CERTIFICATE (Transact-SQL)
  5. データベース暗号化キーを作成
    CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
  6. 可用性グループに含めるデータベースを暗号化
  7. セカンダリサーバーでデータベースマスターキーを作成
  8. 作成したデータベースマスターキーをバックアップ
  9. プライマリサーバーでバックアップを取得した暗号化用の証明書を使用して証明書をインポート
    CREATE CERTIFICATE を FROM FILE で実行
  10. AlwaysOn 可用性グループをスクリプト (T-SQL) ベースで作成

基本的な流れについては 別の SQL Server への TDE で保護されたデータベースの移動 に近いと思います。

それでは各操作のクエリを見ていきたいと思います。

 

■プライマリサーバーの作業


まずは 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 可用性グループをウィザードで設定しようとすると「データベース暗号がキーが含まれています」となり、ウィザードでは設定をすることができません。。

image

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 点を押さえておけばよさそうですね。

Written by masayuki.ozawa

12月 23rd, 2014 at 11:13 pm

Posted in SQL Server

Tagged with

Leave a Reply

*