Solved

SQL server TDE (Transparent Database encryption)

Posted on 2014-11-16
30
364 Views
Last Modified: 2014-11-23
Dear all,

Anyone implement TDE before on SQL server ? it is fastest encrption for MS SQL server ? but that one is on table level ?

anyone implement this before ? please share implement and deploy experience:

1)  what is the pre task and post task to implment this ? any step by step guide?
2) any step by step guide on actually implmentation?
3) Will the storage utiliization increase a lot?
4) Will the server slow down a lot after implementing this ?
5) Any front end application need to change before implementing this so that the application can function well ? of course application vendor has to confirm their application can run on this.
0
Comment
Question by:marrowyung
  • 16
  • 13
30 Comments
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
also, anyway to monitor the encryption is working well for that SQL server ?

Any hardware requirement need for TDE to setup and work? SW TDE can be very slow and we only deploy to some tables, right?

once database enabled that, will the database backup and restore being impact in anyway ?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
from DR point of view, the same key has to deploy on both primary and the mirror/DAG parnter to make sure that failover/switchback works.

will the deployment of key is easlier for DAG than mirror configuration.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
TDE encrypts the data and log files,  mainly to prevent un authorized access to the data base / back up files. It wont encrypt at a table level.
It is really fast, depending on the size of the database, you may need few minutes.
 1)  what is the pre task and post task to implement this ? any step by step guide?
      Need to create a certificate, while creating a cert make sure of the expiry date. if the cert expires, you need to decrypt the database , then encrypt it using the new cert.
 2) any step by step guide on actually implementation?
https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/
 3) Will the storage utilization increase a lot?
No,
 4) Will the server slow down a lot after implementing this ?
There is slight increase in the CPU usage
 5) Any front end application need to change before implementing this so that the application can function well ? of course application vendor has to confirm their application can run on this.
No, the changes are only in the database level.
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
TDE is not on column based and it is as a whole DB per se

Performance wise do check out this check conducted with overheads compared based on the before and afetr applying TDE. E.g. average elapsed-time overhead was stated as 6.36% http://www.databasejournal.com/features/mssql/article.php/3815501/Performance-Testing-SQL-2008146s-Transparent-Data-Encryption.htm

It is the fastest for MS SQL, it depends on the read and write which is no different from the before state. It is supposed to be transparent at "disk" level - some sort like bitlocker with on the fly encrypt/decrypt. It is definitely going to be faster compared to column encryption or bulk field record encryption. But most of DBA detest having encryption as it can affect DB tuning and couple of schedule task as they now also need to consider key mgmt and for some even going to HSM to safeguard asymmetric keys.

In articular, for MS SQL, the TempDB performance has been highlighted as concern since it used as temp store throughout all user and table in doing those crypto operations and other routine task generating temp object etc. It is stated as 3-5% as a whole hence I believe it is at worst ard 6-7% depending on the write operation, but then it is back to basic design to identify area for reado once and write many and segment DB or tbl accordingly.

Most and if not all will opt for higher spec machine to run crypto operation leveraging the crypto chipset like AES-NI and ..Oracle does that with its TDE tablespace and TDE column which you can check out the performance stats too. http://www.oracle.com/technetwork/database/security/tde-faq-093689.html#A12002. For MS SQL, it does it with 3rd party s.w. Catch this https://software.intel.com/en-us/blogs/2012/12/26/securing-databases-with-intel-advanced-encryption-standard-new-instructions-aes-ni
The bottom line is that Intel AES-NI can significantly speed up transaction time

For the queries, I see the article will help and note the key activity to backup and test recovery prior to TDE setup. Also the steps in implementation also emphasis a lot on the verification aspects. it is best you try it out in staging and not production directly
https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"TDE is not on column based and it is as a whole DB per se
"
yeah !

"In articular, for MS SQL, the TempDB performance has been highlighted as concern since it used as temp store throughout all user and table in doing those crypto operations and other routine task generating temp object etc. It is stated as 3-5% as a whole hence I believe it is at worst ard 6-7% depending on the write operation, but then it is back to basic design to identify area for reado once and write many and segment DB or tbl accordingly."

