Are you trying to deploy DACPAC?

This can be done with an SQL Server Management Studio.

DACPAC (Data Tier Application Package) is a single file which contains database model i.e. all files represent database objects. It’s a binary representation of database project compatible with SSDT.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform SQL related queries.

In this context, we shall look into how we can deploy DACPAC with SQL Server Management Studio.

How to deploy DACPAC Database file ?

The deployment process registers a DAC instance by storing the DAC definition in the msdb system database. Then it creates a database, populates that database with all the database objects defined in the DAC.

i. Security and permissions

Authentication logins are stored in a DAC package without a password. When the package is deployed or upgraded, the login is created as a disabled login with a generated password.

To enable the logins, we log in using a login with the 'ALTER ANY LOGIN' permission. We use 'ALTER LOGIN' to enable the login and assign a new password to communicate to the user.

Windows Authentication logins do not require this because SQL Server does not manage their passwords.

A DAC can only deploy by members of the sysadmin or serveradmin fixed server roles, or by logins in the dbcreator fixed server role with 'ALTER ANY LOGIN' permissions. The built-in SQL Server system administrator account named sa can also deploy a DAC.

Deploying a DAC with logins to SQL Database requires membership in the loginmanager or serveradmin roles. Deploying a DAC without logins to SQL Database requires membership in the dbmanager or serveradmin roles.

[Couldn't set permissions in SSMS? We will be happy to assist you!]

How to deploy DACPAC with SQL Server Management Studio ?

Follow the below steps in order to deploy DACPAC.

1. Initially, open SQL Server Management Studio.

2. Then, we connect to the SQL Server Instance containing the database to deploy to.

For example, SQL Server 2008 R2 SQL Express instance.

3. Now, we navigate the tree in the Object Explorer to the database to deploy to.

4. Then, right-click on the database name and select Tasks | Upgrade Data-tier Application…

5. It displays the Upgrade Data-tier Application wizard. It presents the set of steps that the wizard will execute.

6. Here, we click Next.

7. Browse to find and select the DACPAC file. The default expected location is: C:\Users\<userName>\ Documents\SQL Server Management Studio\DAC Packages.

8. Then click Next.

9. The information in the DACPAC compares with the target database.

If the target database changes externally from a DACPAC deployment, it warns of a change in the database

We can proceed anyway, or exit the wizard and research the issue.

10. Then we click Next.

11. It will automatically generate the deployment script.

12. From the above dialog, we can optionally select to save the deployment script to a file.

13. When ready, click Next.

14. The Upgrade Plan will display for review.

a. We can elect to save the report.

b. We again have the option to save the script.

15. Then click Next.

16. A summary displays information that was in use during the deployment.

17. Now we click Next and the deployment process begins.

18. Then it executes the script, completing the DACPAC deployment process.

19. As per desire, click Save Report.

20. Finally, click Finish.

ii. Limitations and restrictions

A DAC can deploy to SQL Database, or an instance of the Database Engine running SQL Server 2005 Service Pack 4 or later.

If we create a DAC using a later version, the DAC may contain objects not supported by SQL Server 2005. We cannot deploy those DACs to instances of SQL Server 2005.

[Need urgent help with the deployment of SQL Database? We are available 24*7. ]