Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Running multiple TDE encryptions at once... Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2014 10:19 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 725, Visits: 5,188
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)
Post #1599834
Posted Thursday, August 7, 2014 7:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 6,369, Visits: 13,703
why on earth would you want to blanket deploy TDE???


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1600606
Posted Thursday, August 7, 2014 10:14 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 725, Visits: 5,188
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...
Post #1600747
Posted Thursday, August 7, 2014 2:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 6,369, Visits: 13,703
Just be aware of the overhead for CPU

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1600894
Posted Thursday, August 7, 2014 3:36 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 17,608, Visits: 15,468
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1600920
Posted Thursday, August 7, 2014 7:59 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 725, Visits: 5,188
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
Post #1600995
Posted Friday, August 8, 2014 12:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 6,369, Visits: 13,703
OK as long as you're aware

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1601033
Posted Friday, August 8, 2014 7:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 17,608, Visits: 15,468
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1601172
Posted Saturday, August 9, 2014 7:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 725, Visits: 5,188
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
Post #1601396
Posted Saturday, August 9, 2014 5:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 20,676, Visits: 32,269
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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1601499
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse