April 29, 2013 at 1:03 pm
I cannot find any information specifically on this at this point, but I was curious if anyone knew if removing page-level compression on an encrypted database will decrypt it? We had a database decrypt this weekend after a deployment, and the only think that logically makes sense to me (since there was no specific decrypt command issued) is that removing page-level compression required the database to be decrypted. Any thoughts?
Jared
CE - Microsoft
April 29, 2013 at 1:35 pm
No, removing compression on a table or set of tables will not disable TDE. As for decrypting the pages, SQL does that every time they're pulled into memory, then they're re-encrypted when they go back to disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2013 at 1:40 pm
GilaMonster (4/29/2013)
No, removing compression on a table or set of tables will not disable TDE. As for decrypting the pages, SQL does that every time they're pulled into memory, then they're re-encrypted when they go back to disk.
Any thoughts on what could do this without actually issuing the command? I'm having some problems correlating events at this point; i.e. could memory pressure do this? Rebuilding a cube? I am just not knowledgeable enough here... Or am I just in denial that someone/thing issued the direct command?
Jared
CE - Microsoft
April 29, 2013 at 2:04 pm
Like with just about all database settings, SQL won't randomly change them for you.
Disabling TDE - ALTER DATABASE <db name> SET ENCRYPTION OFF.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2013 at 2:09 pm
GilaMonster (4/29/2013)
Like with just about all database settings, SQL won't randomly change them for you.Disabling TDE - ALTER DATABASE <db name> SET ENCRYPTION OFF.
I guess it is time to track down the deployment code that did this... Thanks again, Gail.
Jared
CE - Microsoft
April 29, 2013 at 2:16 pm
Or someone could have disabled it manually. Should be in the default trace.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2013 at 2:40 pm
Are there any commands that implicitly decrypt? I know it is not random, but maybe a misunderstanding of a command. The only command I see issued is ALTER TABLE <table name> REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
I am also looking at the default trace, but I'm not sure the event that I am looking for here. Definitely one of my weak points. The code I am running to query the trace and eyeball it is this:
SELECT
*
FROM ::fn_trace_gettable('G:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_284.trc',0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE startTime >= '2013-04-25 19:42'
ORDER BY StartTime I have also tried looking for TEXTDATA LIKE '%ENCRYPT%' to no avail.
Jared
CE - Microsoft
April 29, 2013 at 3:45 pm
To disable TDE, one would run an ALTER DATABASE, that's all.
Take a step back, what was the last point in time when you knew that the DB was encrypted? How did you determine that it had been unencrypted over this deployment?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2013 at 3:53 pm
On a daily basis we spit out the SQL logs into a daily report that our primary reviews. In the SQL logs, we see the following:
Apr 25 2013 7:42PM <server> SQL Log: spid51 Setting database option PAGE_VERIFY to NONE for database <database name>.
Apr 25 2013 7:42PM <server> SQL Log: spid51 Setting database option ENCRYPTION to OFF for database <database name>.
Apr 25 2013 7:42PM <server> SQL Log: spid26s Beginning database encryption scan for database '<database name>'.
This was the time that deployment was occurring. So, our assumption is that something in the deployment (code release, TFS database project deployment, etc.) caused this to occur.
Jared
CE - Microsoft
April 29, 2013 at 4:07 pm
Something or someone (connected on session_id 51) ran two alter database statements there (or went and clicked them in the GUI)
ALTER DATABASE <db name> SET PAGE_VERIFY = NONE
ALTER DATABASE <db_name> SET ENCRYPTION OFF
How far back does the default trace run?
Why 0 for the rollover files? With that, your query would only read the specified trace file, not all the ones you have
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2013 at 4:22 pm
The trace goes back to 2013-04-25 01:34:56.880. I assumed that by using this file (its the oldest) I would get the date I need. It does in fact have the date range I need. I'm just not sure what more data I can get besides the SPID for this event at this point. I am currently trying to check some of our monitoring tables to see if I can match the spid to a login.
Jared
CE - Microsoft
April 29, 2013 at 4:38 pm
I was able to find the processes in a history table we keep. It looks like the user was using DacFX Deploy to deploy the changes. My guess at this point (without talking to the user as it is after hours now) is that the settings on the deployment executed these commands.
Jared
CE - Microsoft
July 25, 2014 at 8:53 am
Hello Mr. Gila,
I have a huge database 600+ GB and it is TDE enabled. I want to make database little smaller. I found some tables are more than 1 million rows.
I need your suggestion if I can do Page compress? is there any script I can compress 20+ tables same time? what are the consequences since it's Prod database and TDE enabled.
Thanks in advance
July 25, 2014 at 1:45 pm
Please post new questions in a new thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply