January 17, 2018 at 11:01 am
Good morning All
I have a few questions about TDE for sql 2016
We planning to implement TDE for 500 prod databases, those dbs are small, average 2GB per db
from time to time we back those up and use them to refresh our QA environment data....for all 500dbs
here is my question,
do i need to specify a decryption key for every single database during the QA restore or do i just temporary disable encryption on prod and qa
restore databases to qa and then just re-enable encryption???
btw, we use dynamic restore script for those 500 dbs...so i am not sure how those 500 unique (per database)decryption keys can be specified for those dbs
thank you for you help!!
January 17, 2018 at 11:48 am
Are all these dbs on the same instance? Or really, do they all use the same certificate to protect the DEK (databsae encryption key)? The DEK is in each TDE database, but it isn't backed up or restored. The certficate that encrypts it, which should exist in the master db, is the one you need to restore to QA. If there are different ones, you can restore all of them to QA, though they need different names.
Keep in mind this compromises security a bit, so protect your QA server.
January 17, 2018 at 11:51 am
You don't need to specify a decryption key to back up the databases, but you also can't just disable encryption either.
What you need to do to backup a TDE-protected database to be able to restore it (in a non-TDE protected environment) is:
1. Decrypt the database
2. Drop the database encryption key (note, this is NOT the certificate used for encrypting the database!)
3. Backup the database
4. Re-create the database encryption key (protected by the certificate)
5. Enable encryption
So, with 500 databases, your refresh is going to take a fair bit of time. If you skip step 2, the backup will still not be restorable to another system (learned this through experience.)
An alternative, but bear in mind this also has some risks associated with it, would be to take the certificate used to encrypt your databases in production and copy it (or them, if you're using many) to your QA environment. Then you can just backup the databases and restore them as normal in QA.
January 17, 2018 at 12:09 pm
SD1999 - Wednesday, January 17, 2018 11:01 AMGood morning All
I have a few questions about TDE for sql 2016
We planning to implement TDE for 500 prod databases, those dbs are small, average 2GB per db
from time to time we back those up and use them to refresh our QA environment data....for all 500dbs
here is my question,
do i need to specify a decryption key for every single database during the QA restore or do i just temporary disable encryption on prod and qa
restore databases to qa and then just re-enable encryption???
btw, we use dynamic restore script for those 500 dbs...so i am not sure how those 500 unique (per database)decryption keys can be specified for those dbsthank you for you help!!
This topic was addressed in the following thread:
Restoring a TDE enabled database question
January 18, 2018 at 9:23 am
SD1999 - Wednesday, January 17, 2018 11:01 AMGood morning All
I have a few questions about TDE for sql 2016
We planning to implement TDE for 500 prod databases, those dbs are small, average 2GB per db
firstly, 500?? :w00t:
SD1999 - Wednesday, January 17, 2018 11:01 AM
from time to time we back those up and use them to refresh our QA environment data....for all 500dbs
here is my question,
do i need to specify a decryption key for every single database during the QA restore or do i just temporary disable encryption on prod and qa
restore databases to qa and then just re-enable encryption???
btw, we use dynamic restore script for those 500 dbs...so i am not sure how those 500 unique (per database)decryption keys can be specified for those dbsthank you for you help!!
You cannot just unencrypt the database and expect to move it from one instance to another and think that things will be fine and dandy, they likely won't.
Even when you remove TDE there may still be portions of the log which haven't been truncated that are encrypted and this requires the certificate which was used to encrypt the Database Encryption Key in the first place.
You're going to need to think very carefully here and here's just a sample of the questions to ask
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply