User profile for PlantBasedSQL

Hey @Arun72 Thanks for your post and apologies for the delay in my reply! Have you been able to solve this at all? Typically adding and instance to SQL Data Catalog does require that you provide the Instance specifically, you can't provide a database name in isolation as that isn't how the tool is designed to work. Could you let me know what type of SQL Server you're connecting to please? Are we talking Azure SQL Database (PaaS), or a separate install like SQL 2017 on a VM etc.? The other thing to think about is what permissions Data Catalog is running under - does the service that's connecting out to the server have the relevant permissions needed to access the instance / database? Let me know if any of this helps - thanks very much, Kindest / comments

Hey @Arun72 Thanks for your post and apologies for the delay in my reply! Have you been able to solve this at all?Typically adding and instance to SQL Data Catalog does require that you provide the...

0 votes

Hey @Arun72 as Dan said, neither Data Masker nor SQL Clone are intended for the subsetting of Data. That being said you can use a mixture of command rules (and FK disable rules where necessary) to potentially remove some data that you don't want to carry through to images, and then use image compaction to shrink the image somewhat before use. I described a similar process in this YouTube video: Let me know if this helps! Kindest / comments

Hey @Arun72 as Dan said, neither Data Masker nor SQL Clone are intended for the subsetting of Data.That being said you can use a mixture of command rules (and FK disable rules where necessary) to p...

0 votes

Hey @Arun72 Thanks for coming back on this - there's no video course per-say however it would be fairly straight forward: - Disable the PKFK relationships/constraints - With a substitution rule, generate a bunch of values into the table referenced by the foreign key (for this example let's say integers between 1 and 99) - With a second insertion rule generate some more values using the same generator e.g. integers from 1 to 99 for the table with the FK reference - With a row-internal rule, go back through with a case statement on the FK column to do something like CASE WHEN NumberColumn IN (SELECT NumberColumn FROM PKTable) THEN DMSPARAM1 ELSE DMSPARAM2 END with DMSPARAM1 being "Do Nothing" and DMSPARAM2 being Integers between 1 and 99, this will check to see if it exists and then if it's not in the PK it will generate a new value - Re-enable the PKFK constraints This isn't fool proof and there is still definitely a chance that a value will be generated that isn't in the PK - the easiest way of doing it would be to use a user-defined data set, but this approach would at least help get you some of the way. Let me know if you have any questions - thank you very much. Kindest / comments

Hey @Arun72 Thanks for coming back on this - there's no video course per-say however it would be fairly straight forward:- Disable the PKFK relationships/constraints- With a substitution rule, gene...

0 votes

Thanks @Arun72 - not a problem at all! Happy to help with any questions (at least the ones I know! [image] ) With the Insertion rule there's no way to currently choose another column on another table as the source of the values. To insert with referential integrity, you'd need to use the FK disable rules in Data Masker to prevent constraint violations, then generate either: - A set of values consistent with the PK relationship (i.e. a user defined data set or a well defined, well bounded rule) OR - A set of values you want to be in the FK and then go back through with a row internal rule to change all the values that don't exist in the original PK column Then then re-enable the FK constraints. I agree it's not ideal, it's possible, but it's not ideal! Let me know your thoughts, Thanks - kindest / comments

Thanks @Arun72 - not a problem at all! Happy to help with any questions (at least the ones I know! )With the Insertion rule there's no way to currently choose another column on another table as the...

0 votes

