Continuous Integration and Continuous Deployment (CI/CD) – SQL Server Database project dependency – Part 3

Previously, we have created an Azure DevOps Continuous Integration (CI) and Continuous deployment (CD) pipelines to independently deploy a SQL Server database project. However, in an enterprise data warehouse environment, the databases are mostly dependent on other databases. Because, in DWH, we access objects from multiple databases (like accessing the staging layer objects into the transformation layer and accessing transformation layer objects into the presentation layer) in procedures and views. If, we will use DatabaseName.SchemaName.TableName pattern in the Visual Studio Database project, we will get a build error (especially if we are using cross-database objects into a view, not inside a procedure). In order to access the objects from other Databases, we need to add a reference to that database project. Similarly, we need to configure our CICD pipeline so that the objects from other databases can be accessible during the build and release processes. In this post, we will discuss how we can reference the objects from one database project to another database project in the Visual studio database project and in the Azure DevOps CICD pipelines.

Setup SQL Server Database project with a cross-database object reference

We have created a database project named “Demo_Database_CICD” in the SQL Server CICD – Part 1 post. Now, we will add another database project named “Demo_Database_CICD_DWH” in which we will access the objects from the “Demo_Database_CICD” database. To add this project into our solution, let’s follow the below steps:

• Right-click on the solution name.
• Go to Add -> New Project.
• From the project template type, choose SQL Server Database Project and click Next.
• On the next screen, use the project name “Demo_Database_CICD_DWH” and click on the “Create” button.
• A new project will be added to the same solution.
• Now, go to SSMS and create a new database named “CICDDemoDB_DWH“.
• Next, add a new view in the Demo_Database_CICD_DWH database named “Dim_Department“. In this view, we are referring to an object from another database named “CICDDemoDB“. The script of the view is as below:

• CREATE VIEW [dbo].[Dim_Department]
  	AS 
  	SELECT 
  	[DeptId],
  	[DeptName]
  	FROM 
  	CICDDemoDB.dbo.vw_Department
  GO

• Now, in order to import the objects from the SQL Server CICDDemoDB_DWH database to the database project, right-click on the project Demo_Database_CICD_DWH and choose Import -> Database.
• Next, click on “Select Connection” and go to the “Browse” tab and select the server as localhost and choose the CICDDemoDB_DWH database from the Database dropdown. Then, click on the “Start” button to start the import process. Once it is imported, click Finish.
• Our Visual Studio solution/project should look like this.

Add database reference to resolve build error for cross-database objects

Once we have imported the database, we can try to build the solution, it will raise the build errors like this.

In order to resolve the above errors, we need to add a reference to the “Demo_Database_CICD” project into the “Demo_Database_CICD_DWH” project. To do so, follow the below steps:

• Go to Demo_Database_CICD_DWH right-click on the References -> Add Database Reference option.

• On the next screen, click on the radio button “Database projects in the current solution” and choose the database from which we are accessing the objects and click on the Ok button. In our case, we need to add a reference to the Demo_Database_CICD project.

• It will add a reference to the Demo_Database_CICD project in the Demo_Database_CICD_DWH project.

• Now, we need to change the way we are referring to the objects from another database. In our case, we are accessing the view “CICDDemoDB.dbo.vw_Department” into view “[dbo].[Dim_Department]“. Let’s open the view and change its definition as below:

• CREATE VIEW [dbo].[Dim_Department]
  	AS 
  	SELECT 
  	[DeptId],
  	[DeptName]
  	FROM 
  	[$(Demo_Database_CICD)].dbo.vw_Department

• We need to replace the database name with the referenced Database Variable Name. We have replaced the database name “Demo_Database_CICD” with its project variable name “[$(Demo_Database_CICD)]“.
• Now, if we will build the database project Demo_Database_CICD_DWH, first, it will build the referenced database project (In our case it is Demo_Database_CICD) followed by the current database project.

Configure Azure DevOps Continuous Integration (CI) pipeline for cross-database object reference

We have already created an Azure DevOps Continuous Integration pipeline in SQL Server Database CI – Part 1. In our CI pipeline, we are using the solution file path (not the project file path) into the MSBuild step during the build process. So, in order to resolve this cross-database reference in the CI pipeline, we just need to add the database project variable name(s) and its value(s) to the CD pipeline variables.

Let’s make the below changes:

• Go to build pipelines and select the build pipeline we want to edit. In our case, we are modifying the existing CI pipeline “CICDDemoPipeline“.
• Click the Edit pipeline button, it will open the task-level details for the CI pipeline.
• Go to the Variables tab.
• Click on the Add button at the bottom of the list.
• Add the variable Demo_Database_CICD with the name-value pairs to the variables list.

Configure Azure DevOps Continuous Deployment (CD) pipeline for cross-database object reference

In the previous post, we have already created a CD pipeline in SQL Server Database CD – Part 2. Now, in order to deploy the project “Demo_Database_CICD_DWH“, we need to add one more deployment task to the CD pipeline and configure the variable values in the release pipeline. We need to make below changes in the Azure DevOps Continuous Deployment (CD) pipeline in order to access cross-database objects.

Change 1 – Add database project variable to the CD pipeline variables

First, we need to add the database project variable name(s) and its value(s) to the CD pipeline variables.

• Go to release pipelines and select the release pipeline we want to edit. In our case, we are modifying the existing CD pipeline “CICDDemoReleasePipeline“.
• Click the Edit pipeline button. It will open the artifacts and stages details of the CD pipeline.
• Go to the Variables tab.
• Click on the Add button at the bottom of the list.
• Add the variable Demo_Database_CICD with the name-value pairs to the variables list.

Change 2 – Add SQL deployment task for the new project

Second, we need to add one more deployment task to the CD pipeline which will be used to deploy the database project “Demo_Database_CICD_DWH“.

• Go to the Tasks tab in the release pipeline.

• Click on the + sign to add a task to the SQL Deployment group. It will add a SQL Server deployment task to the task list.

Change 3 – Pass database variable value as an additional argument in the SQL deployment task

Finally, we need to pass the variable name and its value as an additional argument in the SQL Server deployment task of the CD pipeline.

• Add the DACPAC file path for the project “Demo_Database_CICD_DWH” and set the server field value to your server name (like localhost). Put the Database name as CICDDemoDB_DWH. Use proper authentication. Finally, add the additional argument as “/v:Demo_Database_CICD=Demo_Database_CICD“. It should look like below:

• The final version of the CD pipeline should look like below:

Now, our build and release pipeline is configured to access cross-database objects.

Thanks for the reading. Please share your inputs in the comment section.