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



PowerShell script



Technology / Tools

• Octopus Deploy
• Fluent Migrations
• PowerShell

Creating a custom snippet

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

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)


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

Expand your Snippets Folder

Cleaning up the Insert Snippet Dialog
Insert Snippet in Query window

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


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

Go back to the Code Snippets Manager (^KB)



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

Folder after backup

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.



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