Adding Reference Data in Database Continuous Integration (CI) Pipeline (2024)

By: Haroon Ashraf |Comments (5) | Related: > DevOps

Problem

As a SQL Server database developer or database DevOps engineer my development team has been taskedto include reference data with the database(s) so that deploying the database shouldpopulate the reference tables on any target environment which can help maintainthe reference data throughout the database lifecycle management (DLM).

This step of adding reference data should also be included in the database continuousintegration (CI) pipeline.

Solution

The solution is to use post-deployment scripts which points to reference datascripts to populate reference tables using SQL Server Data Tools (SSDT) with VisualStudio Team Services (VSTS).

During the database continuous integration, DACPAC artifacts are produced as a resultof a successful automated build. This includes referencedata along with database structure. The reference data is vital in testingas well.

Let's go through some of the key concepts of reference data.

What is Reference Data?

Reference data is a reference table or set of reference tables which has supportinginformation for other tables in the database.

In most of the cases, reference tables’ columns are passed as foreign key(s)to main transactional tables of the database.

For example, if we have a country table which contains list of all the countriesthen this is considered as a reference table and its (primary) key column (CountryId)can be passed as a foreign key in another table such as Address.

We can also have custom reference types such as customer type table which isalso a reference table.

How Reference Data is obtained

Next thing that comes to mind is how reference data is obtained or in other wordshow do reference tables get populated.

Initially reference tables may already exist in your Production database so keepingin mind that you comply with GDPR (General Data Protection Regulation which is tobe effective soon) reference tables with data can be obtained from a Productiondatabase as long as the data does not directly contain personally identifiable informationor business sensitive information.

Another way is to create reference tables from scratch based on the requirementssuch as if you are getting feedback (FeedbackId, CustomerName, RatingId, etc.) from customersin the form of a table, then it is worth considering having a rating reference table(RatingId, RatingName, RatingDetail) which can help you in data analysis and reporting.

Often reference tables are created as a result of business requirements or rules.

How Reference Data is maintained

Reference tables in the context of SQL Server Data Tools (SSDT) are part of databaseproject and are created just like other tables with the exception of their datascripts which run in a batch in a post-deployment script.

Debugging the database project creates a debug database first and deploys changesafterwards which is followed by a post-deployment script that populates referencetables in the debug database.

Please go through my tip abouttest-drive development using tSQLt to see an example of how reference data isused in a project from stage to stage.

Adding Reference Data in Digital Services Database Continuous Integration (CI)Workflow

This tip assumes that you have run through all steps in my tip aboutBasic Database Continuous Integration and Delivery (CI/CD) using Visual Studio TeamServices (VSTS).

Now that you have created “Digital Services Database” and enableddatabase continuous integration for your database, it is time to add some referencedata in the database CI/CD pipeline.

Let's first reload the database project to have a look:

Adding Client Type Reference Table

Let's create a table to hold reference data for client types as follows:

CREATE TABLE [dbo].[ClientType]( [ClientTypeId] INT NOT NULL IDENTITY(1,1), [Name] VARCHAR(40) NOT NULL, [Detail] VARCHAR(200) NULL, CONSTRAINT [PK_ClientType] PRIMARY KEY ([ClientTypeId]))

Adding Reference Data for Client Type Table

Reference data for ClientType table can be added in two ways:

1. Create an insert data script directly
2. Debug the project and add data to the table in the debug database and thenscript out the inserted data

Let's debug the project to deploy new changes (ClientType table) to the debugdatabase.

Now right click on ClientType table and then click on “ViewData” and start entering values as follows:

Next click on the top left script icon to create data script as shown below:

SET IDENTITY_INSERT [dbo].[ClientType] ONINSERT INTO [dbo].[ClientType] ([ClientTypeId], [Name], [Detail]) VALUES (1, N'Private Client', NULL)INSERT INTO [dbo].[ClientType] ([ClientTypeId], [Name], [Detail]) VALUES (2, N'Retail Client', NULL)INSERT INTO [dbo].[ClientType] ([ClientTypeId], [Name], [Detail]) VALUES (3, N'Official Client', NULL)INSERT INTO [dbo].[ClientType] ([ClientTypeId], [Name], [Detail]) VALUES (4, N'Global Client', NULL)INSERT INTO [dbo].[ClientType] ([ClientTypeId], [Name], [Detail]) VALUES (5, N'Special Client', NULL)SET IDENTITY_INSERT [dbo].[ClientType] OFF

Creating Reference Data Script

Create a “Reference Data” folder in the database project and adda script named “ClientType.data” under it as follows:

Copy the script in dbo.ClientType.data and paste it in the ClientType.data script.

Next add a post-deployment script and refer to the ClientType.data script asfollows (also make sure SQLCMD is enabled):

Debug Database Setup

Please make sure the “Always re-create database” deployment optionis checked under Debug Menu as follows:

The “Always re-create database” option can helpus to populate reference tables as many times as possible since the debug databaseis going to be created from scratch each time the project is debugged which is goingto delete the existing reference table data and repopulate the table from the scriptwithout causing conflicts.

Considering we are working in Local Dev environment so “Alwaysre-create database” option is fine, however, for other environmentsthe approach may differ depending on the requirements.

At this point, please close without saving the dbo.ClientType.data window aswe have created the ClientType.data script from it. Now, save the project from theFile menu or pressing Ctlr+Shift+S keys.

Quick Data Script Check

Now right click on ClientType table in the debug database and delete it.

Then debug the project to see if Client Type table is back and populated.

The ClientType table must be back with reference data rows in it if you havefollowed all the instructions so far.

Update Client Table to Add ClientTypeId as Foreign Key Constraint

Let's now logically connect the Client table with the ClientType table by introducinga ClientTypeId Foreign Key in the Client table.

Open the client table from the DigitalServices database project and replace theexisting code with the following:

-- Creating Client table with ClientTypeId Foreign KeyCREATE TABLE [dbo].[Client]( [ClientId] INT NOT NULL IDENTITY(1,1) , [ClientTypeId] INT NOT NULL, [Company] VARCHAR(40) NOT NULL, [Email] VARCHAR(320) NOT NULL, [Phone] VARCHAR(50) NULL, [RegistrationDate] DATETIME2, [Status] bit, CONSTRAINT [PK_Client] PRIMARY KEY ([ClientId]), CONSTRAINT [FK_Client_ClientType] FOREIGN KEY ([ClientTypeId]) REFERENCES [ClientType]([ClientTypeId]));

Update Clients View to Add ClientTypeId

The view we created in my previous tip aboutBasic Database Continuous Integration and Delivery (CI/CD) using Visual Studio TeamServices (VSTS) needs to be updated to reflect the new changes as follows:

-- View to see clients informationCREATE VIEW dbo.ClientsASSELECT c.ClientId, ct.Name, c.Company, c.Email, c.Phone, c.RegistrationDate, c.StatusFROM Client cINNER JOIN ClientType ct ON c.ClientTypeId = ct.ClientTypeId

Check the Code into Source Control (Git)

Next check the changes into source control to see an automated Build gettingkicked off as a result of database continuous integration.

Check the web portal to see that the automated Build begins because databaseobjects and data scripts were put under source control:

In a couple of minutes, the Build should succeed as shown below:

Let's now look at the Build details and click on the Artifacts Tab as shown below:

Download DACPAC Artifact

Next click on the Artifacts tab under the Build details page and download DACPAClocally on your dev machine.

You can download the DACPAC file I created.

Publish New Database using DACPAC

Next open another instance of Visual Studio and open SQL Server Object Explorer(SSOX).

Right click on the Database and select “Publish Data-tier Application…”as shown below:

Next browse the DACPAC file and provide the name for the database “DigitalDataServices.Dev”to be published and click on “Publish” button as follows:

Check the Database Built from DACPAC

Next open the database and check the objects that have successfully been created.

Now right click on the ClientType table and click on “View Data”as follows:

Congratulations! The artifact (DACPAC) contains the data along with the databaseobjects and it has not only successfully created the whole database, but also populatedthe ClientType table.

So, we have demonstrated how to add reference data in the database continuousintegration pipeline.

Next Steps

• Try adding more reference tables and their data scripts in the databasecontinuous integration pipeline
• Please go through myTip 4 and see if you can replicate developing multi-customer database usingdatabase continuous integration and delivery along with adding reference data
• Please have a look at myTip 2 to add tSQLt unit tests to your project and see if you can add thosetests in VSTS Build
• Try creating the whole project mentioned in the myTip 3 using test-driven development (TDD)
• Please explore further Database CI/CD by visitingVSTS homepage

About the author