My colleague, Hiren, and I were working on connecting SQL Server on an Azure Virtual Machine to an Azure Search service. We encountered a roadblock when we wanted data change tracking to be enabled not directly on the Server, but as part of the dacpac deployment.

Change tracking is method Azure Search use to keep track of the data it need to sync, delete, or update. Users can decide to manually run a sql script that will allow change track both at the database and table levels, but this process needs to be done on all environments and be remembered on future environment creation. As best practice it is not suggested. If you have a SQL Database project, it is recommended to apply all the changes there do the dacpac that generate the script needed to be ran on all environment.

Thanks to an article by Ray Barley, a former colleague, it gave us light.

First, we need to enable the SQL Database change tracking which is by default disabled. Please note that for the instructions below I’m using Visual Studio 2017 with SQL Server 2016 for this demo.

1. 1. Right Click on the SQL Server project in Visual Studio and go to properties then click on “Database settings” button:
   2. On the Database Settings popup, go the “Operational” tab and scroll to the “Change Tracking” section. You can now enable change tracking:
   3. Now that you have change tracking enabled at the database level, let’s select the table we would changes to be tracked and do the same thing at the table level:
      

You can now build your database project and deploy your dacpac.