In blog DB2 Native Encryption, How to Encrypt DB2 Database, How to Encrypt DB2 Database using Centralized KeyStore we went through DB2 Native Encryption and understood step by step process to encrypt DB2 standalone database using both Local KeyStore and Centralized KeyStore. Encrypting DB2 standalone database involve downtime. In this blog we will go through step by step process to encrypt DB2 database in HADR setup without Downtime, before we proceed I would suggest to check above blogs to understand DB2 Native encryption and steps to configure it.
In this blog we will use 2 Local KeyStore, one for Primary and other for Standby. You can use shared Local KeyStore as well, by creating KeyStore on shared File System.
We will consider a 2 node HADR setup with below configuration:
Standby Node : db2test2, Instance : db2i1
Step 1: Create Local KeyStore on db2test1
Before creating Local KeyStore, create a folder to keep Local KeyStore and restrict access by changing permission to 700 on both Primary and Standby
Primary:
Standby:
Create Local KeyStore on db2test1:
gsk8capicmd_64 -keydb -create -db "TESTDB_HADR.ps12" -pw "P@ssw0rd_Db@i!" -type pkcs12 -stash
Step 2: Create Master Key on db2test1
Before creating Master Key on db2test1, verify if any certificate exist on your Local KeyStore, even if it exist there is no impact as such.
gsk8capicmd_64 -cert -list -db TESTDB_HADR.ps12 -stashed
If certificate exist, then ensure to create Master Key with Label different from existing certificate. In this case we will create a Master Key with label TESTDB_HADR
gsk8capicmd_64 -secretkey -create -db TESTDB_HADR.ps12 -stashed -label TESTDB_HA DR -size 16
Step 3: SCP Local KeyStore files to db2test2 Server
scp TESTDB_HADR.* db2i1@db2test2:/dbdata/db2i1/.KeyStore
Step 4: Validate Local KeyStore file on db2test2
Step 5: Update database manager configuration (DBM CFG) parameters on db2test2
Check HADR Status on db2test2
db2pd -hadr -db testdb
Step 8: Take Online backup on db2test2 and SCP to db2test1
In step 6 after instance restart, encryption got enabled for db2i1 instance on db2test2. Even though database is not encrypted, database backup which will be performed will be encrypted database.
Step 10: Update database manager configuration (DBM CFG) parameters on db2test1
Step 11: Restart database Instance on db2test1
Restart Standby database Instance on db2test1
Step 12: Reinitialize HADR on db2inst1 using backup from db2inst2
Drop DB2 database, it is require to restore database as encrypted database. You cannot restore database as encrypted database over an existing unencrypted database.
db2 drop db TESTDB
Restore database using encrypted database backup taken in step 8. In restore command ensure to specify keyword ENCRYPT and specify MASTER KEY LABEL created in step 2.
db2 restore db TESTDB from . taken at 20210124162735 ENCRYPT MASTER KEY LABEL TESTDB_HADR
Verify HADR Parameters on db2test1
db2 get db cfg for TESTDB | grep -i HADR
Update HADR parameters if require on db2test1
db2 update db cfg for TESTDB using HADR_LOCAL_HOST db2test1db2 update db cfg for TESTDB using HADR_REMOTE_HOST db2test2
db2 update db cfg for TESTDB using HADR_LOCAL_SVC DB2I1_HADR_1
db2 update db cfg for TESTDB using HADR_REMOTE_SVC DB2I1_HADR_2
Start HADR as standby on db2test1
db2 start hadr on db TESTDB as standby
Check HADR Status on db2test1
db2pd -hadr -db testdb
db2pd -hadr -db testdb
Step 13: Verify Database Encryption on db2test1
Below query will list MASTER KEY and KeyStore used for database encryption.
db2 "SELECT substr(master_key_label,1,40) as MasterKey, substr(KEYSTORE_NAME,1,30) as KeystoreName FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"
Step 14: Take Online backup on db2test1 and SCP to db2test2
db2 backup db TESTDB ONLINE COMPRESS
scp TESTDB.0.db2i1.DBPART000.20210124162735.001 db2i1@db2test1:/dbdata/db2i1/
Step 15: Validate Backup file on db2test2
Step 16: Stop HADR on db2test2
Step 17: Reinitialize HADR on db2inst2 using backup from db2inst1
db2 drop db TESTDB
Restore database using encrypted database backup taken in step 14. In restore command ensure to specify keyword ENCRYPT and specify MASTER KEY LABEL created in step 2.
db2 restore db TESTDB from . taken at 20210124165226 ENCRYPT MASTER KEY LABEL TESTDB_HADR
Verify HADR Parameters on db2test2
db2 get db cfg for TESTDB | grep -i HADR
Update HADR parameters if require on db2test2
Start HADR as standby on db2test2
db2 start hadr on db TESTDB as standby
Check HADR Status on db2test2
db2pd -hadr -db testdb
Step 18: Verify Database Encryption on db2test2
db2 "SELECT substr(master_key_label,1,40) as MasterKey, substr(KEYSTORE_NAME,1,30) as KeystoreName FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"
With this both database in HADR setup got encrypted without downtime, to encrypt auxiliary standby database, scp Local KeyStore to auxiliary standby server, update DBM parameters and drop database and reinitialize auxiliary standby database using encrypted backup from primary node.
You can also enable SSL, to encrypt data at transit and for HADR communication. Check blog Enable SSL for DB2 HADR Communication for steps to configure SSL for HADR communication.
If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.
Very useful doc. Thank you.
ReplyDeleteNice document with clear explaination..
ReplyDeleteClear explaination
ReplyDeleteVery good explain
ReplyDeleteHello Choudhary
ReplyDeleteIn step 5 & 6, you have enabled encryption only at Instance level in standby dbtest2
Then u did a takeover, made this as Primary (unencrypted Primary DB)and took backup (encrypted)
Step 9 to 11, enabled encryption at Instance level in dbtest1
Step 12, restored with ENCRYPT option and started HADR
Now at this step, the Primary DB(dbtest2) is still unencrypted, but Standby DB(dbtest1) is encrypted .
Does HADR still work ?
The next steps repeat the cycle to takeover dbtest1 as Primary, take encrypted backup and restore on dbtest2 with ENCRYPT option.
In the IBM documentation - https://www.ibm.com/docs/en/db2/11.5?topic=dne-configuring-native-encryption-in-hadr-environment
The step is to take backup from Primary and restore with ENCRYPT option on Standby, and then do a takeover.
But your steps differ from this .
Any advantage in your method ?
Hi Warren, Yes HADR works even if your standby database is encrypted and primary is unencrypted.
DeleteBenefit of using steps described in this blog is, we don't need downtime except for takeover. If we follow steps mentioned in IBM document, we would need downtime.
If you have leverage of taking your primary down, you can go ahead with IBM document.
Hi Choudhary
ReplyDeleteThanks for the clarification
Since it works with HADR, I suppose it should work with transaction log shipping too . If I want to test with a testbox without setting up hadr ?
Yes it should work with transaction log shipping as well.
DeleteThanks and nice job on summarizing the whole steps..
ReplyDelete