When the source and the target database have a different filegroup structure, schema compare allows you to map the filegroups with each other. Filegroup mapping can be accessed from the main ribbon or the comparison grid context menu.
What are the filegroup mappings?
The filegroup mappings provide a way to map with each other the source and the target filegroups, so that changes in filegroups do not trigger database differences.
Consider, for example, the following scenario:
Database: DB_1
Filegroup: FG_1
Employees table created on FG_1
Database: DB_2
Filegroup: FG_2
Employees table created on FG_2
When the source and the target database are compared, the Employees table, being in two different filegroup FG_1 and FG_2, will appear different. The script generated for the DB_2 database will attempt to create the Employees table on the FG_1 filegroup, while the script generated for the DB_1 database will attempt to create the table on the FG_2 filegroup.
While this might be the expected results in most cases, there are scenarios in which you might want to preserve the filegroup clause. You can achieve this by mapping the filegroup FG_1 to FG_2. That is the same as considering the FG_1 = FG_2 for the purpose of schema compare. Having a mapping changes the comparison and scripting of the Employees table as follows:
- The mapped filegroups FG_1 and FG_2 are considered the same, which means they do not trigger a schema difference between the source and the target Employees tables.
- If other schema differences require the Employees table to be synchronized, the table will be scripted in the mapped filegroup, not in the original one.
Mapping the Filegroups
Schema compare provides a few mapping rules that you can choose from:
- Map the filegroups by name: this is the default filegroup mapping option.
- Ignore the filegroups when comparing schema: the filegroup clause of an object, such as a table or an index, does not trigger a schema difference.
- Custom mappings: allows you to create custom mappings between filegroups.
You can create a filegroup mapping as follows:
- Select a filegroup from the source filegroups
- Select a filegroup from the target filegroups
- Click on the "Map Selected Filegroup" link. A mapping row will appear in the "Mapped Filegroup" list.
To delete a mapping, select it in the "Mapped Filegroup" list and click on the Link button in the middle. Mapping is deleted and its members appear in the "Available Filegroups" lists.
 |
If one of the databases contains filestream filegroups, the Filegroup Mapping form will contain an additional tab that allows you to map the filestream filegroups, similar to the regular filegroups. |
Scripting Filegroups
The mapping form provides an additional option that affects the scripting of the filegroup clause for various database objects. If the option is UNCHECKED, objects that require the filegroup clause are created in the "DEFAULT" filegroup, or, in some cases, the filegroup clause is ignored.
|
The filegroup script option does not effect the database partition schemes. |
Objects effected by the Filegroup Mappings
Database objects effected by the filegroup mappings are the followings:
- Tables: filegroup, the text filegroup, the filestream filegroup and the filegroup option of the change data capture
- Primary Keys: the filegroup and the filestream filegroup
- Unique Constraints: the filegroup and the filestream filegroup
- Relational Indexes: the filegroup and the filestream filegroup
- Spatial Indexes: the filegroup clause
- Full-Text Indexes: the filegroup clause
- Service Broker Queues: the filegroup clause
- Partition Schemes
Best Practices
Mapping the filegroups, when not done properly, could have unexpected consequences. These simple rules can help you achieve the intended result:
- Filegroup mappings are one-to-one.
- Although it is not required, we recommend that you map ALL filegroups. The filegroups that are not mapped explicitly, will be mapped by name (the default rule).
- Filestream filegroups are mapped separately from the regular filegroups. The mapping form will provide an additional tab when databases have filestream filegroups.
- The filegroup scripting option, when unchecked, effects database objects differently. Some objects, such as tables, indexes, are scripted in the "DEFAULT" filegroup. Other features, such as the change data capture, ignore the filegroup option entirely. Database partition schemes are not effected by this scripting option.