Published 12 August 2019
SQL Change Automation supports SQL Compare-compatible filter rules, which provide a simple way to control the inclusion of objects within the script generation and reporting processes.
Filter rules provide the flexibility to allow both include and exclude rules to be specified for your project. Authoring the filter rules themselves can be done using the SQL Compare UI or by editing the filter files directly within a text editor.
Why use filters?
By default, SQL Change Automation will import all objects from the development source when you use the SQL Change Automation to generate migrations. Although you can selectively un-check objects that you do not wish to import, thereby excluding them from your deployment, it can be desirable to exclude certain objects from the import tool altogether (for example, to prevent any changes from being accidentally re-selected for import).
It can be desirable to have SQL Change Automation exclude entire schemas from the tool that can, for example, contain objects that are controlled by a third-party.
When filters are applied
The configured filters are used throughout the database development and deployment workflows. More specifically they are taken into account in the following scenarios:
- Generating an initial baseline script from a target database
- Generating migrations from the development database
- Determining whether the development database is empty and the baseline script should be executed when applying migrations to the development database
- During release when determining whether the database to release to is empty and the baseline script should be executed in the release script
- During release when performing drift analysis and deployment report generation
How it works
Use SQL Compare's filter designer to configure filter rules, and then export the rules to a file called Filter.scpf within the root of the SQL Change Automation project folder (that is, alongside the .sqlproj file). When SQL Change Automation is used to import new changes, the rules defined within the filter file will be applied: any objects that do not satisfy the rules will be excluded from the generated migrations, programmable objects and/or offline schema model.
Editing the filter rules
To edit the filter files after the initial setup has been performed, simply re-open the filter file within SQL Compare, make any necessary edits to the rules and click the Save icon within the Filter pane to overwrite the filter file.
Customizing the filter file path
In some situations, it can be desirable to specify a different filter path, depending on the calling context. For this purpose, SQL Change Automation provides the DatabaseObjectFilterPath MSBuild property setting.
For example, when configuring your build server to report on schema drift in the QA environment, you might want to exclude all user objects. But when reporting on drift in production, you can report on all objects within the environment. This can be achieved by utilizing a different filter file per environment (Filter_QA.scpf, Filter_Prod.scpf, etc).
The DatabaseObjectFilterPath property can be specified by adding it to a property group within your project settings file:
<PropertyGroup> <DatabaseObjectFilterPath>$(MSBuildProjectDirectory)\MyFilter.scpf</DatabaseObjectFilterPath> </PropertyGroup>
The property can also be supplied as an argument to MSBuild in your continuous integration environment using the /p:DatabaseObjectFilterPath argument.