The K2 Installer is not able to configure Microsoft SQL AlwaysOn for automated failover management of the K2 database. Please see the Service Master Key, Database Master Key & Symmetric Key section at the end of this article for a detailed explanation. It is possible, however, for the database administrator to manually create the MS SQL AlwaysOn configuration for an automated failover of the K2 database. This article will give the configuration steps needed to setup the Service Master Key settings, and the Availability Group configuration for the MS SQL Always On automated failover configuration.
This article requires that you directly modify the K2 database. If done improperly or on the wrong version of K2, modification of the K2 database definition, permissions or content may result in system instability or system failure. It is highly recommended to make backups of the K2 database prior to performing any modification. (If available, K2 recommends that you also test database modifications in a non-production environment first.) If you have any concerns, please contact K2 Support before proceeding.
In this section, a sample database ‘MyDB1’ that has one table [Customer], is used to explain the basic concepts of why a manual configuration of the AlwaysOn automatic failover is necessary.
On the Current primary replica, we see the Service Master Key (SMK)
Create the Database Master Key (DMK) on ‘MyDB1’
Create a full backup, followed by a transaction log backup of MyDB1 database and restore it on the secondary replica. This will copy/restore the database master key, certificate and symmetric key as well. Switch to the primary replica and encrypt some random string.
Use either of these queries to test:
The Service Master Key is used to encrypt Database Master Keys, linked server passwords and credentials. It is the root of the SQL Server encryption hierarchy. The Service Master Key directly or indirectly secures all other keys in the tree. The Database Master Key is a database scoped symmetric key that is encrypted by the Service Master Key and stored in the database. It can be used to encrypt certificates and/or asymmetric keys within the database. If a database makes use of a Database Master Key, and that key is encrypted by the Service Master Key, then this encryption will need to be regenerated on the destination instance after any migration. In order to verify this, run the following query.
The "is_master_key_encrypted_by_server" column of the sys.databases catalog view in the master table indicates whether the Database Master Key is encrypted by the Service Master Key. So, after moving databases, the Database Master Key must first be opened using its password, then the ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY statement can be used to add the Service Master Key encryption.Run the following query on the secondary replica:
Re-run the above query to check if the decryption works now.
When the same is done in the scope of SQL AlwaysOn Availability Group, we cannot alter the Database Master Key on the secondary replica because it will be in read-only mode.
So, we have to do a manual failover to secondary replica to bring it out of read-only mode. After the failover, the ‘ALTER MASTER KEY’ operation will succeed and we will be able to decrypt the data. However at the same time, the changes to the Database Master Key on the second SQL server (new primary) are synced to the first SQL server. When the next failover occurs on the first SQL server, we have to redo the ‘ALTER MASTER KEY’ operation on the first SQL server, otherwise the replica cannot decrypt the data. To overcome this manual intervention requires some initial planning when setting up the SQL AlwaysOn Availability Group for databases that have encryption. Before adding any databases to the SQL servers, ensure the Service Master Key is the same on all the SQL servers. If you do this after the databases are added, there might be data loss.
On the first SQL server, run the following to backup the Service Master Key
On all other SQL servers, restore the backed up Service Master Key from the first server.
If you are restoring the Service Master Key after you have added the databases, you may need to add the FORCE parameter to the restore query.
Once this is done, the Service Master Key on all the servers will be identical. You can now add the databases to Availability Group. When the failover occurs to some other SQL server, the symmetric key can be opened because the Service Master Key is the same as the old primary replica.