Ever run into that problem where everybody on your team is using a different database instance name and every time you check out you have to update the config file with your instance name?

Boy have I seen some complicated solutions around this involving reading from environments and having private, unversioned configuration files. One of the developers on my team recommended using SQL Server Aliases to solve the problem. I fumbled around with these for a bit because I couldn’t get them to work. Eventually, with some help, I got there.

Let’s say that you have an instance on your machine called sqlexpress but that your project needs an instance called sqlexpress2012. The first thing is to open up the SQL Server Configuration Manager. The easiest way to do this is to run

SQLServerManager13.msc

where the 13 is the version number of SQL server so SQL 2014 is 12 and SQL 2016 is 13. That will give you

The first thing to check is that your existing instance is talking over TCP/IP.

Then click on the properties for TCP/IPO and in the IP Addresses tab check for the TCP Dynamic Ports setting

Make note of that number because now we’re going to jump to the alias section.

Right click in there and add a new alias

In here we are going to set the alias name to the new name we want to use. The port number is what we found above, the protocol is TCP/IP and the server is the existing server name. You then have to repeat this for the 64 bit client configuration and then restart your SQL server. You should now be able to use the new name, localhost\sqlexpress2012 to access the server.

When you get to be as old as me then you start to see certain problems reappearing over and over again. I think this might be called “experience” but it could also be called “not getting new experiences”. It might be that instead of 10 years experience I have the same year of experience 10 times. Of course this is only true if you don’t experiment and find new ways of doing things. Even if you’re doing the same job year in and year out it is how you approach the work that determines how you will grow as a developer.

One of those problems I have encountered over the years is the problem of updating a collection of records related to one record. I’m sure you’ve encountered the same thing where you present the user with a table and let them edit, delete and add records.

Now how do you get that data back to the server? You could send each row back individually using some Ajax magic. This is kind of a pain, though, you have to keep track of a lot of requests and you’re making a bunch of requests. You also need to track, behind the scenes, which rows were added and which were removed so you can send specific commands for that. It is preferable to send the whole collection at once in a single request. Now you’ve shifted the burden to the server. In the past I’ve handled this by pulling the existing collection from the database and doing painful comparisons to figure out what has changed.

There is a very useful SQL command called UPSERT which you’ll find in databases such as Postgres(assuming you’re on the cutting edge and you’re using 9.5). Upsert is basically a command which looks at the existing table data when you modify a record. If the record doesn’t exist it will be created and if it is already there the contents will be updated. This solves 2/3rds of our cases with only delete missing. Unfortunately, SQL Server doesn’t support the UPSERT command - however it does support MERGE.

I’ve always avoided MERGE because I thought it to be very complicated but in the interests of continually growing I figured it was about time that I bit the bullet and just learned how it works. I use Dapper a fair bit for talking to the database, it is just enough ORM to handle the dumb stuff while still letting me write my own SQL. It is virtually guaranteed that I write worse SQL than a full ORM but that’s a cognitive dissonance I’m prepared to let ride. By writing my own SQL I have direct access to tools like merge which might, otherwise, be missed by a beefy ORM.

The first thing to know about MERGE is that it needs to run against two tables to compare their contents. Let’s extend the example we have above of what appears to be a magic wand shop… that’s weird and totally not related to having just watched the trailer for Fantastic Beasts and Where to Find Them. Anyway our order item table looks like

create table orderItems(id uniqueidentifier,
                        orderId uniqueidentifier,
                        colorId uniqueidentifier,
                        quantity int)

So the first task is to create a temporary table to hold our records. By prefacing a table name with a # in SQL server we get a temporary table which is unique to our session. So other running transactions won’t see the table - exactly what we want.

using(var connection = GetConnection())
{
   connection.Execute(@"create table #orderItems(id uniqueidentifier,
                                                 orderId uniqueidentifier,
                                                 colorId uniqueidentifier,
                                                 quantity int)");
}

Now we’ll take the items collection we have received from the web client (in my case it was via an MVC controller but I’ll leave the specifics up to you) and insert each record into the new table. Remember to do this using the same session as you used to create the table.

foreach(var item in orderItems)
{
    connection.Execute("insert into #orderItems(id, orderId, colorId, quantity) values(@id, @orderId, @colorId, @quantity)", item);
}

Now the fun part: writing the merge.

merge orderItems as target
      using #orderItems as source
      on target.Id = source.Id 
      when matched then
           update set target.colorId = source.colorId, 
                  target.quantity = soruce.quantity
      when not matched by target then 
      insert (id, 
                orderId, 
              colorId, 
              quantity) 
     values (source.id, 
              source.orderId, 
             source.colorId, 
             source.quantity)
     when not matched by source 
      and orderId = @orderId then delete;

What’s this doing? Let’s break it down. First we set a target table this is where the records will be inserted, deleted and updated. Next we set the source the place from which the records will come. In our case the temporary table. Both source and destination are aliases so really they can be whatever you want like input and output or Expecto and Patronum.

merge orderItems as target
      using #orderItems as source

This line instructs on how to match. Both our tables have primary ids in a single column so we’ll use that.

on target.Id = source.Id 

If a record is matched the we’ll update the two important target fields with the values from the source.

when matched then
           update set target.colorId = source.colorId, 
                  target.quantity = soruce.quantity

Next we give instructions as to what should happen if a record is missing in the target. Here we insert a record based on the temporary table.

when not matched by target then 
      insert (id, 
                orderId, 
              colorId, 
              quantity) 
     values (source.id, 
              source.orderId, 
             source.colorId, 
             source.quantity)

Finally we give the instruction for what to do if the record is in the target but not in the source - we delete it.

when not matched by source 
     and orderId = @orderId then delete;

In another world we might do a soft delete and simply update a field.

That’s pretty much all there is to it. MERGE has a ton of options to do more powerful operations. There is a bunch of super poorly written documentation on this on MSDN if you’re looking to learn a lot more.

Ever wanted to copy blobs from one Azure blob container to another? Me neither, until now. I had a bunch of files I wanted to use as part of a demo in a storage container and they needed to be moved over to a new container in a new resource group. It was 10 at night and I just wanted it solved so I briefly looked for a tool to do the copying for me. I failed to find anything. Ugh, time to write some 10pm style code, that is to say terrible code. Now you too can benefit from this. I put in some comments for fun.

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace migrateblobs
{
    class Program
    {
        static void Main(string[] args)
        {
            //this is the source account
            var sourceAccount = CloudStorageAccount.Parse("source connection string here");
            var sourceClient = sourceAccount.CreateCloudBlobClient();
            var sourceContainer = sourceClient.GetContainerReference("source container here");

            //destination account
            var destinationAccount = CloudStorageAccount.Parse("destination connection string here");
            var destinationClient = destinationAccount.CreateCloudBlobClient();
            var destinationContainer = destinationClient.GetContainerReference("destination container here");

            //create the container here
            destinationContainer.CreateIfNotExists();

            //this token is used so the destination client can pull from the source
            string blobToken = sourceContainer.GetSharedAccessSignature(
                       new SharedAccessBlobPolicy
                       {
                           SharedAccessExpiryTime =DateTime.UtcNow.AddYears(2),
                           Permissions = SharedAccessBlobPermissions.Read | SharedAccessBlobPermissions.Write
                       });


            var srcBlobList = sourceContainer.ListBlobs(useFlatBlobListing: true, blobListingDetails: BlobListingDetails.None);
            foreach (var src in srcBlobList)
            {
                var srcBlob = src as CloudBlob;

                // Create appropriate destination blob type to match the source blob
                CloudBlob destBlob;
                if (srcBlob.Properties.BlobType == BlobType.BlockBlob)
                {
                    destBlob = destinationContainer.GetBlockBlobReference(srcBlob.Name);
                }
                else
                {
                    destBlob = destinationContainer.GetPageBlobReference(srcBlob.Name);
                }

                // copy using src blob as SAS
                destBlob.StartCopyFromBlob(new Uri(srcBlob.Uri.AbsoluteUri + blobToken));
            }
        }
    }
}

I stole some of this code from an old post here but the API has changed a bit since then so this article is a better reference. The copy operations take place asynchronously.

We’re copying between containers without copying down the the local machine so you don’t incur any egress costs unless you’re moving between data centers.

Have fun.

Discussion on the Western Devs slack channel today turned to how to manage the lifecycle of databases. This is something we’ve discussed in the past and today’s rehash was brought to us by D’Arcy asking an innocent question about Entity Framework. As seems to happen on slack instead of helping five people immediately told him how wrong he was to be using EF in that way in the first place (we helped him later). Database migrations are a hot topic and there are a lot of options in the space so I thought I’d put together a little flow chart to help people decide which option is the best for their scenario.

