Integrating Database Lifecycle Management into Microsoft’s Application Delivery Process

In order to automate the delivery of an application together with its database, you probably just need the extra database tools that allow you to continue with your current source control system and release management system by integrating the database into it. If you're using the Microsoft stack, then Redgate's tools can help with some of the difficult database parts of the process, as Jason demonstrates.

Redgate DLM Automation, which features in this article, is now part of SQL Change Automation. Find out more

Note: Since the article was written, DLM Automation has replaced the SQL Automation Pack, so a few of the screenshots used in this article may be out of date.

Table of Contents

Required tools:

Microsoft Tools

Redgate Tools

SQL Server 2012 R2 SQL Server Management Studio 2012 SQL Source Control
Visual Studio 2013 DLM Automation (SQL Compare, SQL Data Compare, SQL Release, SQL CI, and SQL TFS Plugin)
Team Foundation Server 2013 SQL Test
Release Management for VS 2013 Update 4 SQL Monitor

Introduction

Background

Application developers wouldn’t think about writing code or working on a team that wasn’t using source control. And, if they’re working on a team and doing agile development, then they’re probably doing continuous integration as well. These good practices are part of the overall Application Lifecycle Management process, and we want to leverage these for your database so that you can deploy you database changes safely, quickly and efficiently.

In this scenario, our team is using TFS for source control and Team Build for Continuous Integration. Let’s get your database into the development process.

If you have any questions/queries/ideas, please email us at dlm@red-gate.com.

Prerequisites

This lab is designed so that you can follow along with each exercise on a VS ALM VM that Microsoft provides. The VS ALM VM is also known as the Brian Keller VM. We’ll refer to it as the BK VM or just VM throughout this document.

The BK VM comes pre-loaded with TFS, Visual Studio, Release Management, and a variety of other goodies to show off Microsoft ALM technology. It also comes with two demo applications: TailSpinToys and FabrikamFiber. We’ll be using FabrikamFiber in this lab.

So to get started you will need to:

  1. Run Hyper-V Manager – I’m using v 6.3.9600
  2. Download the BK VM from Brian Keller’s blog. It’s a 26Gb download, which unzips to 80Gb.
  3. Start up the BK VM in Hyper-V manager.
  4. Download and install Redgate SQL Toolbelt Essentials.
  5. Using Hyper-V Manager, checkpoint the VM so you can get back to this fresh state if needed.

Make sure you’re able to access the VM through remote desktop. Login details:

  • Username: Brian
  • Password: P2ssw0rd

Please use the Brian account for this lab. Using another account may cause permission issues and distract from the main purpose of the lab. The password P2ssw0rd is used across the VM.

It may also be useful to have a look at the FabrikamFiber application we will be using. It is a web application for customer support of an ISP. It is an ASP.NET web application, backed by a SQL Server database. It is source-controlled in TFS, and builds in TFS. To take a look at it:

1. Open Visual Studio 2013.

2. Open the FabrikamFiber.CallCenter solution:
C:\Users\Administrator\Source\Workspaces\FabrikamFiber\Dev\FabrikamFiber.CallCenter\FabrikamFiber.CallCenter.sln
under Recent on the Start Page.

3. Open Team Explorer and Source Control Explorer, to see the files in source control.

4. You can run the solution to see the FabrikamFiber Support dashboard in IE. It looks like this:

2152-1-4f003404-7abe-4323-ac62-ca7e381ab

The document Embracing Continuous Delivery with Release Management for Visual Studio 2013 contains more details about this application.

Recommendations

2 monitors: 1 for these instructions and 1 to run the BK VM in full screen mode so you can follow along.

Disclaimer

Produced as of 28th February 2015. Microsoft and Redgate release frequently, so things may be a little out of date or there may be a better way to do things. If you find any problems, please get in touch with us at dlm@red-gate.com.

The BK VM expires in 10 days if you don’t activate it, and 180 days if you do. (See “Working with…” document for details about activation.) Redgate tools expire after 28 days (14 days for some products). If you need more time, please contact dlm@red-gate.com to extend your trial.

All the work in the following lab will be done in the BK VM because it has all the software we need already installed on it. For our purposes, our QA, Test, Staging, and Production environments are all on the same machine. This is a little easier because we don’t have to worry about network communications, firewalls, extra VMs, etc. The purpose of this lab is to familiarize you with the tools and how you can use them to make your database development easier. Once you decide to go with our tools, then you can build the infrastructure as you require. Every environment is unique, and the best practices will depend on your situation.

FAQs

Help! I’m lost!

No problem. Just email us at dlm@red-gate.com, and we’ll help you out.

But I don’t want to use the Brian Keller VM!

That’s fine. Most of this lab can be performed on your own infrastructure. Just make sure you have at least:

  • Microsoft SQL Server 2012 with Management Studio.
  • Microsoft Team Foundation source control (aka TFS).
  • Microsoft Team Foundation Build server (aka TF Build).
  • Microsoft Visual Studio 2013 Update 3.
  • Microsoft Release Management (aka MS RM) for Visual Studio 2013 Update 3.

Earlier and later versions of these will probably work too, but I can’t guarantee it.

You will also need:

  • A Microsoft SQL Server database you want to develop and deploy.
  • (Optional) An application that uses this database.

I already have MS RM set-up. What’s the minimal work I need to do to add a DB to my system?

85% of the content is still relevant. You will still need to:

  1. Add your DB to source-control (exercise 1).
  2. Add your DB to continuous-integration (exercise 2).
  3. (Optionally) Unit-test your database (exercise 3).
  4. Add SQL Release to MS RM (Microsoft Release Management) (exercises 5, 6, 7).
  5. Add the necessary components and actions to your release path.

You will also need to ensure the correct DB is referenced from each of your environments. This will probably involve rewriting a web.config using variable replacement (see exercise 8).

System Overview

We will create an ALM/DLM (application lifecycle management/database lifecycle management) system. In this lab, we are most concerned with the database aspects of the release. Briefly, the lifecycle is:

  1. Code will be checked-into TFS Source Control.
  2. This will trigger a build in TFS Team Build.
  3. TFS Team Build produces a build, consisting of both a web-app and a database package.
  4. When confirmed, this build enters the MS RM (Microsoft Release Management) pipeline:
    1. The build is released to a QA environment.
    2. Testers verify this build, and sign-off on build quality.
    3. The build is then released to a Staging Environment.
    4. A DBA checks the Staging environment, and reviews the release artifacts. He then signs-off on the quality, and permits a production release.
    5. The build is released to Production. The DBA checks the production environment is OK.

This diagram demonstrates the system:

2152-DLM%20with%20Microsoft%20Stack_Diag

Exercise 1 – Source Controlling your Database

Linking a Database to source-control

  1. Open SSMS 2012.
  2. Click No thanks to the RG Quality Improvement Program.
  3. Connect to localhost using windows authentication.
  4. Right click on the FabrikamFiber-Express database in the Object Explorer, and select Link to source control…

Best Practice – Only link development databases to source control. Do not source control your production environment. Changes should be made in Dev, checked into source control, and then deployed to other environments per your process.

Select Team Foundation Server (TFS) from the list of Source control systems on the left of the dialog.The Server URL should default to http://vsalm:8080/tfs/defaultcollection. Change defaultcollection at the end to fabrikamfibercollection, so the URL is http://vsalm:8080/tfs/fabrikamfibercollection. Click Browse for the Database folder.Create a new folder called Database under FabrikamFiber/Dev and provide a comment (e.g. “New folder to hold database scripts”). It should look like the below. Click OK to the comment, OK to the confirmation dialog, and Select to confirm the Select Scripts Folder Repository dialog. Migration scripts folder should be left blank. Development model should be left as ‘Dedicated database’. Now click Link to link the database to source-control. 2152-1-016c97e3-be27-4cb9-9e58-b968cdfb2You will now see a success dialog. Click OK to continue. 2152-1-b6ebcc3f-f24f-4a85-96b7-d021f6cebOn the Commit changes tab, SQL Source Control should now list all the schema objects that make up the FabrikamFiber database. Note the blue circles – these indicate that the objects have changes yet to be committed to TFS. Enter a commit comment, for instance ‘Initial commit of FabrikamFiber schema’ and click Commit.

2152-1-46f1cb90-6ec4-4772-8035-e2a133b15

It will now tell you that the changes were committed successfully. Click OK to continue.

2152-1-098c3c95-4cce-487f-b9a5-833042f5d

Note that the blue circles in the Object Explorer have disappeared.If you look at the Source Control Explorer in Visual Studio, you will now see the schema in TFS source-control:

2152-1-d08bf8a0-bc0b-4b60-94f4-18ecbf378

Exercise 2 – Getting your Database into the Continuous Integration Process

To set up Continuous Integration (CI), your database must be in source control. This was done in Exercise 1. If you have not done Exercise 1, please do it now.

Application Developers have been using CI for years. We want to add your database to this process. CI is important: you get fast-feedback on commits, a repository of builds corresponding to commits, fast availability of changes for testers, and many other benefits.

To set up database CI, we will be using TFS Team Build and SQL CI, a Redgate tool which is part of DLM Automation.

Part 1: Installing DLM Automation

  1. Download and install DLM Automation (formerly the SQL Automation Pack). Remember – This is not licensed so you only have a 28 day free trial. If you need more time, contact dlm@red-gate.com for an extension.
  2. Install SQL CI
  3. Install the Team Build Plugin
  4. Close Visual Studio.
  5. On the Windows Start Screen, open Intro to SQL Automation Pack 1.
  6. Click ‘Open Folder’ beneath TFS Build plugin (see screenshot below).

    2152-1-7d9ffe01-2c5a-4223-afd4-e783757ed

  7. Double-click on TFSBuildPlugin.vsix to install it.
  8. Reopen Visual Studio 2013 and the FabrikamFiber solution.

Part 2 – Setting up Database CI

  1. In the Visual Studio top menu, select SQL CI > Set upSQL CI…
  2. Navigate to FabrikamFiber\Dev\Database.
  3. In SQL CI project name, set it to FabrikamFiberDatabase.

    2152-1-b9e87640-3d22-49a8-ae01-66292bde3

  4. Click OK.

You will now see the project settings of your database project. This includes things such as the Package name, and what steps are involved in the build. Note a ‘build’ step is already created for you.

This build step will build a Nuget package from your DB scripts. This Nuget package is a single file (called something like FabrikamFiberDatabase.1.0.0.23.nupkg), which contains everything that defines your database.

When building this Nuget package, it checks that the DB creation scripts are correct, checking syntax, constraints, and dependencies by running the scripts against a new temporary database.

  1. In Team Explorer, under Pending Changes, we need to check in FabrikamFiberDatabase.sqlciproj. Add a comment, such as ‘FabrikamFiber database project’, then click Check In.

    2152-1-b2bb88de-9bd7-4c0c-b5de-39b19b0e0

  2. We now need to add a TFS Build Definition that will do a CI build of the application and database.
  3. In Team Explorer, under Builds, click New Build Definition. A new build definition window will now appear.
  4. In General, enter Fabrikam CI as the Build definition name.
  5. In Trigger, choose Continuous Integration.
  6. In Source Settings, change the source control folder from the default $/FabrikamFiber to $/FabrikamFiber/Dev Also, delete the cloaked entry:

2152-1-4b4c9953-641a-4ecb-9fe5-401df4661

  1. In Build Defaults, under Staging location, under Copy build output to the following drop folder, enter \\vsalm\ffdrops . Note: this folder may not be accessible by default – you may need to give Brian.Keller access.
  2. In Process, under 1. Required -> Items to build, click the ellipsis (…). In the Items to Build dialog, click Add…, and choose $/FabrikamFiber/Dev/Database/FabrikamFiberDatabase.sqlciproj. Click Add again, and choose $/FabrikamFiber/Dev/FabrikamFiber.CallCenter/FabrikamFiber.CallCenter.sln Click OK.
  3. Save the build definition by pressing CTRL + S.

2152-1-a362a8a8-7874-4b4e-8741-4aae3ebc3

You should now see the Build Definition in Team Explorer – Builds. It will also be visible on the TFS website at http://vsalm:8080/tfs/FabrikamFiberCollection/FabrikamFiber/_build

Step 3 – Try it!

  1. Create a table in the FabrikamFiber-Express database in SSMS. For instance, run this T-SQL:
  2. Check this in with Redgate Source Control. To do this:

2152-1-aeb91d81-02ae-42c8-a8a7-e16648551

  1. A build will now be queued. It builds quickly – it should take only a few seconds to be completed:

2152-1-e8f7ed50-a6ac-470f-9f1e-0a251db7c

Note: You may see the test CreateNullCustomer fail, which fails the build. This is a test failure built into FabrikamFiber. Either delete the test, repress C# tests running, or implement a fix. Details of the fix can be found on the internet.

  1. If you now double-click on the build, you will go to a Summary page. Click Open Drop Folder to inspect the build artifacts. The build output contains the website build artifacts, and FabrikamFiberDatabase.1.0.0.101.nupkg (or similar). Later on, we will be using this Nuget package to do database deployments.

2152-1-190a5f60-53d2-4407-b459-7d9b3e8ca

Exercise 3 – DB Unit Testing

Here, we’ll add database unit testing to the FabrikamFiber database. Database unit-testing is justified for the same reasons as unit testing of code. It helps prevent regressions, verify new functionality, and ensures compliance with coding standards. It becomes a gatekeeper that ensures that bad T-SQL is never deployed.

It is not necessary to complete this exercise to proceed to the next exercises.

Redgate SQL Test is an SSMS add-in which uses the tSQLt framework. It is installed as part of SQL Toolbelt Essentials, which you installed earlier.

Part 1 – Adding the tests

  1. Open SSMS, and open the FabrikamFiber-Express database in the Object Explorer.
  2. Click the SQL Test icon 2152-1-6041073f-ed9f-4c95-88ec-f8a6f66a4 in the SSMS toolbar.
  3. In the SQL Test window that appears, click Add Database to SQL Test….
  4. Select FabrikamFiber-Express, and click Add Database.

    2152-1-431aba94-7d8f-4030-aaaa-944dfae4b

  5. The next dialog will ask to install the tSQLt Framework. Click OK. Note that it also offers to install SQLCOP tests by default. These are tests that verify certain T-SQL coding standards, e.g. all tables have a primary key.
  6. Click OK on the confirmation dialog.
  7. SQL Test window is now populated with 48 tests. Click on the green arrow, and the tests will run. You will see 7 fail. The FabrikamFiber database does not meet the SQLCop requirements. We will deal with these failures later.

2152-1-be5df7c6-3d3c-4260-835d-b49322128

Now let’s check these in to source control. If you press the Refresh button on SQL Source Control, lots of objects need to be checked in.

  1. Enter a commit comment, such as ‘Added tSQLt tests’ and click Commit.

2152-1-d4bef4a6-88f6-41bc-8a09-8f2f4b05b

The tSQLt tests are now added to source control. Since we have database CI setup, a build will be automatically triggered. If you open TFS Team Build, you should see it build successfully.

Part 2 – Running the tests in CI

