.SHARP{C#DERS}

Over the years I have done deployment many ways, starting with the most primitive copy and paste working my way through various stages of progress. This article will go over how I deal with automated deployments today with some backstory and reasoning mixed in.

I have some workflow patterns that I consider important that help drive some of these decisions that may not make sense if your environment is different.

Developers shouldn’t have access to production

Developers shouldn’t have access to production therefore the production database connection information shouldn’t be available in source control. The web.config in source control should only have a connection string to the local sqlexpress database using integrated security. On very small teams it may be acceptable for the developers to have access to production but as you grow, and especially if your data is sensitive in nature, this isn’t a good idea for the long term.

Each developer should be able to build the entire project on their machine without any external dependencies

For this to work the local build will need to create or update any required databases. It also means any test data needed to make the application work should be available as part of the solution in source control.  A local build should be able to run unit tests, integration tests, and UI tests.  It also should be able produce a zip artifact that contains the compiled website along with scripts and/or tools to create or update the associated database.

The continuous integration server should run the same steps as a local build

For reliability it is best if the CI server runs a process that is also checked into source control. In a perfect world, your CI server runs just one single step – calling a script that is part of your codebase.

Until recently, I been using CruiseControl.Net for years and didn’t have anything in place for database deployment. We used a shared database on a test server and every now and then we would compare test and production to generate some change scripts. As I mentioned in an earlier article, we could deploy to production on a moments notice, unless there was a database change. In that case we needed some manual intervention.

The first step was decoupling from the shared test database. To do this I started with Redgate SQL Compare and later stumbled on a free alternative that I will touch on later in this article. This gave me the DDL scripts necessary to recreate the database structure. For some applications this was enough but others have some required data. For example, an application with a state dropdown list needs the state table populated. If the application has roles, the roles list needs populated. For the most part I just wrote some DML scripts with insert statements but for some of the more complex applications I used some of the SQL Management Studio tools to help. Now that I had the necessary scripts to create the database and some necessary seed data I needed a way to run them. Check out this article where I discuss the AliaSQL tool that helps with this process.

To run the steps for a local build I use Psake which is a powershell DSL for build automation. Psake lets you define a series of “tasks” that can have dependencies on other tasks.

My default task looks like this:

task default -depends Init, Compile, UpdateDatabase, Test, PopulateTestData

The “Init” task cleans up any previous build artifacts. The “Compile” step updates assemblyinfo files with the latest version number, runs msbuild clean then build. The “UpdateDatabase” step runs AliaSQL in Update mode. “Test” runs unit tests, and “PopulateTestData” again runs AliaSQL in TestData mode. There is an additional “Package” task that zips all this up in a neat package.

The CI Server

The final step is getting all this working on the CI server. I use the same Psake script but pass in some parameters that are different than a local build like this as the first step:

build_and_package.bat databaseServer = '.\sqlexpress'; version = '1.1.1' ; build_dir = 'c:\test' ; visualstudioversion = '12.0'

I keep web.config transforms on the CI server (not in source control) that has database connections for the test and production environments using Eric Hexter’s WebConfigTransformRunner tool.

As a final build step, I deploy the compiled code along with the transformed web.config to the test environment.

Deploying to production

At the moment, I have one more script on the CI server that publishes the code with a different transform to the production web servers and uses AliaSQL to run database change scripts on the production database server. I am actively investigating Octopus Deploy for this and the web.config transform steps.

Final words

Using this setup each developer can run the project on their computer with no dependencies. The CI server can run the same psake script so even the CI configuration in source control. Database deployment can happens automatically. If you have a target environment where the DBA doesn’t want a tool making changes in production, the build process also publishes the plain .sql files as part of the zip package that can be run manually.  Using this system I have over 30 applications that I can test locally, verify in the test environment, and deploy to production at a moments notice. Reliable, repeatable, flexible. What more could you want?

Example

I have an example app that uses all these techniques available on Github. It is a simple single table crud app with some contrived business rules so there is something to unit test. It uses the following:

• ASP.Net MVC 5
• Structuremap IOC container
• StackExchange Exceptional error logging handling
• StackExchange MiniProfiler
• Dapper data access
• Dapper SimpleCRUD extensions to Dapper
• SimpleCRUD T4 template for POCO generation
• Automapper
• Bootstrap generic UI
• Microsoft bunding and minification
• AliaSQL database deployment tool
• Assumes you have SQL Server 2008 or 2012 Express installed

Check it out here: https://github.com/ericdc1/AliaSQL-Demo 

Database comparisons with Redgate SQL Compare works great but it is extremely expensive. One of my coworkers, Peter Santiago, came up with a really simple way to generate schema changes using the SQL Server Express sqlpackage.exe tool. It takes your current sqlexpress database and exports it to a “dacpac”, generates a new database off of your create and update scripts, and compares the two.  Now you can make schema changes in the SQL Management Studio and have this tool generate you a change script.  Check it out here: https://github.com/ericdc1/AliaSQL-Demo/blob/master/schema_compare.ps1

Here is a video demo of the whole process: http://www.youtube.com/watch?v=oLC9MZGBFII

If there is something I missed or didn’t make clear enough, leave a comment and I will do my best to help.