Let’s start by looking at the options.

1. Explicit migrations in code
2. Explicit migrations in SQL
3. Desired state migrations

What’s all this mean? First let’s look at explicit migrations vs desired state. In explicit migration we write out what changes we want to make to the database. So if you want to add a new column to a table then you would actually write out some form of please add column address to the users table it is a varchar of size 50. These migrations stack up on each other. This means that after a few weeks of development you might have a dozen or more files with update instructions. It is very important that once you’ve checked in one of these migrations that you don’t ever change it. Migrations are immutable. If you change your mind or make a mistake then you correct it by adding another migration.

migration 1: add column addresss
//oh drat, spelled that wrong
migration 2: rename column addresss to address

The reason for this is that you never know when your database is going to be deployed to an environment. Typically the tools in this space keep track of the migrations which have been applied to a database. If you change a migration which has been applied then they have no way to correct the database and the migration will fail. Best not to get yourself into that situation.

With migrations you can get yourself into a mess of migrations. A project that lasts a couple of years may acquire hundreds or even thousands of migrations. For the most part this doesn’t matter because the files should never change however it can slow down deployments a bit. If it does bug you and you are certain that all your database instances in the wild are current up to a certain migration then you can build a checkpoint. You would take an image of the database or generate the schema and check that in. Now you can delete all the migrations up to that point and start fresh.

These migrations can be created in code using something like entity framework migrations or using a tool like Fluent Migrator - that’s option #1. Option #2 is to keep all the migrations in SQL and use something like Roundhouse. Option #1 is easier to integrate with your existing ORM and you might even be able to generate some of the migrations though tools like EF’s add migration which compares the previous state of your model with the new state and builds migrations (this is starting to blur the lines between options #1 and #3). However it is further away from pure SQL which a lot of people are more comfortable with. I have also found in the past that EF is easily confused by multiple people on a project building migrations at the same time. Explicit SQL migrations are a bit more work but can be cleaner.

The final option is to use a desired state migration tool. These tools look at the current state of the database and compare them with your desired state then perform whatever operations are necessary to take current to desired. You might have seen desired state configuration in other places like puppet or Powershell DSC and this is pretty much the same idea. These tools are nice because you don’t have to care about the current state of the database. If it is possible the tool will migrate the database. Instead of specifying what you want to change you just update the model and the desired state tooling will calculate the change. These tools tend to fall down when you have to make changes to the data in the database - they are very focused on structural changes.

We’ve now looked at all the options so which one should you pick? There is never going to be a 100% right answer here (unless your boss happens to be in love with one of the solutions and will fire you if you pick a different one) but there are some indicators that might point you in the right direction.

1. Is your product one which has a single database instance? An example of this might be most internal corporate apps. There is only one instance and only likely to be one instance. If so then you could use any migration tool. If not then the fact that you can’t properly manage multiple data migrations with SQL Server Database Projects preclude it. Code based migrations would work but tend to be a bit more difficult to set up than using pure SQL migrations.

2. Do you need to create a bunch of seed data or default values? Again you might want to stay away from desired state because it is harder to get the data in. Either of the explicit migration approaches would be better.

3. Is this an existing database which isn’t under source control? SQL server database projects are great for this scenario. They will create a full schema from the database and properly organize it into folders. Then you can easily jump into maintaining and updating the database without a whole lot of work.

4. Are there multiple slightly different versions of the database in the wild? Desired state is perfect for this. You don’t need to figure out a bunch of manual migrations to set a baseline.

5. Are you already using EF and have a small team unlikely to step on each other’s toes? Then straight up EF migrations could be your best bet. You don’t have to introduce another technology or tool. (I should mention here that you can use EF’s automatic migrations to act in the same way as a desired state configuration tool so consider that. Generally the EF experts recommend against doing that in favour of explict migrations)

6. Do you have a team that is very strong in SQL but not modern ORMs? Ah then SQL based migrations are likely you friend. A well-versed team may have already created a version of this. Switch to roundhouse, it will save you time in the long run.

I hope that these will give you a little guidance as to which tool will work best for your project. I’m sure there are all sorts of other questions one might ask to give a hint as to which technique should be used. Please do comment on this post and I’ll update it.

