Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL server TDE (Transparent Database encryption)

Posted on 2014-11-16
30
Medium Priority
?
574 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 13
30 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 40446606
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
ID: 40446615
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
ID: 40447760
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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 65

Expert Comment

by:btan
ID: 40448816
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
ID: 40451613
"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 65

Expert Comment

by:btan
ID: 40451783
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
ID: 40451807
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 65

Expert Comment

by:btan
ID: 40452217
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
ID: 40452563
it seems that TDE only affect CPU, RAM and increase IO workload , and size of any database do not impacted, right?
0
 
LVL 65

Expert Comment

by:btan
ID: 40452596
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 65

Expert Comment

by:btan
ID: 40452621
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
ID: 40454259
"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
ID: 40454429
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 65

Accepted Solution

by:
btan earned 2000 total points
ID: 40454565
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 65

Expert Comment

by:btan
ID: 40454577
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40454611
"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 65

Expert Comment

by:btan
ID: 40454677
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
ID: 40454718
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 65

Expert Comment

by:btan
ID: 40455057
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
ID: 40455094
"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 65

Expert Comment

by:btan
ID: 40455124
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
ID: 40455331
that link is about move that TDE DB to another DB server by detach and attach ,but not backup and restore, right?
0
 
LVL 65

Expert Comment

by:btan
ID: 40456415
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
ID: 40456664
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
ID: 40456666
btan,

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

Author Comment

by:marrowyung
ID: 40456667
do you have any DR/mirror/DAG and respective failover procedue for TDE ed database ?
0
 
LVL 65

Expert Comment

by:btan
ID: 40456707
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
ID: 40461357
"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
ID: 40461359
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 65

Expert Comment

by:btan
ID: 40461457
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 Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

688 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