Deploying a data-tier application in SQL Azure

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.

1. Microsoft.SqlServer.Smo
2. Microsoft.SqlServer.Management.Sdk.Sfc
3. Microsoft.SqlServer.Management.Dac
4. 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.