December 8, 2015 at 12:25 pm
We're being pushed by a prospective customer ( bank ) to add TDE ( Transparent Data Encryption ). PII ( e.g. social security ) columns are already encrypted with Safenet.
I'm wondering how useful this is when the sql backup is 900GB compressed. Is that at risk for theft? Also, we rarely create such a native backup since Netapp SnapManager for sql takes snapshot backups which are mirrored to Disaster Recovery.
The live database files might be as small as 50GB, although the largest is over 500GB. You can't copy those when sql is running ( 99% of the time ). If you got one of the smaller ones, what could you do with it, without the others?
As to the snapshot ( delta ) backups, no idea what TDE does with those but obviously if we continue we'll have a lot of testing to do, aside from any performance issues.
December 8, 2015 at 5:06 pm
Opened a case with Microsoft Premier Support
December 8, 2015 at 11:11 pm
The bank is probably adhering to https://www.pcisecuritystandards.org/, which is useful for Sales :).
TDE is not compressible (pragmatically, you might be able to reduce file size by a pitiful X% after compressing for hours, but you will hate yourself for trying). Using compressed backups is not possible (at least with native backups) and likely to not be pragmatic (with 3rd party backup solutions, if supported). With TDE, instant file initialization is also not possible (even after you let SQL Server Perform Volume Maintenance Tasks). Encryption or decryption is done lazily as a background system process, will take hours, and can block alter database statements. Otherwise I don't think you will notice TDE. For peace of mind, you could do baseline comparisons of realistically distributed, but staged and simulated, loads.
Your snapshot process' copies are likely to be accomplished at the block or sector level, which is well below Windows (let alone SQL). Your snapshot process is likely to freeze and thaw database IO (while the last sectors or blocks are being copied), but that is likely to be the extent of its awareness of SQL Server (I doubt it knows anything about TDE). I would not expect the snapshot process to be at all impacted by TDE, assuming it doesn't compress..
Make sure both of your servers use the same certificate for TDE, otherwise the snapshotted copy will be unreadable by its SQL Server. And at least one 2012 cumulative update needs to be setup (unsure which).
December 9, 2015 at 7:00 am
Thanks for the reply.
Keys/certificates etc were created and backed up,. Encryption is proceeding -- 6% in 30 minutes so far. Inserts, updates, deletes and selects were successful.
I had to flip the database to full recovery model to attempt a log backup but then it considers yesterday’s full backup invalid for the purpose of a log backup. A differential backup ran, but was blocked by a checkpoint process which I presume is related to encryption. Native-style backups may not be possible during encryption.
Normally we use native backups once or twice per year to refresh QA. All normal backups are via that Netapp snapmirror process. In the DR cluster, sql is normally shut down with snap mirror just updating the data and log files. The user databases are all detached normally.
To bring up DR, we run snapmanager for sql restores.
In moving a small encrypted database yesterday, to a new server, this is how I proceeded.
-- restore to sacsqldev002 Create Key on new server with new pwd xxxxxx
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxx'
--After a master key has been created, create a certificate by importing the certificate we created earlier. Here the ‘Decryption By Password’ parameter is same as that provided to export the certificate to a file from source server.
CREATE CERTIFICATE ProdQATDECert
FROM FILE = 'K:\TDEKeysBackups\SacsqlDev003_TDE_Test_Certificate.cer'
WITH PRIVATE KEY (FILE = 'K:\TDEKeysBackups\SacsqlDev003certificate_TDE_Test_PrivateKey.pvk',
DECRYPTION BY PASSWORD = 'xxxxxxx')
-- Restore Database in destination server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply