What is Data-Tier Application

Data-Tier Application Version 1.1 (DAC) was introduced with SQL Server 2008 R2.

With Data-Tier Application, we are able to transfer the databases of our applications with all their requirements (tables, schemas, logins, etc.) to the production environment. In fact, by using this feature, we can move databases from one instance to another instance.

Using Data-Tier Application (DAC), let’s make an example that transfers the database from the test environment to the production environment.

With DAC, we can transfer the database to production with or without data.

As shown below, if we right-click on the database and click Task and then Extract Data-tier Application…, it will create a dacpac file with the entire schema structure without data. Clicking “Export Data-tier Application…” will create a bacpac file that contains the data in the database.

By clicking Extract Data-tier Application, we create a dacpac file that contains only the schema structure without data.

We see a screen like the following. Save to DAC package file contains the path information to save the file. By clicking Browse, we can save it to any location.

Click Next, Next and Finish to complete the process.

Then, right-click the Databases tab on the instance where we will move the schema structure of the database and click “Deploy Data-tier Application…” as follows. If we created our file with export method in the previous step, we would have to do Import Data-tier Application…

Then, we select the file with the dacpac extension that we created in the previous step by clicking browse.

Click Next to proceed. It asks us for the name of the database we will deploy as follows. The name in the previous instance comes automatically. You can change the name if you want. I’ve changed to TestDBNewDACAfter.

In the next steps, we complete the process by clicking next and finish.

If you look at the logins, you will see that the logins that are authorized in the TestDB database on the old instance are moved to the new instance.

But there is an important point to note here. For security reasons, SQL Server does not store passwords in dacpac or bacpac files. When we deploy or import dacpac or bacpac files, it creates a new password and creates the login on the new instance and disables it. Therefore, you must enable login and set the old password again.

If you have made some changes to the old instance and want to transfer only those updates instead of doing the same process again, you can use DAC’s Upgrade Data-tier Application. You can find the details in the article “Update Your Database with Upgrade Data-tier Application“.