I need to share a SQL Server 2008 R2 DB with the development team. The data is classified but the schema and the volumes are not. The DB includes all primitive types (long, decimal, nvarchar etc.) but also: XML- some of the XMLs have schemas outside of the DB (application is writing to it). I would expect a masking process to only go on the value and the attribute values, leaving the elements and the attributes as is. Geometry and Geography- the expected behaviour is for the data to maintain the following characteristics: shape and complexity, number of rings (polygon), number of vertices, coordinate system, area. This does not have to be one to one, but if we have a very complex polygon the developers should have something similar in their environment.

How can I scramble the data to declassify it?

  • 3
    MSSQL Tips has a good article on data masking. – Hannah Vernon Nov 20 '15 at 19:02
  • Are you trying to obfuscate the data or actually prevent the developers (and others) from accessing it? – Dave Nov 20 '15 at 19:08
  • thanks @MaxVernon, what would I do with the special types? – user5495269 Nov 20 '15 at 19:11
  • 1
    hmmm... without knowing more about the requirements of that data it is hard to say; however you might start by considering randomly generating the spatial data. Does the xml data need to be a specific schema, or can it be randomized? Lots of questions; this may be too broad a question for this site. – Hannah Vernon Nov 20 '15 at 19:13
  • 2
    I think it can be salvaged, you just need better specific requirements. What geo/XML values would you want in place of the ones that are there now? What part(s) of those values need to be masked? Can the masked copy be gibberish, or does it still need to make sense (conforms to specific XML schema, or is just valid XML; is a valid geographic location; etc)? Does it have to be the same size, or can you replace all values with placeholders? – Aaron Bertrand Nov 20 '15 at 19:20

I'm going to suggest the following so that you can 1) Share the entire DB definition with developers and 2) allow them, or you, to recover after they mess it up.

Download SSDT and create a database project. Then import your database and remove any configuration/settings/accounts/etc. that shouldn't be seen. You can now simply Publish the database (or just share the visual studio project!) to any server with no data included for your developers to work with. They can populate it with fake data for dev/testing as needed. If you need to provide XML schemas then include that along with your other development requirements.

Developers are smart... if you truly don't want them to have the data then don't give it to them. Even developers with access to production data sets will often generate test data for various reasons, not least of which is that production data may not fulfill test cases.

Not the answer you're looking for? Browse other questions tagged or ask your own question.