DBCC 3604 & "Decrypt encrypted objects on 2005 and 2008"

maassql

I've read a bit about this issue here:
* http://ftp.red-gate.com/messageboard/viewtopic.php?t=961&highlight=&sid=b0aa028580e878972e78492da045245d
* http://ftp.red-gate.com/messageboard/viewtopic.php?t=842&highlight=3604&sid=4dea7c6d2e0ffddb28e0184abe715626

I have a database with 869 objects in sys.objects. The breakdown of objects is at the end of this post. There is one encrypted object, a stored procedure. When I run a comparison involving this db and set the behavior "Decrypt encrypted objects on 2005 and 2008" to true, I receive 3330 entries in the SQL & App Event logs - 1650 pairs of

DBCC TRACEON 3604
DBCC TRACEOFF 3604

When I run the same comparison with the behavior "Decrypt encrypted objects on 2005 and 2008" set to false, I receive 2 entries - 1 pair.

Further, as I'm reading, I see this statement "When Sql Compare saves a snapshot or scripts folder, this option is set, and all encrypted objects are decrypted."

Questions:
* Is it absolutely necessary to perform the decryption steps for every comparison item whenever "Decrypt encrypted objects on 2005 and 2008" is set to on? Could the app not first ask the question, is this item encrypted?

* Is it possible for SQL Compare to write a message to the SQL Log / Windows App Event log saying something like "Hi, I'm such and such app and I'm doing a comparison, sorry for filling up the logs with the following messages [x, y]. Go here to learn more http://..."

* Is the statement regarding snapshots saying that for every snapshot, the behavior "Decrypt encrypted objects on 2005 and 2008" is set to true, and that this can not be changed? If the behavior can be changed, would you please specify?

While all this may seem like a bit to do about nothing, please consider, I've just spent about 3 hours reading, testing, verifying this behavior so that I can assure my new boss @ this new job that I'm not wrecking his server by using one of my favorite tools. In this instance, it's not RedGate who made MegaBytes of entries in his logs, but the brand new guy. His assumption is that RedGate must be a bunch of yahoos for doing such a thing, and that assumption rubbed straight off onto me, because I brought your tools with me. :oops:

ONLY sys.objects
100 DEFAULT_CONSTRAINT
96 FOREIGN_KEY_CONSTRAINT
3 INTERNAL_TABLE
131 PRIMARY_KEY_CONSTRAINT
3 SERVICE_QUEUE
3 SQL_INLINE_TABLE_VALUED_FUNCTION
35 SQL_SCALAR_FUNCTION
221 SQL_STORED_PROCEDURE
5 SQL_TABLE_VALUED_FUNCTION
2 SQL_TRIGGER
41 SYSTEM_TABLE
2 UNIQUE_CONSTRAINT
217 USER_TABLE
10 VIEW

Eddie D

Thank you for your post into the forum.

For SQL Compare to read decrypted objects without using a dedicated admin connection, we have to read in the pages corresponding to the system tables and reconstruct them in memory. As SQL Compare does not know which objects are encrypted, each object must be checked.

The articles you refer to in your post are approximately 4 years old and refer to an older version of SQL Compare, what version of SQL Compare are you using?

Many Thanks
Eddie

maassql

Eddie:

Sql Compare 8.

Second, thank you for taking the time to cover my question.

4 YEARS!!!!! WOW how time flies. I've been using Red Gate's awesome products for almost 10 years now!!!!!! or 8 or 9 whatever. The point is, I tried looking the information up in the forum first And frankly to me, it looks a whole lot like the symptoms described in a March 2005 post to which was posted by Daniel Handley:

Posted: Thu Mar 10, 2005 2:24 pm Post subject:
Hi

Thanks for letting us know about this problem.

We had already spotted this and have fixed it. The fix will appear in the next minor release of the product which will be released shortly.

Regards
Dan

So, my questions still kinda remain from the original post -

:?: What I read was

SQL Compare does not know which objects are encrypted

, which is not true since this info is available in objectpropertyex(sp?), and the SQL Compare already appears to ask the question at the beginning of a run - ( if there are no encrypted objects, then don't use the traceflag code route - even if that behavior is turned on ). What you were probably trying to say is that : SQL Compare has no way to find the encrypted objects within the pages on disk, so takes the approach of reading from every page in a manner which requires the use of the Trace flag. Are you telling me that's absolutely necessary? I'm no expert on internals but it seems to me that with 2005 and 2008 there was a lot more disk if not page level info given. Even if it wasn't, I doubt that the 'name' ( however it works at that level ) is encrypted. Couldn't you find some way to identify only the objects whose pages need to take the trace flag route in your code? Again, if you're asking me why this is important, see my original post, then try running SQL Compare against an instance of Microsoft Dynamics Great Plains. Once with and once without decryption set to on. And if you're gonna tell that joke "so the doctor says, Don't move your arm that way and it won't hurt", please see my third question. One of my tasks is to take snapshots of the db schema every so often and if there is no answer to my third question, it means I am damned to extremely long scripting times. :?

:?: Is it possible for SQL Compare to write a message to the SQL Log / Windows App Event log saying something like "Hi, I'm such and such app and I'm doing a comparison, sorry for filling up the logs with the following messages [x, y]. Go here to learn more http://..."

:?: Is the statement regarding snapshots saying that for every snapshot, the behavior "Decrypt encrypted objects on 2005 and 2008" is set to true, and that this can not be changed? If the behavior can be changed, would you please specify?

Eddie D

Sorry for the delay in replying back to this forum post.

I can confirm there is a bug in SQL Compare. For some reason SQL Compare is generating a TRACEON/TRACEOFF pair for every page it reads.

TRACEON should only be issued once, for all the pages fetched.

Therefore I have submitted a bug report to the development team. The reference for this bug report is SC-4655. I will be maintaining what we call a "watch" on the bug report submitted, which means I will be notified on any updates to the bug report submitted.

I will post back into this forum topic once I have some further information.

Many Thanks
Eddie

brosato

Just thought I'd bump this up. I try really hard to keep the noise down in my log files. The thousands of DBCC TRACEON 3604 are a bit annoying.

tgibbo

Any further developments on this issue?

I have been ckecking for replies on and off since finding this issue a year ago (mainly when someone has run a compare and I have to troll through the 1000's of errors created).

Anything?

cheers,

Tony.

abean

+1, please find an alternative, or at least configure to turn it off if no encryption exists.

LishaZen11

Hi maassql, were you able to figure it out? I'm also having problem.