WITH ENCRYPTION option for Synchronization Script

tlentine

I have what I think is a simple issue to resolve, I just can't quite seem to figure it out.

I am using a combination of SQL Source Control, SQL Compare and SQL Packager for source control, comparison of schemas, and deployment

I can succesfully compare two versions of my database in source control to identify all of the objects that have changed, been added or removed.
So far so good.

Our policy is to encrypt stored procedures, functions, etc. as part of deploying updates to client sites. In the past we've used a home-grown tool for all of this but recently made the switch to your products

I have tried using the option in SQL Compare to include "WITH ENCRYPTION" so that the synchronization script will handle this automatically for me.

However, using that option increases (quite substantially) the number of "Different" objects in my database, presumably because objects in source control are not encrypted.

This makes it incredibly unwieldly to operate since I don't want to re-deploy every single sproc, trigger, function or view.

Is there a way to include "WITH ENCRYPTION" as part of the synchronization script generation process but not have it as part of the comparison process? Or, alternatively, if SQL Packager could add this to each statement that would work as well.

I don't really have a strong preference for where this actually takes place, but just that it does take place so that I do not not have to either hand-code the synchronization script with these changes or review every single object to identify which changes are valid and which are just because the script has "WITH Encryption" while the target db (in source control) does not.

Thanks in advance for any insight you can provide.

tlentine

Never mind, I found the option I was looking for in SQL Packager!

Brian Donahue

Thanks for the update. SQL Packager usually has all of the same schema options as SQL Compare, so you can specify ADD WITH ENCRYPTION to programmability objects. (I say usually because SQL Compare is sometimes a version ahead of the comparison engine used in Packager...)