SQL Server optimization and the performance impact of encryption

A new security feature of transparent data encryption (TDE) is included in the Enterprise and Developer editions of the SQL Server 2008 software. It has a slightly negative overall impact on the performance of the SQL Server optimization. It is in the degree of 3 to 5%. However, if most data is stored in memory, the impact is less.

The encryption is very CPU intensive (central processing unit) because it takes place at the database level. In addition, it is performed on I / O (input / output).

Therefore, servers with a high CPU load and high I / O are more affected. In that case, the performance impact is 28%. However, in the case of servers with low I / O and low CPU load, the impact on performance is low. Still, the main reason for the performance impact is the CPU load, and the high I / O causes minimal impact when the CPU load is low.

Encryption is at the file level, but does not take up disk space, since TDE does not fill the database files on disk even if it fills up transaction logs. Therefore, the experience of a user who accesses the database and sends a query is that the response of the software is as fast as in SQL Server 2005, where only the cell-level encryption function is available.

Even database backups are encrypted when TDE is enabled. Therefore, the certificate that protected the DEK (data encryption key) is backed up and stored along with the database backup. If the certificate is lost, the data becomes illegible. Therefore, it is better to have two backup copies of the certificate, both of which are sent securely for security, separate from the database backup.

On the other hand, in the case of cell-level encryption, the performance impact on SQL Server optimization is 20% greater than for TDE. This is because the encryption and decryption process in this case is manual. Furthermore, encoding changes the value of the column attributes and therefore the column data type must be changed to varbinary and then restored to the original value after decoding. The manual process means that none of the automatic techniques used by the SQL Server query optimization will work.

Cell-level encryption has a number of advantages over the database-level encryption provided by TDE, because the encryption is more detailed and the data is only decrypted when used. It can be useful for a targeted security environment. However, the burden of extensible key management for the administrator is a clear drawback.

The main disadvantage of cell-level encryption when optimizing SQL Server is the high performance fee and administration costs. Even query optimization is affected because indexes on encoded columns are of no benefit.

Source by Dean Forster