- Kubernetes logs: Pod logs, container logs, Docker logs, kubelet logs, and master node logs - Mon, Sep 25 2023
- Kubernetes DaemonSets - Wed, Sep 6 2023
- Static Pods in Kubernetes - Fri, Sep 1 2023
What is a DAC package?
A data-tier application package (DACPAC) is essentially a self-contained compressed file with a *.dacpac extension that contains a schema of all database objects, such as tables, views, functions, and stored procedures. The key thing to note is that a DAC package, by default, doesn't include any user data, which makes it a perfect candidate for database replication between development and production environments.
Data owners can ensure the privacy of their data while extracting and handing out a DAC package from the production database to an external development team. Note that there are certain tools (e.g., SqlPackage) that allow you to include user data in a DAC package, but normally, a DAC package extracted with SSMS and Azure Data Studio is supposed to contain only the schema.
Another important use case for the DAC package is that you can upgrade an existing database. This is very useful when the development team changes the database schema, perhaps to add a new feature to the application. When rolling out the new feature to production, you can extract the DAC package from the DEV database instance and deploy it on the production database to upgrade the schema without affecting the existing data. Of course, you must be a member of the sysadmin, serveradmin, or dbcreator fixed server role to upgrade DAC. The process can be manual or automatic with the help of a continuous deployment (CD) pipeline.
What is a BAC package?
A backup package (Bacpac), on the other hand, contains everything, including schema and user data, making it an ideal choice for admins when it comes to database migration between different SQL Server instances. You can export a Bacpac package from the SQL Server instance running a higher version (e.g., SQL Server 2019) and import it to an instance running a lower version (e.g., SQL Server 2016) without any problem, which is not possible with the native BAK file approach.
A cool thing about both Dacpac and Bacpac packages is that you can use them with SQL Server instances running across different platforms, such as on-premises, Azure, AWS, or Google Cloud.
Data safety and security
This goes without saying, but since we are dealing with SQL Server databases in this post, you must perform a backup of your database. Furthermore, it is highly recommended to deploy (or restore) the Dacpac (or Bacpac) package in an isolated environment, particularly if you receive it from someone you do not trust. Since Dacpac and Bacpac are compressed packages, you can import and inspect their contents on an isolated developer machine running Visual Studio and SQL Server Developer Tools, as discussed here.
Working with a Dacpac package
Let's first discuss how to extract a Dacpac package and how to deploy it on a different SQL Server instance. You can do it with SQL Server Management Studio (SSMS), but we will use Azure Data Studio for this post. To learn the differences between SQL Server Management Studio and Azure Data Studio, check out this post.
For the demo, I will use two SQL Server instances:
- SQL-PROD: Running SQL Server 2016
- SQL-DEV: Running SQL Server 2019
You can see that the SQL Production instance is running an older version of SQL Server, whereas the Development instance is running a newer version.
Extract a Dacpac file
Suppose we are in the process of upgrading our application. We need to create an instance of the production database but without the actual user data. To do so, follow these steps:
- First, launch Azure Data Studio and install the SQL Server Dacpac extension, as shown in the screenshot below:
- Now connect to the Production SQL Server instance (SQL-PROD, in our case). You can see the COMPANY database with a few tables, stored procedures, and functions. The tables have some data.
- To extract the DAC package, or, in simple words, to create a Dacpac file, right-click the database and select the Data-tier Application Wizard option.
If you don't see this wizard, make sure you have installed the SQL Server Dacpac extension, as shown in step 1.
- The first step in the wizard shows four operations: Deploy Dacpac, Extract Dacpac, Import Bacpac, and Export Bacpac. All four options are pretty much self-explanatory, but the description that you see along with each option in the wizard makes it even clearer.At this point, we are interested in extracting a Dacpac file from the production database, so I will choose the second option and click the Next button.
- Now, select the Dacpac settings, and click the Next button.
- Finally, review the settings and click the Extract button.
- On the Tasks tab at the bottom, you can see the progress of the Dacpac extraction.
Deploy a Dacpac file
Now that we have a Dacpac file extracted, you can hand it out to the development team or deploy it on the DEV SQL server instance yourself. To do that, follow these steps:
- Launch the Azure Data Studio and install the SQL Server Dacpac extension, as discussed in the previous section.
- Connect to the DEV SQL server instance (SQL-DEV, in our case).
- Expand the Databases node. Notice that there is no COMPANY database here yet.
- Now right-click the Databases node and select the Data-tier Application Wizard option, as shown in the screenshot below:
- Again, you will see the four options, but this time, we will select the first one (i.e., Deploy Dacpac), and click Next.
- Now, select the Dacpac settings, as shown in the screenshot:
The important thing here is that you need to select the New Database option under the Target Database field because we are creating a new database. When you are ready with the new feature to deploy the changes to production, you need to select the Upgrade Existing Database option here.
- Finally, review the settings, and click the Deploy button.
- When deployment is complete, refresh the Databases node, and you will see the new COMPANY database created on the DEV SQL Server instance.
You can see that all the database objects are there, except the data. This was expected, since we used the Dacpac package. Your developers can now start working on the DEV instance, insert their test data, and make the necessary changes to the database schema. Once everything is ready to roll out to production, you will follow the same procedure to extract the Dacpac from the DEV SQL Server instance and upgrade the production database to merge the new schema changes.
Working with a Bacpac package
Now, let us assume a completely different scenario in which you need to migrate a SQL Server database from an Amazon RDS (or Azure SQL Server) instance to an on-premises SQL Server. If your on-premises server is running the same (or higher) version as that of the source SQL Server, you can use the old-school way, as discussed in this post. But what if the SQL Server version on the target server is older? In that case, if you try to restore a BAK file, you will get an error, as shown below:
System.Data.SqlClient.SqlError: The database was backed up on a server running version 15.00.2101. That version is incompatible with this server, which is running version 13.00.6404. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)
The Bacbac package comes to the rescue in such a situation.
For the demo, I will use the Amazon RDS for SQL Server instance as a source and the SQL-PROD instance as a destination. Our goal is to move the COMPANY database from Amazon RDS, which is running a newer version of the SQL Server (i.e., SQL Server 2019), to an on-premises SQL Server instance, which is running an older version (i.e., SQL Server 2016). Remember, the Dacpac and Bacpac packages are supported, whether your SQL Server instance is on-premises, Azure, AWS, or Google Cloud.
Export a Bacpac file
To export the COMPANY database as a Bacpac package, launch the Azure Data Studio, and make sure the SQL Server Dacpac extension is installed.
- Connect to the Amazon RDS for SQL Server instance where the COMPANY database is currently located.
- Now expand the Databases folder, right-click the COMPANY database, and select Data-tier Application Wizard.
- In step 1, select the fourth (Export Bacpac) option, and click Next.
- Now, make sure the correct source server and database are selected. Also, select a path to export the Bacpac file and click Next.
- In step 3, review the summary, and click the Export button. You can monitor the status of the Bacpac export on the Tasks tab, as shown in the screenshot.
Import a Bacpac file
Once you have the Bacpac package, it can be imported to any SQL Server instance. As already warned, if you do not trust the Bacpac sender, I would strongly suggest importing it to an isolated SQL Server instance.
For the sake of this post, we will assume that we trust the sender, so we will go ahead and import it to the SQL-PROD instance straightaway. To do so, follow these steps:
- Launch the Azure Data Studio, and make sure the SQL Server Dacpac extension is installed.
- Connect to the SQL Server instance to which you want to import the Bacpac file.
- Right-click the Databases folder and select the Data-tier Application Wizard.
- In step 1, select the third (Import Bacpac) option, and click Next.
- Now, browse and specify the path of the Bacpac file. Make sure the target server and new database name are correct, and then click Next.
- Finally, review the settings, and click Import. This will import the COMPANY database from the Bacpac file, including all the data. See the screenshot below for reference:
You just restored a database to an on-premises SQL Server instance using a Bacpac file.
I only covered a few use cases of the Dacpac and Bacpac packages. Admins can use them in a variety of scenarios. In a nutshell, you can use Dacpac to extract/deploy the database schema across different SQL Server instances and Bacbac to export/import the full database, including user data. That is all for this post.