SQL Change Automation 4

Filtering

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.

It is recommended that the .scpf file used by the project is in the project directory so that it can be version controlled and accessed by other team members.

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:

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.

image

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.



Didn't find what you were looking for?