Database Projects – Getting your database partially in a Database Project
In my previous blog post, we finalized our development phase by deploying our changes to the development environment(Database Projects – Deploying Changes). Since we now have a complete overview of how development can be done, we can look at specific use cases.
Quick Overview
Use Case Description
In this blog post, I will describe how you can get a database in source control partially. You might be wondering why you would do that. Well, let’s start by explaining the use case.
A couple of years ago, I was working for a company where a third-party vendor owned the OLTP system. At that point in time, we were not allowed to change any existing objects or create any new objects in the existing schemas. Though, we were required to be able to transfer the data from the OLTP system to the staging environment of our Data Warehouse. To do so, the third-party vendor created a schema in the database where we were allowed to create views and stored procedures to be able to get the data we needed.
That being explained, let’s dive into a practical guide on how you can maintain databases partially in a Database Project and Azure DevOps.
Step-by-Step Example
For the step-by-step example, we will be using the AdventureWorksLT database. In preparation for this blog post, I have created a brand new Database Project, if you’re wondering how to, you can find a step-by-step guide using the following link: Getting started with Database Projects and Version Control.
To show you how you can maintain a part of a database in a Database Project, I will be using an existing view, SalesLT.vw_ProductCateogry in the AdventureWorksLT2019 database.
Let’s say that this is the only view we are allowed to maintain, to get this in our Database Project, we start by creating a Views folder in our Database Project. To get this done, you right-click on your project, choose: Add > New Folder, and give it the desired name.
To continue, I have opened the SQL Server Object Explorer window in Visual Studio (View > SQL Server Object Explorer), connected to my target database and located the View that I want to include in the Database Project. Now I can easily drag and drop the View from the SQL Server Object Explorer to my folder in the Database Project. As a result, I will see the view showing up in the Views folder. (be aware, in Visual Studio 2022 this doesn’t work, I have registered this as a bug)
As soon as the import is done you will see that Visual Studio will be showing errors in the Error List. The errors that you will find are all related to unresolved references to certain objects. Since we don’t have all tables and schemas scripted into our Database Project. To overcome this challenge, we will take a couple of steps, as a first step, we will be navigating to the Solution Explorer and locating our vw_ProductCategory.sql file. Right-click on the file and choose Properties. Now we will take a look at the Build Action.
When creating a new file in the Database Project, Visual Studio will assign Build as the default build action to a script.
By doing this, Visual Studio will always include the file during every single build of the Database Project and during deployment, it will check if the object has been changed. In our case, we will need to set the Build Action to None to start. By doing this, the errors will disappear from the Error List but the file will be excluded by the default build and deployment process. We will solve this in a minute.
Since we still want to be able to deploy this object to the AdventureWorksLT database, we are going to introduce a Post Deployment Script. To create this kind of script, we right-click on our Database Project, and choose Add, New Item… as a search term, we can easily use the word “post”.
We select the Post-Deployment Script file that Visual Studio has suggested, give it a name and choose “Add” to create the script in our Database Project. As a result, we will see a file with the following content:
At this point, I suggest you read the following sentence very carefully: “This file contains SQL statements that will be appended to the build script.”. The most important word here is appended. To get our View deployed, we will add the following line of code to the Post-Deployment Script:
:r .\Views\vw_ProductCategory.sqlBy doing this the code in the vw_ProductCategory file will be added at the bottom of the deployment script, which will be generated by Visual Studio. Now, it is time to explain the catch here… If we take a closer look at the vw_ProductCategory.sql file, you can see that this file starts with CREATE VIEW…
Since the View already exists on the target database, we will need to assist Visual Studio a bit since it will only append this file to the deployment script. To do so, we will add the following lines of code at the top of the script before the CREATE statement:
DROP VIEW IF EXISTS [SalesLT].[vw_ProductCategory]
GOIf we don’t add this code, our deployment will fail since the object already exists on the target database. The GO is required as well, otherwise, this won’t work. As a result, the script should look like this:
As soon as we have executed these steps, we will be able to deploy your changes as you would do in any other Database Project.
Important Notices
When you maintain a database only partially in a Database Project, it will be very important to adjust a couple of deployment properties.
When you deploy by the default setting you will be overwriting database properties during deployment. Which of course won’t be a very good idea when you’re not the database owner. So before deployment make sure you go to the advanced section and disable the “Deploy Database Properties” property.
Another important deployment property which you will need to disable is “Drop Indexes Not In Source”, which can be found in the Drop-section of the Advanced Publishing Settings. I would advise disabling the others in the Drop section as well.