You would think that there were enough introductions to Docker out there already to convince me that the topic is well covered and unnecessary. Unfortunately the sickening mix of hubris and stubbornness that endears me so to rodents also makes me believe I can contribute.

In my case I want to play a bit with the ELK stack: that’s Elasticsearch, Logstash and Kibana. I could install these all directly on the macbook that is my primary machine but I actually already have a copy of Elasticsearch installed and I don’t want to polute my existing environment. Thus the very 2015 solution that is docker. If you’ve missed hearing the noise about docker over the last year then you’re in for a treat.

The story of docker is the story of isolating your software so that one piece of software doesn’t break another. This isn’t a new concept and one could argue that really that’s what kernel controlled processes do. Each process has its own memory space and, as far as the process is concerned, the memory space is the same as the computer’s memory space. However the kernel is lying to the process and is really remapping the memory addresses the program is using into the real memory space. If you consider the speed of processors today and the ubiquity of systems capable of running more than one process at a time then, as a civilization, we are lying at a rate several orders of magnitude greater than any other point in human history.

Any way, docker extends the process isolation model such that the isolation is stronger. Docker is a series of tools built on top of the linux kernel. The entire file system is now abstracted away, networking is virtualized, other processes are hidden and, in theory, it is impossible to break out of a container and damage other processes on the same machine. In practice everybody is very open about how it might be possible to break out of machine or, at the very least, gather information from the system running the container. Containers are a weaker form of isolation than virtual machines.

On the flip side processes are more performant than containers which are, in turn more performant than virtual machines. The reason is simple: with more isolation more things need to run in each context bogging the machine down. Choosing an isolation level is an exercise in deciding how much trust you have in the processes you run to no interference with other things. In the scenario where you’ve written all the services then you can have a very high level of trust in them and run them with minimal isolation in a process. If it is SAP then you probably want the highest level of isolation possible: put the computer in a box and fire it to the moon.

Another nice feature of docker is that the containers can be shipped as a whole. They tend not to be prohibitively large as you might see with a virtual machine. This vastly improves the ease of deploy. In a world of micro-services it is easy to bundle up your services and ship them off as images. You can even have the result of your build process be a docker image.

The degree to which docker will change the world of software development and deployment remains an open one. While I feel like docker is a fairly disruptive technology the impact is still a couple of years out. I’d like to think that it is going to put a bunch of system administrators out of a job but in reality it is just going to change their job. Everybody needs a little shakeup now and then to keep them on their toes.

Anyway back to docker on OSX:

If you read carefully to this point you might have noticed that I said that docker runs on top of the Linux kernel. Of course OSX doesn’t have a linux kernel on which you can run docker. To solve this we actually run docker on top of a small virtual machine. To manage this we used to use a tool called boot2docker but this has, recently, been replace with docker-machine.

I had an older install of docker on my machine but I thought I might like to work a bit with docker compose as I was running a number of services. Docker compose allows for coordinating a number of containers to setup a whole environment. In order to keep the theme of isolating services it is desirable to run each service in its own container. So if you imagine a typical web application we would run teh web server in one container and the database in another one. These containers can be on the same machine.

Thus I grabbed the installation package from the docker website then followed the installation instructions at http://docs.docker.com/mac/step_one/. With docker installed I was able to let docker-machine create a new virtual machine in virtual box.

All looks pretty nifty. I then kicked off the ubiqutious hello-world image

~/Projects/western-devs-website/_posts$ docker run hello-world
Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world

535020c3e8ad: Pull complete 
af340544ed62: Pull complete 
Digest: sha256:a68868bfe696c00866942e8f5ca39e3e31b79c1e50feaee4ce5e28df2f051d5c
Status: Downloaded newer image for hello-world:latest

Hello from Docker.
This message shows that your installation appears to be working correctly.

To generate this message, Docker took the following steps:
 1. The Docker client contacted the Docker daemon.
 2. The Docker daemon pulled the "hello-world" image from the Docker Hub.
 3. The Docker daemon created a new container from that image which runs the
    executable that produces the output you are currently reading.
 4. The Docker daemon streamed that output to the Docker client, which sent it
    to your terminal.

To try something more ambitious, you can run an Ubuntu container with:
 $ docker run -it ubuntu bash

Share images, automate workflows, and more with a free Docker Hub account:
 https://hub.docker.com

For more examples and ideas, visit:
 https://docs.docker.com/userguide/
 

It is shocking how poorly implemented this image is, notice that at no point does it actually just print “Hello World”. Don’t worry, though, not everything in docker land is so poorly implemented.

This hello world demo is kind of boring so let’s see if we can find a more exciting one. I’d like to serve a web page from the container. To do this I’d like to use nginx. There is already an nginx container so I can create a new Dockerfile for it. A Dockerfile gives docker some instructions about how to build a container out of a number of images. The Dockerfile here contains

FROM nginx
COPY *.html /usr/share/nginx/html/

The first line set the base image on which we want to base our container. The second line copies the local files with the .html extension to the web server directory on the nginx server container. To use this file we’ll have to build a docker image

/tmp/nginx$ docker build -t nginx_test .
Sending build context to Docker daemon 3.072 kB
Step 0 : FROM nginx
latest: Pulling from library/nginx
843e2bded498: Pull complete 
8c00acfb0175: Pull complete 
426ac73b867e: Pull complete 
d6c6bbd63f57: Pull complete 
4ac684e3f295: Pull complete 
91391bd3c4d3: Pull complete 
b4587525ed53: Pull complete 
0240288f5187: Pull complete 
28c109ec1572: Pull complete 
063d51552dac: Pull complete 
d8a70839d961: Pull complete 
ceab60537ad2: Pull complete 
Digest: sha256:9d0768452fe8f43c23292d24ec0fbd0ce06c98f776a084623d62ee12c4b7d58c
Status: Downloaded newer image for nginx:latest
 ---> ceab60537ad2
Step 1 : COPY *.html /usr/share/nginx/html/
 ---> ce25a968717f
Removing intermediate container c45b9eb73bc7
Successfully built ce25a968717f

The docker build command starts by pulling down the already build nginx container. Then it copies our files over and reports a hash for the container which makes it easily identifiable. To run this container we need to do

/tmp/nginx$ docker run --name simple_html -d -p 3001:80 -p 3002:443 nginx_test

This instructs docker to run the container nginx_test and call it simple_html. The -d tells docker to run the container in the background and finally the -p give the ports to forward, in this case we would like our local machine’s port 3001 to be mapped to the port inside the docker image 80 - the normal web server port. So now we should be able to connect to the web server. If we open up chrome and go to localhost:3001 we get

Well that doesn’t look right! The problem is that docker doesn’t realize that it is being run in a virtual machine so we need to forward the port from the vm to our local machine

Docker container:80 -> vm host:3001 -> OSX:3001

This is easily done from the virtual machine manager

Now we get

This is the content of the html file I put into the container. Perfect! I’m now ready to start playing with more complex containers.

Tip

One thing I have found is that running docker in virtual box at the same time as running parallels causes the whole system to hang. I suspect that running two different virtual machine tools is too much for something and a conflict results. I believe there is an effort underway to bring parallels support to docker-machine for the 0.5 release. Until then you can read http://kb.parallels.com/en/123356 and look at the docker-machine fork at https://github.com/Parallels/docker-machine.

As part of a build pipeline I’m working on the octopus deploy process needs to talk to the database using roundhouse as a different user from the one running the deployment. This is done because the database uses integrated AD authentication, which I quite like. If this build were running on Linux then it would be as simple as editing the sudoers file and calling the command using sudo. Unfortunatly this is Windows and the command line has long been a secondary concern.

I started by asking on the western devs slack channel to see if anybody else had done this and how. Dave Paquette suggested using psexec. This is a tool designed for running commands on a remote computer but if you leave the computer name off it will run on the local machine. This sounded perfect.

However I had a great deal of trouble getting psexec to work in the way I wanted. The command I wanted to run seemed to fail all the time giving an confusing error code -1073741502. The fix provided didn’t seem to work for me so after an afternoon of bashing my head against psexec I went looking for another solution. Running remote processes gave me an idea: what about powershell remoting?

Some investigation suggested that the command I wanted to run would look like

Invoke-command localhost -scriptblock { rh.exe --some-parameters }

This would remote to localhost and run the roundhouse command as the current user. To get it to work using a different user then the command needed credentials passed into it. I had the credentials stored as sensitive variables in Octopus which set them up as variables in powershell. To turn these into credentials you need to do

$pwd = ConvertTo-SecureString $deployPassword -asplaintext -force
$cred =new-object -TypeName System.Management.Automation.PSCredential -argumentlist $deployUser,$pwd