We just ran a DB build successfully. It would be great if we could run the tSQLt tests on every DB build, and fail the build if there are test failures. This can be achieved using Redgate’s SQL CI.

First, we need to open the project file that we created in Exercise 2:

  1. In Visual Studio, got to Team Explorer > Source Control Explorer.
  2. Navigate to vsalm > FabrikamFiber > Dev > Database, and open the SQL CI Project file, FabrikamFiberDatabase.sqlciproj.
  3. Under SQL CI Steps, click Add a step and choose Test.
  4. The ‘Add test step’ dialog will appear. Enter ‘Test’ as the step name, and click Add.

    2152-1-81cee9d2-efe1-44f8-9851-b8901ff66

  5. Press CTRL + S to save your build definition.
  6. To check-in the change to the project file, go to Team Explorer > Pending Changes, add a comment like ‘Added testing to project file’, and click Check In.

    2152-12c0bb21-7a82-4197-ba08-c6fbf060296

  7. The project file change will trigger a build. The build will fail because as in SSMS, the FabrikamFiber database doesn’t pass all the SQLCop tests.

    2152-1-eb4fc6e4-56c8-43fd-b9f6-591220e96

  8. In TFS Team Build, it won’t show us exactly which tests failed. This is something we are looking to improve. To see a test breakdown, click Open drop folder, and double-click on the trx file. For me, this is called FabrikamFiberDatabase.1.0.0.100.trx.

    2152-1-57842f61-f7da-482f-a307-c4567c0b6

Most of these 9 failures are due to poor database design and administration, and some are safe to ignore. In reality you should investigate and fix failed tests, but let’s just delete the failing tests:

  1. Go to SSMS, and the SQL Test window.
  2. Delete the 9 tests which failed. To delete a test, click on it and press the DELETE key, or use the right-click context menu. Note that in SSMS you may only see 7 test failures, because the build has more test failures than SQL Test within SSMS. You should be sure to delete the “Max degree of parallelism” and “Auto close” tests.
  3. Now, check in these test deletions into source control. Add a comment, such as ‘Deleted failing tests’.

    2152-1-ffbc6e6e-a18e-45cb-a9fc-d34c9978a

  4. A build will now be triggered, which will succeed with no test failures.

Exercise 4 – Making the FabrikamFiber Website not recreate the database every time

The FabrikamFiber Website uses Entity Framework and will automatically delete and recreate the database on every run. This is undesirable for this lab – we want to be able to work on the database. Let’s make a change to suppress this behavior.

  1. In the FabrikamFiber.Web project, in the file Global.asax.cs, find the line Database.SetInitializer<FabrikamFiberWebContext>(new FabrikamFiberDatabaseInitializer());
  2. Replace this with Database.SetInitializer<FabrikamFiberWebContext>(null);

    2152-1-a15e098c-d5de-44d1-bb36-82c724f76

  3. Press CTRL + Shift + B to invoke a build. It should build successfully.
  4. Press F5 to run a debug build. The website should work as usual.
  5. Go to Team Explorer > Pending Changes and click Check In to check in this change. Add a comment, for instance ‘Suppressed database creation’.
  6. If you go to TFS Team Build, you’ll see it start to build automatically. It should finish successfully in just a few seconds.

2152-1-6a009f20-f957-4209-b61d-4f3e62840

Exercise 5 – Microsoft Release Management: Adding the SQL Release tools

Introduction

In order to perform a DB release, we need to add Redgate SQL Release to MS RM. We do this by writing PowerShell scripts that use the SQL Release cmdlets, add an invocation of this script in MS RM as a Tool, and then use this Tool from Actions or Components.

SQL Release provides PowerShell cmdlets for releasing databases safely. Internally, the engine is using Redgate SQL Compare. For instance, running the below in PowerShell will release a database defined in c:\db\DatabaseUpdate to a Staging server:

In this exercise, we’ll add tools and actions to MS RM so these commands can be used easily from within MS RM.

The Scripts

We will be using 4 scripts:

  • PublishDbFromPackage.ps1: This simply deploys from a Nuget package to a database.
  • RedeploytoStagingFromProduction.ps1: To do a realistic trial deployment against a staging database, we need the staging database to look like the production database. This script makes the staging database have the same schema as the production database.
  • CreateDbUpdateResources.ps1: Given a production database, staging database and a Nuget package, this script confirms that staging matches the schema of production and creates a SQL script that will update the production database to the schema defined in the Nuget package. The script is not invoked – it is just stored to disk. Also, it produces a log of changes and warnings, which should be reviewed prior to proceeding with the deployment.
  • PublishDbFromArtifacts.ps1: This performs a deployment from the update resources created by the CreateDbUpdateResources script. It checks for drift in the target database from when the update resources were created and then runs the SQL update script.

Feel free to edit these scripts to meet your own requirements. Learn more about the cmdlets in the SQL Release Documentation.

PublishDbFromPackage.ps1

PublishDbFromArtifacts.ps1

CreateDbUpdateResources.ps1

RedeployToStagingFromProduction.ps1

