Secondary Navigation

RE: [agileDatabases] Re: Database Versioning

It s the system I find to be the best - it doesn t always make it easy, but I find it easier than any other approach - one of the reasons is that I only ever

Message 1 of 46
, Jul 25, 2005

It’s the system I find to be the
best – it doesn’t always make it easy, but I find it easier than
any other approach - one of the reasons is that I only ever have to do it once.

Using versioning tables within your database
will give you the information as to what state the database is in. This can
then correlate back to your source code repository which would be labeled accordingly.

If the databases are different then they’re
different with shared common functionality – source control systems are
built for this. IE: everyone has the same schema however there are many
different stored procedure groups and deployments. This organization can be
handled by the source control using a combination of sharing files and or
subdividing projects into unique areas of functionality.

A copy of the database is necessary for
testing any upgrade no matter what approach you use. You wouldn’t
seriously consider putting out a release into the ether without knowing what’s
going to happen would you? A copy could be made by yourself using repositories
at the different versions or sourced from the client. You don’t
necessarily need the complete database from the client in this scenario, the
source code many suffice. If you have the source code you can build a replica
schema of the clients database and test your release procedure against it to
ensure success.

One of the things we’re working on
(and we’re almost there) is a deployment aid for database professionals
when dealing with many versions of their system. The application will allow you
to deploy our engine which will use a source file (XML which a wizard will
build for you and you include it as part of your release) as it’s
information store for where you want the target database to be. You can set
rules and have the end user enter properties for their system. This would make
this scenario very simple to manage as you’d no longer worry about what
is on the clients machine all you’d need to worry about is the new
functionality.

On 7/25/05, mark baekdal
<mark.baekdal@...> wrote:> The principle I follow is that the source
repository is always updated. This> means any hot-fixes must also be merged into
the central source repository> at the same time they're applied to the
production system. This will ensure> the functionality is never over written.>

That will work fine if you only have one
production system. Whathappens when you have a number of customers
working on a variety ofdifferent versions of the production database.

Yes but now I need to deal with source code
branching hell to dealwith multiple versions of the truth.

> If the source becomes the truth, then all you
need to upgrade any version> database is to use that as the source to
create a database from, compare the> source created database with any target and
synchronize the target database.> The version of the target is largely
irrelevant, the question is - what> version do you want the database to get to?

That is assuming that you have a copy of the
target on hand to do thediff against!

Regards,

Darrel

---
[This E-mail was scanned for viruses for your protection]

Todd Carrico

It sounds like your customers are not making changes to the schema and programmability objects in thier installation. If that is the case, then I think your

Message 46 of 46
, Aug 2 6:21 AM

It sounds like your customers are not making changes to the schema and programmability objects in thier installation.

If that is the case, then I think your approach would work. If you can look at some metadata and know what the current installation looks like, then it is trivial to determine which scripts you need to run to upgrade to what version. Roll back might be trickier.

If you cannot make the assumption that the customer has not made changes to thier installation, then you are pretty much forced into some sort of automated diff.

In that case, what you need IMO is a diff tool that you can package with your installation. Include a snapshot (sorry blatant RedGate verbiage) with your installation and a tool that can apply the delta. This could work in a rollback scenario as well if your tool can take a snapshot of the current install before the "upgrade".

I realise that the most complete way is to use a diff tool, however,
I'm talking about database releases as part of a packaged product, we
won't necessarily have access to the SQL server or database, the
upgrade process is to be wrapped up in a MSI installation package and
as such I want a way to perform a verification against a version
number before upgrading the database. So that's why I thought the
check sum idea was a good way of handling it.

Then there is need to package upgrade scripts for all/some earlier
versions in the one upgrade package, we currently handle this by
saying you have to upgrade the database through each version step
(i.e. to upgrade from 1 to 5 you would upgrade to 2, then 3, 4 and
finally 5). I'm wanting to handle this automatically, and am currently
working on a, hopefully, simple XML schema to help manage this.

Cheers

Alex