Now these can be passed into invoke command as

Invoke-command localhost -authentication credssp -Credential $cred -scriptblock { rh.exe --some-parameters }

You might notice that authentication flag, this tells powershell the sort of authentication and cor credssp you also need to enable Credential Security Service Provider. To do this we run

Enable-WSManCredSSP -Role server
Enable-WSManCredSSP -Role client -DelegateComputer "*"

From an admin powershell session on the machine. Normall you would run these on different machines but we’re remoting to local host so it is both the client and the server.

Finally I needed to pass some parameters to roundhouse proper.

Invoke-command localhost -authentication credssp -Credential $cred -scriptblock { param($roundHouseExe,$databaseServer,$targetDb,$databaseEnvironment,$fName) & "$roundHouseExe" --s=$databaseServer --d="${targetDb}"  --f=$fName /ni --drop } -argumentlist $roundHouseExe,$databaseServer,$targetDb,$databaseEnvironment,$fName

I screwed up. I force pushed a branch but I forgot to tell git which branch to push so it clobbered another branch.

C:\code\project [feature/feature27]> git push -f
Password for 'http://simon@remote.server.com:7990':
Counting objects: 63, done.
Delta compression using up to 8 threads.
Compressing objects: 100% (61/61), done.
Writing objects: 100% (63/63), 9.25 KiB | 0 bytes/s, done.
Total 63 (delta 50), reused 0 (delta 0)
To http://simon@remote.server.com:7990/scm/ev/everest.git
 + 0baa5b8...e9a1c19 develop -> develop (forced update)  <--oops!
 + dbe6fce...5557ae7 feature/feature27 -> feature/feature27 (forced update)

Drat, since I hadn’t updated develop in a few hours there were a bunch of changes in it that I just killed. Fortunately I know that git is really just a glorified linked list and that nothing is ever deleted. I just needed to update where the head pointer was pointing. I grabbed the SHA of the latest develop commit from the build server knowing that it was late at night and nobody else was likely to have snuck a commit into develop that the server missed.

Then I just force updated my local develop and pushed it back up

git branch -f develop bbff5b810a19383fb11950a5d1e36676dd3ca85d  <-- sha from build server
git push

All was good again.

About a year ago Microsoft rolled out Azure point in time restore on their SQL databases. The idea is that you can restore your database to any point in time from the last little while (how long ago you can restore from is a function of the database scale). This means that if something weird happened to your data 8 hours ago you can restore back to that point. It even support restoring databases that have recently been deleted.

My reading of the marketing material around this feature is that it is meant to replace full database backups in a number of scenarios. In fact if you go to do a database export you’re warned about the performance implications and that point in time restore is much preferred. The problem is that it is slow.

Cripplingly. Shockingly. Amazingly. Slow.

The database I’m working with is about 140MiB as a backup file and just shy of 700MiB when deployed on a database server. Downloading and restoring the database on my laptop, a 3 year old macbook pro running an ancient version of Parallels takes between 6 and 10 minutes. Not a huge deal.

On azure I have some great statistics because restoring the database is part of our QA process. Since I switched from restoring nightly backups to using point in time restores I’ve done 45 builds. Of these 6 of them have failed to complete the restore before I gave up which usually takes a day. The rest are distributed like this in minutes

As you can see 23 of restores, or 59% took more than 50 minutes. There are a few there that are creeping up on 5 hours. That is insane. This is a very small database when you consider that these S1 databases scale to 250gig. Even if we take our fastest restore at 7 minutes and plot it out then this is a 29 hour restore process. What sort of a business can survive a 29 hour outage? If we take the longest then it is 47 days. By that time the company’s assets have been sold at auction and the shareholders have collected 10 cents on the dollar.

When I first set this process up it was on a web scale database and used a backup file. The restore consistently took 15 minutes. Then standard databases were released and the restore time increased to a consistent 40 minutes. Now I’m unable to tell the QA team to within 4 hours when the build will be up.

Fortunately I have a contact on the Azure SQL team who I pinged about the issue. Apparently this is a known issue and a fix is being rolled out in the next few weeks. I really hope that is the case because in the current configuration point in time restores are so slow and inconsistent that they’re in effect useless for disaster recovery scenarios as even for testing scenarios.

