You can register now, but on Dec 3, I have a webinar with Microsoft on database devops and Azure. This is a joint effort with Redgate, so I’ll be showing some of our tools.
In the webinar, which I recorded last week, I walk through the idea of Database DevOps in general, showing how we evolve our database development to incorporate automation, use Azure DevOps, and deploy code to an Azure database.
Register today, and I’ll be ready for questions on Dec 3.
This is a quick blog to help me remember what is going on with the Data Masker product. This is for the SQL Server version, but I believe the Oracle one is very similar.
I added a new column to a table, and I had a masking plan already built. How do I get my masking plan to show the new column?
Here is my masking plan:
I added a new column to the DCCheck table, which is under rule 01-0026.
If I open that mask and add a new column, I get this, but I can’t expand the dropdown. All the columns in this table are masked, and data masker doesn’t know about the new one.
I need an updated schema, as the rules do not update in real time. To get this to work, I need to return to the masking plan and double click the controller at the top. This is the schema manager for my set of rules.
Note: If I mask different schemas, I need different controllers.
Once this opens, I can see my connection to a database. In my case, I’m building this in dev areas, so it’s pointed to the QA environment.
If I click the “Tools” tab at the top, I see lots of options, one of which is to refresh.
Once I pick that one, I have a bunch of more options, which gets confusing, but I can click the “refresh all tables” at the top, leaving everything alone. Once that’s done, I get a note.
Once I get this, I can return to my rule, and when I add a new column, and I see it listed.
This isn’t the smoothest flow, but data masker isn’t something that is likely to be in constant use. For many of us, adding new schema items is relatively rare, so we can adjust our plans as needed.
The one good thing is that I can easily find where I need to add a column, as opposed to digging through a number of .SQL scripts.
One of the things I’ve pushed for at Redgate is to document the minimum permissions needed for our products inside a database. Often this isn’t possible as some of our products require sysadmin, but some don’t. I was resetting Data Catalog up on a new machine and decided to limit things.
The installation does create a database, which you can do separately, but if you don’t do this, you need to grant dbcreator for the service account, at least for the install. If you create it, this page shows the more granular permissions if you want to do that.
The permissions needed are:
These permissions allow the service to write data to the various objects in the database.
Classifying databases is different. For this task, the account used to connect, which doesn’t need to be the service account, will read information about the tables, but does not read data. We just need to capture the column name and some metadata.
Again, this page gives a script, but essentially, we need these permissions.
Once those permissions are granted to an account, you use the account to add the instance to SQL Data Catalog.
I’m glad that we’re documenting and limiting permissions where possible. We do understand data privacy and protection are important, and the advocates at Redgate try to push developers to use as limited permissions as possible when building products.
Recently someone asked a good question about SQL Data Compare. How can they add applicationintent to the connection?
If you are using Data Compare, and you are reading from production systems, load is a concern. With Availability Groups (AG), we can point SQL Data Compare to the secondary replicas and limit the load on the writable primary. How can we do that? Well, it’s actually easy.
We have a doc page for encrypted connections, and we can use the same type of action to work with AGs.
In a new project, I typically see something like this:
What I can do is edit the server connection on the right. Just click in there, and then add this:
Aristotle\SQL2017;applicationintent=readonly
This will add the option in the connection string.
If I open the project file, I will see the XML, where I can also edit these connections if desired.
I can do this for any of the options I need for connection strings.
