使用 TDE 進行數據庫鏡像

我需要鏡像一些數據庫並在它們上使用透明數據加密(TDE)，因為我們的數據必須在“靜止”時加密。

我在主體和鏡像上都設置了 TDE。當我設置兩個數據庫的鏡像時，我遇到的問題就出現了。由於我使用的是 TDE，我不知道通過 gui 設置鏡像的方法，所以我不得不使用 t-sql 來完成工作。

下面是我在鏡像伺服器上使用的程式碼

--Restore the full backup to the mirrored mdf and ldf
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
RESTORE DATABASE TDE
  FROM disk = '\\SERVERNAME\SQL_Stuff\Backup\TDE_FULL.bak'
     WITH NORECOVERY,
      REPLACE,
      MOVE 'TDE' TO 'E:\TDE.mdf',
     REPLACE,
     MOVE 'TDE_log' TO 'G:\TDE.ldf'
CLOSE MASTER KEY 
GO

--Restore the log backup to the mirrored db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
RESTORE LOG TDE
   FROM DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_LOG.trn'
   WITH NORECOVERY;
CLOSE MASTER KEY
GO


--Drop/Create Mirroring endpoint on mirror
--DROP ENDPOINT TDE
CREATE ENDPOINT TDE
   STATE = STARTED
   AS TCP ( LISTENER_PORT = 7025 )
   FOR DATABASE_MIRRORING (
       ROLE = PARTNER
       );
GO

--Check the endpoints for the mirror
USE MASTER
SELECT * FROM sys.database_mirroring_endpoints
GO

--Set the principal on the mirrored db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER DATABASE TDE SET PARTNER = 'TCP://PRINCIPAL.DOMAIN.local:7022'
GO
CLOSE MASTER KEY
GO

下面是我在主體伺服器上使用的程式碼。

----------------------Mirroring Section----------------------------------

--Full Backup of Principal
USE TDE
GO
BACKUP DATABASE TDE
TO DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_FULL.bak'
   WITH COMPRESSION,
        NAME = 'Full Backup of TDE';
GO

---Log Backup of Principal
USE TDE
GO
BACKUP LOG TDE
TO DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_LOG.trn'
   WITH COMPRESSION,
        NAME = 'Log backup of TDE'
GO

--Drop/Create Mirroring endpoint on principal
--DROP ENDPOINT TDE
CREATE ENDPOINT TDE
   STATE = STARTED
   AS TCP ( LISTENER_PORT = 7022 )
   FOR DATABASE_MIRRORING (
       ROLE = PARTNER
       );
GO

--Check the endpoints for the princple
USE master
select * from sys.database_mirroring_endpoints
GO

--Set the mirror db on the principal db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER DATABASE TDE SET PARTNER = 'TCP://MIRROR.DOMAIN.local:7025'
CLOSE MASTER KEY
GO

我首先設置了鏡像端點，然後是主體端點。然後我在鏡像上發出ALTER DATABASE，然後在主體上發出，我得到錯誤：

Msg 1416, Level 16, State 31, Line 2
Database "TDE" is not configured for database mirroring.

我不知道該怎麼辦。鏡像處於“正在恢復”狀態，但我確定該錯誤與主體數據庫有關。

謝謝你提供的所有幫助！

主要 TDE 的更新 程式碼：

--Create Master Key in Master Database
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1Password';
PRINT 'created master key'
go

--Backing up the master key file
USE master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password';
BACKUP MASTER KEY TO FILE = '\\SERVERNAME\TDE_Master_Key.key' ENCRYPTION BY PASSWORD = '1Password';
GO

--Create Server Certificate in the Master Database encrypted with master key (created above) which would be used to create USER database encryption key.
USE Master
CREATE CERTIFICATE Cert_For_TDE WITH SUBJECT = 'Master_Cert_for_TDE', EXPIRY_DATE = '3500-Jan-01';
Go

--Backing up the server cert file
--USE master;
BACKUP CERTIFICATE Cert_For_TDE TO FILE = '\\SERVERNAME\TDE_Cert.cer' 
   WITH PRIVATE KEY ( FILE = '\\SERVERNAME\TDE_Cert_Key.key', ENCRYPTION BY PASSWORD = '1Password');
GO

--Create user database key
USE TDE
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE Cert_For_TDE;
GO

--Enabling Transparent Database Encryption for the USER Database
USE master;
GO
ALTER DATABASE TDE SET ENCRYPTION ON
GO

TDE鏡像程式碼：

--restore the backed up key to the mirror
use master
RESTORE MASTER KEY
   FROM FILE = '\\SERVERNAME\TDE_Master_Key.key'
   DECRYPTION BY PASSWORD = '1Password'
   ENCRYPTION BY PASSWORD = '1Password';
GO

--restore the backed up cert to the mirror
USE Master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
CREATE CERTIFICATE Cert_For_TDE    
FROM FILE = '\\SERVERNAME\TDE_Cert.cer' WITH PRIVATE KEY ( FILE = '\\SERVERNAME\TDE_Cert_Key.key', DECRYPTION BY PASSWORD = '1Password');
GO

Update2 sys.database_mirroring_endpoints 與 sys.tcp_endpoints 在 Principal show 中加入：

endpoint_id name    principal_id    state_desc  role_desc   connection_auth_desc    certificate_id  encryption_algorithm_desc   port    ip_address
65545   TDE 261 STARTED PARTNER NEGOTIATE   0   RC4 7022    NULL

sys.database_mirroring_endpoints 與 sys.tcp_endpoints 在鏡像顯示中加入：

endpoint_id name    principal_id    state_desc  role_desc   connection_auth_desc    certificate_id  encryption_algorithm_desc   port    ip_address
65537   TDE 261 STARTED PARTNER NEGOTIATE   0   RC4 7025    NULL

找到一個有評論的網站。

我將程式碼添加到恢復密鑰和證書之後

--Mumbojumbo to get mirroring to work
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

它就像一個魅力，我不得不用新伺服器的服務主密鑰加密我恢復的主密鑰，這有點道理。我猜。

聳聳肩

引用自：https://dba.stackexchange.com/questions/2100