Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday 6 September 2017

Step by Step Configure TDE Transparent Data Encryption Enabled Database on Log shipping

Step by Step Configure TDE Transparent Data Encryption  Enabled Database on Log shipping 

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;

Tuesday 5 September 2017

Manual Transaction Log Backup Even in Same Path Will Break Log shipping

Manual Transaction Log Backup Even in Same Path Will Break Log shipping 

Description:

Manual log backup or using Maintenance Plan taking Transaction log backup @ primary even in same path or different path is causing Transaction log shipping to fail. 

Solution:

we should need to manually restore Transaction log backup of  that file to resolve then later we should need to disable log backup or we should need to exclude database on maintenance plan @ primary

Here I am going to give 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


Secondary 2: User-PC\SQL2012CONVTEST


Transaction log backup Job info @ Primary


Transaction log path info @ primary

it is same path for already we configured log shipping on primary



we can see after Run transaction log backup we got file same path as see below


After we ran copy job and trying to restore we got job failed



Error giving more info as this transaction log backup maintenance plan file can not restore it is too recent to apply even in same path


Even in report give info as it copied before transaction log backup as see below


To resolve this issue

we should need to restore manual as Transaction log backup maintenance plan file as see below


you can see we got successfully restored manually


we got restore success as after running job manually


we also find from report file latest file copied as see below


we can confirm via jobs as see below

so we should need to disable transaction log backup



or we should need to exclude log shipping involving database on primary 
as see below





That is it Keep Blogging!!!!!!!!!!!!!!!!!!!

Happy Teachers Day 2017!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!