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

 

SQL Server Reference Data Best Practices - Part 1


By:   |   Last Updated: 2018-05-23   |   Comments (5)   |   Related Tips: 1 | 2 | More > DevOps

Problem

Reference data is added to the SQL Server database by multiple developers and testers, thereby causing inconsistencies in the database due to reference data updated by one developer getting overwritten by another developer/tester and so on.  In this tip we will look at how this can be solved.

Solution

If you are not familiar with reference data, please refer to my previous tip to get the basics of SQL Server reference data.

Adopting reference data best practices will not only avoid such conflicts, but also keeps the database in a consistent and reliable state.  Before we jump into reference data best practices, it is worth considering replicating the problem scenario.

Replicating Problem (Scenario)

To replicate the problem, let’s setup a sample database with reference data (table). This tip assumes you are working with SQL Server Data Tools (SSDT).

CarsSample Database Setup

Create a CarsSample database and populate it using the following scripts using SQL Server Object Explorer:

cars sample

Creating CarsSample Database Project from the Database

Create a blank database project and call it CarsSample and then import the previously created database into the project:

solution explorer

There is no reference data involved since we have just one table:

honda

Adding Reference Tables

Add the following reference tables in the database project:

  1. CarMake
  2. CarType
-- (1) Adding CarMake reference table
CREATE TABLE [dbo].[CarMake]
(
   [CarMakeId] INT NOT NULL IDENTITY(1,1),
   [Name] VARCHAR(40),
   [Detail] VARCHAR(200)    
   CONSTRAINT [PK_CarMake] PRIMARY KEY CLUSTERED ([CarMakeId])
)
 
-- (2) Adding CarType reference table
CREATE TABLE [dbo].[CarType]
(
   [CarTypeId] INT NOT NULL IDENTITY(1,1),
   [Name] VARCHAR(40),
   [Detail] VARCHAR(200), 
    CONSTRAINT [PK_CarType] PRIMARY KEY Clustered ([CarTypeId])
 
)

The reference tables once defined requires the main table Car to be updated so reference table columns are passed as foreign keys to the main table as follows:

-- Car table updated to fit reference tables columns passed as foreign keys
CREATE TABLE [dbo].[Car] (
    [CarId]   INT          IDENTITY (1, 1) NOT NULL,
    [CarMakeId]    INT NULL,
    [RegYear] INT          NULL,
    [CarTypeId]    INT NULL,
    CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED ([CarId] ASC), 
    CONSTRAINT [FK_Car_CarMake] FOREIGN KEY ([CarMakeId]) REFERENCES [CarMake]([CarMakeId]), 
    CONSTRAINT [FK_Car_CarType] FOREIGN KEY ([CarTypeId]) REFERENCES [CarType]([CarTypeId]),
)

First, make sure that the target connection is pointing to CarsSample database, also keep Deploy database properties, Always re-create database and Block incremental deployment if data loss might occur options checked as shown below:

cars sample

Debug the SQL Database Project to deploy the changes we have just made, provide the target connection is set to CarsSample database:

car make

Simulating Dev1 and Dev2 Tasks to Add Reference Data

At this point, we have both a main table and a reference table ready except the data scripts are not yet written.

Assume, Dev1 has been tasked to add data to CarMake table and Dev2 has been tasked to add data to CarType table.

To demonstrate this, we must create two database projects from CarsSample database and call them CarsSampleDev1 and CarsSampleDev2.

Create CarsSampleDev1 database project from CarsSample database to simulate Dev1 local development environment:

import database
cars sample

Similarly, create another SQL Database Project from CarsSample database and call it CarsSampleDev2:

import database

Both projects are ready now:

cars sample solution

Declarative Database Development Style

SQL Server Data Tools (SSDT) offers declarative database development, which has many benefits including not needing to worry about the state of the database when adding new or modifying existing database objects.

When it comes to adding reference data, it is added in the form of data script(s), that run as a post-deployment script during the publish or debug process.

According to Garry Trinder, a declarative database style makes database deployment repeatable, which means it does not matter how many times you deploy the same changes to your debug database, it remains consistent.

By default, data scripts used to populate reference tables are non-repeatable unlike database objects.

Setting up Dev1 Development Environment

Setup development environment for Dev1 represented by CarsSampleDev1 is as follows:

target connection string

The use of localdb mimics local development environment for Dev1.

The checked deployment option “Always re-create database” ensures that the reference data scripts are repeatable and with each new deployment the reference tables get re-populated.

Adding Dev1 Reference Data (CarMake)

Create a folder and call it Reference Data then add the following script to it:

Also, add the post deployment script to the run data script (after enabling SQLCMD option):

/* 
Post-Deployment Script Template 
-------------------------------------------------------------------------------------- 
This file contains SQL statements that will be appended to the build script. 
Use SQLCMD syntax to include a file in the post-deployment script. 
Example:      :r .\myfile.sql 
Use SQLCMD syntax to reference a variable in the post-deployment script. 
Example:      :setvar TableName MyTable 
               SELECT * FROM [$(TableName)] 
-------------------------------------------------------------------------------------- 
*/ 
:r ".\Reference Data\CarMake.data.sql" 

The project should look as follows:

cars sample

DeDebug the project to deploy changes to the localdb which is like a local development environment for Dev1.

debug database

Debug database has been successfully created for the first and reference table for Dev1 (CarMake) has also been populated by the post deployment script.

Adding Dev1 Publish Profile for Dev Server

Next step is to create a publish profile to deploy the SQL Database Project managed locally by Dev1 to Dev Server which is a shared development environment.

Right click on CarsSampleDev1 and click on “Publish” as shown below:

publish

Create publish profile for dev server by pointing to your local SQL instance as we are simulating dev server:

database

Click “Advanced…” and check “Always re-create database” option in the publish profile.

Running Dev1 Publish Profile

Run publish profile and see all the Dev1 changes getting deployed to shared dev database:

publish cars sample

Viewing Shared Dev Database (CarsSample)

Take a closer look at CarsSample shared dev database published by Dev1 using publish profile.

The CarMake table got populated so the reference table managed by Dev1 has been successfully populated as shown below:

car make

Setting up Dev2 Development Environment

Setup development environment for Dev2 the same way Dev1 environment was setup except the reference table CarType needs to be populated instead of CarMake.

target connection string

Adding Dev2 Reference Data (CarType)

Create a folder and call it Reference Data then add the following script to it:

Also, add post deployment script to run data script:

/* 
Post-Deployment Script Template 
-------------------------------------------------------------------------------------- 
This file contains SQL statements that will be appended to the build script. 
Use SQLCMD syntax to include a file in the post-deployment script. 
Example:      :r .\myfile.sql 
Use SQLCMD syntax to reference a variable in the post-deployment script. 
Example:      :setvar TableName MyTable 
               SELECT * FROM [$(TableName)] 
-------------------------------------------------------------------------------------- 
*/ 
:r ".\Reference Data\CarType.data.sql" 

The project should look like as follows:

reference data

Adding Dev2 Publish Profile for Dev Server

Add Dev2 publish profile pointing to the same shared database (CarsSample) same as Dev1 profile settings and create publish profile “CarsSampleDev2.publish.xml”.

Run Profile and View Published Database for Dev2

Run the publish profile and view the published database, particularly view reference tables:

car type

This time CarType reference table is populated but CarMake reference table changes done by Dev1 are gone.

So, we have successfully replicated the problem and it is obvious that “Always re-create database” deployment option is best suited if only one developer is responsible for adding reference tables and the data scripts, which is not always the case.

Changes done by Dev1 get overwritten by the changes done by Dev2 because of the repeatable nature of the deployment scripts.

Potential Solution

One of the possible solutions of the above problem is to use alternative way of creating repeatable data scripts so that the changes done by one developer don’t get overwritten by another developer in the context of reference table data.

One thing is for sure we have to avoid using “Always re-create database” deployment option but somehow, we have make the scripts repeatable to comply with standard practices.

This means, we only need to insert data if it does not already exist.

Let us implement this for Dev2 by first unchecking “Always re-create database” and then replacing CarType.data.sql script with the following code:

-- Add reference data to CarType table if does not already exist
SET IDENTITY_INSERT [dbo].[CarType] ON
 
IF NOT EXISTS (SELECT * FROM CarType
WHERE CarTypeId=1 and Name='Estate') 
INSERT INTO [dbo].[CarType] ([CarTypeId], [Name], [Detail]) VALUES (1, N'Estate', NULL)
 
IF NOT EXISTS (SELECT * FROM CarType
WHERE CarTypeId=2 and Name='SUV') 
INSERT INTO [dbo].[CarType] ([CarTypeId], [Name], [Detail]) VALUES (2, N'SUV', NULL)
 
IF NOT EXISTS (SELECT * FROM CarType
WHERE CarTypeId=3 and Name='Saloon') 
INSERT INTO [dbo].[CarType] ([CarTypeId], [Name], [Detail]) VALUES (3, N'Saloon', NULL)
 
IF NOT EXISTS (SELECT * FROM CarType
WHERE CarTypeId=4 and Name='Hatchback') 
INSERT INTO [dbo].[CarType] ([CarTypeId], [Name], [Detail]) VALUES (4, N'Hatchback', NULL)
 
IF NOT EXISTS (SELECT * FROM CarType
WHERE CarTypeId=5 and Name='MPV') 
INSERT INTO [dbo].[CarType] ([CarTypeId], [Name], [Detail]) VALUES (5, N'MPV', NULL)
 
SET IDENTITY_INSERT [dbo].[CarType] OFF	

Publish the project to Dev server using the publish profile and see the results:

publish database
car type

Now delete first two rows in the table and run the publish profile again.

The table still contains all the five records even though the first two records were deleted before the publish profile was rerun. The data script is working fine.

A similar sort of script can be written for CarMake table managed by Dev1.

The above approach does the job, but a more comprehensive solution and more about reference data best practices is going to be presented in the next part of this article.

Reviewing Reference Data Best Practices

Let's extract some best practices that we have learned so far when it comes to reference data:

  1. The database objects in the database project do not care about the state of the database which is going to be updated, they straightaway get deployed and end up either adding new objects or modifying existing objects, but this does not hold true for reference data.
  2. The reference data scripts must be repeatable so that running them again and again do not cause any inconsistency in the database.
  3. Always re-create database deployment option is only suitable when deploying changes to debug database inside local dev environment because in that case each developer has his/her own debug database.
  4. Always re-create database deployment option must be avoided when publishing database project to Dev or Test environment.
  5. Publishing database project to Production directly using a publish script is not recommended as the changes must be reviewed by a DBA or infrastructure team before going on Live.
  6. The recommended practice to add reference data to the database is by using data scripts which are run by post deployment script once the project is debugged or published.
  7. The reference data should explicitly handle the identity columns in the data scripts to avoid potential issues caused by identity inserts.
Next Steps
  • Please try implementing the potential solution completely such that it also removes any record on the dev database which is not part of the predefined table(s).
  • Please try adding more environments such as Test or QA and extend the solution to handle reference data deployment to these environments.
  • Please go through my previous tip and try implementing the potential solution mentioned in that tip.


Last Updated: 2018-05-23


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.



    



Wednesday, December 12, 2018 - 5:00:40 PM - Haroon Ashraf Back To Top

Please see below some supporting information which helps us to understand the use of Debug Project vs Publish Scripts in this tip:

  1. Shared dev database CarsSample is created by setup scripts in the beginning of this tip
  2. Shared dev database itself is created on a Local SQL Server instance to mimic Shared dev environment
  3. A SQL Database Project is created out of the shared dev database by importing it
  4. Then two new reference tables are added to this database project along with modifying the main table Car
  5. The database project changes are deployed to the shared dev server (database) by simply debugging the project (pressing F5) after making sure that debug Target Connection String points to the shared dev database (on local SQL Server) For example: Data Source=.\SQL2012;Initial Catalog=CarsSample;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True
  6. At this point there is no Publish script involved we simply debug the database project to deploy changes to the target database which is CarsSample

If you are not able to deploy changes by debugging the database project then please first check Target Connection String under Debug settings points to your shared dev database or not?

Secondly to troubleshoot I highly recommend please delete the shared dev database CarsSample and then debug the project if all is fine then you should see CarsSample database getting created as a result of debugging project.

Publish scripts in this tip are used in the database projects by dev1 and dev2 to deploy their changes finally to the same shared dev database CarsSample but please also remember, debug project for dev1 and dev2 can also deploy changes to their respective debug databases.

 


Wednesday, December 12, 2018 - 12:12:42 PM - Salam Back To Top

 Yes, cocrrect, I followed exact same steps, when I do debug for anyone, it seems that it validates ly , I did publish so I can see the impact in the DBs


Wednesday, December 12, 2018 - 10:28:50 AM - Haroon Ashraf Back To Top

Dear Salam,

Thank you for your feedback.

In order to understand the problem could you please provide some more details.

When you debug does not publish, are you referring to the first debugging of the database project in the walkthrough (after we have created it by importing the database)?

Please see below the startup steps summary to help you point out when did you try debugging the project:

  1. A sample database with one table (Car) is created and populated from the setup scripts
  2. A database project is created by importing the sample database
  3. Reference tables are added and Car table is updated
  4. Project is debugged with Always re-create database deployment option enabled 

Is this where you are not able to deploy changes to the debug database Car?

If this is the step you were not able to perform then skipping this step and moving to the next steps is not going to help.

Please first try to check why Debug is not working for you rather than using Publish script.

Can you also please try to create a blank database project with a single table and one column and try to debug the project to deploy changes to the debug database and see if it works for you?


Wednesday, December 12, 2018 - 8:46:17 AM - Salam Back To Top

 Hi, Degug does not publish, I needed to click "Publish" in order to run the scripts


Wednesday, December 12, 2018 - 7:47:22 AM - Salam Back To Top

 HI, I tried to "Debug the SQL Database Project to deploy the changes" but getting the error

Severity Code Description Project File Line Suppression State
Error  SQL71508: The model already has an element that has the same name dbo.Car. CarsSample ..........dbo\Tables\Car.sql 1 

in spite of the fact that "Always Re-create database is checked


Learn more about SQL Server tools