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.

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?

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?

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.

LishaUncovering the meanings of your dreams is possible, visit sitehere which has a dream dictionary free to analyze your dreams. It'sdream dictionary nowand their are thousands of dreams analyzed so you can lookup any dream.