what is tbl ? so the size of tempdb will be a concern too ? how "larger" it will be ?

"The bottom line is that Intel AES-NI can significantly speed up transaction time "

it is not for me to set it up right? we have to buy sth from Intel ?

so the with TDE, the diskspace will have an issue but overall , how much larger will be the size of MS SQL data and log file ?
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
Apologies for short form, I meant "tbl" for table. TempDB is the temp area to hold transaction processing output etc. It is actually elastic meaning able to expand as required if more processing activities are happening. But this auto growth make not be friendly to real time sensitive application and the growth also need to match the I/O subsystem. MS has a good article on TempDB. Pls see http://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

AES NI is the hardware chip which the system need to have native build into the motherboard. For overall sizing TempDB is a good estimate how good the DB is performance and optimised. Capacity planning for TempDB  http://technet.microsoft.com/en-us/library/ms345368(v=sql.105).aspx
Autogrowing by 1MB, for example, is quick, but you might need to do it so often that it becomes a bottleneck. Autogrowing by at least 10MB for the transaction log is a good place to start, but you may need it to be higher to provide enough space to avoid autogrowing again quickly. The best option is to avoid autogrowing in the first place by correctly sizing the files.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I read all about tempdb sometimes ago but what I want to know is ,any real life example of how BIGGER it growth when compare with no TDE turned on ?

"AES NI is the hardware chip which the system need to have native build into the motherboard. "

so we have to make sure that hardware is there first.
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
TempDB activities and size are not impacted with TDE enabled. It does not hold any TDE related processing. However, note that the tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. Hence TempDB read and write will need to be decrypt and encrypt but still this has nothing to do with the TempDB size. At most the CPU and memory will incur additional load as a whole system per se. if interested, this old article talks more on the TempDB when TDB is enabled... https://www.simple-talk.com/blogs/2008/11/07/tde-under-the-hood-with-tempdb/

yes hardware need to have it
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
it seems that TDE only affect CPU, RAM and increase IO workload , and size of any database do not impacted, right?
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
Yes and No. The resource as mentioned are affected as more processing required but the size of DB (not TempDB) is increased as the sorage is now encrypted database, although they are of fixed size with scrambled data at rest. the TDE crypto keys and certificate  should be separately stored from the user and their appl db.
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
This is a good link to compared existing Windows encryption and how TDE can augment them as part of defense in depth strategy http://technet.microsoft.com/en-us/library/cc278098(v=sql.100).aspx#_Toc189384694
Cell-level encryption provides much more granular control including explicit key management although at a cost to performance, ease of use, and administration. BitLocker and EFS provide protection in situations that TDE does not such as crash dump information or hibernation files (if protecting the system volume or system folders). BitLocker and EFS (and to a much more limited degree, cell-level encryption) can be used to protect system databases (master, model, resource, and msdb), which cannot currently be encrypted by TDE.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"Yes and No. The resource as mentioned are affected as more processing required but the size of DB (not TempDB) is increased as the sorage is now encrypted database,"

encrypted equal to increase in size ?

do you have an idea on how "larger" of user database is usually after TDE tunned on ? how many percent increased ?

from that link, can't udnerstnad what is that mean:

"For disk space concerns, TDE does not pad database files on disk although it does pad transaction logs as previously noted in How Data is Encrypted."

is that only means mdf file willl not growth extra but log will growth faster.

"Cell-level encryption provides much more granular control including explicit key management although at a cost to performance, ease "

We don't use cell-level encryption, so we don't need that and I am sure that a key management software is need on this!

For TDE, as database is encrypted, how can we restore that encrytped backup file ?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
one thing, any diff between TDE of SQL server 2008/ 2008 R2 enterprise edition's and SQL server 2014 enterprise edition's TDE ?
0
 
LVL 61

Accepted Solution