The cloud has been such an omnipresent force in my development life that I’d kind of forgotten that IIS even existed. There are, however, some companies that either aren’t ready for the cloud or have legitimate legal limitations that make using the cloud difficult.

This doesn’t mean that we should abandon some of the niceties of deploying to the cloud such as being able to promote easily between environments. As part of being able to deploy automatically to new environments I wanted to be able to move to a machine that had nothing but IIS installed and run a script to do the deployment.

I was originally thinking about looking into PowerShell Desired State Configuration but noted brain-box Dylan Smith told me not to bother. He feeling was that it was a great idea whose time had come but the technology wasn’t there yet. Instead he suggested just using PowerShell proper.

Well okay. I had no idea how to do that.

So I started digging. I found that PowerShell is really pretty good at setting up IIS. It isn’t super well documented, however. The PowerShell documentation is crummy in comparison with stuff in the .net framework. I did hear on an episode of Dot Net Rocks that the UI for IIS calls out to PowerShell for everything now. So it must be possible.

The first step is to load in the powershell module for IIS

Import-Module WebAdministration

That gives us access to all sorts of cool IIS stuff. You can get information on the current configuration by cding into the IIS namespace.

C:\WINDOWS\system32> cd IIS:
IIS:\> ls

Name
----
AppPools
Sites
SslBindings

Well that’s pretty darn cool. From here you can poke about and look at the AppPools and sites. I was told that by fellow Western Dev Don Belcham that I should have one AppPool for each application so the first step is to create a new AppPool. I want to be able to deploy over my existing deploys so I have to turff it first.

if(Test-Path IIS:\AppPools\CoolWebSite)
{
    echo "App pool exists - removing"
    Remove-WebAppPool CoolWebSite
    gci IIS:\AppPools
}
$pool = New-Item IIS:\AppPools\CoolWebSite

This particular site needs to run as a particular user instead of the AppPoolUser or LocalSystem or anything like that. These will be passed in as a variable. We need to set the identity type to the confusing value of 3. This maps to using a specific user. The documentation on this is near impossible to find.aspx).

$pool.processModel.identityType = 3
$pool.processModel.userName = $deployUserName
$pool.processModel.password = $deployUserPassword
$pool | set-item

Opa! We have an app pool. Next up a website. We’ll follow the same model of deleting and adding. Really this delete block should be executed before adding the AppPool.

if(Test-Path IIS:\Sites\CoolWebSite)
{
echo "Website exists - removing"
Remove-WebSite CoolWebSite
gci IIS:\Sites
}

echo "Creating new website"
New-Website -name "CoolWebSite" -PhysicalPath $deploy_dir -ApplicationPool "CoolWebSite" -HostHeader $deployUrl    

The final step for this site is to change the authentication to turn off anonymous and turn on windows authentication. This requires using a setter to set individual properties.

Set-WebConfigurationProperty -filter /system.webServer/security/authentication/windowsAuthentication -name enabled -value true -PSPath IIS:\Sites\CoolWebSite

Set-WebConfigurationProperty -filter /system.webServer/security/authentication/anonymousAuthentication -name enabled -value false -PSPath IIS:\Sites\CoolWebSite
}

I’m not completely sure but I would bet that most other properties can also be set via these properties.

Well that’s all pretty cool. I think will still investigate PowerShell DSC because I really like the idea of specifying the state I want IIS to be in and have something else figure out how to get there. This is especially true for finicky things like setting authentication.

I’ve had this blog post percolating for a while. When I started it I was working for a large company that has some internal projects I was involved with deploying. I came to the project with a background in evolving projects rapidly. It has been my experience that people are not upset that software doesn’t work so much as they are upset that when they discover a bug that it isn’t fixed promptly.

Velocity is the antidote to toxic bugs

Unfortunately the company had not kept up with the evolution of thinking in software deployment. Any change that needed to go in had to pass through the dreaded change management board. This slowed down deployments like crazy. Let’s say that somebody discovered a bug on a Tuesday morning. I might have the fix figured out by noon. Well that’s a problem because noon is the cut off for the change management meeting which is held at 5pm local time. So we’ve missed the change management for this week, but we’re on the agenda for next week.

Day 7.

The change management meeting comes around again and a concern is raised that the change might have a knock on effect on another system. Unfortunately the team responsible for that system isn’t on this call so this change is shelved until that other team can be contacted. We’ll put this change back on the agenda for next week.

Day 14.

