SQLServerCentral / Editorials / SQLServerCentral.com / On Database Migrations and Agility / Latest Posts

RE: On Database Migrations and Agility

sturner — Tue, 07 Feb 2012 08:37:42 GMT

[quote][b]GSquared (1/9/2012)[/b][hr]But what most developers mean by "Agile" is actually "Cowboy". Database development can be quite Agile. It creates a horrible mess when it's done Cowboy-Coder style.[/quote]Eloquently stated sir. I've seen a few of these cowboy designs. The thing that amazes me is it really doesn't take much longer to come up with a real design Vs. a hack. Any disparity reverses itself when you factor in the countless man hours needed to support the cowboy design (fixing bugs & hacking it even more to add features).

RE: On Database Migrations and Agility

Dream Weaver — Mon, 06 Feb 2012 14:50:46 GMT

[quote][b]Evil Kraig F (1/22/2012)[/b][hr][quote][b]Peter Schott (1/22/2012)[/b][hr]So overrides were put in place in certain places, the cart was modified, etc etc. Eventually this cart proc became about 30 pages long, and was so convoluted that processing a cart to completion could take up to 10 seconds. This was unacceptable due to ever increasing volume order.A full redesign of the structure, incorporating the new modules into the core structure, allowed for a much fuller and less intricate system of maintaining the data. This took 3 months of pure dev work, no new features, no ... you get the drift. It wasn't possible to do this in small doses, we had to redesign basically from scratch for all the new modular components that had been included.I'm all for agile as long as the time is taken every few years to clean up the mess it leaves behind.It's not because you can't plan for the scope, it's because the very NATURE of Agile is planned scope creep.[/quote]This also applies to DEV's comment: [quote] I would like to add here that few of my colleagues read about Agile DEV from BOL or some BLOG and blindly started following it. [/quote]Agile development is hard. I can't see how anyone could do it without a lot of study and intense effort, hopefully with someone who knows the ropes and can guide you along. (No, I'm not a consultant. But I wish we could hire one; it would help a lot.)A wealth of Agile "best practices" have been amply documented. Not studying these or applying them to your project is a recipe for disaster. I find that most people who think that Agile can't work, or have had a poor Agile experience, did not apply these best practices and often apparently don't even know what they are. It's the same as if a DBA failed to learn and follow best database practices (which are out there to be learned, and many people can help you learn them), then blamed SQL Server for their project disasters. Understanding SQL Server best practices takes a lot of time, study, effort, and experience. But if you're not following them, it's not SQL Server's fault. If you think that implementing the practices takes too much time for your situation, well, good luck to you--but don't blame SQL Server when things go poorly.And no, if you end up with a 30-page code module (be it SQL or c# or any other code in your project), that performs poorly and is difficult to understand, you're not following Agile best practices. "Refactoring" constantly, aggresively, is the best practice that avoids this situation. If you get good at it, which takes study and experience, it will speed up rather than slow down development and keep code quality high. If you think that refactoring and other best Agile practices takes too much time for your situation, well, good luck to you--but don't blame Agile development when things go poorly.It's easy to find good sources of information for Agile development in general, but I know of only one person, Scott Ambler, who specifically addresses the database portion in depth. He even has a book specifically about refactoring databases. There have been large, complex projects that have had success using his ideas.

RE: On Database Migrations and Agility

Dev — Sun, 05 Feb 2012 05:20:15 GMT

The biggest strength of Agile Development is its biggest weakness as well i.e. ‘narrowed vision’. You don’t see the problem as a whole but as bunch of problems jumbled together, with assumption that each piece of problem is independent (or less dependent) of another. This assumption may & may not be true in real life.I like agile development but my style is not Pure-Agile. I had limited the scope of agile development to SQL coding only. It doesn’t include Data Modeling. The reason is simple (and to follow the building analogy) to make the solid foundation & skeleton for the building first. Once I am convinced that the foundation can handle few floors weights without much adjustment, I don’t care which room of the building or which floor user wants to see first.I would like to add here that few of my colleagues read about Agile DEV from BOL or some BLOG and blindly started following it. The projects been delivered to customers and customer was happy as well. But the delivery involved too much integration costs and over-time of DEV team. It’s not feasible IMHO.

RE: On Database Migrations and Agility

Evil Kraig F — Sun, 22 Jan 2012 16:50:08 GMT

[quote][b]Peter Schott (1/22/2012)[/b][hr]Along those lines, ones you have a decent Continuous Delivery system in place, this shouldn't be as much of a problem. You have your downstream environment(s) that are exact copies of your production ones, gen up the change script(s) using your automated tools, then just hand them off to the team doing the changes in Prod. They should have sufficient permissions to do the changes needed and ideally you have at least one downstream environment that's a duplicate of your production environment so the testing and notes about other pieces of the equation can be handled. We've been using the VS2010 DB Projects to store and push our changes and are pretty close to having the ability to just build/deploy all the way up the chain.[/quote]Build and deployment of new modules aren't really my concern there. The equivalent of adding a scoring system to the existing inventory system isn't where Agile starts to breakdown, at least in my mind, from a database perspective.However, allow me an actual example that I walked headfirst into a few years ago.A simple e-sales system had started with a solid core scope and understanding. Products were included, carts were built, etc etc. It worked very well. Attached to this were additional modules. Eventually, a catalog system was built in for custom client pricing. Certain deals were to be offered to certain clients. For example, students at particular schools could order things cheaper by being a student.So overrides were put in place in certain places, the cart was modified, etc etc. Eventually this cart proc became about 30 pages long, and was so convoluted that processing a cart to completion could take up to 10 seconds. This was unacceptable due to ever increasing volume order.A full redesign of the structure, incorporating the new modules into the core structure, allowed for a much fuller and less intricate system of maintaining the data. This took 3 months of pure dev work, no new features, no ... you get the drift. It wasn't possible to do this in small doses, we had to redesign basically from scratch for all the new modular components that had been included.I'm all for agile as long as the time is taken every few years to clean up the mess it leaves behind.It's not because you can't plan for the scope, it's because the very NATURE of Agile is planned scope creep.

RE: On Database Migrations and Agility

Peter Schott — Sun, 22 Jan 2012 15:18:07 GMT

[quote][b]Evil Kraig F (1/22/2012)[/b]With that caveat, I agree you can modularly build databases within the agile structure. However, I also agree with Gus. I've seen too many shops that got their prod powers removed and suddenly Agile is 'problematic'. It's all to often a Cowboy mentality.[/quote]Along those lines, ones you have a decent Continuous Delivery system in place, this shouldn't be as much of a problem. You have your downstream environment(s) that are exact copies of your production ones, gen up the change script(s) using your automated tools, then just hand them off to the team doing the changes in Prod. They should have sufficient permissions to do the changes needed and ideally you have at least one downstream environment that's a duplicate of your production environment so the testing and notes about other pieces of the equation can be handled. We've been using the VS2010 DB Projects to store and push our changes and are pretty close to having the ability to just build/deploy all the way up the chain.As for complete rebuilds, ideally the project hasn't changed too much along the way and/or you've done the appropriate research before starting that you have a pretty good idea of where this is going in the long run so total redesigns aren't needed. :) If not then you have a lot of other problems to deal with in data conversions, rollbacks, new structures, and all sorts of other things. It's necessary sometimes, but it can often be avoided with some good discussions and planning up front. (not to the waterfall point, but enough where you've got a good enough design for most of the objects)

RE: On Database Migrations and Agility

ganotedp — Sun, 22 Jan 2012 13:20:42 GMT

Good to meet you! Just had this vague notion that Craig Ferguson was more than just a late night TV host... what if he were a database guy by day! ;-)

RE: On Database Migrations and Agility

Evil Kraig F — Sun, 22 Jan 2012 12:46:55 GMT

[quote][b]ganotedp (1/22/2012)[/b][hr]I can't really concur with Dreamweaver's analogy with human development. But Evil Craig F (Ferguson?) is spot on with:[/quote]Craig Farrell. :) Pleasure to meet ya. It's in the sig. The nickname's a joke that I happened to like. :cool:

RE: On Database Migrations and Agility

ganotedp — Sun, 22 Jan 2012 11:41:32 GMT

I can't really concur with Dreamweaver's analogy with human development. But Evil Craig F (Ferguson?) is spot on with:[quote]The powers that be must understand that you WILL require entire sprints for re-design of the core module to integrate new work. It's not an optional choice. This will require downtime when the fixes are put into place.[/quote]Continuing the building analogy, it's like responding (to the customer's request for a bedroom after seeing the dynamite kitchen): "Sure, but we've got to shut down the kitchen, and tear down some of the existing work to rebuild this into a 2 room module. You won't be able to use the kitchen for a while."Sure, it's not an exact analogy. More like you're building modular housing that you can crane into position. But you still need time to extend or replace the foundation, and move all the owner's stuff (data) from the one room into the new 2 room module, etc. etc.Of course, I think most people would concur with Peter Schott that:[quote] I'd much rather start small enough to show something and get feedback as we go. That helps us make course corrections and tackle more important needs as they come up instead of delaying them further. [/quote]In my more cynical moments, I feel like I'm one of the workers on the (in)famous Winchester Mystery House...http://en.wikipedia.org/wiki/Winchester_Mystery_Househttp://www.prairieghosts.com/winchester.html

RE: On Database Migrations and Agility

Evil Kraig F — Sun, 22 Jan 2012 01:15:03 GMT

[quote][b]Peter Schott (1/11/2012)[/b][hr]I actually like the Agile plan when it can work. I've seen too many large DB Projects fail due to using waterfall (think Amazon Falls) methodology and then years of work to deliver the finished project. I'd much rather start small enough to show something and get feedback as we go. That helps us make course corrections and tackle more important needs as they come up instead of delaying them further.[/quote]I agree with this (having been wandering amongst a few agile teams of late) but with a caveat. The powers that be must understand that you WILL require entire sprints for re-design of the core module to integrate new work. It's not an optional choice. This will require downtime when the fixes are put into place.With that caveat, I agree you can modularly build databases within the agile structure. However, I also agree with Gus. I've seen too many shops that got their prod powers removed and suddenly Agile is 'problematic'. It's all to often a Cowboy mentality.

RE: On Database Migrations and Agility

Dream Weaver — Sat, 21 Jan 2012 18:16:35 GMT

[quote][b]ganotedp (1/7/2012)[/b][hr]The not-so-flattering Agile analogy (not invented by me) is like trying to build a house one-room-at-a-time (where software is the "fine carpentry and dry wall" and data structures are the "foundation and roof"). "O, wow, I really like this kitchen" says the customer, "let's add a bedroom". Seems easy if you're doing fine carpentry and drywall. However, how do you do Agile foundation work and roofing? Brings to mind the parable told by the radical rabbi about the foolish man building his house on sand. The wise man built on a solid foundation.[/quote]I wanted to write a more lengthy response to this analogy than would be appropriate in this space. You can find it at: [url=http://surroundingthecode.wordpress.com/2012/01/22/agile-development-the-wrong-analogy-again-how-about-this-one]http://surroundingthecode.wordpress.com/2012/01/22/agile-development-the-wrong-analogy-again-how-about-this-one[/url]

RE: On Database Migrations and Agility

Peter Schott — Wed, 11 Jan 2012 11:32:47 GMT

We do Agile dev in our shop and spend a decent amount of time planning DB changes up-front to avoid doing it over as we move forward. Sometimes we overplan, sometimes underplan, but it helps to at least attempt to get it right up-front. It helped once the team finally understood that you couldn't just drop tables and create new ones like you can with DLLs and EXEs. It took a while for that to sink in, but they understood it and it helped us handle DB migrations a little better in the future. There are still times we need to do a large data operation the night of a release, but on the whole knowing that helped us make better plans for the future. Finding the correct balance of "just enough" as opposed to "plan it as far out as we can" can still be challenging, but having the team backing up those decisions makes a huge difference.I actually like the Agile plan when it can work. I've seen too many large DB Projects fail due to using waterfall (think Amazon Falls) methodology and then years of work to deliver the finished project. I'd much rather start small enough to show something and get feedback as we go. That helps us make course corrections and tackle more important needs as they come up instead of delaying them further.

RE: On Database Migrations and Agility

Jeff Moden — Mon, 09 Jan 2012 22:00:56 GMT

[quote][b]GSquared (1/9/2012)[/b][hr]But what most developers mean by "Agile" is actually "Cowboy". [/quote]Hear here!!! GUS FOR PRESIDENT!!! :-D

RE: On Database Migrations and Agility

GSquared — Mon, 09 Jan 2012 10:34:29 GMT

The point of Agile is to allow business rules in the software to evolve as business rules in the business evolve.There are ways to build a database to allow for ease-of-evolution, and hence Agile databases. Ironically, they require even more up-front planning and homework, not less.But what most developers mean by "Agile" is actually "Cowboy". Database development can be quite Agile. It creates a horrible mess when it's done Cowboy-Coder style.

RE: On Database Migrations and Agility

Greg Edwards-268690 — Mon, 09 Jan 2012 09:03:46 GMT

[quote][b]Jeff Moden (1/8/2012)[/b][hr][quote]It therefore always surprises me when Application programmers tell me that all 'Agile' techniques are directly applicable to database development.[/quote]Heh... what did you expect, Phil? Many application programmers and their managers have misinterpreted the Agile Manifesto as meaning there doesn't need to be [i]any [/i]documentation or preparation and have also misinterpreted what Knuth said about pre-optimization being the root of all evil. With exceptions, of course, most of the application programmers that I've met and worked with personally all think the same thing... disk space is cheap, machines with more memory are coming, stored procedures are an evil to be avoided, and an RDBMS is just a place to store data.As a very interesting (to me, anyway) sidebar, I'm currently working with 3 wonderful Application Developers that have seen the proverbial light and actually get it. Like the song says, "I'm in Heavennnnn!" I think that part of the reason why they get it is because they're the ones that have to clean up after their "Agile/No Pre-Optimization/Design-on-the-fly" predecessors.[/quote]Amazing what happens when confronted clean up is involved.Kind of like when I ground boxes on the floor, and then started to weld.Every welder should have to grind their own welds.It tends to improve the process.:hehe:

RE: On Database Migrations and Agility

Phil Factor — Mon, 09 Jan 2012 02:27:04 GMT

The Agile stuff ain't new at all. I worked in a CSC team in the Nineties practicing some techniques that were recognizably 'Agile'. The only difference was that we used Big sheets of paper stuck on the walls with Blu-tac rather than post-its and called the meeting 'shirtsleeve meetings'. I was in the Data Architecture team and we insisted on getting the data architecture planned out in its entirety before the coding started. It was the only part of the project that got finished, all beautifully mapped out in ERwin.

RE: On Database Migrations and Agility

Jeff Moden — Sun, 08 Jan 2012 18:27:45 GMT

[quote]It therefore always surprises me when Application programmers tell me that all 'Agile' techniques are directly applicable to database development.[/quote]Heh... what did you expect, Phil? Many application programmers and their managers have misinterpreted the Agile Manifesto as meaning there doesn't need to be [i]any [/i]documentation or preparation and have also misinterpreted what Knuth said about pre-optimization being the root of all evil. With exceptions, of course, most of the application programmers that I've met and worked with personally all think the same thing... disk space is cheap, machines with more memory are coming, stored procedures are an evil to be avoided, and an RDBMS is just a place to store data.As a very interesting (to me, anyway) sidebar, I'm currently working with 3 wonderful Application Developers that have seen the proverbial light and actually get it. Like the song says, "I'm in Heavennnnn!" I think that part of the reason why they get it is because they're the ones that have to clean up after their "Agile/No Pre-Optimization/Design-on-the-fly" predecessors.

RE: On Database Migrations and Agility

ganotedp — Sat, 07 Jan 2012 11:48:53 GMT

The not-so-flattering Agile analogy (not invented by me) is like trying to build a house one-room-at-a-time (where software is the "fine carpentry and dry wall" and data structures are the "foundation and roof"). "O, wow, I really like this kitchen" says the customer, "let's add a bedroom". Seems easy if you're doing fine carpentry and drywall. However, how do you do Agile foundation work and roofing? Brings to mind the parable told by the radical rabbi about the foolish man building his house on sand. The wise man built on a solid foundation.

On Database Migrations and Agility

Phil Factor — Sat, 07 Jan 2012 11:19:40 GMT

Comments posted to this topic are about the item [B]On Database Migrations and Agility[/B]