by:
btan earned 500 total points
Comment Utility
Pardon me to clarify see this from MS
TDE protects data "at rest", meaning the data and log files. ...Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.
The size increased as there are certificate and keys that is not present w/o TDE but those are minimal size and not of signifcance. For the backup, see
When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, parts of the transaction log may still remain protected, and the certificate may be needed for some operations until the full backup of the database is performed.
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
See enhancement from 2014
In SQL Server 2014, TDE functionality takes the non-encrypted backup data and encrypts the data before writing it to disk. In order to preserve a major compression factor of SQL Server backup compression in conjunction with the newly introduced encryption, the compression is performed on the backup data first, before the encryption is applied to the compressed data. With this order of actions, a major compression factor can be preserved while the backup as it rests on the backup vehicle is encrypted.
Saw this which you may be interested
Q: Is there a performance impact for using TDE?

A: Yes, some performance overhead is involved in using TDE. The encryption and decryption process do require additional CPU cycles. The overhead 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.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, parts of the transaction log may still remain protected, and the certificate may be needed for some operations until the full backup of the database is performed."

tks. is the certification and private key part of Windows files backup instead of database backup ?

one thing is, what I read is, I can't restore database to a different server for DR purpose, then how can I setup the DR process ? must use mirroring (SQL 2008/2008 R2) or DAG (SQL 2012/2014) ?

failover process should be the same ?

SQL 2014 TDE seems only better on encrypted database backup, so the TDE of SQL 2012 and SQL 2008 are the same ?
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
the keys are stored in different place.
2008 R2 - http://msdn.microsoft.com/en-us/library/ff773063(v=sql.105).aspx
stores the backup of the certificate and the private key file, in the default data location for this instance of SQL Server (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA).
2014 - http://msdn.microsoft.com/en-us/library/ff773063(v=sql.120).aspx
SQL Server stores the files created here in C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA by default. Your file names and locations might be different.
The links above is on moving a TDE Protected Database to Another SQL Server. The backup script is stated in a/m links too.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
but those is not about DR process of encrypted TDE database, any reference?

the detach and attach approach by some article is not possible, this is what I am worrying about. the link didn't say about if the same key and cerf need to be store in the same folder as the source server before the attach works.
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
DR for MSSQL should be based on the existing DR you have in place and the additional DR with TDE enabled still need the backup and restore of the keys and cert. The DR for MSSQL is actually getting a bit out of the discussion which may render any separate discussion. Some sharing on (still) the crypt stuffs for TDEto backup and restore
http://mssqltrek.com/2011/09/02/sql-server-encryption-%E2%80%93-part4how-to-restore-an-tde-enabled-database-encrypted-backup/
https://cprovolt.wordpress.com/2013/07/16/sql-server-2008-r2-disaster-recovery-after-transparent-data-encryption-tde-implementation/
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"The DR for MSSQL is actually getting a bit out of the discussion which may render any separate discussion. "

the reason I ask is after implementing TDE, the DR process can be different, which I am going to implement for that.  without TDE, I am not going to ask this at all.

"the additional DR with TDE enabled still need the backup and restore of the keys and cert."

just copy it to the same folder in the DR SQL server?
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
Yes - See my last posting which include the script to backup and restore
2008 R2 - http://msdn.microsoft.com/en-us/library/ff773063(v=sql.105).aspx
2014 - http://msdn.microsoft.com/en-us/library/ff773063(v=sql.120).aspx
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
that link is about move that TDE DB to another DB server by detach and attach ,but not backup and restore, right?
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
there is backup and restore of cert e.g. BACKUP CERTIFICATE, CREATE CERTIFICATE.
You can catch the list of related BACKUP and RESTORE Statements (Transact-SQL)
 @ http://msdn.microsoft.com/en-us/library/ff848768(v=sql.105).aspx
OR
See Examples shared in below too

SQL Server –Encrypting and Securing Native Backups Using Transparent Data Encryption (TDE) -  using BACKUP DATABASE and BACKUP CERTIFICATE
http://sqlserverzest.com/2013/10/03/sql-server-encrypting-and-securing-native-backups-using-transparent-data-encryption-tde/

