Database change management

[Sean McBeth]

For all the software devs out there, I'm curious about how other people use and live with databases, specifically the types of tools they use around their database to simplify work with it. For background info, what database(s) do you use, and what is the scope of your project? What kind of tools do you use to manage change with your database (implicit or explicit schema, server deployments, etc.)? What kind of "generator" tools are you using (thick- or thin-ORM, data access layers, whatever)? What pain points do you still experience? Thanks!

For myself, I have worked on a lot of custom enterprise resource planning systems over the years. Data is generally small, and highly relational. It's been mostly SQL Server, but there is some MySQL in there too. In other words, all pretty basic RDBMSs. I've played around with ORM systems like NHibernate and Entity Framework, found them quite useful for designing the initial database, but lacking something in the long run for any time changes were necessary after a nontrivial amount of data had been recorded. As a result, I've tended to try to leave the database changing in the database realm and use only a thin ORM (direct table mapping) with classes automatically generated from the schema. This seems to avoid lost data problems, but comes at a high development cost for constantly repeating yourself and not having change management ala source control in place.

Redgate has a tool called SQL Compare that I've used in the past that makes diffing and merging databases a snap, but it is very expensive. Without a SQL Compare-like tool, it's really easy to miss changes to tables and stored procedures without extremely comprehensive testing (and really, who does that?) when deploying from a development- to testing- or production-environment.

[Adam Kaufman]

as much as I hate to say it, switching to Visual Studio Database Projects has improved my db satisfaction dramatically. super simple to set up, even with an existing database. apparently you can use it with MySQL as well...

http://www.jeffongames.com/2007/07/mysql-and-visual-studio/

--
To post to this group, send email to hive76-d...@googlegroups.com
To unsubscribe send email to hive76-discuss...@googlegroups.com
For more awesome goto http://groups.google.com/group/hive76-discussion?hl=en
---
You received this message because you are subscribed to the Google Groups "Hive76 Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hive76-discuss...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Sean McBeth]

Do you think this is a symptom of VS's Solution File system? I've personally had a lot of trouble managing change outside of the Solution File channel in VS projects, but in other systems it has not been a problem. My feeling was always that it was because VS was trying to do too much in regards to combining all of your editing and file management needs in one place. It can be a handy feature to not have to context-switch in and out of the IDE. If I'm in an environment where all of my project pieces fit into the VS model, it works great, but that is getting harder and harder to do these days. 

[Amir Tahvildaran]

I started to use liquibase for change management: http://www.liquibase.org/  They were the first (that I found) to help manage schema changes as code.  Since then some others have cropped up but I haven't compared.  The general idea is you create update scripts those get versioned with the code and run at deployment ("liquibase update").  

Hibernate has some modes to do schema updates but I wouldn't trust it to update my production database.

I'm still on the previous version of liquibase, not sure what's been going on in v2 but I expect to check it out soon.

I've got a project where I demo'd some infrastructure technolgies, not sure if it will be of use to you (the slides don't have liquibase stuff in it because I was presenting to drupal people):

https://github.com/amirdt22/wip

As far as your background question, I try not to worry about the database I am using.  (I prefer to use postgres)  Most of the tools I use are database agnostic or aim to abstract your from the databsae specifics (hibernate/liquibase).  I really hate to get tied to a database (using stored procs/custom types/etc).  In Java I use hibernate (or JPA/Spring).  Grails uses GORM.  PHP apps can/should use PDO.  I've never developed on windows, so you'll have to tell me about NHibernate I assume it's a good port?

-Amir

On Thu, Mar 21, 2013 at 1:22 PM, Sean McBeth <sean....@gmail.com> wrote:

--

[Dan Shookowsky]

I do most of my work with SQL Server and Visual Studio.  Visual Studio has a database project that you can use to reverse engineer the schema of a database (tables, indexes, constraints, users, procs,etc).  You can then compare your project with the database.  

My development cycle is to edit the file in VS2010 before executing it on my dev database.  When it comes time to deploy to testing/prod, I can compare the project and those servers or compare dev to the destination server and generate a script

All of the schema files are also source controlled with SVN or TFS - that's one thing that I think is lacking from Redgate.  It's useful for answering questions like 'who removed this index and why are the reports taking hours versus seconds?'

On Thu, Mar 21, 2013 at 1:22 PM, Sean McBeth <sean....@gmail.com> wrote:

--

[Sean McBeth]

Amir: yeah, NHibernate is a very mature port of Hibernate to the .NET platform. It's been around for years, probably about as long as Hibernate itself. Just had someone else suggest Liquibase on another forum, so that is promising.

Dan: I'm on VS right now, but the plan is to eventually get off of it. Might have a waystop through MonoDevelop on the way to something else. I'd really like to figure out a pretty language-agnostic and editor-agnostic approach to this.