Maya Malakova

4 December 2019

• SQL Change Automation
• Database Development in SSMS

Using SQL Change Automation in SSMS to Track Static Data Changes

Often, a database build will need to supply any ‘static’ or ‘immutable’ data required for dependent applications to function. This might include ZIP or postal codes, the names of countries or currencies, standard tax rates, and so on. This static data should be relatively small in volume and, as its name suggests, is not expected to change frequently. However, if it does change, or new static data is added, during development, then the scripts that deploy these data changes, along with those that deploy the schema changes, need to be included in the build package.

The SQL Change Automation Development tools include extensions that integrate directly into your chosen SQL Server development GUI, Visual Studio or SQL Server Management Studio. This allows teams to develop new databases and modify existing databases, using migrations, in a way that promotes DevOps practices.

In SQL Change Automation v4.1, we’ve added support for the tracking of static data tables to the SSMS extension, alongside existing support in the VS extension. In this article, I’ll show how to add static data tables that you wish to track directly through a new Data tab in the SSMS extension, and which columns within those tables. I’ll demonstrate how SCA will then automatically create migration scripts to deploy any changes to this data, allowing the team to see a preview of the changes before generating the migrations.

Configuring static data tracking from within SSMS

Version 4.1 of SQL Change Automation in SSMS adds a new Data tab to the project view, where the users can pick the tables to track. In previous versions, this had to be done manually, by adding tables to the SQL Change Automation project settings (.sqlproj) file. Users that have databases with large number of tables will also benefit from using the filter that will allow them to find the tables they want to add easily.

Adding tables to the Data tab

Tracking tables with large volumes of data, via migrations can deteriorate performance because SQL Change Automation generates the static data in the form of individual INSERT statements for each row, and tracks changes via a line by line comparison between data in the source and target. Our recommendation is that it’s fine to track tables with less than 10K rows, but for larger data sets we recommend the ‘Seed data‘ method, where all data is saved to a flat data file and uploaded via a BULK INSERT statement. We display the number of rows each tracked table contains helping the user set up their project in a sensible way.

Row number warning

SQL Change Automation needs a primary key in order to track data so we try to make it obvious which tables can be tracked, and which ones would require adding a primary key first.

Table without primary key

Column-level control of static data tracking

Some columns within static data tables, such as Date Created or Date Updated columns, might hold values that we don’t want to replace with new values from development. Just hit the Edit pencil for a table you’re tracking and select which columns you want to track (previously, it was only possible to exclude columns from tracking by manually editing the project settings file).

Column level configuration of static data

Seeing static data changes from within SSMS

Changes in static data are now visible on the Generate migrations view of the SSMS add-in.

Data changes diff

After confirmation, the newly generated migration gets included in the project. Once shared in version control the changes will be picked up automatically by the deployment components of SQL Change Automation, such as our Azure DevOps plugins, and deployed to a target database.