Adding the tools to MS RM

  1. Download and install the SQL Release – now part of DLM Automation.
  2. Create a directory called C:\DbDeployment\Scripts\.
  3. Add the four files defined above to this directory.

    2152-1-9012d13d-397d-499f-8a34-17523e68c

  4. Open MS RM, go to Inventory > Tools.
  5. Click New, and fill in these details:
  6. Name: ‘SQL Release – Publish DB from artifacts’.
  7. Description:‘Import a DB deployment from the specified path. Then publish it to target database’.
  8. Command: ‘powershell’.
  9. Arguments: ‘-command ./PublishDbFromArtifacts.ps1 -DatabaseUpdateResourcesPath ‘__DatabaseUpdateResourcesPath__’ -DatabaseServer ‘__DatabaseServer__’ -DatabaseName ‘__DatabaseName__’ -DatabaseUserName ‘__DatabaseUserName__’ -DatabasePassword ‘__DatabasePassword__’ ‘.
  10. In Resources, click Add, and add PublishDbFromArtifacts.ps1.

In Parameters, select Standard next to DatabasePassword and change this to Encrypted. Click Save to save this tool. 2152-1-f1ae02e3-2b27-4652-a508-1d361f021 Repeat this procedure for the other 3 files, according to these tables:

Name SQL Release – Create DB Update Resources
Description Work out what is needed to make the production database look like the database defined in the nuget package. Export this deployment to the specified path.
Command powershell
Arguments -command ./CreateDbUpdateResources.ps1 -PackageFilePath ‘__PackageFilePath__’ -DatabaseServer ‘__DatabaseServer__’ -ProductionDatabaseName ‘__ProductionDatabaseName__’ -StagingDatabaseName ‘__StagingDatabaseName__’ -DatabaseUserName ‘__DatabaseUserName__’ -DatabasePassword ‘__DatabasePassword__’ -DatabaseUpdateResourcesPath ‘__DatabaseUpdateResourcesPath__’
Resources CreateDbUpdateResources.ps1

Name SQL Release – Publish DB from package
Description Publishes a DB from a Nuget package
Command powershell
Arguments -command ./PublishDbFromPackage.ps1 -PackageFilePathPattern ‘__PackageFilePathPattern__’ -DatabaseServer ‘__DatabaseServer__’ -DatabaseName ‘__DatabaseName__’ -DatabaseUserName ‘__DatabaseUserName__’ -DatabasePassword ‘__DatabasePassword__’
Resources PublishDbFromPackage.ps1

Name SQL Release – Redeploy to staging from production
Description Make the staging DB the same as the production DB
Command powershell
Arguments -command ./RedeployToStagingFromProduction.ps1 -DatabaseServer ‘__DatabaseServer__’ -ProductionDatabaseName ‘__ProductionDatabaseName__’ -StagingDatabaseName ‘__StagingDatabaseName__’ -DatabaseUserName ‘__DatabaseUserName__’ -DatabasePassword ‘__DatabasePassword__’
Resources RedeployToStagingFromProduction.ps1

We now need to add actions and components corresponding to these tools. This will be done in the next two exercises.

Exercise 6 – Microsoft Release Management: Creating database actions

In order to invoke tools in a release template, we need to create actions or componentsthat invoke those tools. We will create actions corresponding to the SQL Release – Publish DB from artifacts and SQL Release – Redeploy to staging from production tools.

  1. In MS RM, go to Inventory > Actions.
  2. Click New.
  3. In Name, enter ‘SQL Release – Publish DB from artifacts’.
  4. In Description, enter ‘Import a DB deployment from the specified path. Then publish it to target database’.
  5. In Categories, choose MS-SQL.
  6. In Tool used, choose ‘SQL Release – Publish DB from artifacts’.
  7. See that Arguments and Parameters are auto-populated.
  8. Click Save & Close.

2152-1-03480f73-7af7-44db-9e45-f94001b69

  1. Repeat this process, using these parameters:
  2. Name: SQL Release – Redeploy to staging from production.
  3. Description: Make the staging DB the same as the production DB.
  4. Categories: MS-SQL.
  5. Tool used: SQL Release – Redeploy to staging from production.

Exercise 7 – Microsoft Release Management: Creating components for database release

We now need to create components corresponding to releasing the database. A component is a build definition with an accompanying deployment mechanism. For instance, a component could be a web-app build and XCOPY.

Firstly, we have a QA environment. Here, we can deploy the database directly from the package to the server. We create a component that does this as so:

  1. Go to Configure Apps > Components.
  2. Click New.
  3. For Name, enter ‘Fabrikam DB for QA’.
  4. For Description, enter ‘Release DB to QA from a package’.
  5. Choose Builds with application, and under Path to package enter ‘\’ (i.e. just a backslash).
  6. In the Deployment tab, set the Tool as SQL Release – Publish DB from Package.
  7. Click Save & Close.

2152-1-9a8a786d-da7d-4be4-b961-c31dff227

Secondly we have a Staging environment. This component is deployed using a different mechanism: an update script is created from the package, which is then reviewed by a DBA before being executed. This component is defined as follows:

  1. Go to Configure Apps > Components.
  2. Click New.
  3. For Name, enter ‘Fabrikam DB for release’.
  4. For Description, enter ‘Build deployment artifacts from Nuget package’.
  5. Choose Builds with application, and under Path to package enter ‘\’.
  6. In the Deployment tab, set the Tool as SQL Release – Create DB Update Resources.
  7. Click Save & Close.

