Deploying Dynamic Data Masking Changes

I wrote a short piece over on the Redgate blog that covers how SQL Compare can catch and build a script to move Dynamic Data Masking (DDM) code from one database to another.

As the surface area of SQL Server programming changes, moving these items from one instance or database to another becomes important. I know that Microsoft doesn’t always make this easy or smooth, so we need tools to help.

DDM changes are fairly simple, but I can see these being implemented in lots of code, as they’re an easy way to mask an obscure some data in an app without code changes, but we need to be sure these changes get deployed from development to production.

Or production to development, if you’re the kind of person that works that way Winking smile. Don’t worry, SQL Compare can help there, too.

SQL Compare 12 Beta

The Beta is out. I’ve been waiting for this, and was glad to see Carly’s post on the product. I’ve been involved in looking at some early designs and functionality, and have been waiting for the product to get to the point I could play with it more.

Be sure you grab the beta if you like Compare, give it a try, and send us feedback. We really try to be responsive as a company, and certainly want Compare and Data Compare to be the best products out there.

New Look and Feel

Redgate has been looking to get a new look and simpler designs as a company. You can see that with the SQL Compare installer.

2016-06-03 16_49_37-SQL Compare

The icons have changed, but that’s not all. When you run SQL Compare, you notice this right away.

2016-06-03 17_41_23-New Project_

Once you select the items to compare, you see a familiar connection dialog.

2016-06-03 17_41_57-New Project_

If I compare, I see various results. In this case, I’ve got two demo databases in two different stages. Each has objects the other doesn’t, and I can get a cleaner (IMHO) view of the objects.

2016-06-03 17_45_27-SQL Compare - C__Users_way0u_Documents_SQL Compare_SharedProjects_(local)_SQL201

There are plenty of SQL Server 2016 enhancements, a few bug fixes, and lots of look and feel changes. There’s still some work to do, but we’re confident this version is ready to show. Let us know how the product works for you and what you’d like to see changed.

Clearing Out a Database With SQL Compare

I ran into a question recently about how to clear out a database of all objects. I assume someone was testing some type of deployment scenario, and didn’t want to drop the database, but rather just remove objects and redeploy.

I know SQL Compare could do this, so I mocked up a quick project.

I’ll start with a database. I grabbed one I had lying around, and in this case, I’ll use the Sandbox database, which has a number of tables in it.

2015-07-23 11_19_06-XML_Basic_Queries.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (69)) - Microsoft SQL

Perhaps I’m testing upgrades, installations, or something and don’t want to drop this. Perhaps it’s in Azure, and recreating it is a pain. Maybe I want users/roles/security still to be there. Whatever the reason, this is a use case that SQL Compare can handle.

First, I need a new database.

2015-07-23 11_36_06-SQLQuery2.sql - ARISTOTLE.tSQLt_Exercises (ARISTOTLE_Steve (65))_ - Microsoft SQ

With this, I can now run SQL Compare and look at my two databases.

2015-07-23 11_36_49-New Project_

I am comparing the blank database to the existing one. The empty database is essentially development. I want to get the existing one to look like this one. I might need to edit the filter rules on the left to exclude roles, users, etc., but running the comparison shows me the differences that I can examine in more detail.

2015-07-23 11_37_47-SQL Compare - New Project_

In my case, I did exclude security, and once I was happy with the list, I clicked the Deployment Wizard button. This immediately generated a script, but gave me warnings.

2015-07-23 11_38_05-Deployment

I am well aware that dropping these objects could result in lost data. That’s because I’m removing tables. However that’s what I want.

If I open the script in an editor, I can see all the drops, properly ordered, in the script.

2015-07-23 11_40_46-SQL Compare844a2b43-aebf-4f51-bf84-ab3f73065053.sql - (local).sandbox (ARISTOTLE

This is a quick way to remove the objects from a database to test your installation or rebuild of a database. I’m not sure this makes sense for most deployments, as you’ll usually want to just remove the objects that were added in the failed deployment.

Generating a rollback script is a task for another day. For now, if you need a clean database, here’s one way to remove everything from an existing database.

Building Rollback Scripts with SQL Compare

There’s a neat switch in SQL Compare that lets you build rollback scripts. It looks like this:


I’ve used this before to help with deployments, as many of you have. The idea is that when you finish with your code in your dev environment, you run a comparison with production, doing something like this:


I’ve set the source as my development place, and the destination as production. I click "Compare Now", get a script that will deploy the changes from development to production, and save it. I can use that script as part of my deployment process.

However as soon as I’ve saved that script, I return to this screen, and I click the "Switch" button at the bottom.


If you look closely, you’ll see that my source (on the left) and destination (on the right), have changed places. This means I’ll now generate a script that takes my production environment and generates the script to get back production from development.

This is a rollback script.

It doesn’t work in all situations, and you really have to think about what you’re changing, but if you’re just doing views/procedures/functions, this is a great way to get that quick rollback script that you store alongside the deployment script in case things go badly.

Excluding Schemas in SQL Compare

I saw this question posted the other day and thought it was a great idea.


I haven’t been a big schema user in the past, but I see the value of excluding some schemas. CDC for sure, but I know some people have an ETL schema, which is much nicer than having Staging_Customers. I think the exact same structure in ETL.Customers moving to dbo.Customers is a good idea.

sql-compare-logoI sent a note to the PM for SQL Compare, since I thought this might be one to bump up on the list of things that might make it into Compare 11. We’re on Compare 10 now, which is amazing. I remember Andy Warren talking about how cool this product was back in 2002 when it was just SQL Compare.

The PM told me we already do this, so I started digging in. Sure enough, we can do it.

I can do a quick compare of two databases on a test instance. I’ll pick a new project and select two similar, but slightly different dbs.


Once the comparison runs, you see there are three objects that are in one database, but not the other.


This shows me I have a schema (ETL) and a table (ETL.Contact) in that schema, as well as a separate table in another schema (Person.Contact2). I want to exclude the ETL stuff, so what can I do?

There’s a Filter Rules button, that shows the filters on the left side by default. It contains all the types of objects, which is what I normally use it for, excluding some types.


However I can click the “Edit Filter Rules” button, and I get a dialog, where I can enter various conditions.


Here I’ve excluded (upper left) objects if the Schema begins with ETL. I can click OK and then re-run the comparison. I’ll then get:


Better, but I still have the ETL schema, which I may not want to move over.

As a visual cue (if you look for it), I can see I have a rule set in the Filter pane on the left. It’s subtle, but it’s in a maroon-like color.


If I go back, I can actually add a second condition here and change my operators to prevent mismatches with similar names, by using an “Equals” operator.


Now I get what I wanted. Note that this took effect immediately and I didn’t have to run the comparison again. SQL Compare finds all the changes and then filters them.


If this is something I do regularly, like move from Development to QA, I can save the project:


The next time I need to run this, instead of using the New Project dialog, I can just open this one and run the comparison.