sys.dm_database_encryption_keys migration

[janezhang401]

Is there a way to migrate sys.dm_database_encryption_keys from source, to target database?

I have 'always encrypted keys' - column master keys, and column encryption keys in source database. 

However, when I finish the migration, at target, I don't see these information. As well as at target, the "sys.dm_database_encryption_keys" don't have any encryption information. 

[OritA]

Hi, 

In general since the system table include system related information that is used by Replicate for its function we recommend not to include system table in the list of tables for replication. 

Regards,

Orit

[SushilKumar]

Hello Team,

To add more to previous TSE comment " sys.dm_database_encryption_keys is a dynamic management view (DMV) in SQL Server that returns information about the encryption keys for the databases on the server. "

This information mainly resides in MSDB. Qlik replicate mainly replicate user tables/Data which configured to push to intended target. 

And it's as dynamic management view (DMV). It populates and retain data from some Base System tables which are internal to SQL engine When encryption is enabled at instance. Database level. 

And any replication tool which works upon the SQL extraction method get required info post querying the MSDB or the DB which hold the user tables which are participating into replication setup.

Regards,

Sushil Kumar 

[SachinB]

Hello @janezhang401 ,

Thanks for contacting Qlik community link.

Migrating encryption keys stored in sys.dm_database_encryption_keys as part of a Qlik replication process can be a complex task. While Qlik Replicate primarily focuses on replicating data between heterogeneous databases and platforms.

For migrating Always Encrypted keys (Column Master Keys and Column Encryption Keys) from a source database to a target database, you need to follow a different process since Always Encrypted keys are stored differently and managed separately from TDE keys.

If you're migrating the entire database, including Always Encrypted keys, you can perform a backup of the source database and restore it onto the target database. This will include the keys along with the database. However, this approach might not be feasible if you are only migrating specific objects or if you need to migrate keys to a different server or instance.

Regards,

Sachin B

[janezhang401]

Hi Sushi,

Thanks for responding. Can qlik transfer DMVs?

[SushilKumar]

Hello @janezhang401 

dynamic management view (DMV) cannot be transfer. It populates and retain data from some Base System tables which are internal to SQL engine When encryption is enabled at instance. Database level. 

And whenever we start the instance and database they populate again and purges the retained info once shutdown issues. Normally DMV used for performance analysis.

Regards,

Sushil Kumar