Post reply

Migrate DB with encrypted column / AlwaysOn AG readable secondary setup

  • February 23, 2016 at 5:09 pm

    #325058

    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.

  • February 24, 2016 at 1:35 am

    #1860641

    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

  • February 24, 2016 at 6:11 am

    #1860687

    Thanks Kenny.

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

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

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