In most cases the default mapping mechanism that pairs the objects based on the schema name and the object name is what is needed. However, there are often scenarios in which the same table may be owned by different schemas in different databases, or the tables on the development server for example may have the names prefixed with dev_ whereas the same tables in the production server do not have that prefix. In such cases you may want to define certain mapping rules that would allow you to pair objects that would otherwise not be paired together by IDERA SQL Data Compare.
The Mapping Rules can be accessed from the ribbon or from the action links on the right panel of the comparison tab.
There are three types of rules you can set:
- Schema Mapping Rules. By default SQL Data Compare performs an exact match of the schema names, however you have two additional options:
- Ignore schema name. In this case table [dbo].[T1] on database 1 will be paired with table [user1].[T1] on database 2, in other words the schema name is irrelevant.
- Manually map the schema names. This allows you to choose which schema name from database 1 to map to which schema name from database 2. If you map schema [S1] from DB1 to schema [S2] from DB2 then [S1].[T1] from DB1 will be paired with [S2].[T1] from DB2. To un-map a pair of schemas double click on the icon in the middle of the schema pairs on the Mapped Schemas box; that will break the mapping and move the pair of schemas to the Unmapped Schemas area. To map two schemas click on the schema on the left to select it; click on the schema you want to map it to on the right box and then click on the Map Selected Schemas link.
- Name Mapping Rules. By default SQL Data Compare performs an exact match on the object names, however, you can choose to ignore certain prefix and/or postfix. For example you can indicate that on the left database the dev_ prefix and the _old postfix and on the right database the qa_ prefix and the _new postfix should be ignored. In such case table [dev_T1_old] from the left database will be paired with [qa_T1_new].
- Data Type Mapping Rules. By default data types are mapped by compatibility. If column C1 of table T1 on DB1 is of the type varchar but the same column of table T1 on DB2 is of type nvarchar then those two columns will be paired together since those two data types are compatible with each other. However, you have the option to enforce an exact type match for your comparison. When you choose the Map data types by name then two columns will only be paired if they are of the same exact type.
Total compare from data to schema with IDERA SQL Comparison Toolset. Learn more > >