At the enterprise and developer editions of the software SQL Server 2008, fresh security system feature of crystal clear information encoding (TDE),sql server transaction log was included. He has several negative overall impact on performance to optimize SQL Server. It is in the range of 3 to 5%. However, if most of the data stored in memory, less impact.
Encryption is very CPU (central processing unit) intensive as it is at the database level. In addition, she performed in I / O (Input / Output).
Thus, the servers that are experiencing high CPU load and high I / O impact more. The effect of treatment within 28% in this case. However, in the case of servers with low I / O and low CPU impact on productivity is low. Despite this, the main reason for the implementation of impact load on the processor and high I / O causes minimal effect, if the CPU load is low.
Encryption at the file level, but do not take disc space because the PAD is not TDE database files on disc, even if PAD transaction logs,sql server transaction log. Thus, the experience which the user entrees the database and directs a request that the reaction of the software as quickly as it did in SQL Server 2005, in which only the cell-level encryption available.
Even backups of the database was encrypted when the TDE. Thus, a certificate, which defended the DEK (data encryption key) copy and stored with the backup database. In the case of loss of evidence, information will get illegible. So, it’s best to have two transcripts of the certification and both of which are called filing away safely apart from the backup database for security.
Then again, in the event of cell-level encryption, the impact on performance optimization of SQL Server on a 20% higher than for the TDE. This is since the encoding and decoding action in this case manually. In addition, by encrypting the value of the Attributes column, change and, consequently, the column data type must be changed to VARBINARY recharge, and then return to its first value after decoding. Non-automatic procedure stands for that none of the automatic processes applied by SQL Server query optimizer will do work.
Cell-level encoding has several advantages compared with the level of database encoding provided by TDE in the fact that encryption is more accurate, and that information isn’t decoded till used. This can be useful for targeted safety. But the burden of advanced key management for an administrator at a clear-cut disadvantage.
The biggest disadvantage of cell-level encoding to optimize the SQL Server is a superior penalty and administrative costs. Even query optimization depends on the quality indexes on coded columns provide no benefit.
all of the above functions could be very well understood by analysing sql server transaction log
tags:
4 sql server transactions per second 3 microsoftodbc sql server driverinvalid transaction state 2 sql server transactions 1 sql server 2000 transaction log backup 1 sql server how many transactions per second
