Running multiple TDE encryptions at once...
-
I'm going to be enabling TDE on all the DBs on my servers over the next couple weeks, and have a question. I'm thinking about setting up the encryptions to be run as Agent jobs on the weekends in Production, and I'm wondering if it will work out to about the same amount of time or faster (or slower) if I have multiple Agent jobs, one per-database, rather than one big Agent job with one step per database.
Basically, running multiple "Set encryption = on" tasks at the same time.
I'm starting to think it'll work out to about the same amount of time as doing them in serial (multiple job steps,) just because with multiple job steps, the CPU / SQL is focussed on one DB at a time, but I'm hoping someone might have a thought on this (or even better, experience)
I'd set this up in a test lab, but my testing machines are shut down prepatory to a move in a couple weeks...
Thanks,
Jason
(PS. I've already started testing TDE in our QA environment, but didn't set up the encryption in Agent)
-
why on earth would you want to blanket deploy TDE???
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
We're required to comply with the DoD STIG guides for our systems, and one of the requirements is "data files containing sensitive information must be encrypted" and "SQL Server must protect data at rest..." Well, they consider *everything* in the DBs to be "sensitive," so...
Unfortunately, while I could argue that it's not needed, I'd get more results trying to dig my way to Jamaica with my bare hands, so it's easier to just bite the bullet and do it...
-
Just be aware of the overhead for CPU
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
I am running about 12 databases on one server totaling 1.5TB - all with TDE enabled. The total cpu cost is about 5%.
The problem with enabling Encryption on all of them via different jobs is the encryption sometimes gets hung up and will show 0 in percent_complete and also show that it is encrypting. This happens from time to time and requires restarting the encryption for that database (disable re-enable). Unless a long delay is ok, you might want to monitor the progress.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Perry: I think CPU-wise we should be OK, and I'm not going to "bulk encrypt" all the other DBs when I'm done, I'll be doing it in chunks. We're targeting DBs initially that have "real" sensitive information in them, then moving on to the others.
Plus, these DBs are all far more read-heavy then insert-heavy, so that should help some.
SQLRnnr: At this point, I've decided to go with one Agent job, one DB per step, starting with the smaller DBs (under 1GB {Yes, under a GB}) first, then moving on to the bigger DBs. If one fails, it'll fail the Agent job and e-mail me, so I can restart skipping the problem child and fix it after. Plus I'll check on it off-and-on throughout the day / weekend...
Thanks guys!
Jason
-
OK as long as you're aware
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
jasona.work (8/7/2014)
Perry: I think CPU-wise we should be OK, and I'm not going to "bulk encrypt" all the other DBs when I'm done, I'll be doing it in chunks. We're targeting DBs initially that have "real" sensitive information in them, then moving on to the others.Plus, these DBs are all far more read-heavy then insert-heavy, so that should help some.
SQLRnnr: At this point, I've decided to go with one Agent job, one DB per step, starting with the smaller DBs (under 1GB {Yes, under a GB}) first, then moving on to the bigger DBs. If one fails, it'll fail the Agent job and e-mail me, so I can restart skipping the problem child and fix it after. Plus I'll check on it off-and-on throughout the day / weekend...
Thanks guys!
Jason
Curious what you will be putting in the agent step - just the command to encrypt the database?
If that command fails - great. But that command can succeed in about 1 ms. It doesn't wait for the database to fully encrypt.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Jason,
Yes, each step in the Agent job was the "Create database encryption key" and "alter database enable encryption" steps.
Aanndd thanks to this, I got all paranoid when one of the DBs being encrypted seemed to "stall" at 100% complete. It took it a few minutes to get to 100% (about what it took in QA) and did eventually report as being encrypted and the job went on to the next, but still...
🙂
Later today I'll go back and double check everything. If any of the DBs are in a "weird" state, I can drop them and restore from the backups I took before hand...
Thanks!
Jason
-
jasona.work (8/7/2014)
We're required to comply with the DoD STIG guides for our systems, and one of the requirements is "data files containing sensitive information must be encrypted" and "SQL Server must protect data at rest..." Well, they consider *everything* in the DBs to be "sensitive," so...Unfortunately, while I could argue that it's not needed, I'd get more results trying to dig my way to Jamaica with my bare hands, so it's easier to just bite the bullet and do it...
Can you provide the STIG? We encrypt PII/HIPPA data but not everything. Plus, all our servers are running SE so TDE isn't available.
-
Lynn Pettis (8/9/2014)
Can you provide the STIG?Yep, here you go matey 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Lynn:
Here's the info:
STIG ID: SQL2-00-021300 Rule ID: SV-53265r1_rule Vuln ID: V-40911
The reason we went with TDE rather than full-disk encryption such as the check mentions, is we're on virtuals, so Bitlocker isn't really an option and the disks in the SAN aren't encrypted in any way, so...
Jason
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply