SQL Server Always On Series -
Adding Transparent Data Encryption (TDE) Enabled User Database to Always On Availability group

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Msg 15578, Level 16, State 1, Line 10
There is already a master key in the database. Please drop it before performing this statement.


TITLE: Microsoft SQL Server Management Studio
------------------------------
This wizard cannot add a database containing a database encryption key to an availability group. Use the CREATE or ALTER AVAILABILITY GROUP Transact-SQL statement instead. For more information see SQL Server Books Online.

This database lacks a full database backup. Before you can add this database to an availability group, you must perform a full database backup.
------------------------------
BUTTONS:
OK
------------------------------

--Create database JB_DB

create database JB_DB
go

-- Create Master Key for TDE

USE master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='QKyrk@%wIj$p97';
GO

-- Create Certificate

USE master;
GO
CREATE CERTIFICATE JB_TDE_Certificate
WITH SUBJECT='Certificate for TDE';
GO

-- Create Encryption key

USE JB_DB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE JB_TDE_Certificate;

-- Enable TDE for database JB_DB

ALTER DATABASE JB_DB SET ENCRYPTION ON;
GO
--Create database JBS_DB

create database JBS_DB
go

-- Create Encryption key

USE JBS_DB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE JB_TDE_Certificate;

-- Enable TDE for database JBS_DB

ALTER DATABASE JBS_DB SET ENCRYPTION ON;
GO
select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete,encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

USE master;
GO
BACKUP CERTIFICATE JB_TDE_Certificate
TO FILE = 'C:\temp\JB_TDE_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JB_TDE_Certificate.pvk',
ENCRYPTION BY PASSWORD='QKyrk@%wIj$p97');

USE master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='QKyrk@%wIj$p97';
GO

CREATE CERTIFICATE JB_TD_Certificate
FROM FILE='C:\temp\JB_TDE_Certificate.cer'
WITH PRIVATE KEY (
FILE = 'C:\temp\JB_TDE_Certificate.pvk',
DECRYPTION BY PASSWORD='QKyrk@%wIj$p97')

USE master
GO
ALTER AVAILABILITY GROUP [JB_AG] ADD DATABASE [JB_DB]

USE master
GO
ALTER AVAILABILITY GROUP [JB_AG] ADD DATABASE [JBS_DB]

USE master
GO
ALTER DATABASE [JB_DB] SET HADR AVAILABILITY GROUP = [JB_AG];

USE master
GO
ALTER DATABASE [JBS_DB] SET HADR AVAILABILITY GROUP = [JB_AG];

Reference - [ Ссылка ]

свернуть