Support Center

ASP.NET & SQL Server DB Deployment with Publish Profiles

Last Updated: May 02, 2013 11:26PM AEST
This guide involves deploying SQL Server databases using ReadyRoll SQL Projects, an extension for Visual Studio 2010/2012. Otherwise, the features mentioned below ship with Visual Studio 2012, or Visual Studio 2010 with the Azure SDK installed.

When working with ASP.NET-based applications, you'll often need to couple this with changes to your underlying database schema.
 
Typically you'll be working in an isolated development environment when preparing changes, but when it comes time to promote up to an environment other than your own, you'll likely need to consider how to deploy your Web application assets along with the related database changes.
 
Ordinarily, these two elements have to be deployed separately. Fortunately, Visual Studio ships with the ability to package and deploy the two together using the WebDeploy method of the Publish command.

By the end of this tutorial you will be able to:
 
  • Publish a ASP.NET Web Application & ReadyRoll database project to a remote IIS & SQL Server instance

Get the source code for the projects used in this tutorial.

 

1. Create the solution & configure the ReadyRoll SQL project

 
Get started by creating a new solution in Visual Studio, with an ASP.NET MVC 4 Web Application project and a ReadyRoll SQL Server Database project. Add a table to your database and import it into the project using the ReadyRoll DBSync tool window.
 
Your solution should now look something like this:

 
Now we'll ensure that the ReadyRoll database project always builds before the Web Application does. To do this, right-click the Web Application project in Solution Explorer and select Project Dependencies.


Check the database project from the Depends on list and click OK:

 

Next, we'll set up the database project so that it outputs a special type of script that can be used to deploy the database. 

Right-click the database project and select Properties. Switch to the Project Settings tab and check SQLCMD package (.sql file) under Output types.



Now let's test the new configuration. First we'll switch to the Release configuration (this becomes important during set up of the publish profile; more on that later):

 

Right-click the database project and select Build.


Note that a script has been output as part of the build, named MyDatabase_Package.sql (sample)

When we execute this script as part of the Publish step, it will incrementally deploy all the T-SQL migration scripts contained within the MyDatabase ReadyRoll project to the database, creating the database on the target server if necessary. It does this by wrapping each of your Deploy-Once scripts in a conditional statement to ensure each is executed a single time only in the pre-defined order.

 

    


This deterministic style of database deployment means that the same script can be executed against all of your target environments to apply any pending migrations, creating or altering your database objects as defined in your T-SQL migrations.
 




 

2. Configure the Web Application 

 

With the ReadyRoll project set up for package builds, we can link the Web Application to the Database Project to allow the two to be deployed together.

First let's add a connection string under the root node of the web application project's Web.config file. This will point to the data source underlying the database project:

  
  <connectionStrings>
    <add name="[TargetDatabase]" 
         connectionString="Data Source=(LocalDb)\Projects;
Initial Catalog=[TargetDatabase];Integrated Security=SSPI" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>

**Substitute [TargetDatabase] with the name of the database, eg. MyDatabase.

Next we'll add a parameters file to the project to allow substitution of SQLCMD variables during a Publish operation.

To do this, right-click the Web Application project in Solution Explorer, and add a blank new XML file to the project root called parameters.xml with the following content:
  ?<?xml version="1.0" encoding="utf-8" ?> 
  <parameters>
    <parameter name="Sql script variable $(DatabaseName) in [ProjectName] scripts"
      description="Please provide the database name."
      defaultValue="[TargetDatabase]"
      tags="" />
  </parameters>

In our example, we'll substitute both [ProjectName] and [TargetDatabase] with MyDatabase.
In the next step we'll set up a publish profile to deploy the Web Application along with the Database.


 

3. Create a WebDeploy Publish profile


In this step we'll create a publish profile that deploys the web application and database package together, in this case against a local IIS server and SQL Server instance.


Right-click the Web Application project in the Solution Explorer and select Publish:



From the Publish Profile drop-down, select <New...> and specify a name, eg. Deploy-Dev:



From the Publish method drop-down list select Web Deploy and specify the target IIS server and Web Site/Application for your deployment:



Next, specify the connection string of the target environment (in this case, our Dev environment is located on localhost\sql11). Then check Use this connection string at runtime (update destination web.config). Also check Update database:



In the Configure Database Updates dialog, un-check [Auto schema update] and specify the location of the package deployment script generated earlier:



Finally, click Publish to kick off WebDeploy for the first time.

If you switch to the Output window in Visual Studio, you will see the progress of the Build/Package/Publish operation.


The output indicates that the publish succeeded, so let's check that the deployment had the intended effect in the target environment.


In IIS Manager the Web Application appears to have been deployed successfully: a new app has been created under the Default Web Site.


Now let's have a look at the database within SQL Management Studio (SSMS):

In the Object Explorer we can see that the changes have been applied to the target database server. The [__MigrationLog] table reveals that 2 migrations were applied during the Publish operation, in this case creating and then altering the structure of the [dbo].[Customer] table.



Oh, one last thing... don't forget to switch back to Debug! But remember that our publish profile will always use the Release configuration when deploying.


Remember: for each subsequent deploy after the database is initially created, ReadyRoll will only incrementally deploy your database: when you publish, the script will simply execute any pending migrations on the target SQL Server server instance, leaving the contained data intact. If you need to recreate the database, first drop the database from target server and then re-publish the Web Application project.


 

Summary

 
ReadyRoll's deterministic database package scripts require no external inputs to produce its build artifacts, which makes setting up a repeatable, co-ordinated deployment process for all your application assets an easy task.

The next logical step is to look at automating your deployments. Scott Hanselman has written a great blog post on some of the automation enhancements to Web Deploy that shipped with Visual Studio 2012:
 

If you'd like to try a more holistic approach to automated application deployment, we recommend checking out Octopus. Check out our knowledge base article to find out how to get started:
 

 
readyroll.support@red-gate.com
http://assets1.desk.com/
false
readyrollsql
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete