November 21, 2014 at 7:44 am
The SQL Server 2008R2 instance I have inherited already has Transparent Data Encryption (TDE) implemented on some of the databases on it, but it has now been determined that all the databases on this instance have TDE switched on. So my questions then concerning the ones that are not already encrypted using TDE:
1. How does switching TDE on affect the backup set(s) for a database? Will an immediate full backup be needed in order to keep point in time restores viable? If so, should I overwrite existing backup sets and start a new one? Or will the restoration of backups not care if say I start with an unencrypted full backup with one or more transaction log backups taken after the encryption is implemented?
2. We also have a development environment where the same databases are used for development efforts. Will these need to be replaced with restored backups from production, or can I just implement TDE on them using the same certificate/database master key used to encrypt the production versions? The development databases we use from the production versions that have already been encrypted were restored using these keys/certs so they are already on the development server.
My apologies if these are stupid/redundant questions, but I cannot seem to locate any guidance on these questions -- so any help is appreciated here.
November 21, 2014 at 8:28 am
Siberian Khatru (11/21/2014)
1. How does switching TDE on affect the backup set(s) for a database?
Once TDE is enabled in the database all backups will be dependant upon the certificate or key used to encrypt the database.
Siberian Khatru (11/21/2014)
Will an immediate full backup be needed in order to keep point in time restores viable? If so, should I overwrite existing backup sets and start a new one? Or will the restoration of backups not care if say I start with an unencrypted full backup with one or more transaction log backups taken after the encryption is implemented?
No, although to avoid confusion for anyone attempting to restore the database it may not be a bad idea if you were to take an immediate full backup so that all backup files are immediately dependant upon the cert\key.
Siberian Khatru (11/21/2014)
2. We also have a development environment where the same databases are used for development efforts. Will these need to be replaced with restored backups from production, or can I just implement TDE on them using the same certificate/database master key used to encrypt the production versions? The development databases we use from the production versions that have already been encrypted were restored using these keys/certs so they are already on the development server.
You can manully encrypt the databases on the dev server if you really want to, although i would have thought it more sensible to perform a refresh from the prod to dev to test your TDE management skills
😉
Note: the database master key is not required from a source server to restore TDE protected databases to a target server, in fact, it's probably better if you don't. All that's needed is the cert that the database is encrypted with.
Siberian Khatru (11/21/2014)
My apologies if these are stupid/redundant questions, but I cannot seem to locate any guidance on these questions -- so any help is appreciated here.
This is[/b][/url] a good start
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 21, 2014 at 9:32 am
Perry Whittle (11/21/2014)
Siberian Khatru (11/21/2014)
1. How does switching TDE on affect the backup set(s) for a database?Once TDE is enabled in the database all backups will be dependant upon the certificate or key used to encrypt the database.
Siberian Khatru (11/21/2014)
Will an immediate full backup be needed in order to keep point in time restores viable? If so, should I overwrite existing backup sets and start a new one? Or will the restoration of backups not care if say I start with an unencrypted full backup with one or more transaction log backups taken after the encryption is implemented?No, although to avoid confusion for anyone attempting to restore the database it may not be a bad idea if you were to take an immediate full backup so that all backup files are immediately dependant upon the cert\key.
Siberian Khatru (11/21/2014)
2. We also have a development environment where the same databases are used for development efforts. Will these need to be replaced with restored backups from production, or can I just implement TDE on them using the same certificate/database master key used to encrypt the production versions? The development databases we use from the production versions that have already been encrypted were restored using these keys/certs so they are already on the development server.You can manully encrypt the databases on the dev server if you really want to, although i would have thought it more sensible to perform a refresh from the prod to dev to test your TDE management skills
😉
Note: the database master key is not required from a source server to restore TDE protected databases to a target server, in fact, it's probably better if you don't. All that's needed is the cert that the database is encrypted with.
Siberian Khatru (11/21/2014)
My apologies if these are stupid/redundant questions, but I cannot seem to locate any guidance on these questions -- so any help is appreciated here.
The reason I wondered about separately encrypting the dev databases was due to ongoing development happening there. I would personally like to just bring down the latest Prod DBs and make them dev, but this is not my call.
Thanks for the link and the advice sir. Appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply