Problem
As a SQL Server database developer, I would like to automate the availability of a DACPAC and a setup script of a sample database so that any changes to the sample database will automatically change the DACPAC and the setup script for the sample database.
Solution
One of the possible solutions is to use a declarative database development tool such as SQL Database Project to create and manage the database structure and configuring the debug output of the project to a desired accessible folder which contains the latest version of the DACPAC and setup script for the sample database.
Why You Should Automate DACPAC and Setup Script Availability
It is worth knowing why we need to automate the DACPAC and setup script availability in the first place, but before that it is also important to know why we need multiple methods to create a sample database.
Setup Script and DACPAC
Database development testing often requires an in-house ready to use sample database which must offer the following for quick creation of the database:
- DACPAC
- Setup script
Please remember that DACPAC is simply a package which contains the database structure and reference data (if required) while the setup script refers to the T-SQL script (file) used to create a sample database.
Both DACPAC and setup scripts can be used to create a database instantly, however, it is up to the database developer or tester to choose the method of preference based on the purpose.
Please refer to the tip Creating Multiple SQL Server Database Backup Formats in SSDT – Part 1 for detailed information about making these backup types available manually.
Technical Writers and Setup Scripts
These backup types (DACPAC and setup script) not only help database developers and testers to build a sample database, but they can also be used by technical writers to support their T-SQL examples.
Impact of SQL Database Project Changes on DACPAC and Setup Script
A SQL Database Project by default creates a DACPAC as a result of a successful Build which is then needed to be manually copied to the desired DACPAC folder available to the database developers or testers from a source control perspective.
This may become a laborious task since any time there is minor change in the database project it requires the existing DACPAC available to be reused to be replaced with newer one.
The same is true for the setup script which does require SQL Database Project to be configured to create such a script at the desired location or you have to manually create one script copying the T-SQL behind the database objects defined by the SQL Database Project.
Pre-Requisites
Please have a look at the pre-requisites of this tip to understand and implement it in a better way.
T-SQL and SQL Server Data Tools (SSDT)
This tip assumes that the readers have a good understanding of T-SQL scripts along with basic know-how of creating and managing SQL Database Project through SQL Server Data Tools (SSDT) in Visual Studio.
In order to get familiar with SQL Database Project please refer to the tip Developing Similar Structured Multi-Customer Databases with SQL Server Data Tools (SSDT)
Git Source Control
This tip also assumes that the readers are familiar with basics of source control particularly Git source control and are comfortable to check their database objects into source control.
Planning the Solution
In order to implement the solution, we need to think about the existing and expected scenario regarding creating a database through SQL Database Project.
The following things must be planned as part of the solution:
- Choosing Database Management Tool
- Choosing Source Control
- DACPAC and Setup Script Availability Challenges
Choosing Database Management Tool
We are using SQL Database Project to create and manage sample database and its DACPAC and setup script availability for development testing and other uses.
Choosing Source Control
The Git source control is going to be used for this solution although any other compatible source control can also serve the purpose.
DACPAC and Setup Script Availability Challenges
Those of you who regularly work with SQL Database Projects already know that debugging the database project does create a DACPAC file after a successful Build, but that DACPAC file needs to be manually copied over to the desired location for general use in the context of source control (folder).
We need to find some way to ensure that the DACPAC is either automatically copied over to the desired location or it is created at the desired location without exposing the other important project files which must not be put under source control.
As far as setup script is concerned it has to be created manually by copying the database objects definitions (T-SQL) and make it available in the form of setup script unless the SQL Database Project allows some way to do it.
This is illustrated as follows:
Automating DACPAC and Setup Script
Let’s start working on the solution step by step in order to achieve the desired objective.
Create Azure DevOps Project using Git source control
The DACPAC and setup script has to be available for anyone who has access to the resource to be reused that requires this SQL Database Project to be part of the source control.
Create an Azure DevOps project and name it Automating DACPAC and Setup Script Availability as follows:
Please note that I have chosen the visibility of the project as Private where the intention is to make the project along with DACPAC and setup script available internally while you can set it to Public which means anyone can access it and the available DACPAC and setup script, but in that case please ensure that you are authorized to do so and the project along with sample database DACPAC and setup script does not contain any sensitive reference data or object(s) which must not be shared with Public.
Go to the Repo section and rename the repo for (ease of use) as WatchesV2-dacpac-setupscript:
Connect with DevOps Project
Open Visual Studio and click Manage Connections… as shown below:
Then connect to the Git repo called WatchesV2-dacpac-setupscript under Manage Connections as follows:
Create SQL Database Project
Create a SQL Database Project called WatchesV2 under a new Solution WatchesV2 DACPAC Setup Script:
The blank SQL Database Project is ready now:
Create DACPAC and Setup Script Folder
Next, right click WatchesV2 project and click Add New Folder and add new folder named DACPAC-Setup-Script which is going to host the DACPAC and setup script of the sample database WatchesV2.
The folder is created instantly as follows:
Check DACPAC-Setup-Script Folder Contents
Please locate this folder using windows explorer which must be in the same repo path where the SQL Database Project and the solution is saved:
If you check the contents of the folder DACPAC-Setup-Script it is empty at the moment because the project build has not been processed.
Create WatchType Table
Let’s now add a new table called WatchType to the SQL Database Project using the following T-SQL code:
CREATE TABLE [dbo].[WatchType] ( [WatchTypeId] INT NOT NULL IDENTITY(1,1), [Name] VARCHAR(50) NOT NULL, [Detail] VARCHAR(150) NOT NULL, CONSTRAINT [PK_WatchType] PRIMARY KEY ([WatchTypeId]) )
Setup Target Platform
You can set your desired target platform such as SQL 2017, SQL 2016, SQL 2014, etc. under Project Settings in order to make sure your database is compatible with your desired SQL Server version.
Build the Project
Please press F5 now to build the project:
Now using Windows Explorer go to the bin/debug folder of the project to see the contents:
The DACPAC file has been successfully created and that is what we need to be available for general use in the desired folder which is going to be put under source control since by default Git source control does not take into account bin/debug folder contents and the reason behind this is beyond the scope of this tip.
Debug the Project
The next step is to debug the project by pressing F5 which is going to deploy the changes to the debug database after creating a setup script in the same bin/debug folder:
If you view the bin/debug folder of the project you are going to notice a WatchesV2.sql which is the setup script has been added to this folder as a result of successful build and debug process.
Check DACPAC-Setup-Script Folder Contents
So, what is the issue when both DACPAC and setup script are created as a result of successful build and debug?
Actually, the DACPAC-Setup-Script folder is still empty:
We need to manually copy the DACPAC and setup script from the bin/debug to this folder each time there is a small change in the database project.
Configure Project Build Settings
Go to the Build settings and point Build output path to the DACPAC-Setup-Script folder replacing bin/debug and save the changes:
Debug the Project and Check DACPAC-Setup-Script Folder
View the desired folder where we want the DACPAC and setup script availability after debugging the project and surprisingly it is not empty anymore, rather it contains the required DACPAC and setup script for the sample database:
Put Work under Git Source Control
Please save all your changes along with SQL Database Project and its solution to the Git source control by first checking the changes to be saved by clicking the pen icon at the bottom bar of Visual Studio or pressing CTRL+ALT+F7 key:
Right click WachesV2.dll and click ignore this local item since we don’t want this file to be saved into source control.
Add initial commit as comment and then from the drop down select Commit All and Push as shown below:
Check Azure DevOps
Go to Azure DevOps project and click Files from the left navigation bar and then locate the folder DACPAC-Setup-Script under project WatcheV2 under solution WatchesV2 DACPAC Setup Script:
Congratulations, you have successfully automated the process of creating a database DACPAC and setup script availability for database developers and testers who have access to this repository.
Next Steps
- Please create a test database WatchesV3 from the available DACPAC and see if it is the exact copy of the sample database WatchesV2 or not
- Please Run the setup script by enabling SQLCMD and replacing WatchesV2 with WatchesV4 to create sample database WatchesV4 and see if it matches with WatchesV2 debug database or not
- Please try to automate the tasks of creating BACPAC file.