Exercise 8 – Creating a Release Path

In MS RM, we need to create a release path. This defines the stages in a release. We want three stages: QA, Staging and Production

  1. In MS RM, go to Configure Paths > Agent-based Release Paths.
  2. Click New.
  3. Enter ‘Fabrikam DB/Web Release’ as the Name
  4. Enter ‘Release of Fabrikam, both DB and Web’ as the Description.
  5. Use Add to add 3 stages.
  6. In Stage, from left-to-right, set them as being QA, Staging and Prod.
  7. Set the stages according to this table:
Stage QA Staging Prod
Environment Int-QA Int-Staging Int-Prod
Approver QA Team Ops Team Ops Team
Owner QA Team Ops Team Ops Team
Validator QA Team Ops Team Ops Team

Note: You may not have an ‘Int-Staging’ environment. If not, go to Configure Paths -> Environments and click New to add one. Click Link Existing to link it to our server VSALM.

  1. Click Automated wherever available.
  2. Click the green add symbol to add an Approver to the QA stage. Set it as QA Team.
  3. Click the green add symbol to add an Approver to the Prod stage. Set it as Ops Team.
  4. Click the envelope icon next to the Approval Step approvers. This will set it to send notification emails at these stages.
  5. Click Save & Close.

It should look like this:

2152-1-e273f766-1b22-4cb9-94b1-c979d259d

Exercise 9 – Microsoft Release Management: Tokenizing the web.config

FabrikamFiber has a web.config where the connectionString is hard-coded against a single database: FabrikamFiber-Express. We need to have different environments referencing different websites: a QA website that uses a QA database, a Staging website that uses a Staging database, etc. To achieve this, we can use the tokenization and variable replacement features of MS RM.

  1. Go to Visual Studio, and open the FabrikamFiber.CallCenter solution and FabrikamFiber.Web project.
  2. Copy Web.config to Web.config.token.
  3. Change the Build action of Web.config.token to content.
  4. In Web.config.token, replace the ‘FabrikamFiber-Express’ string in the connectionString with ‘__FabrikamDbName__’ (note the double underscore) in lines 9 and 10.
  5. Commit this change to TFS.
  6. Check that the build output in the drop folder contains Web.config.token. It should be a file like: C:\ffdrops\Fabrikam CI\Fabrikam CI_20130714.2\_PublishedWebsites\FabrikamFiber.Web\Web.config.token.

2152-1-e506eb07-b978-4bdd-b755-7585d56c2

We now need to edit the component in MS RM to do variable replacement in web.config:

  1. Open the Fabrikam Call Center component in MS RM in Configure Apps > Components.
  2. Go to the Configuration Variables tab.
  3. Choose Variable Replacement Mode as Before Installation.
  4. Click Add and enter ‘FabrikamDbName’ in Name.
  5. Under File Extension Filter, enter ‘*.config.token’. This means the only files ending .config.token will have this variable-replacement applied.
  6. Click Save & Close.

2152-1-89b4e3b3-3c2d-4afa-9b98-c81de19a6

The final step is to copy the web.config.token over web.config during the release. This is part of building the release process, and will be covered later.

Exercise 10 – Microsoft Release Management: Creating the release template

Now we want to create the Release Template that defines the exact procedure of doing a release of our web-app/database. This will largely be irrelevant if you already have a release template you want to add a database to. If this is the case, skim this exercise and skip to Exercise 11.

We will be basing our template on the existing Fabrikam Call Center template. You may wish to browse this template before continuing with the exercise.

To create our new release template:

  1. Go to Configure Apps > Agent-based Release Templates.
  2. Click on Fabrikam Call Center, and then Copy.
  3. In the resulting Properties dialog, choose these options:

2152-1-c42cc11c-291d-43b0-99a5-99d17a975

This will create a copy with nearly all web application deployment information filled in for us. Have a look around the QA, Staging and Prod stages. You will see that the QA and Prod stages have lots of steps, but the Staging stage is empty. We will fill it in momentarily. First, let’s add the web.config configuration to the QA and Prod stages:

  1. Go to the QA stage.
  2. After the Fabrikam Call Center component, add a Delete File(s) or Folder action. To do this, simply drag it from the toolbox on the left, it is under Windows OS.
  3. Set FileFolderName as ‘c:\FabrikamRM\WebSite\UAT\Web.config’.
  4. Immediately after this action, drag in a Move File or Folder action.
  5. Set FileFolderName as ‘c:\FabrikamRM\WebSite\UAT\Web.config.token’, and DestinationName as ‘c:\FabrikamRM\WebSite\UAT\Web.config’.

2152-1-1fc0da0f-3abc-4c26-9b02-b62c0728b

  1. Do exactly the same in the Prod Stage, except replace UAT with Prod in the folder names:

2152-1-7d0553b2-1c95-4941-96b8-4944174fc