SQL Server – Restoring a TDE Encrypted Database to a Different Server - using RESTORE DATABASE and CREATE CERTIFICATE  http://sqlserverzest.com/2013/10/03/sql-server-restoring-a-tde-encrypted-database-to-a-different-server/

other useful resources
Backing Up and Restoring Databases in SQL Server - http://msdn.microsoft.com/en-us/library/ms187048(v=sql.105).aspx
How to enable/remove Transparent Data Encryption (TDE) - http://blogs.msdn.com/b/batuhanyildiz/archive/2012/10/16/how-to-enable-remove-transparent-data-encryption-tde.aspx
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
oh, man , you show me a lot, thanks !


one question, how much you do for the TDE configuation, you already implement that for a long time?

"there is backup and restore of cert e.g. BACKUP CERTIFICATE, CREATE CERTIFICATE.
 You can catch the list of related BACKUP and RESTORE Statements (Transact-SQL)
  @ http://msdn.microsoft.com/en-us/library/ff848768(v=sql.105).aspx
 OR
 See Examples shared in below too

 SQL Server –Encrypting and Securing Native Backups Using Transparent Data Encryption (TDE) -  using BACKUP DATABASE and BACKUP CERTIFICATE
http://sqlserverzest.com/2013/10/03/sql-server-encrypting-and-securing-native-backups-using-transparent-data-encryption-tde/"

this seems to me that backup and restore the TDE ed database is the same as before ! right? nothing special.

As long as I backup  and restore the same DB to other DB server is ok, I don't have any concern.

for restore, I guess right:

"To restore successfully, we will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. As a best practice, we should immediately back up the certificate and the private key when we enable TDE. However, we can still take backup the certificate and private key now in the source server as shown below if not done earlier.""

"How to enable/remove Transparent Data Encryption (TDE) - http://blogs.msdn.com/b/batuhanyildiz/archive/2012/10/16/how-to-enable-remove-transparent-data-encryption-tde.aspx "

tks for that, good to roll back in case TDE create sth wrong for me.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
btan,

I just "follow" you, please connect with me.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
do you have any DR/mirror/DAG and respective failover procedue for TDE ed database ?
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
pardon me the link is the guideline to go with your DR plan, and apologies I have no other specific DR plan pertaining to TDE DB other than those recommended than MS article shared so far. You should have a DR plan not specific to DB only - this is all part of BCP and criticality analysis to do up impact analysis and churn out the RTO and RPO to suite your env. You cannot reuse other DR or BCP as it differs.

Other means for DB HA, you should then explore the application delivery controller (ADC) such as F5 networks or Citrix. That is off topic already please.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"You cannot reuse other DR or BCP as it differs.
"

I don't mean that!

"You cannot reuse other DR or BCP as it differs.
"

they can be a guideline, just like that technically follow steps. the real DR one and BCP one will be diff from company to company.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
btan,

from the link you send me, I still only see backup and restore DB but not some kind of mirror failover procedure for TDE database.
0
 
LVL 61

Expert Comment

by:btan
Comment Utility
Hope this blog helps in mirroring the encrypted database using T-SQL.
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/31/setting-up-database-mirroring-in-sql-server-2008-using-t-sql-when-the-database-is-encrypted-using-transparent-data-encryption.aspx

As for the HA and failover aspects, the blog is on to configure Always On for a TDE database
http://blogs.msdn.com/b/sqlserverfaq/archive/2013/11/22/how-to-configure-always-on-for-a-tde-database.aspx

On related note, the links may be of interest

See "Follow Up for TDE Protected Databases" (way below inside site)
http://msdn.microsoft.com/en-sg/library/ff878487.aspx

General High Availability (Database Engine)
http://msdn.microsoft.com/en-us/library/bb522583(v=sql.105).aspx

This blog talks more about Availability group and AlwaysOn in 2012
https://www.simple-talk.com/sql/database-administration/sql-server-2012-alwayson/
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now