Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Adding Reference Data in Database Continuous Integration (CI) Pipeline


By:   |   Last Updated: 2018-04-03   |   Comments (5)   |   Related Tips: More > DevOps

Problem

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

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

Solution

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

During the database continuous integration, DACPAC artifacts are produced as a result of a successful automated build. This includes reference data along with database structure. The reference data is vital in testing as 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 supporting information 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 countries then 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 is also a reference table.

database reference data diagram for SQL Server Continuous Integration

How Reference Data is obtained

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

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

Another way is to create reference tables from scratch based on the requirements such as if you are getting feedback (FeedbackId, CustomerName, RatingId, etc.) from customers in 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 database project and are created just like other tables with the exception of their data scripts which run in a batch in a post-deployment script.

Debugging the database project creates a debug database first and deploys changes afterwards which is followed by a post-deployment script that populates reference tables in the debug database.

Please go through my tip about test-drive development using tSQLt to see an example of how reference data is used 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 about Basic Database Continuous Integration and Delivery (CI/CD) using Visual Studio Team Services (VSTS).

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

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

solution explorer tree for SQL Server Continuous Integration

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])
)			
create reference table script for SQL Server Continuous Integration

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 then script out the inserted data

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

reference tables for SQL Server Continuous Integration

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

generate table script for SQL Server Continuous Integration

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

SET IDENTITY_INSERT [dbo].[ClientType] ON
INSERT 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
			
sql server insert commands for SQL Server Continuous Integration

Creating Reference Data Script

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

add reference script to build for SQL Server Continuous Integration

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

solution explorer reference data for SQL Server Continuous Integration

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

sql post deployment script template for SQL Server Continuous Integration

Debug Database Setup

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

debug database always re-create database for SQL Server Continuous Integration

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

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

At this point, please close without saving the dbo.ClientType.data window as we have created the ClientType.data script from it. Now, save the project from the File 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 have followed 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 introducing a ClientTypeId Foreign Key in the Client table.

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

-- Creating Client table with ClientTypeId Foreign Key
CREATE 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 about Basic Database Continuous Integration and Delivery (CI/CD) using Visual Studio Team Services (VSTS) needs to be updated to reflect the new changes as follows:

-- View to see clients information
CREATE VIEW dbo.Clients
AS
SELECT
    c.ClientId,
    ct.Name,
    c.Company,
    c.Email,
    c.Phone,
    c.RegistrationDate,
    c.Status
FROM Client c
INNER 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 getting kicked off as a result of database continuous integration.

team explorer commit all and push for SQL Server Continuous Integration

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

build definitions for SQL Server Continuous Integration

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

sucessful build for SQL Server Continuous Integration

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

build successful for project for SQL Server Continuous Integration

Download DACPAC Artifact

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

download dacpac for SQL Server Continuous Integration

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:

publish data-tier application for SQL Server Continuous Integration

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

publish data tier application for SQL Server Continuous Integration
data tools operations for SQL Server Continuous Integration

Check the Database Built from DACPAC

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

sql server object explorer for SQL Server Continuous Integration

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

sql server table designer for SQL Server Continuous Integration

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

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

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


Last Updated: 2018-04-03


get scripts

next tip button



About the author
MSSQLTips author Haroon Ashraf

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, May 09, 2019 - 10:01:32 AM - Haroon Ashraf Back To Top

Hi Ashraful,

Please remember that if this problem falls beyond the scope of this tip then you have to find the related tip on MSSQLTips to post your comments there but until then I would like to give it a go.

Let us first rephrase your problem statement whict at the moment is bit ambiguous:

I am implementing CI/CD of my database project. For single database (database name Test), it's works fine but got an error when release the artifact after added a database (Test2) as a reference

You began with a single database project pointing to Test debug database and implemented database continuous integration for that which worked fine for you. The only way to check is to check the contents of your Artifact produced as result of successful Build.

If you can download the Artifact which must have DACPAC file and then create the same Test database from it then your database build workflow is fine.

So can you please confirm the following things:

(1) Are you getting dacpac file in the Artifact folder as a result of successful Build on Azure DevOps?

(2) Are you able to create the same databse out of that DACPAC?

If yes then next you added a reference database Test2 to your project and your Build process is broken?

Have you followed this tip to add a reference database to your database project because one of the ways is to use two difference database projects in a single solution and then refer one project in another?

Can you please refer to this tip first and then come back to me if you need more assistance:

https://www.mssqltips.com/sqlservertip/5205/free-database-unittesting-for-sql-server-data-tools/

(search for) Next add the OfficeSuppliesSample_Data reference as follows


Thursday, May 09, 2019 - 6:30:52 AM - Ashraful Back To Top

Thanks for your quick response Ashraf.

I am implementing CI/CD of my database project. For single database (database name Test), it's works fine but got an error when release the artifact after added a database (Test2) as a reference. Error is

*** An error occurred during deployment plan generation. Deployment cannot continue.

Missing values for the following SqlCmd variables:Test2.

The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1

Please let me know if you need any other details.

Thanks


Tuesday, May 07, 2019 - 10:57:42 AM - Haroon Ashraf Back To Top

Thank you for reading the article and posting your valued comments, Ashraful!

Can you please give some more details when you say "you want to add database as reference"?

Does this mean you are not referring to the SQL database project with reference data scripts rather you are interested to add a whole database as a reference to the Project?

If yes then can you please briefly describe the purpose behind it so that we can give you the best possible answer.


Tuesday, May 07, 2019 - 7:12:28 AM - Ashraful Back To Top

Great article.

What will be the process if I added a database as a reference. How to build and release the pipeline.

Looking forward to hear from you.

Thanks


Tuesday, April 10, 2018 - 9:05:01 AM - Adeela Back To Top

 

 Very informative and useful specially while referencing the data in database table 


Learn more about SQL Server tools