Deploy additional sql scripts during automated deployment with web deploy for a LightSwitch project

Introduction

You can find several webdeploy related articles on this blog. Although direct publishing from visual studio is practical, when a more fine-grained control over the deployment process is required, you will soon opt for a deployment via a script. Consequently, the initial publish of the LightSwitch application will be a publish to a package on disk.

When it comes to database deployment, MsDeploy can also deploy the database at the same moment the webserver is updated with the latest bits. That’s great, because it means you will never end up with a de-synchronization between your application and your database.

Why might we need additional sql scripts during deployment?

The database deployment features are great but some pieces are missing. Now, the pieces which are missing are very hard to incorporate my a machine. Let’s give a meaningful example. The sql scripts will nicely contain all tables, primary keys, relations, stored procedures for aspnet security, … But, imagine you have a search screen on let’s say customers and one of your search boxes refers to the field Region. If you have thousands of records, you will be able to speed up the performance of your search screen by creating of sql level another index on the region field. That’s simple logic, but not for the database script engine. The fact that this index is needed can not be derived by a simple algorithm.

As a result, we would like to deploy an other sql script during our automated deployment.

An example

Let’s assume we have already following script to trigger the deployment of both the IIS application and the database:

SET _sourcePackagePath="D:\WS\ExploringMsBuild\DbDeploymentTestApp\DbDeploymentTestApp\Publish\DbDeploymentTestApp.zip"

msdeploy.exe -source:package=%_sourcePackagePath%  -dest:auto,IncludeAcls='False',AuthType='Basic' -verb:sync -allowUntrusted -setParamFile:"D:\WS\ExploringMsBuild\DbDeploymentTestApp\DbDeploymentTestApp\Publish\SetParameters.xml"

Following parameter file is used:

<?xml version="1.0" encoding="utf-8"?>
<parameters>
  <setParameter name="DatabaseAdministratorConnectionString" value="Data Source=.;Initial Catalog=DbDeploymentTestApp;User ID=sa;Password=xxxxxxx" />
  <setParameter name="DatabaseServer" value="." />
  <setParameter name="DatabaseName" value="DbDeploymentTestApp" />
  <setParameter name="DatabaseUserName" value="sa" />
  <setParameter name="DatabaseUserPassword" value="xxxxxxxx" />
  <setParameter name="DbDeploymentTestApp_IisWebApplication" value="Default Web Site/DbDeploymentTestApp" />
</parameters>

 

Great that’s deploying both our app and the database.

Extend the deployment with an additional sql script.

For simplicity let’s assume I want to deploy additionally following script. It’s about the creation of a table, in practice, we would rather deploy additional indexes, or stored procedure, etc. …

 

USE [DbDeploymentTestApp]

CREATE TABLE [dbo].[ASillyTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
	[Field1] [nvarchar](255) NOT NULL
 )
GO

In order to trigger the execution of the above sql script we would need to adapt our deploy.cmd as  follows:

msdeploy.exe -verb:sync -source:dbFullSql="D:\WS\ExploringMsBuild\DbDeploymentTestApp\DbDeploymentTestApp\Publish\AdditionalDbScript.sql" -dest:dbFullSql="Data Source=.;Initial Catalog=DbDeploymentTestApp;User ID=sa;Password=xxxxxx"

Conclusion

Pretty simple, no?

Enjoy.