Using SQL Compare and TFS for Continuous Delivery

RMD

I am using the SQL Automation Pack to do continuous delivery.

To make sure the source I'm obtaining matches the build that is actually being deployed, I'd like to pass something like /revision1:[BUILDNUMBER or LABEL or something], but it seems like the revisions here can either be HEAD or the Red-Gate specific DB version information.

This makes true continuous delivery in an environment where I'm deploying different builds to different servers nearly impossible. I don't always want the latest version, and I don't know what the redgate version matches a particular build number.

We are about to move to TFS Release Management (formerly InRelease), and will have a DEV->QA->Prod type deployment progression. This means it will be very common to have different versions in each of these environments, and matching schema to a particular build number becomes critical.

Any help would be greatly appreciate.

jonathan.hickford

Happy New Year

Would be great to talk more about what you're looking to setup, hopefully we can help you get this setup how you need.

Couple of questions about what your setup is like:
- Are you deploying code and a database with each build, or just the database?
- Are you using TFS as a continuous integration server?

My email is jonathan.hickford@red-gate.com, if it's easier to have a phone call let me know your number via email and I can give you a ring.

RMD

Jonathan,

We are deploying both code and the database with each build, and yes, we're using TFS 2012. (Although we are moving to 2013 shortly.)

Right now we use TFS Deployer - which basically executes a powershell script when the build quality is set to some predetermined value on the build. When we move to TFS 2013, we'll be using the Release Management project to do the same thing for every build.

As it stands right now, we only deploy to a dedicated test/qa environment using TFS Deployer. Our team is fairly small (~5 people), so doing a bit of coordination to ensure that somebody doesn't check in a breaking DB change in between the time I've kicked off a build and told it to deploy isn't a huge deal.

But we are expanding, and we are moving to a more formal dev/qa/prod automated release system. This means it is fairly likely we will have different versions deployed to each environment, and without the ability to tie a DB release to a code release, we're dead in the water.

Your Migrations v2 stuff seems to solve this problem by no longer relying on extended properties in the DB to store your own "schema version" info... but from what I hear, we're still 6+ months away from v2 migrations being available generally.

jonathan.hickford

Hi,

Thanks for the info. There are a few different ways that our customers are doing things similar to do this, I'll run through a couple below. Very happy to hop on the phone to talk more too.

One way is to use Red Gate Deployment Manager alongside SQL CI. This can be setup so that whenever you make a commit to either the application or database the CI server will create two packages. One that contains a build of the code and one which contains the scripts folder representation of your database. These are then both passed to Deployment Manager as a 'release' (a specific pair of versions of the DB and code). This release can be deployed automatically to your dev/CI environment . The same release can then be deployed as a 'push button' deployment to QA, staging and production environments. It's a good match if you're planning to run a continuous delivery approach. It ensures that the same pair of versions of code and database end up together as they move through each environment. Diagram of Deployment Manager Workflow

Another approach is to use a version control release branch for each environment. When developers commit code to the 'dev' branch your CI system can build to the dev environment using SQL CI. When it's time for a QA deployment then you can merge the required code onto the QA branch and have the CI system deploy the CI environment. You can do that for both the database and the application (either in separate repos or as a single combined repo). It's a bit more fiddly to work with in my opinion.

There's a Stack Overflow thread with a few diagrams that match this 2nd approach.

Hope that helps

Jonathan

RMD

Jonathan,

This all makes sense, and it's basically what I assumed the answer was.

As we've settled on using TFS/Release Management for CI/CD, and as the solution of branching/merging when deploying to each environment leaves a bad taste in my mouth, I think I'd like to try Migrations v2.

Would the model I'm trying to go for work well with v2 Migrations?

Basically, I would set up sqlcompare to execute as part of the deployment process. For the command line, I would point it at the location of the source folder for the build in question. Since that source is a snapshot of the code *and* the DB for the current build, and since you no longer rely on "schema version" to figure out what migration scripts to run, I should be able to then complete the comparison and update the target deployment.

My worry is that I believe the way that Release Management works is that the build output moves from environment to environment. I.e.:

1.) Get source by label for build XXXX.
2.) Build output.
3.) Take build output and deploy it to environment X.
4.) When approved, take build output and deploy to environment Y.
etc.

So I would need to make sure that by source controlled DB schema was part of my build output. I would then just make sure I always performed the comparison against that directory structure, presumably, in my build drop location.

Sound right? Or does the fact that a temp DB is used to generate a complete deployment script - migrations and all - mitigate the need to worry about this?

jonathan.hickford

Hi,

I think your steps sound correct, your build artefacts include a scripts folder and the compiled code. Say you want to deploy build XXXX to QA
- Look at the scripts folder which was output during from build XXXX
- Run a SQL Compare between that scripts folder and the QA database to generate a script
- Run the script on QA database
- Deploy the code

Just to clarify your intended uses case with migrations v2. I assume you'll be using migration scripts occasionally to add custom SQL scripts for cases when SQL Compare can't work out what to do automatically. For example renaming a table or adding a not null constraint to an existing column.

Migrations v2 are stored within the target and source databases (and thus will be in any script folder representations of those databases). Providing you are using a version of SQL Compare that has migrations v2 beta functionality, then any migration scripts that have not already been run against the target database will be added to the deployment scripts SQL Compare generates.

Jonathan

RMD

Jonathan,

Excellent. I read through the documentation you guys have up, and was hoping it would work this way.

jonathan.hickford

Great - do let us know how you get on, or if you have any other questions.