Change management meeting number 2. The people responsible for the other system are present and confirm that their system doesn’t depend on the altered functionality. We can go ahead with the change! Changes have to go in on Fridays after noon, giving the weekend to solve any problems that arise. This particular change can only be done by Liz and Liz has to be at the dentist on Friday. So we’ll miss that window and have to deploy during the next window.

Day 24.

Deployment day has arrived! Liz runs the deployment and our changes are live. The minor problem has been solved in only 24 days. Of course during that time the user has been hounding the team on a daily basis, getting angrier and angrier. Everybody is pissed off and the business has suffered.

##Change management is a difficult problem.

There is a great schism between development and operations. The cause of this is that the teams have seemingly contradictory goals. Development is about changing existing applications to address a bug or a changing business need. For the development team to be successful they must show that they are improving the product. Everything about development is geared towards this. Think of the metrics we might use around development: KLoCs, issues resolved, time to resolve an issue, and so forth. All of these are about improving the rate of change. Developers thrive on rapid change.

Operations, on the other hand, their goal is to keep everything running properly. Mail server need to keep sending mail, web server need to keep serving web pages and domain controllers need to keep doing whatever it is that they do, control domains one would assume. Every time there is a change to this system then there is a good chance that something will break. This is why, if you wander into a server room, you’ll likely see a few machines that look like they were hand built by Grace Hopper herself. Most operations people see any change as a potential disturbance to the carefully crafted system they have built up. This is one of the reasons that change management boards and change management meetings have been created. They are perceived as gatekeepers around the system.

Personally I’ve never seen a change management board or meeting that really added any value to the process. Usually it slowed down deploying changes without really improving the testing around whether the changes would have a deleterious effect.

The truth of the matter is that figuring out what a change will do is very difficult. Complex systems are near impossible to model and predict. There is a whole bunch of research on the concept but it is usally easier to just link to

Let’s dig a bit deeper into the two sides of this issue.

##Why do we even want rapid change?

There are a number of really good reasons we’d like to be able to change our applications quickly

1. Every minute spent with undesirable behaviour is costing the business money
2. If security holes are uncovered then our chances of being attacked increase the longer it takes us to get a fix deployed
3. Making smaller changes mean that when something does go wrong the list of potential culprits is quite short

On the other hand we have pushing back

1. We don’t know the knock on effect of this change
2. The problem is costing the business money but is it costing more money that the business being shut down totally due to a big bug?

Secretly we also have pushing back the fact that the ops team are really busy keeping things going. If a deployment takes a bunch of their time then they will be very likely to try to avoid doing it. I sure can’t blame them, often “I’m too busy” is not an acceptable excuse in corporate culture so it is replaced with bogus technical restrictions or even readings of the corporate policies that preclude rapid deployments.

If we look at the push back there is a clear theme: deployments are not well automated and we don’t have good trust that things won’t break during a deployment.

##How can we remove the fear?

The fear that ops people have of moving quickly is well founded. It is these brave souls who are up at oh-my-goodness O’clock fixing issues in production. So the fear of deploying needs to be removed from the process. I’m sure there are all sorts of solutions based in hypnosis but to me the real solution is

If something hurts do it more often

Instead of deploying once a month or once every two weeks let’s deploy every single day, perhaps even more than once a day. After every deploy everybody should sit down and identify one part of the process that was painful. Take that one painful part and fix it for the next deploy. Repeat this process, involving everybody, after each deploy. Eventually you’ll pay off the difficult parts and all of a sudden you can deploy more easily and more often. It doesn’t take many successes before everybody becomes a believer.

##What do the devs need to do?
As a developer I find myself falling into the trap of believing that it is the ops people who need to change. This is only half the story. Developers need to become much more involved in the running of the system. This can take many forms:

• adding better instrumentation and providing understanding of what this instrumentation does
• being available and involved during deploys
• assisting with developing tooling
• understanding the sorts of problems that are faced in operations

Perhaps the most important thing for developers to do is to be patient. Change on this sort of a scale takes time and there is no magic way to just make everything perfect right away.

I firmly believe that sort of change management we talked about at the start of the article is more theatre than practical. Sometimes it is desirable to show management that proper care and attention is being paid when making changes. Having really good test environments and automated tests is a whole lot better than the normal theatre, though.

It is time to remove the drama from deployments and close the Globe Theatre of deployments.