Cross Database Table-to-Table rules are designed to copy the contents of a table in the SQL Server database referenced by the current Rule Controller and update corresponding rows in a table in a different target database. The target database does not have to be an SQL Server database (although it can be) and the target database can be located on a completely different server and/or instance. An unlimited number of Cross Database Table-to-Table rules can be added to a masking set.

Cross Database Table-to-Table rules are created using the New Cross Database Table-to-Table rule dialog box. The method of operation is simple. The remote database connection information is entered at in the area at the top of the form and the remaining contents are configured as described in the New Cross Database Table-to-Table rule help file. 

The New Cross Database Table-to-Table rule help file contains full details regarding the configuration of a Cross Database Table-to-Table rule.

Features of the Cross Database Table-to-Table rule

The Target Database

The target database does not have to be located on the same server as the source database and can be of a different type than the source database. Currently both SQL Server and Oracle target databases are supported. Provision has been made to add other database types at a later date.

The Target Table

The target table can be any table which can be updated by the login information specified in the Cross Database Login Panel. The target table does not have to have the same name or structure as the source table.

The Sync. Columns

The rule requires the specification of the names of the columns to be synchronized. The synchronization columns in the target table can have different names than those of the source table and, if of a different datatype, should be of a datatype suitable for automatic conversion by the target database upon update.

The Join Columns

The rule also requires the specification of the names of the columns by which the two tables should be joined. For every row to be synchronized in the source table the join columns are used to identify matching rows in the target table. Once the equivalent rows are identified, the specified target table synchronization Columns are updated with the contents of their respective source table synchronization Column contents. The join columns in the target table can have different names than those of the source table.

In general, it is a good idea to always ensure the columns specified for the target side of the join condition are indexed. If they are not indexed, the rule will be much slower as the remote database will need to scan the entire contents of the remote table for each row which needs to be synchronized.

Moving a Subset of the Source Table

It is possible, using the Where Clause feature, of the Cross Database Table-to-Table rule to configure the rule to move only a subset of the rows in the source table to the remote table.

Important Note: It is important to realize that if there are join values in the target table that are not present in the source table, the Cross Database Table-To-Table Synchronization rule will have no effect on those rows. These columns will not be masked because there are no rows on the source side which can update them. In such cases a Cross Database Command rule is usually written to update the columns in the target table with a default value before the synchronization takes place. This ensures that the sensitive data in the target table gets some form of masking even if the subsequent synchronization operation does not update it.

Error Management and Handling

An error inserting the data into the remote table will normally stop execution of the masking set and all further rules. If required, the error can be trapped and the execution of subsequent rules will be possible.

Important Note: The error manager does not trap errors on a row-by-row basis. In other words if it is not possible to update a row (perhaps due to a primary or foreign key violation) the rule will error. If that error is trapped, then the rule will just stop and no further rows will be moved. The rule will not attempt to move further rows to the remote table. In such a case, the remote table will be in an indeterminate state.

Creating Cross Database Table-to-Table Rules

Cross Database Table-to-Table rules are created by launching the New Cross Database Table-to-Table rule form using the New Rule button located on the bottom of the Rules in Set tab.

As with all Data Masker rules - Cross Database Table-to-Table rules execute in the order specified by their Rule Block and Dependency state.

How to Create a New Cross Database Table-to-Table rule