On 27/07/05, Crowhurst,Christian <christian.crowhurst@...> wrote:
>
> Hi Alex,
> If you have access to a diff tool then I've found the most appropriate way
> of checking that the customer database is the purported version, is to do a
> diff against the version that was originally deployed to the customer site
> and the customers database as at now. All the tools I've tried will then
> give you a nice report detailing what's changed.
>
> As to recoding version number in a database - we use a simple table that
> records major, minor, build and revision number from the build that produced
> that database that is now being deployed.
>
> Christian
> -----Original Message-----
> From: agileDatabases@yahoogroups.com
> [mailto:agileDatabases@yahoogroups.com]On Behalf Of alexweatherall
> Sent: 27 July 2005 14:36
> To: agileDatabases@yahoogroups.com
> Subject: [agileDatabases] Re: Database Versioning
>
> Hi,
>
> Regarding the problem of storing a version number with the database.
> I've looked at a variety of ideas regarding this, from recording a
> version number against each object as an extended property, to a
> single function that reports the current version and is updated in
> sourcesafe everytime we do a release.
>
> The biggest problem I've found is how do I know that a customer's
> production database is actually the version it reports to be - as
> there is nothing to stop (whether through a change control process
> or not) a table being altered, a procedure being added, dropped
> altered etc without updating the version information in that object.
>
> I have been investigating using a checksum value on the procedure
> comments or the tables columns etc to attempt to verify the version
> i.e. store the expected checksum for each object in a table against
> the version number and when checking the version number, run the
> checksum routine against the objects in the database to check they
> match - however this has been fairly complicated, but I think might
> be on the right track with this idea.
>
> Any thoughts?
>
> Thanks
>
> Alex Weatherall
> TeleWare.com
>
>
> --- In agileDatabases@yahoogroups.com, Tim Andersen
> <timander37@y...> wrote:
> > Sadly, I haven't had time to read many threads from
> > this group for a while but this topic caught my
> > attention.
> >
> > I release a development database every week (~550
> > tables) and apply all changes that ~30 developers
> > request for Oracle and for DB2. In addition, I
> > maintain
> > nearly a dozen test databases. As you can imagine,
> > this takes quite a bit of effort. I have developed a
> > few techniques that I would like to share:
> >
> > 1. common view for comparing metadata.
> > This is essential to be able to compare the structures
> > of a database and to see if my Oracle database
> > structure matches my DB2 database structure. Also, I
> > can compare test databases to the release database to
> > see if it contains the correct structure.
> > Additionally I can create a difference report for
> > everyone to easily see the changes. This is
> > absolutely key to be able to verify a database has the
> > structure I expect it to have. with each version, I
> > take a backup of my master database and we keep a list
> > of which application version it corresponds to so I
> > can get back to that point.
> >
> > 2. required data.
> > This might be specific to my case, but another
> > challenge is to implement and distribute required data
> > that our application is dependent upon. I have
> > developed utilities to extract this required data as
> > SQL statements (in correct order for RI) to be able to
> > distribute it to customer and test databases.
> > dbcompare from dbbalance.com is a good tool for
> > analyzing differences in required data. I am
> > currently trying to get approval to improve this
> > utility to be able to make it more automated.
> >
> > 3. data-stamp.
> > this is probably not a very good practice, but it has
> > worked better than I had expected. at the end of each
> > required data script I release, I change a value in a
> > properties table to contain the current database
> > release version. this is displayed on the login page
> > pf the application. users, testers, programmers can
> > all tell me immediately what database release version
> > they have which saves me a lot of investigation and
> > saves me from having to maintain customer database
> > lists and in-house test-database lists.
> >
> > Recently, our development team has started branching
> > code which doesn't play very nice with my version
> > process for database releases. It has been
> > challenging, to say the least.
> >
> > I don't really expect much for replies to this long
> > rant, but the topic has been interesting to me because
> > I have spent long hours trying to think of the best
> > way to control versions.
> >
> > If anyone would like example SQL to create the views I
> > mentioned, please email me directly.
> >
> > Tim
> >
> >
> > --- "Crowhurst,Christian"
> > <christian.crowhurst@e...> wrote:
> >
> > > My pleasure Mark. You've been (and continue to be)
> > > very helpful and supportive. What I didn't mention
> > > is buying DbGhost feels that we've got a personal
> > > consultant on tap for some of the more challenging
> > > db change control issues - but then I didn't want to
> > > sound like some marketing guy!
> > >
> > > I agree that the ROI more than pays for DbGhost but
> > > sometimes it can be hard work persuading the IT
> > > accountants to look past the initial purchase cost.
> > >
> > > Christian
> > >
> > > -----Original Message-----
> > > From: mark baekdal
> > > [mailto:mark.baekdal@i...]
> > > Sent: 11 April 2005 10:35
> > > To: agileDatabases@yahoogroups.com
> > > Subject: RE: [agileDatabases] Database Versioning
> > >
> > >
> > >
> > > Hello Christian.
> > >
> > >
> > >
> > > Only two things to say:
> > >
> > >
> > >
> > > I don't like open source for many reasons but
> > > primarily because I feed my family by writing code
> > > and having others pay for it which is in essence
> > > what all IT programming professionals do.
> > >
> > > I believe in my heart of hearts that the application
> > > is incredibly inexpensive due to the cost savings
> > > made and also in comparison with one creating such a
> > > well tested and supported product. Unfortunately all
> > > RDBMS come with slightly different SQL which make it
> > > impossible to do a totally cross platform system.
> > > Some will disagree with this statement however all
> > > my extensive research has shown that there is always
> > > a caveat to the statement of being cross-platform.
> > >
> > >
> > >
> > > That being said, thank you very much for your
> > > comment - it really does help.
> > >
> > >
> > >
> > >
> > > regards,
> > > Mark Baekdal
> > > http://www.dbghost.com
> > >
> > > http://www.innovartis.co.uk
> > >
> > > +44 (0)208 241 1762
> > > Database change management for SQL Server
> > >
> > >
> > >
> > >
> > > _____
> > >
> > >
> > > From: Crowhurst,Christian
> > > [mailto:christian.crowhurst@e...]
> > > Sent: 11 April 2005 08:33
> > > To: agileDatabases@yahoogroups.com
> > > Subject: RE: [agileDatabases] Database Versioning
> > >
> > >
> > >
> > > Guys,
> > >
> > > I'm one of Mark's (rather happy) customers. I can
> > > honestly say that (so far) this DbGhost does indeed
> > > fill the gap in tool support that a SQL Diff tool
> > > (for example) leaves open. We're dropped using Diff
> > > tools as a means of propagating changes as they just
> > > weren't scalable above more than 2-3 developers.
> > > We're successfully using this product to:
> > >
> > > 1) verify that the database in source control will
> > > deploy to production (and QA in our case). DbGhost
> > > does this by creating a new database from the ddl
>
> > > scripts in source control, diffing this db against
> > > (probably a copy) of the production db to generate a
> > > change script which is then executed to upgrade this
> > > production db
> > >
> > > 2) verify that the database in source control (the
> > > ddl script per object) can be executed to create a
> > > new database without error. This includes both the
> > > schema and "static" data (eg lookup data and
> > > meta-data). This catches those errors, for example,
> > > where a developer has changed the name of a column
> > > but forgot to change a view that referenced that
> > > column
> > >
> > > 3) synch local developer databases with what's in
> > > source control, this is the way in which we perform
> > > a "Get" for our databases. Personally I have this
> > > scheduled to run against my development dbs before I
> > > get into work and once when I'm at lunch. The key
> > > point here is that DBghost uses whatever the ddl
> > > scripts that are in the developer's working
> > > directory which will include those scripts that he's
> > > working on that are not yet checked into sc. Again
> > > ghost works in the same fashion as 2): does a Get
> > > latest, creates a new db from the scripts in the
> > > working directory, does a diff against this db and
> > > the developer's local db to generate a change
> > > script, executes this script to upgrade the target
> > > and then drops the temporary db that ghost used to
> > > do a diff.
> > >
> > >
> > >
> > > Those changes that cannot be accommodated by running
> > > a diff between two databases are handled by custom
> > > scripts. DBGhost allows you to inject custom scripts
> > > that a developer has written. The most common
> > > scenario where you'd write a custom script is for
> > > renames to columns and tables. DBGhost creates the a
> > > temporary db from the ddl scripts in sc and then
> > > executes the custom script against the *target*
> > > database before a compare is made, in the case of a
> > > rename the object is renamed in the target db before
> > > the diff is run. There are other injection points
> > > but this is the most common.
> > >
> > >
> > >
> > > This all means that instead of writing specific
> > > ALTER scripts and checking these into version
> > > control we simply modify the CREATE scripts and let
> > > DbGhost work out what has changed.
> > >
> > > We've been using DbGhost for about 4 months now so
> > > its perhaps early days for seeing that it
> > > accommodates all our requirements but with the
> > > ability to inject custom scripts into the process
> > > I'm pretty confident.
> > >
> > >
> > >
> > > Any way, all that said I'm a big believer in open
> > > source so I'd love to see a project that would
> > > realise similar benefits (sorry Mark). Plus DbGhost
> > > only works for SQL Server, plus its not what I would
> > > say low cost (sorry Mark again).
> > >
> > >
> > >
> > > Christian
> > >
> > > -----Original Message-----
> > > From: mark baekdal
> > > [mailto:mark.baekdal@i...]
> > > Sent: 11 April 2005 07:42
> > > To: agileDatabases@yahoogroups.com
> > > Subject: RE: [agileDatabases] Database Versioning
> > >
> > > Have any of you guys looked at DB Ghost?
> > >
> > >
> > >
> > > It is a tool dedicated to SQL Server change
> > > management.
> > >
> > >
> > >
> > >
> > > regards,
> > > Mark Baekdal
> > > <http://www.dbghost.com> http://www.dbghost.com
> > >
> > > <http://www.innovartis.co.uk>
> > > http://www.innovartis.co.uk
> > >
> > > +44 (0)208 241 1762
> > > Database change management for SQL Server
> > >
> > >
> > >
> > >
> > > _____
> > >
> > >
> > > From: eligolovinsky [mailto:egooli@g...]
> > > Sent: 08 April 2005 18:02
> > > To: agileDatabases@yahoogroups.com
> > > Subject: [agileDatabases] Database Versioning
> > >
> > >
> > >
> > >
> > >
> > > I'm thrilled to have stumbled upon this group.
> > >
> > > I've been working on an open source project for
> > > managing database
> > > versions and I've been discussing it on several
> > > forums, but this
> > > group is actually dedicated to that subject.
> > >
> > > I've scanned through some of the posts here
> > > (especially the Database
> > > Change Management thread that Luis Gois has started
> > > November last
> > > year) and it seems that my asessment that there is
> > > no good-enough
> > > tool for the job is correct.
> > >
> > > I'm trying to get the opinions of as many people as
> > > I can for the
> > > project so that the result will actually become a
> > > helpful tool.
> > >
> > > I've posted some preliminary thoughts on this on a
> > > wiki at
> > > http://www.cmcrossroads.com/cgi-
> > > bin/cmwiki/bin/view.cgi/CM/DaversySpecs
> > >
> > > I'd really like to hear what you think about this.
> > > We might actually
> > > be able to do some good for the XP community and
> > > other developers.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Click here
> > >
> >
> <https://www.mailcontrol.com/sr/wQw0zmjPoHdJTZGyOCrrhg==>>
>
> > > to report this email as spam.
> > >
> > > --- [This E-mail was scanned for viruses for your
> > > protection]
> > >
> > >
> > >
> > >
> > >
> > > This e-mail, and any attachment, is confidential and
> > > is intended only for the use of the individual to
> > > which it is addressed. If you have received it in
> > > error, please delete it from your system, do not use
> > > or disclose the information in any way. The contents
> > > of this message may contain personal views which are
> > > not the views of the ECA Group, unless specifically
> > > stated.
> > >
> > >
> > >
> > >
> > >
> > > _____
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > > * To visit your group on the web, go to:
> > > http://groups.yahoo.com/group/agileDatabases/
> > >
> > >
> http://groups.yahoo.com/group/agileDatabases/
> > >
> > >
> > > * To unsubscribe from this group, send an email to:
> > > agileDatabases-unsubscribe@yahoogroups.com
> > >
> > <mailto:agileDatabases-unsubscribe@yahoogroups.com?
> subject=Unsubscribe>
> > >
> > >
> > >
> > > * Your use of Yahoo! Groups is subject to the Yahoo!
> > > Terms of Service <http://docs.yahoo.com/info/terms/>
> > > .
> > >
> > >
> > > This e-mail, and any attachment, is confidential and
> > > is intended only for the use of the individual to
> > > which it is addressed. If you have received it in
> > > error, please delete it from your system, do not use
> > > or disclose the information in any way. The contents
> > > of this message may contain personal views which are
> > > not the views of the ECA Group, unless specifically
> > > stated.
> > >
> > >
> >
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Small Business - Try our new resources site!
> > http://smallbusiness.yahoo.com/resources/
>
>
>
>
> This e-mail, and any attachment, is confidential and is intended only for
> the use of the individual to which it is addressed. If you have received it
> in error, please delete it from your system, do not use or disclose the
> information in any way. The contents of this message may contain personal
> views which are not the views of the ECA Group, unless specifically stated.
>
>
> ________________________________
> YAHOO! GROUPS LINKS
>
> Visit your group "agileDatabases" on the web.
>
> To unsubscribe from this group, send an email to:
> agileDatabases-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
> To unsubscribe from this group, send an email to:
> agileDatabases-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
> ________________________________
>

Yahoo! Groups Links

Your message has been successfully submitted and would be delivered to recipients shortly.