SQL Server Performance

Transparent Data Encryption

Discussion in 'SQL Server 2008 General DBA Questions' started by RVG, Sep 16, 2010.

  1. RVG Member

    Hi,
    I've implemented TDE for a database and Cell level encryption for a database. Here are my two question for reg these issues.
    1. where exactly can we see whether any database master key is OPEN or NOT (is there are DMV to see the master keys?)
    2. when we open the master key and taking a backup and regards to that backup what will the effect on the backup?
    Thanks,
    Ravi.
  2. satya Moderator

    For the first Q you can use :
    SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
    FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
    ON db.database_id = dm.database_id;
    GO

    For second Q, when you try to restore that backup the KEY must be present to complete the process.

Share This Page