Hi @Arun72 thanks for your comment - I believe this will work with any file so long as you're uploading into the correct datatype column, but obviously you will need access to the datasets folder to specify the file that you would like to be uploaded. I should also mention that it will be the same file every time that gets uploaded, it won't loop around a selection of files. This also depends if you'd like to be uploading say full XML or JSON INTO the column, as opposed to the file itself (so I guess depending on if you're using VARBINARY or NVARCHAR(MAX) for your datatype too) as you might be able to (if you don't have access to the datasets directory) write a command rule which updates the table and inserts a particular few lines of XML or JSON into the column - this way you wouldn't need to alter the datasets. I hope this helps! Kindest / comments

Hi @Arun72 thanks for your comment - I believe this will work with any file so long as you're uploading into the correct datatype column, but obviously you will need access to the datasets folder t...

0 votes

Hi @Arun72 Thank you for your post - Insertion rules are intended to only generate brand new rows of data, so to effectively "Insert" values for the pre existing rows, you will need to use a Substitution Rule for the affected columns, with a WHERE clause set to "No WHERE Clause" Because you already have some data in the rows, this is technically classed as a masking operation, not insertion. Let me know if you have any further questions! Thank you very much! / comments

Hi @Arun72 Thank you for your post - Insertion rules are intended to only generate brand new rows of data, so to effectively "Insert" values for the pre existing rows, you will need to use a Substi...

0 votes

My pleasure @don_don Glad the workaround worked out for you! Sorry it's not as simple as it should be but happy to keep you working forwards with it! Let me know if there's anything I can do to help out further! / comments

My pleasure @don_don Glad the workaround worked out for you! Sorry it's not as simple as it should be but happy to keep you working forwards with it!Let me know if there's anything I can do to help...

0 votes

Fabulous @don_don glad to hear it! Thanks for letting me know :simple smile: Have a great week! / comments

Fabulous @don_don glad to hear it! Thanks for letting me know :simple smile: Have a great week!

0 votes

Hi @don_don Thank you for your post! I'm hoping you can clarify something for me before I try to help or feed this back to the development team - there's one thing I'm struggling to understand. If you have [address] and [address_version] on the same DB - is there any reason why you cannot simply create a new rule pointed at address_version to mask that at the same time as address? Or are you looking to simply reuse this rule for the other table so that it makes tackling the other 500 tables easier? As for the 2-3 updates per year, does that mean specifically to the schema, therefore you'll have to update the masking set, i.e. to capture new tables and columns? In any case I will try to answer your questions: There is currently no way within the UI to easily clone a rule and re-point it at another table with a similar definition... however if we're clever about this we COULD manipulate the DMSMASKSET file VERY carefully to achieve what you're looking for. You would have to create a rule targeting [address], then hit "clone rule" so you had 2 identical rules targeting [address] - save this down and then open the DMSMaskSet file in Notepad++ or VSCode, find the second rule and change the table name. I tried this myself with a couple of example tables: [image] and it worked just fine: [image] [image] [image] [image] I realize that modifying the XML is not _ideal_ but it is a short term workaround at the very least. I should also say, that 500 Tables is a lot to be masking, and it seems unrealistic to go through each and every one to generate masking rules for them - have you considered using SQL Data Catalog alongside Data Masker? It has an integration that would allow you to generate masking rules automatically based on your classifications of those tables! Let me know if you'd like some more information! Kindest / comments

Hi @don_don Thank you for your post! I'm hoping you can clarify something for me before I try to help or feed this back to the development team - there's one thing I'm struggling to understand.If y...

0 votes

Good morning @Arun72 Thank you for your post! You're absolutely right that the Redgate tools are intended to be as helpful End To End as possible, so you're correct in that the flow should be as follows: 1 - SQL Data Catalog - get the databases you wish to protect classified and tagged 2 - Data Masker for SQL Server - Once you know the sensitivity of your databases and tables, use Data Masker to generate masking sets either manually by hand or automatically generate substitution rules directly from data catalog 3 - SQL Clone - Hand this masking set to SQL Clone which will create a single, sanitized "Image" (A VHDX file) and from that Image you can create dozens (even hundreds) of low time & space footprint clones for dev and test workflows Which gives you the full Prod -> Dev/Test/UAT flow without taking up a lot of space and whilst keeping your data safe. Another use of the Data Catalog is compliance through DevOps. As you deploy changes up towards production, for instance, you could add in checks that call Data Catalogs REST API or PowerShell module to check if all columns in Pre-Prod had been classified and only approve the deployment to Prod if this were the case, otherwise failing and getting developers to correct this prior to deployment. A large amount of this process can also be automated. I'm not sure that I would put SQL Compare or SQL Data Compare in the same category, however. You absolutely COULD use it to validate the data had been masked, but Data Masker produces reports during the masking run anyway, which satisfies most people in that sense. If you want a bit more of a run down of the Redgate capabilities and how t hey can help you out moving forwards I would recommend contacting your account manager - either I can let them know or you can reach out - just let me know what you prefer [image] Let me know if you have any further questions - thank you very much! Kindest / comments

Good morning @Arun72 Thank you for your post!You're absolutely right that the Redgate tools are intended to be as helpful End To End as possible, so you're correct in that the flow should be as fol...

0 votes

How can we help you today?

PlantBasedSQL

Comments