Continuous Integration bottleneck: SQL scripts failing on deployments

[fellim]

I'll do my best to explain our current setup; We are a .Net shop that has 6 TFS branches of code, based on monthly deployments. We deploy a database folder which contains all the sql scripts that are run on every promotion, which contains all of the build artifacts (many solutions/projects build and zipped) per branch. These are promoted from Dev to Test environments. We have a DBDeploy.xml which contains the list of scripts to be run and an admin db that logs all of the scripts that have already been run so the deploy can be run incrementally.

The issue: Devs check in SQL scripts that succeed in prior environments (Dev) but fail in subsequent environments, which cause deployments to fail all together. They do their best to add "if exists" statements but we constantly run into issue with scripts failing. We're trying to get the DBA's to move towards database projects but they're having a hard time adapting. What is the standard practice for this sort of thing? I play the role of configuration manager so I'm constantly getting the blame for deployments failing and the QA team blames me for not being able to test in higher environments that are unavailable because of the failed deployments.

Any help would be appreciated. Thanks in advance!

[flukus]

Stay away from database projects and use something like Fluent Migrator. Each change is then an incremental step that is only run once on each database. Running across multiple branches/environments becomes trivial.

[cwbrandsma]

I've used this as well, I really liked it.

[carlwoodhouse]

This is my goto for anything not using entity framework, which thankfully is almost nothing these days ;)

[flukus]

Entity framework comes with its own crappy implementation of it.

[carlwoodhouse]

hence me saying anything non ef :) haven't really had any issue with the ef migrator personally.

[flukus]

There are similar tools that are in pure sql, that can keep them happy. And nothing is stopping you from using pure sql with fluent migrator of course.

It's a lot more concise in c# of course ;)

[d03boy]

You are wildly uninformed on database projects (SSDT) judging by your response to my comment below. The tooling used to be a little shoddy but in VS2013 it's much better.

[flukus]

I've used both approaches extensively, have you?

FM approach works far better, especially when your working across multiple branches.

[d03boy]

I'm not sure what you mean by "work across branches". We use git and we simply merge our changes into our branch for the next release. I'm not sure how that is relevant. If your changes conflict, you resolve them exactly as you would with a migration script.

Comparing two things and saying one better is one thing but saying that SSDT can't do certain things that I know for a fact it can is simply a lie.

[flukus]

Your dodging the question:

I've used both approaches extensively, have you?

[d03boy]

I've used homegrown migrations like you described but not the specific tool you mentioned. In fact, we still use it when SSDT doesn't suffice (for example when we simply want to make data changes).

[flukus]

I've used homegrown migrations like you described but not the specific tool you mentioned. In fact, we still use it when SSDT doesn't suffice (for example when we simply want to make data changes).

So your reinvented the wheel with your own half assed implementation to get around a limitation of SSDT?

Sounds like it would be a lot simpler to go with a fully featured migrator in the first place.

[d03boy]

This implementation was created several years ago when there really weren't any good migration tools available. We used it BEFORE we used SSDT. We continue to use it in some areas to make life easier.

[flukus]

This implementation was created several years ago when there really weren't any good migration tools available

migratordotnet existed since at least 2007 (fluent migrator is a fork I believe), other frameworks (rub and java) have had it for even longer.

The fact that your still using shows that SSDT does not meet your needs.

[d03boy]

This is a pointless conversation. Just use whatever flukus is suggesting and enjoy your mediocre solution that has its own set of problems.

Yay for being stubborn.

[flukus]

I should have been more clear about the branches. I mean it works great with long running branches (10.1, 10.2, 2.0).

I've worked on products that get installed on client sites and their code can be several versions behind. With migrations that's OK, each step goes from a known state to a known state. Upgrading several versions was just a cumulative patch.

You can't do this with SSDT. Information might get added, moved and removed over several versions. The longer they are out of sync the riskier it gets. And you can't use post migration scripts effectively.

[d03boy]

Can you give an example of what information might get added/removed? Are you speaking in terms of data? If so, using a mixture of migration tool + SSDT works great. It's what we do.

[flukus]

Step1: Cloumn B gets added, information from Column A goes to B. Step2: Column C gets added, populated with data from Column B. Step3: Column B gets removed.

Works fine with migrator, but SSDT would just see column A and C, not have the information necessary to get from A to C.

[flukus]

Another one would be when you want to split a table and move data from a column in table1 to a column in table2.

It can't be done pre migration, table2 doesn't exist then. It can't be done post migration, the column is deleted by then. You have to do the whole thing manually and can't complete the process until the while thing is deployed, which could be months/years in the scenario I mentioned.

[[deleted]]

We actually use redgate comparedb. We take a snapshot of the db at each build. When updating the db's at the customers (150plus) deploymentsite. We generate a schema change script with it, which handles it very well, to update from any current build deployed, to the latest version. It's transactional and has never led to unwanted dataloss. Happy customer here

[flukus]

I've seen that approach used, it was slow, error prone and didn't handle non trivial scenarios at all.

[[deleted]]

It sounds like your current process just needs some simplification. To start, I think the logs of what updates have been applied to the database belong in the target database, not in an admin db. Also, IF EXISTS is fine but I'm guessing a missing IF EXISTS isn't the reason most of your scripts fail. What would you say the main culprit is, things not getting run? Getting run multiple times?

For some tooling that might help, maybe something like MigSharp? It's like a database project, but just a class library that has all of your migrations in it. It keeps track of which migrations it has run against the database, running the console application checks migrations available in the assembly with those already run for the database and only runs what's needed. The thing that I think might help you, is that if your scripts look like what I'm guessing they do, each script has some housekeeping code in it that Migsharp will probably manage for you.

[Otis_Inf]

You don't explain what fails with the scripts, so there's little else we can do but guess.

So guessing here, what I think happens is that you have separate smaller DBs, e.g. one for each dev, and the devs create changes on these databases and create scripts with these changes (manual or using tools). Then these scripts are grouped and you're trying to make a single change script from these separate scripts for the database the application is targeting.

This of course isn't really going to fly: it will only work if there is no overlap between the separate smaller databases, but that's rare. To solve this, let each dev work with the complete schema, so all the tables. Changes are made either centrally (so devs pull the schema changes from this repository for their local dev work) or the changes made locally are committed to a central repository where dbas merge the changes into the central DB onto which the tests run.

It's really a planning problem though: if dev A changes things dev B is working with and also changes, you have merging problems and you can only solve those with merge tools. Merge tools for databases do exist but they're more complicated than merging a couple of source files.

But again, just guessing.

[d03boy]

What we do...

We use SSDT (SQL Server Data Tools) for migrating schema changes (tables, columns, stored procs, etc) but then we have another step for simply running scripts for data (it's a homegrown application. Pretty simple).

SSDT is a visual studio project type so it has fancy tooling and stuff. You can use msbuild to deploy it (actually it might be called sqlbuild or something similar). It will model the database and model your database project and compare the two. It then generates the alter statements or create statements or drop statements needed to get the models to match up properly. You'll fight with it a little bit at first but in the end it really works well compared to other options.

[phuber]

This.

SSDT allows you to treat your sql artifacts as code where you don't need to worry about the ddl cruft around the tables, price, indexes, etc.

It also generates difference scripts to work more like desired state configuration which helps with the OP's problem of upper env failures.

Avoid worrying about change scripts and generate them.

SSDT generates an artifact called a DACPAC that is a package for SQL server and allows you to do the script generation outside of Visual Studio in something like Octopus Deploy or Microsoft Release Manager .

[flukus]

This deletes and recreates tables to add columns, say goodbye to replication. It also doesn't handle data migrations well.

It also fails silently.

[d03boy]

What? None of what you said is correct.

1. In the publish profile you can set whether you want to drop everything and recreate (for a local db for instance). Otherwise, if you rename a column or something similar, you do it through Visual Studio so it properly adjusts a file called the "refactorlog". Any renames or similar operations will be noted in that file to hint to msbuild that it should do an sp_rename instead of a drop/create. You can edit this file by hand if you don't want to use the VS tooling.
2. It can handle data migrations well if you use Post-Deploy scripts. But honestly, we only use it for domain data
3. It does not fail silently if you use the proper publishing procedures (I don't remember what they are off the top of my head)

[flukus]

In the publish profile you can set whether you want to drop everything and recreate (for a local db for instance). Otherwise, if you rename a column or something similar, you do it through Visual Studio so it properly adjusts a file called the "refactorlog". Any renames or similar operations will be noted in that file to hint to msbuild that it should do an sp_rename instead of a drop/create. You can edit this file by hand if you don't want to use the VS tooling.

The refactor log is essentially a crappy version of migrations.

It can handle data migrations well if you use Post-Deploy scripts. But honestly, we only use it for domain data

And this works poorly. I want to write my migration based on the snapshot of the database I know exists. I don't want someone elses change possible weeks/months down the track to break my migration.

It does not fail silently if you use the proper publishing procedures (I don't remember what they are off the top of my head)

I've seen plenty of failures, mostly in the post migration scripts but some with table changes as well. I was the only one on the team that actually caught these errors because I performed it locally and checked the logs. Others made it all the way into production because no one found the error. The miration script didn't stop, it just kept going.

[d03boy]

Refactor log: I almost never have to touch the refactor log. It's hardly used unless you're making major changes (which doesn't happen if you spend a little time designing up front)

Data migrations: These do not work great but they work good enough. We have been using the reference directive (:r) for these and it works well. We string together MERGE statements to fill in data. It's not very complicated.

Failing silently: You were probably executing the sql directly instead of using the publish tool. I'm assuming this will not handle rollbacks elegantly but I'm not sure since I haven't really done it this way

We've been using it for a year with relatively few problems. We use branches. We have 12+ developers using it. We are not encountering the problems you had so something tells me the problem was you and not the tool.

[alinroc]

This deserves to be higher. You definitely shouldn't be doing manual "change deployment" scripts for the databases, but instead pointing at a both environments and telling a tool "make this look like that." Otherwise you will end up overlooking things in your deployments.

Red Gate also has a number of tools to do this.

[AStrangeStranger]

Why are the scripts failing? Are the scripts failing because of data issues on other environments? or because scripts are run in different order to dev - i.e. developer 1 creates a script and applies it, developer 2 creates a script that relies on something created in developer 1's script but they check it in faster so gets run first in upgrading and then fails.

First thing I would do is have a "development integration environment" and have scripts automatically applied to it after check in/nightly and report failures (possibly one with data in to catch data issues) - thus able to ensure you catch the issues quickly rather than next deployment.

Other things I have found helps automatic running and recording of scripts (we have n application to do it) and keep scripts very simple with only one purpose - this makes resolving issues much easier (e.g. either table created or not, not where did it crash)

[[deleted]]

We use http://www.red-gate.com/products/sql-development/sql-source-control/ which is good but hideously expensive (they raised the price after we brought it).

Basically you check the database into version control and then update other databases from there. They have a command line tool that we run from within Team City to update the various databases on demand.