This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2008 introduced Transparent Data Encryption (TDE) technique to encrypt the different database files like data file, log file and backup file.
As a Database Professionals, we are also responsible for all kinds of data and database security.
You can find an N number of similar TDE theory over the internet, but still, I would like to write an underlying note about what is TDE and why it is important.
Any underlying encryption algorithm brings your data into a state that cannot be interpreted by anyone without applying a decryption key, password or certificates.
As a DBA, we have to also maintain the decryption key, password and certificates information backup because once we lost this information, we cannot decrypt the encrypted data or database.
TDE uses for bulk encryption at the different type of database files.
You can enable TDE at the database level, and once we enabled, it encrypts all data into the pages before it writes to the disk and it decrypts when reading from the disk.
Once you enable TDE on a database, you do not require any other application code to manage encryption and decryption of the data so that you can use this feature without changing a single line of application code.
TDE uses the AES and 3DES encryption algorithms, and the encryption and decryption operations are run on background threads by SQL Server and database backup also encrypted by TDE.
TDE only encrypts the data before writing to the disk, so there is also another different state where TDE is not applying encryption.
When you are sending and transferring your data between SQL Server and other application, the TDE doesn’t apply any encryption.
Before writing to the disk, data is coming from buffer pool and into this buffer pool area data is available in the plain text format, so TDE doesn’t apply any encryption in this buffer pool.
The performance overhead is involved in using TDE. The encryption and decryption process does require additional CPU cycles. The cost for using TDE ranges from about 3 percent to 30 percent, depending on the type of workload.
SQL Server instances with low I/O and low CPU usage will have the least performance impact. Servers with high CPU usage will have the most performance impact.
In the next post, You can find require T-SQL scripts and steps to enable TDE on the SQL Server Database.
