A data-tier application (.dacpac) creates or updates database schema for SQL Server. Usually, database administrators deploy the application in SQL Server using Management Studio (SSMS). Sometimes, we have to deploy the application programatically. This post explains how to deploy a data-tier application to SQL Azure using .NET.
While deploying using a program, we use a background task, typically a worker role in Azure. At the time of writing this blog, there are limited options to do this. SQL Azure exposes a Management API using a REST interface. However, the interface does not have any API to create or update database or deploy a data-tier application. So, it is not possible to use the Azure Management API.
Powershell is a good option to consider. MSDN Forums has a good thread that explains how to deploy data-tier application using Powershell. For us, executing a powershell script is not a good option. Because the script requires a snap-in. With SQL Azure, the snap-in option is not available.
SQL Server assemblies
Use SQL Server assemblies. These assemblies are not available in GAC within the worker role. So, copy the following assemblies.
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.Management.Sdk.Sfc
- Microsoft.SqlServer.Management.Dac
- Microsoft.SqlServer.ConnectionInfo
Create DacStore
Connect to the SQL Azure instance. And create a DacStore
using the connection object as follows.
string connStr = "Server=tcp:gdpr9zr3i0.database.windows.net; Database=master;User ID=vijay;Password=Pass@word1; Trusted_Connection=False;"; var conn = new SqlConnection(connStr); var srvConn = new ServerConnection(conn); srvConn.Connect(); var store = new DacStore(srvConn);
Load application
The output of a data-tier application is a .dacpac
file. Store the .dacpac
file as an embedded resource. Load the .dacpac
file into a stream. From the stream, create a DacType
object.
var assembly = this.GetType().Assembly; var stream = assembly.GetManifestResourceStream( this.GetType(), "RetailDb.dacpac"); var dac = DacType.Load(stream);
Deploy application
Install
method in DacStore
object does the actual deployment. The first parameter is the object we created earlier from the .dacpac
file. Provide the database name in a DatabaseDeploymentProperties
object. On running the Install
method, a database is created or updated using the application.
string dacName = "RetailDb"; var properties = new DatabaseDeploymentProperties( srvConn, dacName); store.Install(dac, properties, true);
Future versions of SQL Azure Management API should have methods to deploy a data-tier application. In the meantime, the above code snippet helps to deploy to SQL Azure using a worker role.