Migrate DB with encrypted column / AlwaysOn AG readable secondary setup

  • SQL!$@w$0ME

    SSChampion

    Points: 12345

    Hello guys,

    What are the steps to follow if I need to migrate a db with column level encryption enabled.

    Any additional steps to follow if this db is part of always on availability group so that the encrypted column can be decrypted from the readable secondary db.

    I have a requirement to migrate this db to sql 2014 from sql 2008.

    Many thanks.

  • Kenny Jozi

    SSCrazy

    Points: 2004

    On the destination server after migration. OPEN the database master key using the same password as on the source server.

    NB : The OPEN command works for the same session only, the decryption capability does not work for other sessions or when the current session closes. then ALTER the database master key to associate it with the current instance’s service master key

    OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘yourMasterKey’

    GO

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

    GO

    follow this link https://www.simple-talk.com/sql/database-administration/encrypting-your-sql-server-2012-alwayson-availability-databases/ to read about AAG and TDE

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • SQL!$@w$0ME

    SSChampion

    Points: 12345

    Thanks Kenny.

    The article mentioned is for TDE enabled db. Not for column level encrypted db.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply