Make the Reference Table Cool Again

This article talks about centralizing reference tables in Azure Information Studio during the database development process for ease of deployments to multiple environments such as dev, test and production.

Additionally, the readers who are also interested in Azure Information Studio tool are going to sympathise the importance of having a solid database development strategy for reference information direction which is often overlooked or remains unnoticed. The part of reference data for any database development and testing is undeniable just at the same time, the strategy to manage reference data centrally is as well very crucial to avoid whatsoever conflict that may occur.

About Reference Tables

At that place is ever some room for reference tables in any database design because without them the database does non make much sense. Permit us quickly understand them.

What is a Reference Table?

A Reference Table is a table that contains the information for the main table. In other words, the reference tabular array contains a specific set of data to be used by other tables.

What is Reference Data?

The data that a reference tabular array contains is known as reference data.

What is the deviation betwixt Reference Data and Reference Table?

Technically, the reference tabular array refers to the structure (cavalcade name, data blazon, etc.) and reference data ways the data independent by that construction (blueish, green, white, etc.).

What is Reference Table Management?

Reference table management is a way of managing a reference tabular array such that it does not lose its quality from surroundings to environment and serves as a central for populating reference data to any target surround for whatsoever purpose that helps in preserving the standard of reference data.

Instance of a Reference Table

A reference table can exist whatever table that contains information for other tables for example we tin can say Color is a reference table that contains different types of colors such as blue, white, green, etc. Now this table tin can be used in another table called Sentry to ascertain different watches having unlike colors. Delight recollect you tin can simply create a reference tabular array in Azure Data Studio just like the way you lot create any other table.

The reference table is illustrated equally follows:

Database Evolution Strategy for Centralizing Reference Tables

Let us now talk near the core strategy, nosotros have, to centralize a reference table or a group of reference tables belonging to our database of interest.

Nosotros but have to think of a mode to control the reference table earlier it gets deployed to multiple environments for multiple purposes. In other words, we would like to centrally manage the reference table design and information and then that it tin can be easily ported from one surroundings to some other environs.

Environments of Involvement

We are mainly interested in the post-obit three types of environments:

1. Dev (development environment)
2. Test (test environment)
3. Prod (production environs)

Now central management of reference data means we must exist able to fix upward the above environments from a single central place that likewise holds the single source of truth for that information.

The SQL Database Project Manner

One of the ways to get control over the flow of reference tables and reference information is to switch to state-based development which means you should build your database fromSQLSQL Database Projection considering and so it is the responsibility of the Database Project to build, manage and publish reference tables to multiple environments and this is one of the core specialties of choSQLng SQL Database Projection. In other words, you should define your reference tables solSQL in a SQL Database Projection and then it is going to be managed from there.

Reference Data Structure and Data Scripts

If you have decSQLd to use SQL Database Project for your reference tables and data direction and then you lot may put your reference tables every bit data scripts to be executed by Database Project deployments to populate reference tables in one case they are created.

This is typically a 2-step procedure:

1. Yous define your reference table in the database project
2. You lot define your reference data equally a data script that can populate your reference tabular array once the projection is deployed to any desired environment

Handling Reference Information Differences in Different Environments

I of the biggest challenges in keeping a reference table within your database project is to understand how to deal with the state of affairs when you lot have a difference of reference information in unlike environments.

For example, y'all begin with a reference table that contains information virtually dissimilar two colors blue and dark-green and in an ideal scenario you would similar to accept these two rows in each of the environments but what if you lot find blue, dark-green, yellow in dev and bluish, green, orange in test and blue, dark-green, crimson in Production. In other words, what is the strategy to reconcile reference data if you find three different versions in 3 unlike environments?

For example, if yous say yous are managing reference tables through a database project then having a decent fix of rows works because production may pick new reference data on the fly so we have to limit ourselves to dev and test that they should have consistent reference information and for product we just add the standard rows while keeping newly added data unaffected there.

Well, at the end of the day information technology entirely depends on your business concern requirements and preferences but keeping the surface facts in mind why would yous like to restrict adding new reference data on the Production server since it is well-nigh e'er a natural procedure unless yous have exceptions.

Let us build the project in Azure Data Studio and see for ourselves how it goes so.

Developing Centralized Reference Tables and Reference Data Strategy in Azure Data Studio

You can develop a quick reference table and data strategy by following these simple steps:

1. Creating a new SQL Database Project
2. Creating a Reference Tabular array in the database projection
3. Creating a Reference Data binder structure in the database projection
4. Adding a Information Script to populate the Reference Table to the Reference Data binder
5. Adding a Post Deployment Script
6. Calling the Information Script in the Post Deployment Script
7. Running a Build to come across all is fine
8. Publishing the Projection to multiple environments (databases)
9. Checking the Reference Table and Reference Information

Prerequisites

Please become through the following articles if you are not already familiar with SQL Database Project and the required tool:

1. Declarative Database Development in Azure Data Studio (sqlshack.com)
2. Two ways to build SQL Database Projects in Azure Data Studio (sqlshack.com)

This article assumes that you take fulfilled the requirements (required extensions take been added) to start using SQL Database Projection in Azure Data Studio.

Creating a new SQL Database Project

Please open Azure Information Studio, right-click on the Projects sidebar and click Create new:

Create a fresh SQL Database Projection named BestLamps using the following settings as a general guideline while you are free to choose the Target Platform and location that suits your requirements:

The SQL Database Project congenital in Azure Data Studio is ready to be used at present.

Creating a Reference Table in the database project

The next stride is to create a reference table in the project, so right-click on the Project (BestLampts) and click on Add together Tabular array followed by inputting the table proper name as Colors.

Create the tabular array Color using the post-obit SQL code:

-- Creating a reference table color CREATE TABLE [ dbo ] . [ Color ] ( [ ColorId ] INT NOT NULL IDENTITY ( 1 , ane ) , [ Proper noun ] VARCHAR ( 50 ) , [ Detail ] VARCHAR ( 200 ) , CONSTRAINT PK_Color PRIMARY KEY ( ColorId ) )

The table should outset showing upward in the database project at present.

Creating a Reference Information folder structure in the database project

Right-click on project BestLamps and click Add together New Binder followed past naming the binder as ReferenceData as shown below:

Adding a Information Script to populate the Reference Tabular array to the Reference Data folder

One time the desired folder is created, please add a data script in the ReferenceData folder while that script must define a style to insert reference data to the reference table (Color).

Now, please remember creating a data script is a bit tricky in SQL Database Project when using Azure Data Studio equally compared to doing the same thing in Visual Studio considering of the limitation y'all may come across.

The data script is supposed to incorporate the insert script that populates a reference table, only it must exist called inside a postal service-deployment script as in this way nosotros can call multiple data scripts at once (in order through post-deployment script).

Correct-click on ReferenceData folder and click on Add Script to add together a new script proper name Color.data using the following T-SQL code:

-- Color Reference Table Information Script -- Remove data from tabular array color TRUNCATE Table dbo . Color -- Add together reference data to the table color Set up IDENTITY_INSERT Color ON INSERT INTO dbo . Color ( ColorId , Proper noun , Detail ) VALUES ( 1 , 'Blueish' , 'This is blueish color' ) , ( ii , 'Greenish' , 'This is green colour' ) , ( 3 , 'Yellowish' , 'This is yellow color' ) Ready IDENTITY_INSERT Color OFF

The construction of the database project should be as follows:

We demand to change the Build property of the script to None so that information technology tin can exist run by Post Deployment script and to practise this we accept to right click on the Projection and click on Edit .sqlProj File:

Expect for the post-obit line of lawmaking in the project file:

< Build Include ="ReferenceData \ Colour . information . sql" / > < / ItemGroup >

Delight supplant information technology with the following:

< None Include ="ReferenceData \ Colour . data . sql" BuildAction ="None" / > < / ItemGroup >

Please salvage your changes and do as directed one time you are asked to reload project after the in a higher place changes.

Adding a Mail service Deployment Script and calling the Data Script in the Mail Deployment Script

Add a post-deployment script by right-clicking on the Database Project (BestLamps) and clicking on Add Post-Deployment Script:

Please enable SQL CMD and type the following lines of code in the Post-Deployment Script:

-- This file contains SQL statements that will be executed later on the build script. : r ". \ ReferenceData \ Colour . data . sql"

Running a Build to see all is fine

Delight right-click on the Project (BestLamps) and click Build to ensure that at that place are no errors:

Publishing the Project to multiple environments (databases)

Please fix the Target Platform (past correct-clicking on the project and clicking Change Target Platform) that suits your requirements while in this walkthrough we are choosing SQL Server 2017.

Right-click on BestLamps SQL Database Project and click on Publish and so set the publish environment this could exist your Dev or Test server:

Now we tin can simply point to our Product server using the same Publish process in Azure Data Studio and if there is no production server, yet we can publish the production database on the same server with Prod postfix:

Checking the Reference Table and Reference Data

Finally, switch to the Server tab in Azure Data Studio and locate the recently published databases:

Let u.s. query the Color tabular array past running the following script against BestLamps every bit follows:

-- View Colour Reference Table (data) SELECT c . ColorId , c . Name , c . Detail FROM dbo . Color c

The output is every bit follows:

Congratulations nosotros have successfully accomplished the objective of this walkthrough which was to centralize the Reference Table and its underlying data using Azure Information Studio.

Final Word

The strategy to centralize reference information may vary from one professional scenario to another and nosotros implemented 1 such method to have like reference data rows in all the environments, but it is absolutely fine to use the schema comparing tool to resolve whatsoever departure betwixt SQL Database Project reference data and BestLamps Production database that may happen from time to fourth dimension and in that case, yous have to decide whether to include or exclude Production surroundings.

Even so, please remember at the end of the twenty-four hour period it is non the reference data strategy that just works rather information technology is the strategy that works and complies with your business requirements matter plus the I leave it to you to decide how big role is the choice of best tools similar Azure Data Studio in helping you accomplish your business objective.

• Writer
• Recent Posts

Haroon's deep involvement in logic and reasoning at an early historic period of his academic career paved his path to become a information professional.

He holds BSc and MSc Degrees in Computer Science and also received the OPF merit honor.

He began his professional life as a reckoner programmer more than a decade ago, working on his first information venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Archetype ASP along with developing reports and archiving many years of data.

His piece of work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, evolution, testing, implementation and migration along with Database Life Cycle Direction (DLM).

He has too received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular involvement and potential.

View all posts past Haroon Ashraf

augustinedrablent.blogspot.com

Source: https://www.sqlshack.com/centralizing-database-reference-tables-for-dev-test-and-prod-deployments-in-azure-data-studio/