Πώς προσθέτουμε μία βάση δεδόμένων που έχει ενεργοποιημένο TDE Encryption σε SQL Server Always On Availability Group

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
• Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024

Σε προηγούμενο άρθρο έχουμε δεί ότι το TDE (Trasparent Data Encryption) είναι το encryption μίας βάσης δεδομένων σε φυσικό επιπέδο. Δηλαδή πραγματοποιεί κρυπτογράφηση στα data files, log files και backups.

Όταν μία βάση δεδομένων στον SQL Server έχει ενεργοποιημένο TDE (Trasparent Data Encryption), θα δούμε ότι αν πάμε μέσω wizard να την προσθέσουμε σε Always On Availability Group ότι δεν θα μας το επιτρέψει:

Σε αυτό το άρθρο θα δούμε τον τρόπο με τον οποίο μπορούμε να προσθέσουμε μία βάση δεδομένων που έχει ενεργοποιημένο TDE σε Always On Availability Group.

Το παράδειγμα

Στο παράδειγμα μας έχουμε φτιάξει μία βάση δεδομένων με το όνομα demo_tde.

Έχουμε δημιουργήσει το master key encryption και το certificate ώστε να μπορούμε να ενεργοποιήσουμε το TDE το SQL Server Instance:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'Passw0rd'
GO

CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate'
GO

Έχουμε πάρει backup το certificate με δημιουργία private key:

BACKUP CERTIFICATE tdeCert TO FILE = 'C:\backup\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\backup\tde.pvk' ,  ENCRYPTION BY PASSWORD =  'Passw0rd' ); 

Έπειτα δημιουργήσαμε ένα encryption key που κάνει χρήση το certificate αυτό και ενεργοποιήσαμε το encryption στη βάση:

use demo_tde
go
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert;

ALTER DATABASE demo_tde
SET ENCRYPTION ON;

Αν τρέξουμε το παρακάτω query θα δούμε ότι η βάση demo_tde είναι σε status Encrypted:

SELECT
[db].name,
[db].is_encrypted,
[k].encryption_state,
[k].percent_complete,
CASE [k].encryption_state
WHEN 0 THEN 'Not Encrypted'
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 '
ELSE 'Not Encrypted'
END AS 'Desc'
FROM
sys.dm_database_encryption_keys [k]
RIGHT JOIN
sys.databases [db] ON [k].database_id = [db].database_id;

Τα βήματα

Στο primary node

Σαν πρώτο βήμα καλό είναι να έχουμε κάνει disable το Transaction Log backup καθώς αν είναι πολύ μεγάλη η βάση δεδομένων και αργήσει η διαδικασία, θα χρειάζόμαστε όλα τα Transaction Log backups που εκτελέστηκαν σε αυτό το διάστημα.

Έπειτα παίρνουμε ένα full backup την βάση δεδομένων:

BACKUP DATABASE [demo_tde] 
TO DISK = N'C:\backup\demo_tde_full_co.bak' WITH STATS = 10;
GO

Στο secondary node

Πρώτα μεταφέρουμε από το primary node στο secondary, το backup του certificate(C:\backup\tde.cer), το private key (C:\backup\tde.pvk) και το backup της βάσης δεδομένων (C:\backup\demo_tde_full_co.bak).

Δημιουργούμε το master key encryption και κάνουμε restore το certificate με το αρχείο backup, το private key και τον κωδικό του:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'Passw0rd'
GO

create certificate tdeCert from file ='C:\backup\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\backup\tde.pvk' ,  DECRYPTION BY PASSWORD = 'Passw0rd'); 
GO

Κάνουμε restore την βάση δεδομένων με NORECOVERY:

RESTORE DATABASE [demo_tde] 
FROM DISK = N'C:\backup\demo_tde_full_co.bak' WITH NORECOVERY, STATS = 10;
GO

Με το που ολοκληρωθεί, θα δούμε ότι είναι σε status Restoring...:

Στο primary node

Τώρα γυρνάμε στο primary node και προσθέτουμε την βάση δεδομένων στο Availability Group με T-SQL:

*Σε περίπτωση που είχαμε ήδη άλλη replica την βάση δεδομένων σε άλλο node κάνουμε skip αυτό το βήμα

USE master 
GO
ALTER AVAILABILITY GROUP [DP-AG] ADD DATABASE [demo_tde]

Στο secondary node

Έπειτα γυρνάμε πάλι στο secondary node και δηλώνουμε ότι η βάση δεδομένων ανήκει σε αυτό το Availability Group:

USE master
GO 
ALTER DATABASE [demo_tde] SET HADR AVAILABILITY GROUP =  [DP-AG];

Σε περίπτωση που έχει εμφανίσει το παρακάτω μήνυμα, το αγνοούμε καθώς σημαίνει ότι η βάση δεδομένων προστέθηκε ήδη στο Availability Group:

Msg 41145, Level 16, State 1, Line 19
Cannot join database 'demo_tde' to availability group 'DP-AG'.  The database has already joined the availability group.  This is an informational message.  No user action is required.

Τώρα αν κάνουμε refresh θα δούμε ότι ειναι σε status Synchronized που είναι και αυτό που θέλουμε για Synchronous commit. Αν είχαμε επιλέξει Asynchronous commit θα έπρεπε να είναι σε status Synchronizing:

Με το παρακάτω query μπορούμε να δούμε πληροφορίες για το health status της βάσης δεδομένων στο Availability Group:

SELECT ag.name AS [AG_Name], ar.replica_server_name AS [ReplicaServerName], ar.availability_mode_desc AS [AvailabilityMode], adc.[database_name], 
       hars.role_desc as [Role],  drs.synchronization_state_desc as [SynchronizationState], drs.is_commit_participant as [IsCommit], 
       drs.synchronization_health_desc as [SyncHealth],drs.database_state_desc as [DatabaseState],
       drs.last_commit_time as [Last Commit Update Time]
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)
ON drs.group_id = adc.group_id 
AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
ON drs.group_id = ar.group_id 
AND drs.replica_id = ar.replica_id
INNER JOIN sys.dm_hadr_availability_replica_states hars 
ON drs.replica_id = hars.replica_id 
where database_name='demo_tde'
ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE);