Loading ...
Sorry, an error occurred while loading the content.

Re: Database Refactorings and Packages

Expand Messages
  • Dave Sanders
    ... Actually, I tried to post to this thread about dbghost several days ago and got sidetracked. I used DBGhost at a previous job, and, quite frankly, it was a
    Message 1 of 28 , Jun 15, 2006
    • 0 Attachment
      --- In agileDatabases@yahoogroups.com, "mark_baekdal" <
      > Yes I do sell the software, and yes we are a company who does try to
      > create a profit. I'm not trying to hide anything, but if you want to
      > learn why I've never failed a database release and always delivered
      > within the agreed time scales, you can either visit our site -
      > www.dbghost.com <http://www.dbghost.com> or ask me questions here and
      > I'll answer them.

      Actually, I tried to post to this thread about dbghost several days
      ago and got sidetracked.

      I used DBGhost at a previous job, and, quite frankly, it was a
      godsend. It does pretty much what they say it does, and if you alter
      your processes a bit then it works with minimal support or tweaking.
      Of course, there will always be wierd cases or exceptions, but I'd say
      it takes about 95% of the work out of maintaining your scripts.

      I was a manager in development (which I only mention because it means
      that I had the power to dictate how the developers worked, a luxury
      most don't have) and we had multiple databases across multiple
      environments to manage. Our problem mostly stemmed in pushing up
      database changes in a sane manner from one environment to the next.
      Generally this would end up being the "great gathering of scripts" and
      then spending two days getting them all to work, with multiple rounds
      of QA.

      What we moved to was a combination Subversion + DBGhost solution,
      where we would have the developers keep a copy of the dev database on
      their machines. The would then go through this process:

      1. Update to the latest scripts from SVN
      2. Apply those changes to their local database via DBGhost
      3. Work, making whatever db changes they needed, without having to
      really keep track of them (unless they were adding new reference tables).
      4. Run the DBGhost scripter to script out their entire DB
      5. Use SVN to commit whatever scripts changed to the repository.

      From there, the configuration management guys would take over and
      manage the repository, could branch the DB code, and run branches up
      the pipe. At the same time, we were starting to build a pretty
      sizeable set of unit tests too. Between both decisions, we probably
      saved upwards of 75% of time in QA. (Which was great, because then QA
      could go back to focusing on scripting out the rest of the testing,
      instead of having to test everything by hand.)

      I left the company before we had built up any automated processes to
      manage the developer steps, but I'm sure it could have been done. Now
      that I'm doing independent development I don't have as much a need for
      DBGhost now, but I gotta say, there are definitely times when it would
      come in handy.

      Good product, and it helped us out a lot when we were transitioning
      over to agile practices. Your mileage may vary.

      D
    • Garris, Nicole
      So, Dave, the DBGhost scripter creates a script for the database, or a script for the database changes, depending on whether it s a new or existing database.
      Message 2 of 28 , Jun 16, 2006
      • 0 Attachment

        So, Dave, the DBGhost scripter creates a script for the database, or a script for the database changes, depending on whether it’s a new or existing database. But it doesn’t deal with the data. (That is why the reference tables had to be dealt with separately—its crucial that reference table data be exact.) Did I get this right?

         


        From: agileDatabases@yahoogroups.com [mailto: agileDatabases@yahoogroups.com ] On Behalf Of Dave Sanders
        Sent: Thursday, June 15, 2006 5:12 AM
        To: agileDatabases@yahoogroups.com
        Subject: [agileDatabases] Re: Database Refactorings and Packages

         

        --- In agileDatabases@ yahoogroups. com, "mark_baekdal" <

        > Yes I do sell the software, and yes we are a company who does try to
        > create a profit. I'm not trying to hide anything, but if you want to
        > learn why I've never failed a database release and always delivered
        > within the agreed time scales, you can either visit our site -
        > www.dbghost. com <http://www.dbghost. com>
        or ask me questions here and
        > I'll answer them.

        Actually, I tried to post to this thread about dbghost several days
        ago and got sidetracked.

        I used DBGhost at a previous job, and, quite frankly, it was a
        godsend. It does pretty much what they say it does, and if you alter
        your processes a bit then it works with minimal support or tweaking.
        Of course, there will always be wierd cases or exceptions, but I'd say
        it takes about 95% of the work out of maintaining your scripts.

        I was a manager in development (which I only mention because it means
        that I had the power to dictate how the developers worked, a luxury
        most don't have) and we had multiple databases across multiple
        environments to manage. Our problem mostly stemmed in pushing up
        database changes in a sane manner from one environment to the next.
        Generally this would end up being the "great gathering of scripts" and
        then spending two days getting them all to work, with multiple rounds
        of QA.

        What we moved to was a combination Subversion + DBGhost solution,
        where we would have the developers keep a copy of the dev database on
        their machines. The would then go through this process:

        1. Update to the latest scripts from SVN
        2. Apply those changes to their local database via DBGhost
        3. Work, making whatever db changes they needed, without having to
        really keep track of them (unless they were adding new reference tables).
        4. Run the DBGhost scripter to script out their entire DB
        5. Use SVN to commit whatever scripts changed to the repository.

        From there, the configuration management guys would take over and
        manage the repository, could branch the DB code, and run branches up
        the pipe. At the same time, we were starting to build a pretty
        sizeable set of unit tests too. Between both decisions, we probably
        saved upwards of 75% of time in QA. (Which was great, because then QA
        could go back to focusing on scripting out the rest of the testing,
        instead of having to test everything by hand.)

        I left the company before we had built up any automated processes to
        manage the developer steps, but I'm sure it could have been done. Now
        that I'm doing independent development I don't have as much a need for
        DBGhost now, but I gotta say, there are definitely times when it would
        come in handy.

        Good product, and it helped us out a lot when we were transitioning
        over to agile practices. Your mileage may vary.

        D

      • Dave Sanders
        ... It creates a script for each object in your database, as if it were brand new. (Including scripting out any reference data to files.) Then when you run
        Message 3 of 28 , Jun 16, 2006
        • 0 Attachment
          --- In agileDatabases@yahoogroups.com, "Garris, Nicole"
          <Nicole.Garris@...> wrote:
          >
          > So, Dave, the DBGhost scripter creates a script for the database, or a
          > script for the database changes, depending on whether it's a new or
          > existing database. But it doesn't deal with the data. (That is why the
          > reference tables had to be dealt with separately-its crucial that
          > reference table data be exact.) Did I get this right?
          >

          It creates a script for each object in your database, as if it were
          brand new. (Including scripting out any reference data to files.)
          Then when you run DBGhost against another DB, it will use all of the
          scripts to create a brand new empty database next to it (which you can
          tell it to delete when its done, and is usually named with a timestamp
          name) and then it compares the two databases, then creates and runs
          scripts of its own to update your DB to the new version.

          So, the scripts can generate a brand new DB on the fly, or they can
          apply changes to an existing database on the fly. It also is smart
          enough for some scripts to come back and try to re-run failed scripts,
          in case there was a dependency it couldn't pick up.

          This was IDEAL for our situation, because we had several environments,
          representing different testing and production zones. We could then
          "run the changes up" by simply pushing all of the scripts to the new
          environment and running DBGhost against that DB instance. And, with
          SVN, we could obviously go back and pull any complete branch or
          revision of the DB we needed, at any time.

          I _think_ they have a trial, and if so, definitely check it out. Only
          takes a few minutes to figure out.

          D
        • malcolmeleach
          The Scripter also scripts out the reference data for the database, hence it s name DB Ghost Data and Schema Scripter ;-) Once the reference data are in
          Message 4 of 28 , Jun 18, 2006
          • 0 Attachment
            The Scripter also scripts out the reference data for the database,
            hence it's name "DB Ghost Data and Schema Scripter" ;-) Once the
            reference data are in scripts under source control they can then be
            simply managed and versioned along with all the other database code.

            When it comes to releasing the new schema and data the DB Ghost
            Change Manager is then used to propagate the changes (compare the
            SQL and data then update the target database with the differences)
            from the source control system to your target databases (test, UAT,
            production etc.)

            It's a simple and extremely powerful way to manage database changes
            and refactoring tasks - one that leaves you with a full audit trail
            of who changed what, when and why right where it should be - in your
            source control system. Even better, this process works with *any*
            source control system as all it requires is a representation of your
            database in the form of object creation scripts and boiler plate
            INSERT scripts for the reference data somewhere in the file system.

            A simple way of thinking about this is to remember what it's like
            developing a version 1 "greenfield" database. You just keep the
            CREATE scripts up to date and build a new database every time - easy
            and rock solid. It's only from version 2 onwards that things become
            tricky due to the need to preserve data. DB Ghost was written to be
            the magic black box that enables you to continue working like you
            did for version 1 and yet still be able to reliably push those
            changes out without losing any data.

            DB Ghost has been doing this for four years now and our process has
            just been totally vindicated by Microsoft with the release of Visual
            Studio Team System for Databases. Microsoft have finally recognised
            the power and simplicity of this approach the only downside is that
            they are charging 10 times the price for it! Go figure...they must
            have a lot of R&D to recoup ;-)

            --- In agileDatabases@yahoogroups.com, "Garris, Nicole"
            <Nicole.Garris@...> wrote:
            >
            > So, Dave, the DBGhost scripter creates a script for the database,
            or a
            > script for the database changes, depending on whether it's a new or
            > existing database. But it doesn't deal with the data. (That is why
            the
            > reference tables had to be dealt with separately-its crucial that
            > reference table data be exact.) Did I get this right?
            >
            >
            >
            > ________________________________
            >
            > From: agileDatabases@yahoogroups.com
            > [mailto:agileDatabases@yahoogroups.com] On Behalf Of Dave Sanders
            > Sent: Thursday, June 15, 2006 5:12 AM
            > To: agileDatabases@yahoogroups.com
            > Subject: [agileDatabases] Re: Database Refactorings and Packages
            >
            >
            >
            > --- In agileDatabases@yahoogroups.com
            > <mailto:agileDatabases%40yahoogroups.com> , "mark_baekdal" <
            > > Yes I do sell the software, and yes we are a company who does
            try to
            > > create a profit. I'm not trying to hide anything, but if you
            want to
            > > learn why I've never failed a database release and always
            delivered
            > > within the agreed time scales, you can either visit our site -
            > > www.dbghost.com <http://www.dbghost.com <http://www.dbghost.com>
            > or
            > ask me questions here and
            > > I'll answer them.
            >
            > Actually, I tried to post to this thread about dbghost several days
            > ago and got sidetracked.
            >
            > I used DBGhost at a previous job, and, quite frankly, it was a
            > godsend. It does pretty much what they say it does, and if you
            alter
            > your processes a bit then it works with minimal support or
            tweaking.
            > Of course, there will always be wierd cases or exceptions, but I'd
            say
            > it takes about 95% of the work out of maintaining your scripts.
            >
            > I was a manager in development (which I only mention because it
            means
            > that I had the power to dictate how the developers worked, a luxury
            > most don't have) and we had multiple databases across multiple
            > environments to manage. Our problem mostly stemmed in pushing up
            > database changes in a sane manner from one environment to the
            next.
            > Generally this would end up being the "great gathering of scripts"
            and
            > then spending two days getting them all to work, with multiple
            rounds
            > of QA.
            >
            > What we moved to was a combination Subversion + DBGhost solution,
            > where we would have the developers keep a copy of the dev database
            on
            > their machines. The would then go through this process:
            >
            > 1. Update to the latest scripts from SVN
            > 2. Apply those changes to their local database via DBGhost
            > 3. Work, making whatever db changes they needed, without having to
            > really keep track of them (unless they were adding new reference
            > tables).
            > 4. Run the DBGhost scripter to script out their entire DB
            > 5. Use SVN to commit whatever scripts changed to the repository.
            >
            > From there, the configuration management guys would take over and
            > manage the repository, could branch the DB code, and run branches
            up
            > the pipe. At the same time, we were starting to build a pretty
            > sizeable set of unit tests too. Between both decisions, we probably
            > saved upwards of 75% of time in QA. (Which was great, because then
            QA
            > could go back to focusing on scripting out the rest of the testing,
            > instead of having to test everything by hand.)
            >
            > I left the company before we had built up any automated processes
            to
            > manage the developer steps, but I'm sure it could have been done.
            Now
            > that I'm doing independent development I don't have as much a need
            for
            > DBGhost now, but I gotta say, there are definitely times when it
            would
            > come in handy.
            >
            > Good product, and it helped us out a lot when we were transitioning
            > over to agile practices. Your mileage may vary.
            >
            > D
            >
          • Alex Weatherall
            Hi, I too use DBGhost daily and find it invaluable. One thing to note is that it runs against, as far as I am aware, SQL Server only. I don t know whether or
            Message 5 of 28 , Jun 20, 2006
            • 0 Attachment
              Hi,

              I too use DBGhost daily and find it invaluable. One thing to note is that it runs against, as far as I am aware, SQL Server only. I don't know whether or not they are developing this for other RDBMS vendors. However that's the platform I work on and it's good for me.

              We develop the scripts in any text editor (and Query Analyser - i know it should be a Z but I'm from the UK and always forget!) straight from source control and check back in. Once ready to "build" the database we run DBGhost which gets the latest version (or any version) of the files (one per object) and creates a new database from it. It also performs a compare with an existing database and can produce the change scripts. I find I use this when building an upgrade script but I also use RedGate's SQL Compare as I find that tool easier migrate bespoke object changes between existing development databases that might not be following our source controlled version 100%. (We have many applications accessing a few databases so this is common).

              If you use it appropriately with your version control tool then you are able to perform a change script between any version of a database schema. It is also possible to run bespoke change scripts when performing updates so if you have any complicated change logic or versioned change files (as described by Scott) then this technique could potentially be used as well, certainly to check upgrade logic and handle more complicated changes - which is how I am starting to use it.

              I'm trying to implement the some of refactoring ideas presented in Scott and Pramod's book with change scripts instead of letting this tool handle the change scripts and I'm finding it quite hard to decide which changes I need to script manually and which I can trust to the software :-) however after trial and error it's becoming clearer. Normalisation refactorings such as Move Column and Merge Tables etc usually need manual intervention as there are data migration decisions to be made, however other refactorings such as Add/Drop Constraint, Add FK Constraint, Add Method can mainly be handled by writing the scripts for the new objects and building and comparing the database in DBGhost.

              I am at early doors with implementing the refactoring techniques described by Scott, certainly as formally as Scott and Pramod present in their book.
              But this tool certainly is a powerful aid in Agile DB development for SQL Server.

              Thanks,

              Alex
            • Garris, Nicole
              Thanks Dave and Malcolm! You have convinced me that DBGhost makes it feasible to use source control for database evolution. Wow! And the price is very nice!
              Message 6 of 28 , Jun 20, 2006
              • 0 Attachment

                Thanks Dave and Malcolm! You have convinced me that DBGhost makes it feasible to use source control for database evolution. Wow! And the price is very nice! (Too bad it only works with Microsoft’s SQL Server.)

                 


                From: agileDatabases@yahoogroups.com [mailto: agileDatabases@yahoogroups.com ] On Behalf Of Dave Sanders
                Sent: Friday, June 16, 2006 10:51 AM
                To: agileDatabases@yahoogroups.com
                Subject: [agileDatabases] Re: Database Refactorings and Packages

                 

                --- In agileDatabases@ yahoogroups. com, " Garris, Nicole "
                <Nicole.Garris@ ...> wrote:

                >
                > So, Dave, the DBGhost scripter creates a script for the database, or a
                > script for the database changes, depending on whether it's a new or
                > existing database. But it doesn't deal with the data. (That is why the
                > reference tables had to be dealt with separately-its crucial that
                > reference table data be exact.) Did I get this right?
                >

                It creates a script for each object in your database, as if it were
                brand new. (Including scripting out any reference data to files.)
                Then when you run DBGhost against another DB, it will use all of the
                scripts to create a brand new empty database next to it (which you can
                tell it to delete when its done, and is usually named with a timestamp
                name) and then it compares the two databases, then creates and runs
                scripts of its own to update your DB to the new version.

                So, the scripts can generate a brand new DB on the fly, or they can
                apply changes to an existing database on the fly. It also is smart
                enough for some scripts to come back and try to re-run failed scripts,
                in case there was a dependency it couldn't pick up.

                This was IDEAL for our situation, because we had several environments,
                representing different testing and production zones. We could then
                "run the changes up" by simply pushing all of the scripts to the new
                environment and running DBGhost against that DB instance. And, with
                SVN, we could obviously go back and pull any complete branch or
                revision of the DB we needed, at any time.

                I _think_ they have a trial, and if so, definitely check it out. Only
                takes a few minutes to figure out.

                D

              • Curt Sampson
                ... It s possible (though certainly not as nice) to do this without DBGhost. I ve been doing it for years. ... If you want to start in on this sort of thing
                Message 7 of 28 , Jun 26, 2006
                • 0 Attachment
                  On Tue, 20 Jun 2006, Garris, Nicole wrote:

                  > Thanks Dave and Malcolm! You have convinced me that DBGhost makes it
                  > feasible to use source control for database evolution. Wow!

                  It's possible (though certainly not as nice) to do this without DBGhost.
                  I've been doing it for years.

                  > And the price is very nice! (Too bad it only works with Microsoft's
                  > SQL Server.)

                  If you want to start in on this sort of thing with PostgreSQL, you
                  might grab the tools and example app over at pgtools.sourceforge.net
                  to see how you can do automated loading and unit testing using this
                  sort of thing. I think I described update scripts somewhere in the docs
                  on the web site, though I don't recall off-hand. This stuff should be
                  reasonably easy to port for other database systems that have a Unix
                  command-line-based sql tool, as well.

                  cjs
                  --
                  Curt Sampson <cjs@...> +81 90 7737 2974
                  The power of accurate observation is commonly called cynicism
                  by those who have not got it. --George Bernard Shaw
                • ebr102
                  This is a matter of isolating the change. The question should not be How do I get the database refactorings to work with a global package?” This won t
                  Message 8 of 28 , Jul 17, 2006
                  • 0 Attachment

                    This is a matter of isolating the change.  The question should not be "How do I get the database refactorings to work with a global package?”  This won't happen because it is global.  The question should be "How do I make a package local to the environment I am manipulating?"  Without isolating environments it is like doing parallel development against code in a flat file system.

                    I have not used Oracle in years so I will leave the specifics to those with domain knowledge.  It may require completely separate database instances.

                    When it comes to promoting these refactorings up the code chain I prefer the accumulation method.  Our project uses a "scorched earth" approach to our builds: 

                    ant refreshDB

                    will drop all artifacts in the database, create all tables, views, and stored procedures then populate it with configuration and canned test data.  Then it runs the delta SQL, which is the exact DDL and DML needed to change the database from the baseline to what the developer needs it to be.  Each developer appends their changes to the end of the file; ClearCase takes care of concurrency issues.  This exact script is used during the stabilization day at the end of the iteration to update the QA databases at which time the SQL in the delta is integrated into the creation scripts (also in source code control) and zeroed out.  All versions of this delta since the last release are used at release time to update the production database.

                    There are several advantages to this, first is the delta is tested every build.  Second, the DBA's have a well known place to look for database changes.  Third, a developer has unlimited freedom to experiment, they have a fully sandboxed environment.  Fourth, database changes are associated with code changes when everything is checked in.  Fifth, changes are small and easily understood.

                    The down side:  it is a manual step to integrate the changes into the creation scripts.  I doubt a tool will be able to solve this problem for us because we have made some interesting implementation choices.   I'd be happy to detail them if anyone in interested, but in QA and PROD the database used by the J2EE data source is just a set of views looking into tables on other databases.

                    We are very agile, we use Sybase.  We have over 200 tables, 20 developers and a "one day a week" DBA.  We have done 6 releases so far this year, some had significant schema changes.  We still have some versions released over a year ago running against the same database.  The first few times we released and changed the database, management was absolutely white knuckled; they have since gained trust in our process and embraced agility.
                  Your message has been successfully submitted and would be delivered to recipients shortly.