When the source and the target database have a different filegroup structure, Idera SQL 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.

About filegroup mapping

Filegroup mapping provides a method to map the source and the target filegroups with each other, so that filegroup differences do not require database synchronization. Consider, for example, what happens when you compare the following databases:

Database: DB_1
Contains the filegroup: FG_1
Contains the Employees table created on FG_1 filegroup

Database: DB_2
Contains the filegroup: FG_2
Contains the Employees table created on FG_2 filegroup

The default comparison finds a schema difference in the Employees table triggered by the filegroup clause. The script generated for the DB_2 database attempts to create the table on FG_1 filegroup, while the script generated for DB_1 attempts to create the table on FG_2 filegroup.

While this could work in most of the cases, there are scenarios in which you might want to keep the filegroup unchanged when transferring objects between the DB_1 and the DB_2 databases. You can achieve this by creating a mapping for the filegroups FG_1 and FG_2. When a mapping exist, the comparison and scripting of the Employees table changes as follows:

Working with filegroup mappings

IDERA SQL Schema Compare provides a few mapping rules that you can choose:

You can create a filegroup mapping as follows:

To delete a mapping, select the mapping row in the Mapped Filegroup grid, and then click Link. The members of the mapped filegroup will appear on the Available Filegroup grids.

If one of the databases contains filestream filegroups, a second tab appears in the mapping form that allows you to map filestream filegroups in the same way you map the regular filegroups.

Scripting filegroups

The mapping form provides an additional option that affects the scripting of the filegroup clause for various database objects. When unchecked, database objects that support the filegroup clause are created in the DEFAULT filegroup or, in some cases depending on the object, the filegroup option is ignored.

Te filegroup script option does not affect the database partition schemes.

Objects affected by filegroup mappings

Database objects affected by the filegroup mapping option include:

Best practices and restrictions

Mapping the filegroups, when not done properly, could produce unexpected results. These simple rules and restrictions can help you achieve the intended results:

 |    |  |   |   |