How to commit and/or update SQL Server database static data to a source control repository (4242974)

Challenge

In order to version static data, just like database objects e.g. Tables, a mechanism needs to be created to identify only those tables designated as “static” (see below) and to come up with a reproducible process, that will Commit the data in these tables to source control. This article covers this process

Static data is non-transactional data that does not change over time, or even if there are changes, they are not as frequent as for other categories data. Examples of static data include names of cities, zip codes and propriety codes used internally in a company.

Solution

ApexSQL Data Diff is a 3^rd party tool for comparing and synchronizing SQL database data between various data sources. It compares data between live databases, backups as well as between source control projects/labels and script files. ApexSQL Data Diff natively supports all popular source control systems such as Git, Team Foundation Server, Subversion, Mercurial and Perforce.

For the purpose of this article, we’ll use a Git repository where an existing database schema is already committed. Pubs database will be used as an example. The goal is to commit the static data to the repository so it can be versioned as well.

In order to commit the static data of the Pubs database to source control, the following steps should be performed:

1. Start ApexSQL Data Diff
2. In the New project form, select the Database option from the Source drop down list and point to the Pubs database

3. As a destination, select the Source control option from the drop down list and click the Setup button in order to configure source control connection. In the source control setup wizard choose the Git option from the Source control system drop down list:

4. In the next step, provide source control credentials, repository path and eventually a folder inside the repository where the static data will be committed:

5. After the repository is set, click the Finish button to close the source control wizard. Before initiating the comparison process, tables and even columns can be filtered so only tables and columns that contain static data can be included. In order to filter tables, expand the Advanced tab, and choose the Object filter tab. Tables that contain static data can be checked (1), and for each table, columns that do not contain static data can be unchecked (2):

6. Once the tables and columns containing the static data are checked, clicking the Compare button initiates the comparison process
7. In case of the initial commit of the static data, all selected tables will be shown as missing in the ApexSQL Data Diff main grid:

8. At this point, specific records (rows) from any table can be filtered out if needed, just by unchecking the box for the appropriate row:

9. When all tables with static data are set, a project file can be saved, in order to be used later in the automation process. To save the project file, use the Save/Save as buttons from the main ribbon:

10. Once everything is set, we can synchronize static data in a SQL Server database with the source control repository. To initiate the process, click the Synchronize button from the main ribbon:

11. This initiates the Synchronization wizard. In the Output options tab, select the Synchronize to source control option, from the Action dropdown list:

12. When the process is finished, the Results dialog shows what is committed to the repository:

13. Checking the Git repository, we can verify that the selected static data has been successfully committed. In order to verify this, navigate to the Tables folder for the selected database:

14. Opening any of the SQL files (for example dbo.stores.sql) gives the INSERT script which represents the way static data is stored on the repository:

Using the described workflow, static data can be initially committed to the repository.

Automation

Let’s create a PowerShell script that contains ApexSQL Data Diff CLI switches in order to automate the process of committing static data. In addition, the script contains data stamped output summary:

After this is set, save the PowerShell script as CommitStaticData.ps1

In the abovementioned PowerShell script, previously saved project file includes encrypted password for the used Git repository, as well as for the SQL Server connection. In case there is a need to handle credentials in a different way, here are 4 ways of handling database/login credentials during automated execution via the CLI

In order to schedule the saved script, various methods can be used, such as Windows task scheduler, SQL Agent Job, or a PowerShell project. All of these are explained in the Four ways of scheduling ApexSQL tools.

Once the script is executed, it will create the summary containing the return code at the end of the output file:

Changes:

-----------------------------------------------------------------------
Name              Schema                  Updated   Added    Deleted
-----------------------------------------------------------------------
pub_info	   dbo   	            0         8         0
stores             dbo                      0         6         0
 
Total:		 		            0        14         0
-----------------------------------------------------------------------

ApexSQL Data Diff return code is 0

To see a full list of return codes and a description for each one of them, check the Common return error code section part of the General usage and common CLI switches article.