more on masking

Since our last post, we’ve added new features and fixed a few bugs! Please update to 0.9.730.

New features

  • Each table update is now a transaction
  • Support for NULL values and nullable columns
  • Support for deterministic masking, which means that a value will always be masked with the same value across tables

You can see these in the new config output from New-DbaDbMaskingConfig.

Want to know more about deterministic masking? Check out Sander’s blog post, hot off the press: deterministic masking with dbatools.

Bug fixes

  • Some masking types like UserName were ignored
  • Columns containing NULL values did not get updated
  • Sometimes a bit would be interpreted as a single character

I also updated the sample mask config. If you tried before and it failed, my apologies. We fixed one bug and introduced another. It should work now 👍

Invoke-DbaDbDataMasking -SqlInstance sql2017 -FilePath https://sqlps.io/maskconfig

Before

After

WhatIf I ran this?

We do support very detailed -WhatIf scenarios in this invoker command. Run WhatIf to see what would happen if the command were to execute.

A video with sound!

I gave in and made a video with sound! You can view my fifth attempt on our YouTube Channel 😁.

Happy Holidays, Everyone!

- Chrissy

3 thoughts on “more on masking

  1. Tony Santangelo (BassManDBA) Reply

    Awesome to see this new masking function progressing so quickly.

    One additional feature (if not already included) would be the addition of masking exclusions on columns that contain such data as email addresses etc. based on some configuration input criteria. This would allow you to exclude masking any dataset that has a specific email patterns (e.g. EmailAddress NOT LIKE ‘@corpdoman.com’). This would cover situations where DevOps folks like to use their corp emails in the data to test certain application functionality and would allow us to mask all other email addresses but skip those we don’t wish to mask.

    Sure we can argue that the dev teams should have scripts to update the dev databases after we’ve refreshed and masked them, but in at least one case in my environment the DevOps team has test data in the prod databases to test application email functionality so they preferred that I just exclude any data that matched a given email domain when the masking was run after a refresh.

  2. Martin Guth Reply

    Nice blog post about a feature playing with sounds worthwile.
    In my database there are lots of XML columns. How could one handle these? I did not find anything particular about it in the microsoft docs site but could imagine that one is just left with the options “mask with null value” or “mask with static value”.

    • Chrissy LeMaire Post authorReply

      That is a great point! XML is currently not supported in our commands, though Sander tried using regex to do the XML and it was very challenging. We could actually add a field called StaticValue to the config that could do static values for any field! I’ll pass it by him.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.