SQL Server Optimization And The Performance Impact Of Encryption
By Dean Forster
In the Enterprise and Developer editions of the software SQL Server 2008, a new security feature of transparent data encryption (TDE) has been included. It has a slightly negative overall performance impact on the SQL Server optimization. It is to the extent of 3 to 5 %. However, if most of the data is stored in memory, the impact is lesser.
The encryption is highly CPU (central processing unit) intensive as it takes place at the database level. Moreover, it is performed at I/O (input/output).
Therefore, servers that experience a high CPU load and high I/O are impacted more. The performance impact is to the extent of 28% in such a case. However, in the case of servers with low I/O and low CPU load the performance impact is low. Even so, the primary reason for the performance impact is the CPU load and the high I/O causes minimal impact if the CPU load is low.
The encryption is at the file level but does not occupy disk space as TDE does not pad the database files on disk, even if it does pad 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 it was in SQL Server 2005 in which only cell-level encryption feature is available.
Even backups of a database become 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 will become unreadable. Therefore, it is better to have two backups of the certificate and both of which are sent for archiving safely separate from the database backup for security.
On the other hand, in the case of cell-level encryption, the performance impact on SQL Server optimization is 20% more than it is for TDE. This is because the encryption and decryption process in this case is manual. Further, due to encryption the value of the column attributes changes and therefore the data type of the column must be changed to varbinary and then recast back to the original value after decryption. The manual process means that none of the automatic techniques used by the SQL Server query optimizer will work.
Cell-level encryption has a number of advantages over the database level encryption provided by TDE in that the encryption is more granular and that data is not decrypted until used. It can be useful for a targeted security environment. However, the burden of extensible key management for the administrator is a distinct disadvantage.
The greatest disadvantage of cell-level encryption on SQL Server optimization is in the high performance penalty and the cost of administration. Even query optimization is affected as indexes on encrypted columns offer no benefit.
About the author
Learn more about Database Management and SQL server database at => http://www.whatisdatabasemanagement.com
from http://www.FreeArticlesAndContent.com
|
|
Copy This Article
For FREE!!!
You can use this article and copy it on your own website
for free! All you have to do is make sure the article
is copied with no changes and includes the "About
The Author" text. Also please ensure that all url's
are hyperlinked according. Thank you. |
Link To This Article - And We'll
Link Back To Your Website!
You are more then welcome to link to this article! All
you have to do is copy this webpage address from the
address bar and create a link on your website. Please
use the title of this article for your link text. Please
get in contact once you have linked to this article
and we'll link back to you! Thank you. |
|
|
|
Other great articles from this category...
|
Related Sites
|
|