Now, let’s fill it in the Staging stage:

  1. Right-click on Prod in the deployment sequence at the top.
  2. Click Copy Deployment Sequence.
  3. Right-click on Staging in the deployment sequence.
  4. Click Paste Deployment Sequence.
  5. In the Paste Deployment Sequencedialog that appears, choose Paste.

The Staging stage will now be populated.

The staging stage will have various things we need to correct. Basically, we need to replace every instance of ‘PROD’ with ‘Staging’, and set the port number to 8001:

  1. In Remove Web Site, set ‘FabrikamPROD’ to ‘FabrikamStaging’.
  2. In Copy File or Folder:
  3. In Fabrikam Call Center, set InstallationPath to ‘C:\FabrikamRM\WebSite\Staging’.
  4. In Delete File(s) or Folder, set FileFolderPath to ‘C:\FabrikamRM\WebSite\Staging\Web.config’.
  5. In Move Folder or Folder:
  6. Set FileFolderPath to ‘C:\FabrikamRM\WebSite\Staging\Web.config.token’.
  7. Set DetsinationName to ‘C:\FabrikamRM\WebSite\Staging\Web.config’.

In Create Web Site:

  1. Set SiteName to ‘FabrikamStaging’.
  2. Set PortNumber to ‘8001’.
  3. Set PhysicalPath to ‘C:\FabrikamRM\WebSite\Staging’.

In Rollback, Copy File or Folder, set:

  1. SourceFileFolder to ‘C:\FabrikamRM\Backup\Staging’.
  2. SourceFileFolder to ‘C:\FabrikamRM\Website\Staging’.

In Rollback, Create Web Site:

  1. Set SiteName to ‘FabrikamStaging’.
  2. Set PortNumber to ‘8001’.
  3. Set PhysicalPath to ‘C:\FabrikamRM\WebSite\Staging’.

When you’re done, click Save & Close.

Exercise 11 – Microsoft Release Management: Adding the DB to the release template

We now want to add the database operations to the release template. This will largely be a matter of dragging items from the toolbox to the template, and entering some fields.

Adding the components to the toolbox

  1. Go to Configure Apps > Agent-based Release Templates > Fabrikam App and DB.
  2. In the Toolbox, right click on Components.
  3. Click Add.
  4. From the Components dialog, select both components and click Link.

All three components will now be listed in the toolbox:

2152-1-f6b716c8-6751-4abc-bf54-a62aed6b7

Adding the component to QA

  1. Go to the QA stage.
  2. Drag Fabrikam DB for QA to the sequence, as the first step.
  3. Set:
  4. Set DatabaseServer as ‘.’ (i.e. the local server).
  5. Set DatabaseName as ‘FabrikamUAT’.
  6. Leave DatabaseUserName and DatabasePassword blank. This means we will use Windows Authentication to connect to the database. If they are filled-in, SQL Authentication will be used.
  7. Set PackageFilePathPattern as ‘FabrikamFiberDatabase*.nupkg’.

2152-1-67dafc9e-012b-4ee6-bbe4-12914e057

Now, as the first step of a QA deployment, SQL Release will deploy from the NuGet package in the build output to the database FabrikamUAT, which is used by the UAT web-application.

Note: UAT stands for User Acceptance Testing, a type of QA. For our purposes, it is synonymous with QA.

Setting up the Staging Sequence

The Staging sequence is the most complicated. Four database-related steps are required:

  1. Make the Staging DB schema identical to the Production DB schema.
  2. Create deployment resources. That is: create the upgrade script needed to perform the deployment to production. Other resources (for instance, a list of warnings) are also produced.
  3. Test this upgrade script by applying it to the Staging DB.
  4. Insist on Manual Intervention by the user. I.e., the user must check the resources and authorize continuation.

Let’s set this up now:

  1. Go to the Staging sequence.
  2. Drag SQL Release – Redeploy to staging from Production as the first item in the sequence. It will be in the Toolbox under MS-SQL. Set:
  3. DatabaseServer as ‘.’.
  4. ProductionDatabaseName as ‘FabrikamProd’.
  5. StagingDatabaseName as ‘FabrikamStaging’.
  6. DatabaseUserName and DatabasePassword can be left blank.

Drag Fabrikam DB for release as the second item in the sequence. It will be in Components in the Toolbox. Set:

  1. PackageFilePath as ‘FabrikamFiberDatbase*.nupkg’.
  2. DatabaseServer as ‘.’.
  3. ProductionDatabaseName as ‘FabrikamProd’.
  4. StagingDatabaseName as ‘FabrikamStaging’.
  5. DatabaseUserName and DatabasePassword can be left blank.
  6. DatabaseUpdateResourcesPath as ‘C:\DbDeployment\Artifacts’.

Drag SQL Release – Publish DB from artifacts as the third item in the sequence. It will be in the Toolbox under MS-SQL. Set:

  1. DatabaseUpdateResourcesPath as ‘C:\DbDeployment\Artifacts’.
  2. DatabaseServer as ‘.’.
  3. DatabaseName as ‘FabrikamStaging’.
  4. DatabaseUserName and DatabasePassword can be left blank.

