Mirroring database with encrypted column by symmetric key [SQL2008]

• Dam_DBA

  Mr or Mrs. 500

  Points: 519

  More actions

  January 26, 2015 at 9:34 am

  #294738

  Hi there,

  here is the situation : I have a primary instance, who host three databases, and (this is the trick) one got encrypted column by symmetric key (we are using SQL2008, Enterprise Edition).

  The goal is to setup database mirroring with a secondary instance for theses three databases, and begin able to decrypt encrypted data on secondary after failover.

  As I'm more or less new in data encryption, what should I consider to setup this mirroring ?

  This is what i'm thinking for the case of the database with encrypted column :

  - Restore database with encrypted data on secondary with norecovery, and its subsequent logs backup file

  - Restore master key (backuped from primary) on secondary database in norecovery mode

  - Set up mirroring

  Do I forget something ?

  Some article speak about Transparent Data Encryption. Is it related to this case ? (I understand that TDE works with a certificate in master database, and all keys in users database are created with this certificate. And finally, encryption has to be set ON on primary database).

  Actually, a secondary already exists for this instance but we have to replace the server. I guess TDE is not currently used, because encryption is OFF on primary databases.

  Thanks a lot !

  Dam