Monthly Archives: January 2016

Integrate Database Migrations with Octopus Deploy

Summary


Prior to Octopus, we had a proprietary way of deploying database changes. Moving to Octopus Deploy forced us to find an alternative approach. I had used Fluent Migrations on an earlier project and I was excited about using it again.

Benefits

  • Database source is treated as first class citizen and remains in the project.
  • Full text search can easily be done to find each time a field or sproc was changed.
  • Stored Procedures can be easily compared between migrations.
  • Version History maintained in database (see screen capture below) showing exactly when each migration was applied (and Version, ITMS, PBI, or other info).
  • Multi-user Two people can be working on their own db changes at the same time, each in their own migration. (The normal rules that both of them should not be changing the exact same objects still apply).
  • Ability to apply multiple “iterations” during a sprint (instead of waiting until the end)
  • All Database changes are tracked together.
  • This approach eliminates the need to push a db change using email or some other method. In our current environment, we are forced to do that. For a simple change, it just doesn’t make sense to create a new branch of managed code, jump through the Fortify hoops and everything else that is involved in rolling a simple change, force all users out of the system, etc… With this approach, adding a simple migration should take less than 30 minutes and it is done.
  • Gives visibility to all changes, which can greatly assist troubleshooting down the road.
  • Fortify Tango not required. The current Implementation folder is not part of the Fortify process and this shouldn’t need to be either. There is no User component to this. This is not an application that is being installed for the user.
  • Database changes are not tightly coupled to managed code changes. Some people would list this as a Con, but having them “separate” has some benefits.
  • Best Practices (and possibly worst) are more easily followed for future devs on how to script an extended property or foreign key. The prior scripts are part of the project and can be searched and copied.
  • Developer is not forced to use migrations. The can still wait until right before the deploy, let a tool do the compares, generate the scripts, and then just copy them into the folder.
  • Deploy and rollback can easily be ran multiple times during development, EXACTLY as they will be ran during deployment.
  • Visual Studio 2015 new project – added by default
  • Screens

    Migration history in DB

    Migration01

    PowerShell script

    Migrate02

    Technology / Tools

Markdown site (Wiki) to share project information

Summary

I needed an easy way to share information with other members of our team. Some people were using OneNote, which is a great product, but sharing things can be a little clunky. Our organization was working towards an enterprise solution for this problem, but I needed something right now for what I was working on.
I chose Markdown since I figured it would be easy to convert it to whatever solution the company decided. This ended up being a very useful tool and provided a quick easy way to collaborate with other teammates.

Screens

Home Page

Wiki01

Home Page – Markdown

Example markdown
Wiki02

Checklists

Added ability to easily create lists.
Wiki03

Tools used

Wiki04

Help pages

To make it easy for others to edit pages, I created a simple help page with examples.
Wiki06

Wiki05

Read Database Settings

Since this is custom code, it was easy to add the ability to execute Sql statements when the user clicked a link. In this example, I display the AppSettings by environment on the page.
Wiki07

Go/NoGo Dashboard for System Health

Summary

The idea for this project was to create a “Health” dashboard similar to Amazon or Azure. In our environment, we don’t seem to have a problem getting emails when things fail.

Our problem was that were not getting notified about events that should have happened but did not. For example, if the process to import a file normally runs every morning, but it does not run one morning, we didn’t know about it. Since it didn’t run, it did not send an email about any problem.

Another benefit of a health dashboard is for a quick “sanity check” when a problem is reported. As a first step, we can check the dashboard to see the overall health of the system.

Screens

Home Page

The main page allows the user to quickly filter the checks by the environment (Prod, Test, Dev).
Gng01

Home Page – Dark

I am not a designer (I can barely match a pair of socks). However, I do like options. Bootstrap/BootSwatch made it easy to do that. I allow the user to pick the theme they like, simply by clicking the links in the lower right of the screen.
Gng01b

Dashboard filtered by tag

The system supports tags to make it easy to group related checks. For example, all tasks running on a specific server may be tagged with that server name.
Gng02

Edit Task & Tags

Example of screen to edit task description and tags along with any notes for the specific check.
Gng03

Show Triggers

Each check can have multiple triggers. This allows some pretty flexible scheduling as shown in this example.
Gng04

Edit Trigger

Clicking a trigger in the list brings up the modal edit dialog. I tried to use buttons to make it easy and visual for the user to “see” the schedule.
Gng05

View Actions

Gng06

Edit SQL Action

Example dialog to edit the Sql used for the check.
Gng07

History Tab

The helps determine how often a check is failing. It also includes any notes that were added about a failure.
Gng08

Edit Event Note

Clicking the pencil in the far right column allows the user to enter a note about a specific event. This can be helpful to explain why some event failed.
Gng09

Export to Excel

This serves two purposes.

  1. Backup of existing configuration.
  2. I didn’t complete this piece, but the idea was to be able to add checks to the spreadsheet and import it back into the system.

Gng10

WebAPI Generated Doc

Here is an example of the API Doc we get for free with WebAPI.
Gng11

Trello Punch List

Trello screenshot showing a portion of the project punch list.
Gng12

Technology / Tools

  • C#
  • WebApi
  • AngularJS
  • Bootstrap/BootSwatch
  • Windows Service

Sql Code Snippets

Creating a custom snippet

Create a Snippets folder somewhere (I like c:SqlSnippets)

Sql01

Use the following template as your first snippet

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>Logging - Standard Template</Title>
      <Shortcut></Shortcut>
      <Description>Insert script to read Logging</Description>
      <Author>Company Name Here</Author>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Code Language="SQL">
        <![CDATA[
 SELECT
        DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), TimeStamp) AS RunTime
      , Message
      , UserName
      , . . .
 FROM Logging.dbo.LogTable WITH (NOLOCK)
WHERE TimeStamp > DateAdd(MINUTE, -30, GetUtcDate())
ORDER BY TimeStamp
]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Open Code Snippet Manager (^KB)

Sql02

  • Click Add button
  • Navigate to your new Snippets folder
  • Click the Select Folder button

Expand your Snippets Folder

Sql03

Cleaning up the Insert Snippet Dialog

Insert Snippet in Query window

  • Press ^KX to bring up the Insert snippet window (before our change)

Sql04

This dropdown is too busy and it is hard to find our custom snippets.

  • Go back to the Code Snippets Manager (^KB)

Sql05

  • Open the folder shown in the Location textbox.
  • Make a backup folder to save these scripts and move all files into the backup folder

Folder before backup

Sql06

Folder after backup

Sql07

Remove Missing Folders

Go back to the Code Snippets Manager and click the *Remove* button for all the missing folders. When you are done, it should look like this.
Sql08

Insert new Template

Finally, the payoff. Press ^KX to Insert a tempalte. It should be clean like this:
Sql09

Senior Software Developer – Resurgent Capital Services

Jun 2014 – Present

  • Created a File Validation site using Azure, SignalR, & Redis. Project included unit tests and I was able to obtain 97% code coverage.
  • Developed a Go/NoGo Dashboard to provide a “quick glance” of system health. The site includes an AngularJS website, WebAPI service layer, and a Windows Service to perform the system checks.
  • Created a simple Wiki site to display and edit Markdown to help document projects and share information.
  • Integrated Database Migrations with Octopus Deploy
  • Installed Discourse in Azure in attempt to foster group discussions. (Jury is still out on people using the tool)
  • In addition to the fun projects listed above, I have also helped maintain existing legacy applications. (Some of the apps have 12+ year old stored procedures that still run hundreds of times per day)