When working with ASP.NET-based applications, you'll often need to couple this with changes to your underlying database schema.
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
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.
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.
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:
- Tiny Happy Features #3 - Publishing Improvements, chained Config Transforms and Deploying ASP.NET Apps from the Command Line
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: