A little curiosity on my part led me to research a little further on the available pieces of SQL Server Tools. A cross-platform tool for continuous integration and deployment, called SqlPackage, is part of SQL Server Data Tools. I picked it up because continuous integration, delivery and deployment are now must-have in the modern-day application development paradigm.

We’d talk about the following in this article:

1. The Database Management Life Cycle
2. Complexities of the database release management process
3. Introduction to SQL Server Data Tools and SqlPackage
4. Using SqlPackage.exe, with examples

In the database release management process, Continuous Integration (CI) and Continuous Deployment (CD) are considered as challenges. Continuous Deployment (CD) step is critical stage for the delivery when it comes to database release management process. Let me review some of the reasons behind its complexity.

1. In most of the case, the delivery environment is not identical to the testing environment
2. Nature of the release, understanding the impact of the release and such parameters are significant in the database life cycle management
3. Understanding the current state of the production systems is an absolute necessity
4. Database protection for current delivery systems, which defines the backup and rollback process is a parameter to consider
5. The trade-offs for the backup and rollback process are based on the type of hardware in place, which should be accounted for
6. The outage window has to be measured and defined

Here is a good article that covers these challenges in much more detai: Why is database continuous integration and delivery so hard and how to make it easy; SQL Server database DevOps pain points and solutions

I most organizations, the database administrators are the only technicians allowed to make changes to the database. Their job is to scrutinize the change management scripts before the implementation in production. In some cases, several developers may have developed the change script, which may lead to changes to various components of the database objects. Though the production environment can be simulated in the test environment using the production data, there’s no guarantee that the change would be successful; things could still go wrong for various reasons. Therefore, aspects like the order of execution of the change management script is vital to the release management process.

Next-generation technologies are compatible with the loosely-coupled architectures of the current environments, with various components providing different functionalities. Many organizations are working towards upgrading their technology competency to accelerate software delivery, the most significant challenge being the ability to meet customer needs as quickly as possible. And for this, we may have to cut down on some of the complexities. For instance, some applications may use a combination of SQL and NoSQL databases. You’ve got to consider the nature of these databases. If good care is not taken in testing, and the deployment goes through without having the integration tested, the impact would be on the live data, and any glitch in the deployment will pull down the entire system. One aspect to consider is whether the native database tools have CI/CD capabilities, or if there’s a need to go for a third-party tool.

Let us discuss the various states of the Database Release Management process:

In some cases the deployment may not go as expected for various reasons. Though the developers initiate the Change Management (CM) process, the order in which the script executes, and state of the environment decide the status of change management ticket.

SqlPackage.exe

Microsoft has created a very powerful cross-platform command line tool called SqlPackage.exe, which they provide for free. This tool is part of the SQL Server Data Tools installation, which is capable of handling environments involving SQL Server, Azure SQL Database and Azure SQL Data Warehouse.

It’s included in the downloadable SQL Server Data Tools package.

1. If you have updated to the latest SSDT, you’ll find SqlPackage.exe and the related DLLs in <VS Install Directory>\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130. For Visual Studio 2013, the VS install directory is C:\Program Files (x86)\Microsoft Visual Studio 12.0; it’s and it’s 14.0 for Visual Studio 2015.
2. SQL Server Management Studio (SSMS) and the standalone DAC Framework MSI both install to the system-wide location. This is C:\Program Files (x86)\Microsoft SQL Server\130\Dac\bin.pram

In this section we can have a quick look at how SQLPackage.exe can be used for the continuous deployment process. To get detailed information about the parameters, type the respective action type followed by ?.

To view the details of export action type

Similarly for other action types, you can issue the following commands

• Extract is used to create a database snapshot (a file, .dacpac) from Windows Azure SQL Database or an on-premise SQL Server.
• Export exports the database schema as well as the user data to a BACPAC file from a Windows Azure SQL Database or an on-premise SQL Server.
• Import is the opposite of Export, of course, which imports content from a BACPAC file into a new database on Azure SQL or SQL Server.
• Publish looks at the .dacpac file for schema and incrementally updates the existing database schema—again, this database could be an Azure SQL database or a SQL Server database.
• DeployReport creates a report in XML format; the XML is created by a publish action.
• DriftReport creates an XML of the changes made to a registered database since its last registration.
• Script creates a T-SQL incremental update script to update the schema of the target; it matches the schemas of the source as well as the target.

Let’s now look at some examples

Export the local database to a bacpac file

To export the database using SqlPackage, run the following command.

Here,

• ssn specifies the server name that contains the database,
• sdn defines the source database name,
• tf specifies the location of the dac or bac files.

In the following example, we are connecting to the SQL instance (node2) on Linux.

Let’s start importing the SQLShackDemo database using the BACPAC file. The BACPAC file was exported using SqlPackage.exe in the previous example.

• sf specifies the source file to be used as the source of action instead of database.
• tsn specifies the name of the server that hosts the target database,
• tdn specifies the name of the target database,
• tu is the SQL Server user that is used to get access to the target database,
• tp specifies the password that is used to get access to the target database.

SqlPackage.exe makes extracting the dacpac file pretty straightforward, even if we’re working on more than one database. We can even automate this process using PowerShell.

Or,

Change the values of the following parameters as per your environment setup:

• Location of the sqlpackage.exe
• /tf: The target location of the dacpac file
• /scs: Change the ServerName and DatabaseName

This is a great feature for SQL databases deployment. This way, incremental updates are applied very easily without preparing the SQL scripts.

We can compare any two artifact files and generate the differential script using the scripting option. The diff.txt file is the script file generated by comparing the source file with the target dacpac file.

In the following example, the ApexSQLBackup database is exported to a .dacpac file and then the dacpac file, ApexSQLBackup_3.dacpac is compared with ApexSQLBackup_1.dacpac. The result is then written to a text file.

That’s all for now; but see the index below for more articles in this series

Wrapping up

Release management is a culture. It requires a lot of team work and team effort. More importantly, it requires tight documentation and a deep understanding of the system as a whole. With well-defined and refined process along with good teamwork, planning and testing would lead to a successful release management cycle.

After a detailed study, and testing a lot of examples, I can say that there is no one approach to serve one’s purpose—there’s no “One size fits all”. Most of the time, third party tools may fix some of the difficulties while managing the large releases, but not all. And at the same time, native tools could very well handle most of the work in some environments; for example, SQL Server Data Tools can handle the state-based method very well.

Table of contents

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021