Wondering how to deploy DACPAC? We can help you.

As part of our Server Management Services, we assist our customers with several SQL queries.

Today, let us focus on how we can deploy DACPAC with SQL Server Management Studio.

How to deploy DACPAC

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.

• 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? We’d be happy to assist!]

How to deploy DACPAC with SQL Server Management Studio

Follow the below steps suggested by our Remote Server Monitoring engineers 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.

• 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 help with the deployment? We are available 24*7]

Conclusion

In short, the deployment process registers a DAC instance by storing the DAC definition in the msdb system database. Today, we saw an effective method our Support Engineers employ in order to deploy DACPAC.