locked
Doubt about TDE RRS feed

  • Question

  • Hello,

     

     

    I have just one doubt about how TDE works, i know that TDE works only on physical media, only on disk and not in memory, know too that the encryption are on the 8kb sql server pages and thats the way FILESTREAM data isen´t encrypted by TDE, but, look this scenario:

    - Just active TDE on a database

    If a new page is required it will be created on memory, and when go to the disk it going to be encrypted, but, left say that my database have 2TB data on disk, whats happens on the page that doesen´t is required by an user and don´t go to memory? how they are encrypted?

     

    Books online just say thet some background processes are executed....=\


    Fabrizzio A. Caputo
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCTS SQL Server 2008
    Developer Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    Friday, July 29, 2011 12:50 PM

Answers

  • Thats correct. And this is what runs as a background process.

    Thanks,
    Manu

    Friday, July 29, 2011 5:20 PM

All replies

  • Hi,

     

    When you enable TDE using ALTER DATABASE command it performs the basic checks like Edition check, Read-Only Filgroup check, presence of DEK etc.

    It immediately returns a message telling the encryoption is successful. But still the data is not encrypted really in the disk by then.

    It starts a background process which does the encryption in I/O path - Re-encryption scan/encryption scan.

    The background process can be viewed as system spid running the command ALTER DATABASE when you check in sys.dm_exec_requests.

    Hope this helps.

     

    Thanks,
    Manu
    Please mark this as answered if this answers your question

    Friday, July 29, 2011 3:31 PM
  • Manu,

     

    But to encrypt a page, this one goes to memory and then back to disk? so i have more cpu consume?


    Fabrizzio A. Caputo
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCTS SQL Server 2008
    Developer Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    Friday, July 29, 2011 3:33 PM
  • When you first enable TDE, background processes are fired-up to perform the encryption of the data files and we force a rollover of the current VLF to a new VLF in the transaction log. This occurs for the user database where TDE is enabled and with TempDB. Any new data pages that are written to disk will be encrypted including any log entries. This means you will likely see some elevated CPU utilization initially until the data files are all encrypted.

    Any implementation of encryption in the database will consume extra CPU cycles; doesn't matter what database you use. With TDE, the impact we typically see is in the single digits but if your workload is already very CPU intensive, the overhead can go close to 30%. So if you have an older 1-2 proc server and it's already very busy (>60% sustained utilization), you might want to hold off on implementing TDE. With modern multi-core servers where 8 cores and higher are common, your users will not likely notice any changes.

     


    No great genius has ever existed without some touch of madness. - Aristotle
    Friday, July 29, 2011 4:15 PM
  • I second Joe on this. When you implement the TDE for the first time the CPU utilization increases. But if you consider encrypting the data helps you more by exploiting the CPU resources you have, you can go for TDE.

    It is always recommended to perform the encryption during low activity on the server. Encryption status can be checked by querying the DMV sys.dm_database_encryption_keys.

    As soon as you implement TDE and you see a very high CPU and you have some important operations that are running you can use the trace flag 5004 to pause the background encryption or use 5005 to slow down the encryption process.

    You may see high log file growth if the database being encrypted is huge. Other issues you may see include slow query performance, Significant overhead in log shipping and mirroring, backup restore issues like Database master key missing on target server during restore, Missing certificate, Creation of changed DEKs on target which are changed on source between log records etc.

    The recovery process during the database startup will be slow as the recovery runs as a single threaded process after implementing TDE on a database.

    Please let me know if you still have further questions.

     

    Thanks,
    Manu
    Please mark this as answered if this answers your question

    Friday, July 29, 2011 4:30 PM
  • So,

     

    Just to finish, when i enable TDE, old pages goes to memory and then back encrypted to disk by the backgroud processes? every page, even those one that wasen´t requested by some user...?


    Fabrizzio A. Caputo
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCTS SQL Server 2008
    Developer Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    Friday, July 29, 2011 5:02 PM
  • Thats correct. And this is what runs as a background process.

    Thanks,
    Manu

    Friday, July 29, 2011 5:20 PM
  • Thanks a lot!
    Fabrizzio A. Caputo
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCTS SQL Server 2008
    Developer Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    Friday, July 29, 2011 5:22 PM