Deploy Azure DB via Web App – Using VS 2013 Release Management – Part 2 – Setup RM Component and Template to Deploy

In the Part 1 of this post, creating a web deployment package with dacpac, is explained. To deploy the dacpac to Azure DB via web deployment package, a Release Management template should be setup. The same component created in article “How to Deploy to Azure Websites with TFS build 2013 and VS Release Management 2013”, can be enhanced to deploy the database, using the dacpac file in web deployment package.

 
In the Configuration Variables tab the newly added token __DemoAzureDBCon__ (adding the token in publish profile explained in Part 1) is set as a variable.

Next step is setting up an Azure DB. To do this create an SQL database in Azure management portal.

Select Create a new Azure DB server, to create a new DB server for Azure DB. Make sure to select Allow Azure services to access server option.

Database created in Azure with above options.

A web app in Azure is created to deploy the web application.



Copy the connection string (ADO.Net connection string) from the database connection strings and add it in the created web app settings, as a SQL Database connection.

Make sure to update the correct password to the connection string (Copied one has {your_password_here}).
Server=tcp:ch-demodbsvr01.database.windows.net,1433;Database=DemoAzureDB1;User ID=ch-dbsvradmin@ch-demodbsvr01;Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

In the Web App download the publish profile.

From the publish profile copy the values and set the parameters in the Release Management template.

• WebAppName = Package zip file name (Package created with TFS build in Part 1).
• PublishUrl = Publish Profile –> PublishUrl.
• PublishUser = Publish Profile –> userName
• PublishPassword = Publish Profile –> userPWD
• AzureWebAppName = Publish Profile –> msdeploySite
• DemoAzureDBCon = Publish Profile –> SQLServerDBConnectionString

With this template a release can be triggered using the build (Part 1).

Web Application gets deployed.



Database deployed with Web App.

Created Azure DB can be viewed in SQL Object Explorer in VS.

Allow the IP if prompt.

Deploy Azure DB via Web App – Using VS 2013 Release Management – Part 1 – Package SQL dacpac with Web Deploy Package in TFS Build

As explained in the post “How to Deploy to Azure Websites with TFS build 2013 and VS Release Management 2013” a tool can be created in VS 2013 Release management to deploy web sites to Azure. Below are the steps to deploy a azure database with the web application, using the same tool (enhanced to handle database deployments).
In the solution taken for as example a Web application project and a Database project created.

For demo purpose one table added to the database and created in localdb (db server provided with Visual Studio).






This database is added to the Web Application project by defining a connection string in the web.config file.

In the publish profile (for details on creating a publish profile for the web application refer the article here) database connection should be defined as a token understandable by the VS 2013 Release Management (__ as prefix and suffix).

Once this is published the required deployment packages created.

But the .dacpac file to deploy the db is not added to the web package zip file. It is required to get .dacpac inside the web package to deploy it with web application deployment.

In order to get the .dacpac file to web package change the publish profile (pubxml) content as shown below.
Current

Changes highlighted.

• DB Connection object group enabled.
• Default created “DbDacFx” object type is disabled.
• Added new “DbDacFx” with dacpacAction Deploy and path set to relative path of the .dacpac file being built for the SQL project. Note that bin\debug path is set here which is not going to work with TFS builds.

Then there should be a project dependency added to build the SQL project before the web application project to make sure the availability of .dacpac file.

With the above change when the web application is published as a web deploy package, the .dacpac file gets included in it.

The next step is to get this packaged via TFS builds. As explained in the article here TFS build can be setup for this purpose with build arguments specified below.
/p:CreatePackageOnPublish=true /p:DeployOnBuild=true;PublishProfile="TFSAzureRelease"

As expected FS build fails with error, unable to find .dacpac file.

By changing the publish profile relative path to .dacpac file as below as will give a successful build.



Web deployment package in the drop now contains the .dacpac file.

Set parameter xml is created with the tokens added for DB connection.

In Part 2 let’s look at how to enhance the release management component, to deploy web application package (explained in article here), with the database .dacpac file to Azure DB.