As the last item in the main sequence (i.e after ‘Create Web Site’ but before ‘Rollback’) add a Manual Intervention. This is under Control Flow in the Toolbox.

  1. Set the Recipient as Ops Team.
  2. Under Instructions, enter this text:
  • ‘Please verify:
  1. 1) Staging website at http://localhost:8001/ is operating successfully.
  2. 2) SQL update script at C:\DbDeployment\Artifacts is sensible.
  3. 3) Warnings and changes at C:\DbDeployment\Reports are acceptable.’.

2152-1-6d3e3875-3727-43c7-b297-262357f16

2152-1-6d5cb22e-81e7-437b-8040-77d8047f7

Staging is now set up as required.

Setting up the Production Sequence

In production, we simply need to deploy to the production database from the artifacts we verified.

  1. Go to Prod in the sequence.
  2. Drag SQL Release – Publish DB from artifacts as the first item in the sequence. It will be in the Toolbox under MS-SQL. Set:
  3. DatabaseUpdateResourcesPath as ‘C:\DbDeployment\Artifacts’.
  4. DatabaseServer as ‘.’.
  5. DatabaseName as ‘FabrikamProd’.
  6. DatabaseUserName and DatabasePassword can be left blank.

Click Save & Close.

2152-1-ead22e97-a042-4bec-bcf6-1b1f138f4

Exercise 12 – Microsoft Release Management: Let’s do a database release

We’re now ready to do a release. Before we start, create empty databases for FabrikamUAT, FabrikamStaging and FabrikamProd on the server.

  1. In Releases > Releases, click New Agent-based.
  2. In the Properties dialog:

2152-1-ba90c4a1-27aa-42b8-9d71-ac9c9ee4a

  1. Click Start to start the release.
  2. A release process like this should appear. To see future steps, click Include Future Steps (bottom left).

2152-1-08e88344-a16f-4b62-b149-494c919fe

  1. Once the QA steps have completed, you’ll have a pending Approval Request. At this point, QA should check that the QA website passes the tests, and the QA database is correct. A QA representative should then:
  2. Go to My Approval Requests, and select the item.
  3. Click Approve.
  4. Enter under Comments “All tests passed on QA environment”.

    2152-1-a34e4971-28e1-439b-a9cd-45c146bd8

  5. Click OK.

Now the deployment to Staging will occur. Once it’s completed, you’ll be prompted to approve a manual intervention step. Go to Approval Requests again, and click on the … under Details:

2152-1-754bd939-8051-4bcf-a3e5-5d94bddbe

  1. Go to C:\DbDeployment\Artifactsas instructed. Here you will find Update.sql. Open it. This will show what steps are necessary to deploy the DB changes.
  2. Go to C:\DbDeployment\Artifacts\Reports as instructed. Here, have a look at Changes.html and Warnings.html. Respectively, these show the changes that will take place when the production deployment occurs, and any warnings you should know about (for instance, that a table is being dropped or a column is being deleted).
  3. Check the staging database FabrikamStaging and the staging website http://localhost:8001/ . They should match what you want in production.
  4. Back in MS RM, click Approve to continue with the deployment.
  5. Enter ‘Staging website looks fine. We’re ready to release’ as Comments, and click OK.

The Production deployment will now occur. Once it is done, you will again have an approval request waiting. Once you have verified the production database and website are fine, approve this as well. Overall, the deployment should look like this:

2152-1-860e0966-fcc3-4586-87fb-fc8cf6a67

Exercise 13 – DLM Dashboard

The DLM Dashboard DLM Dashboard (previously called SQL Lighthouse) provides a visual overview of the state of all your databases across your environments and monitors schema changes. It can be configured to send an alert – both for notifications of unexpected changes (drift) and confirmation that intended changes happened successfully. It also provides a history of SQL changes line by line, who made the changes, and when.

  1. Download and install the DLM Dashboard (beta).

What’s next?

The next thing to do is to implement this on your own infrastructure. Obviously, your environment is different to that described above, and you may want to make some changes/omissions/additions.

Here’s a few things you might want to do:

Invoke a release from Team Foundation Server

You can make MS RM automatically do a release when a build occurs. This requires a couple of steps – search the internet to find out more.

Customize the release process

There’s probably a lot you want to change about the release process. In particular, notifications may be sent to various email addresses.

Deploy to multiple databases

You can do this by adding multiple components/actions to your release process. Or you can adapt the PowerShell to deploy to multiple targets.

Working with data

The above process ignores data. You may require Static Data in your databases. Search Redgate SQL Source Control help for topics on this. Also, you may wish to use Redgate SQL Data Generator to generate test data for your QA (and possibly Staging) stages. Search Redgate SQL CI help for information on how to make this part of your CI process.

Make two builds

In this lab, we have one build for both the application and the database. This is correct for most people, because:

  • You want one united build of your whole application.
  • It’s easier to talk about one build and one build number.
  • Application-level automated tests may exercise database code/schema, so it’s important to run those when the database changes.

However, you may wish to create a separate build definition just for the db. This may be desirable if multiple applications use a single db.

Feedback

If you have any feedback on this guide or would like to talk to us about database development and deployment processes, please email us at dlm@red-gate.com.