Mask a primary and foreign key relationship across a different schema

VmWare

Is there a way to mask a primary/foreign key relationship that are using different schemas? The two tables are on two different controllers so when I open "Synchronization Manager", it doesn't show the foreign key in the other table.

DanC

Hi @VmWare

Have you had a look at the following document? - https://documentation.red-gate.com/dms/data-masker-help/tech-tips/how-do-i-change-a-primary-key-value-and-all-associated-foreign-keys

You could try the following suggestion:

You'll need to create a new Substitution Rule ->Click on the 'Change Managers' tab ->Click on the 'Convert to Sync. Mgr Rule'.  This then creates a block of rules that you can then configure to change the ID and go across schemas.  You can also add other rules into the Sync. Mgr Rule.

You'll also need to have a second rule controller configured with a schema structure containing the target table.

After that just enable the two cross schema rule options on the Rule Options drop down of the Misc. Setup tab. This will make it possible to see the schema structure of the other Rule Controllers in the Sync. Mgr. Table-to-Table rule. Once these are enabled the warning message should not appear.

It is important to remember that the Sync. Mgr. (also sometimes referred to as a PKFK Change Manager in V5) can only sync the data in the remote table if that table is in the same database as the source table. It cannot go cross instance. Also the login for the source rule controller must have sufficient rights to read and update the target table.

VmWare

Hi DanC,

Sorry I am bit confused about the process. When I normally sync two tables with a relationship, I just specify the other table's foreign key in the sync manager, however when it is cross schema, sync manager does not detect the other foreign key. 

I'm also not too sure what you meant when you said you could "Change the ID" to go across schemas and when you said to "enable the two cross schema rule options on the Rule Options drop down of the Misc". I do see the option to enable "cross database" but there is no rule for cross schema. Just wondering if there is some documentation to show this process as I have successfully done syncing across tables of the same schema just not tables from another schema.

Thanks,
Derek

DanC

Hi @VmWare

Once you have created the sync manager on the primary key table you can just use the "Rule Generation By Column" - search for the column name you want to synchronize, check it, add the marked column(s) and Data Masker will generate the rules appropriately

VmWare

I'm getting an error when I try to do it via column name. I attached screenshots below of my test tables and the error I'm getting. For reference "EmployeeNo" is the primary key in the first table which is referenced by the EmployeeNo column as a foreign key in the second table.

DanC

Hi @VmWare

In this case, it would be great to get a copy of your masking set to troubleshoot, I appreciate you probably won't want to share this across the forum!

What I'll do is reach out to you via a support ticket directly and we can go from there.