Visual Studio Database Project + Web Project Into One Web Package. YES. FOR REAL.

Article
06/23/2014

Visual Studio SQL Database Projects are super cool projects that let you create and manage your SQL databases in Visual Studio. They produce a .dacpac file that you can deploy to a SQL Database using msdeploy or other tools.

Visual Studio Web Projects are a great way to create a web site (duh).

Recently I was deploying my VS Web Project to a “web package .zip” file. And then using the .zip file to publish my web site to Azure (using some Azure PowerShell cmdlets mainly).

This was going great, until I wanted to publish the Database Project and the Web Project at the same time. To do this I figured I’d just put the .dacpac file INSIDE of my web package .zip file.

Well, I couldn’t figure this out for the longest time and didn’t find much help online.

So I better write it down here, or who knows if I’ll ever remember it.

Simplest Walkthrough Ever – Make a dacpac

First you’ll want to make a SQL Database Project. This is pretty straightforward.

Create a New Project in Visual Studio and select “SQL Server Database Project”. After it’s created, right click the project and select “Add Table”. Add whatever kind of table you want here.

In the solution explorer, double click on “Properties”. For this example we are going to change the “Target Platform” type to be “SQL Azure Database”.

Now “Build” the solution. In your \bin\debug folder you will find the output which will be a “.dacpac” file. Save this for later.

Simplest Walkthrough Ever – Make a web project

Now we’ll make a simple web project. Create another project, and select “ASP.NET Web Application”. Let’s make an “Empty” web project here. We don’t need anything fancy.

Just build this project when it’s done creating. The output will also be in the bin\debug folder.

Simplest Walkthrough Ever – Make a Manifest.xml file

We need to make a “Manifest.xml” file now. This file will contain some parameters about are web package that we are going to build. And we will give the manifest.xml file as input to MSDEPLOY.exe

The manifest file will be called Manifest.xml and will look like this:

Alright, next thing is to write a MSDEPLOY command to consume this manifest file and output a web deploy package.

I’ve written out below the necessary steps I used to create a package using MSDEPLOY:

CALL "c:\Program Files (x86)\IIS\Microsoft Web Deploy V3\msdeploy.exe" ^
-verb:sync ^
-source:manifest=Manifest.xml ^
-dest:package=MattsWebData.zip ^
-declareParam:name="IIS Web Application Name",defaultValue="YourAzureWebSiteName",tags="IisApp" ^
-declareParam:name="IIS Web Application Name",kind=ProviderPath,scope="IisApp",match="^c:\\yourWebProjPath\\obj\\Debug\\Package\\PackageTmp$" ^
-declareParam:name="IIS Web Application Name",kind=ProviderPath,scope="setAcl",match="^c:\\yourWebProjPath\\obj\\Debug\\Package\\PackageTmp$" ^
-declareParam:name="DefaultConnection-Web.config Connection String",defaultValue="Data Source=azureserver.database.windows.net;Initial Catalog=azuredb;Persist Security Info=True;User ID=yourUser;Password=yourPW" ^
-declareParam:name="DefaultConnection-Web.config Connection String",kind=ProviderPath,scope=dbDacFx,match="^C:\\pathToYourDBProject\\bin\\Debug\\DBProj.dacpac" ^
-declareParam:name="DefaultConnection-Web.config Connection String",kind=XmlFile,scope="c:\\pathToYourWebProject\\obj\\Release\\Package\\PackageTmp\\Web\.config$",match="/configuration/connectionStrings/add[@name='DefaultConnection']/@connectionString"

Put that into a .BAT script, or type it from a command prompt.

This is going to create a web package, and correctly set the parameters for us inside of the web package.

Run it.

After this is done, you will have a Web Deploy Package .ZIP file that contains your dacpac AND your web project!

Publish it

Before you publish it, make sure you’ve already create an Azure Web Site and an Azure Database with the correct names as you’ve specified. You can do this right through the Azure Portal (https://manage.windowsazure.com).

The easiest way in the whole world to publish a web project to an Azure Website is to just use the Azure PowerShell cmdlets.

Here’s how you do that:

1) Download the Web Platform Installer - www.microsoft.com/web/downloads/platform.aspx

2) Search on “Azure PowerShell” and install it (you “may” have to reboot after install).

3) Open up Azure PowerShell as an Administrator.

-Set your execution policy for PowerShell using “Set-ExecutionPolicy RemoteSigned”

-Run “Add-AzureAccount” to add your Azure subscription to the session

-Run “Publish-AzureWebsiteProject –Name yourwebsite –Package .\yourwebpackage.zip”

4) Wait around 20 seconds

That should be it. Check it out on Azure and you should see that your database was updated and the website was updated.

Help, it didn’t work!

If it didn’t work here’s a couple things to try…

-Use the KUDU service to examine the data logs and see what the actual error was (navigate down to the .\data\msdeploy\ folder).

-Also you can try opening up the web deploy .zip package you made. Look at the “Parameters.xml” file in there. Make sure there’s no obvious path errors or anything like that. Also, make sure you don’t have “^^” double caret symbols either. If you do, you can easily just extract the zip file to a folder. Modify the parameters.xml file. And then ZIP the whole thing up again back into a .zip file.

I hope this was helpful. Let me know if it worked or didn’t work for you.

FOLLOW ME too – https://twitter.com/trampsanstom

THANKS!

-Matt Sampson