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.

Edit 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. 

Example: exclude database users from your SQL Change Automation project

Many teams prefer to manage database permissions outside of version control. These teams frequently have requirements to use Active Directory groups to manage permissions. They also commonly utilize different domains for different environments and trusts are not established between these domains. For these teams it is desirable to make two configuration changes:

1. Configure SQL Change Automation so that it excludes users from being scripted. To do this, add a filter file to your project which excludes users. You can create your own filter file for this using the instructions in this guide, or download and customize Filter.zip.
2. Configure SQL Change Automation so that it does not script out permissions for users. To do this, set the schema comparison option SyncOptionIgnoreUsersPermissionsAndRoleMemberships to 'True' in the project's sqlproj file.

Additionally, if you wish to dynamically create users and grant role permissions based on the server name you are deploying to, this may be done with a post-deployment script.

Customize 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.