Step by Step Configure TDE Transparent Data Encryption Enabled Database on Log shipping
Secondary:1 User-PC\MSSQL2012DESTSQL
Run backup job on primary
SQL Server Transparent Data Encryption
SQL Server Transparent Data Encryption (TDE), is an encryption mechanism, available in the Enterprise edition of SQL Server 2008 and later. It is used to protect the database physical files, rather than protecting the data itself. This includes the database data, log, backup and snapshot files as mentioned previously. Using the SQL Server Transparent Data Encryption, unauthorized users will not be able to access the database’s data, by preventing them from attaching or restoring the database files to another SQL instance. Also it will be protected inside the backup media if it is stolen.
The SQL TDE encryption, as the name indicates, transparent to the applications that connects to the database, as the encryption is at the page level. The data is encrypted while it is at rest on the disk and decrypted during the read process on its way to the memory. An authorized user will be able to access the encrypted database content, without being aware that the database is encrypted or any action or extra code required from his side.
more info Ref:
https://www.sqlshack.com/how-to-configure-sql-server-mirroring-on-a-tde-encrypted-database/
Demo:
Primary: User-PC\MSSQL2012Source
Secondary:1 User-PC\MSSQL2012DESTSQL
Secondary 2: User-PC\SQL2012CONVTEST
Log shipping Database: Testdbgrowth
Primary: User-PC\MSSQL2012Source
Secondary:1 User-PC\MSSQL2012DESTSQL
Here you can see the table info @ Primary
Create and Backup Master KEY @ Primary
We should need to create Master Key if not exists and Backup Master Key
See the Master Key info
Create Database Encryption Key and Enable Encryption of Database
See the Certificate here
Backup Full Database and Transaction Log backup @ primary
Destination (Secondary)
see the master key info @ secondary
Restore Master Key of Source(Primary) to Destination (Secondary) and Open Master Key @ Secondary and Create Certificate which create on Primary
We should need to restore Master key which we backedup on primary and Open Master Key and
Create certificate which we created and Backed up on Primary
Note:
Be aware difference below to avoid confusion while create certificate
Create certificate is using DECRYPTION BY PASSWORD
Backup Certificate is using ENCRYPTION BY PASSWORD
RESTORE Database Backup @ Secondary:
give overwrite option
We should need to give new path while restore on destination and
We should need to give WITH restoring option (WITH NORECOVERY) To allow more file like transaction log backup to restore again
Now we are going to restore Transaction log backup
We should need to mention as WITH Standby option to read users
Transaction Log backup Restored Successfully
Configure Log shipping @ Primary
Click Enable this as primary option and click Backup settings and give path(network and local)
Now give Add button to add secondary
connect option connect secondary and select third option as we already backup/restore database from primary to secondary
Copy files to give Copy location (network folder path)
Restore option to give Standby mode and give 1 minute delay to ensure proper process on copy/restore
Now Log shipping configured successfully
Backup job info @ Primary
Copy and Restore job info @ Secondary
you can see table reflected on secondary
Run backup job on primary
Run copy job on secondary
Run restore job @ secondary
To see the reports on log shipping
see the report shows copy and restore file same
we can query the Backup file info @ primary as see below
To see Backup file info @ primary
SELECT distinct
s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn
FROM
msdb..backupset AS s INNER JOIN
msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
(s.database_name = 'ALL_Test_TDE_LogShipping')
ORDER BY
s.backup_finish_date DESC;
Compare of both as see below
Script
To Create Master key @Primary
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
To Open Master key @Primary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
USE master
GO
To see Master key @Primary
use master
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date
from sys.symmetric_keys
To Backup Master key @Primary
use master
BACKUP MASTER KEY TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Create Certificate @Primary
use master
CREATE CERTIFICATE ATTL_Logshipping_TDE WITH SUBJECT = 'ATTL_Logshipping_TDE'
To Backup Certificate @Primary
use master
BACKUP CERTIFICATE ATTL_Logshipping_TDE
TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'
WITH PRIVATE KEY
(
FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',
ENCRYPTION BY PASSWORD = 'Ws@2012'
);
GO
To see Master key info @Primary
USE master
SELECT name, pvt_key_encryption_type ,pvt_key_encryption_type_desc ,issuer_name ,expiry_date ,start_date
FROM sys.certificates where name = 'ATTL_Logshipping_TDE'
GO
To Create Database Encryption @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ATTL_Logshipping_TDE
GO
To Enable Encryption on Database @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
ALTER DATABASE [ALL_Test_TDE_LogShipping]
SET ENCRYPTION ON
GO
To see Encryption status @Primary
use master
SELECT db_name(database_id), EncryptionState =
CASE encryption_state
WHEN 1 THEN 'Unencrypted'
WHEN 3 THEN 'Encrypted'
END
FROM sys.dm_database_encryption_keys
To Backup Full Backup @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP DATABASE [ALL_Test_TDE_LogShipping] TO DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_bk.bak'
WITH NOFORMAT, NOINIT, NAME = N'ALL_Test_TDE_LogShipping-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
To Backup Log Backup @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP LOG [ALL_Test_TDE_LogShipping] TO DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_TLBK.trn'
WITH NOFORMAT, NOINIT, NAME = N'ALL_Test_TDE_LogShipping-Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Destination:
To see Symmetric Key file info @Secondary
USE master
GO
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date
from sys.symmetric_keys
To Restore Master key @Secondary
USE master
RESTORE MASTER KEY
FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
DECRYPTION BY PASSWORD = 'Ws@2012'
ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Open Master key @Secondary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
To Create Certificate key @Secondary which we created and backedup on Primary
USE master
CREATE CERTIFICATE ATTL_Logshipping_TDE
FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'
WITH PRIVATE KEY (FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',
DECRYPTION BY PASSWORD = 'Ws@2012');
GO
To see Backup file info @ primary
USE master
SELECT distinct
s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn
FROM
msdb..backupset AS s INNER JOIN
msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
(s.database_name = 'ALL_Test_TDE_LogShipping')
ORDER BY
s.backup_finish_date DESC;
