Encrypt DB2 HADR Database Setup without Downtime

In blog DB2 Native Encryption, How to Encrypt DB2 DatabaseHow 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:

Primary Node : db2test1, Instance : db2i1

DB2 HADR Configuration










Standby Node : db2test2, Instance : db2i1

DB2 HADR Configuration










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:

Create Folder for Local KeyStore







Standby:

Create Folder for Local KeyStore








Create Local KeyStore on db2test1:

 gsk8capicmd_64 -keydb -create -db "TESTDB_HADR.ps12"  -pw "P@ssw0rd_Db@i!" -type   pkcs12 -stash

Create Local KeyStore






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

Check Certificate in Local KeyStore




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

Create Master Key in Local KeyStore






Step 3: SCP Local KeyStore files to db2test2 Server

 scp TESTDB_HADR.* db2i1@db2test2:/dbdata/db2i1/.KeyStore

SCP Local KeyStore to Standby





Step 4: Validate Local KeyStore file on db2test2

Validate Local KeyStore file on Standby





Step 5: Update database manager configuration (DBM CFG) parameters on db2test2

db2 update dbm cfg using KEYSTORE_LOCATION <path>
db2 update dbm cfg using KEYSTORE_TYPE PKCS12

Update DBM Parameter on Standby














Step 6: Restart database Instance and reinitialize HADR on db2test2

Deactivate database and stop HADR on Standby Database.

db2 deactivate db testdb
db2 stop hadr on db testdb

Deactivate database and stop HADR







You should deactivate standby database first and then stop hadr, else it will through error SQL1769N reason code 2.

Restart Standby database Instance

db2stop
db2start

db2stop db2start








Instance restart on db2test2 enable DB2 Native Encryption at instance level.

Start HADR as standby on db2test2

 db2 start hadr on db testdb as standby

Start HADR as Standby





Check HADR Status on db2test2

 db2pd -hadr -db testdb

HADR Status



















Step 7: Takeover HADR on db2test2

db2 takeover hadr on db TESTDB

Takeover HADR




Check HADR Status on db2test2

db2pd -hadr -db testdb

HADR Status












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.

db2 backup db TESTDB ONLINE COMPRESS
scp TESTDB.0.db2i1.DBPART000.20210124162735.001 db2i1@db2test1:/dbdata/db2i1/

Online backup


Step 9: Validate Backup file on db2test1

Verify Backup File







Step 10: Update database manager configuration (DBM CFG) parameters on db2test1

db2 update dbm cfg using KEYSTORE_LOCATION <path>
db2 update dbm cfg using KEYSTORE_TYPE PKCS12

Update DBM Parameter











Step 11: Restart database Instance on db2test1

Deactivate database and stop HADR on db2test1.

db2 deactivate db testdb
db2 stop hadr on db testdb

Deactivate database and stop HADR




Restart Standby database Instance on db2test1

db2stop
db2start

db2stop db2start






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

Drop DB2 Database



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

Restore DB2 Database as Encrypted database with MASTER KEY



Verify HADR Parameters on db2test1

db2 get db cfg for TESTDB | grep -i HADR

HADR Parameters









Update HADR parameters if require on db2test1

db2 update db cfg for TESTDB using HADR_LOCAL_HOST db2test1
db2 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

HADR Parameters







Start HADR as standby on db2test1

 db2 start hadr on db TESTDB as standby

Start HADR as Standby



Check HADR Status on db2test1

db2pd -hadr -db testdb

Check HADR Status












Step 13: Takeover HADR on db2test1

db2 takeover hadr on db TESTDB

Takeover HADR





Check HADR Status on db2test1

db2pd -hadr -db testdb

Check HADR Status











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())"

Verify Encryption







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/

Online backup













Step 15: Validate Backup file on db2test2

Verify Backup File






Step 16: Stop HADR on db2test2

Deactivate database and stop HADR on db2test2.

db2 deactivate db testdb
db2 stop hadr on db testdb

Deactivate database and stop HADR







Step 17: Reinitialize HADR on db2inst2 using backup from db2inst1

db2 drop db TESTDB

Drop DB2 database




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

Restore DB2 Database as Encrypted database with MASTER KEY




Verify HADR Parameters on db2test2

db2 get db cfg for TESTDB | grep -i HADR

HADR Parameters











Update HADR parameters if require on db2test2

db2 update db cfg for TESTDB using HADR_LOCAL_HOST db2test2
db2 update db cfg for TESTDB using HADR_REMOTE_HOST db2test1
db2 update db cfg for TESTDB using HADR_LOCAL_SVC DB2I1_HADR_2
db2 update db cfg for TESTDB using HADR_REMOTE_SVC DB2I1_HADR_1

Update HADR Parameters







Start HADR as standby on db2test2

 db2 start hadr on db TESTDB as standby

Start HADR as Standby




Check HADR Status on db2test2

db2pd -hadr -db testdb

Check HADR Status













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())"

Verify Encryption






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.


9 comments:

  1. Nice document with clear explaination..

    ReplyDelete
  2. Hello Choudhary
    In 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 ?



    ReplyDelete
    Replies
    1. Hi Warren, Yes HADR works even if your standby database is encrypted and primary is unencrypted.

      Benefit 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.

      Delete
  3. Hi Choudhary
    Thanks 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 ?

    ReplyDelete
    Replies
    1. Yes it should work with transaction log shipping as well.

      Delete
  4. Thanks and nice job on summarizing the whole steps..

    ReplyDelete