Database Version Control
Database Version Control
I.e. putting all your defintions for tables, views, procedures, functions, assemblies, etc. into f.x. SVN.
What tools do you use - if any?
#dbvcs
I.e. putting all your defintions for tables, views, procedures, functions, assemblies, etc. into f.x. SVN.
What tools do you use - if any?
#dbvcs
Here at work we have our own tools to do the updates and we create scripts matching the release. Thus, for release 1, you'll have Release1_0_0_0.sql on a shared drive and that's where we add the fields and all the relevant scripts.
ReplyDeleteI keep metadata+(only relevant) data extracted to text files (IBExpert command line) under version control (git) and run it whenever the database changes so I have clear commit diffs of what changed when.
ReplyDeleteWe store SQL scrips in CVS repository. There is initial release script (always kept up-to-date) and alters from one release to another. We have "schema version" term to determine which alters need to be run to get desired version
ReplyDeleteI use TMS Data Modeler! ;)
ReplyDeleteI store database skeleton in form of SQL in my git.
ReplyDeleteI'm interested in this one too. I once looked into it and found a few products/projects for db version control, but did not find an obviously dominant player. This was a few years ago, so maybe something has come up since then.
ReplyDeleteOur challenge is that we have 5 developers working on the same DB, from 4 different locations, merging their local development versions back into the central version, and updating their local versions from the central version.
ReplyDelete130 tables
200+ views
70+ functions
320+ procedures
+ triggers, synonyms and assenmblies
In total, the sql files currently take some 4.5Mb+ of space.
So far, I have not found a single tool that allow me to do such syncing by a single click. The tools I have found, appear to be written by people who believe check out/check in is state of the art :/
We currently use db diff tools to sync, but these tools only see differences, from A to B, and doesn't reveal which of the changes that are the most recent, so overwriting a newer version is a frequent event. :(
This thing has potential ...
ReplyDeletehttp://scriptdb.codeplex.com/
We use liquibase. It supports multiple database vendors, preconditions, database refactorings, etc. Works very well so far.
ReplyDeleteUsing own-tool, which persists to a diff-able and human-readable XML file, so it can be stored in the VCS, with full support for merges, blames, etc.
ReplyDeleteLars Fosdal You might find some of Scott Allen's articles of interest. The following link is for the last article in a series, but it's the only one of the series with all previous article links (at the top).
ReplyDeletehttp://odetocode.com/blogs/scott/archive/2008/02/03/versioning-databases-branching-and-merging.aspx
redgate has a product, but I believe it's only for SQL Server databases. http://www.red-gate.com/products/sql-development/sql-source-control/
ReplyDeleteWe do use SQL Server, but the RedGate product seems to be overpriced and underfeatured. Currently, I am battling to get ApexSQL Version to work with the SVN 1.7.8 client. It's an uphill struggle, and the product is slow as molasses.
ReplyDeleteLinas Naginionis Liquibase looks interesting, but isn't the process of hand coding XML files a bit arduous? Do I misunderstand the method by which one uses Liquibase? If not, are there any "clients" to make using it easier?
ReplyDeleteKevin Powick I don't think it's arduous, you just need to get used to it at first. I've also written PsPad extension which enables liquibase commands using editors autocomplete feature. But even if you're writing XML file with plain editor it is not very hard to learn these commands since liquibase has quite good documentation. And many times it is much easier to use liquibase refactoring commands than write custom SQL scripts, e.g. this is how you create a table in liquibase: http://www.liquibase.org/manual/create_table. IMO it is much more readable format than writing SQL script (of course you can use SQL scripts anytime you want in liquibase if you want to).
ReplyDeleteLiquibase looks interesting, but - judging from the questions on StackOverflow, it has some rather deep issues to solve?
ReplyDeletehttp://stackoverflow.com/questions/tagged/liquibase
It is kinda surprising how few and how bad the available tools are. You'd think this was a really common challenge?
ReplyDeleteI recommend you to review dbMaestro TeamWork (http://www.dbmaestro.com) as it is the only tool that combines the version control (check-out/check-in) and generating the deployment scripts.
ReplyDeleteRegarding version control, dbMaestro TeamWork, change policy enforcement is embedded in the database engine, so no need to manually create the change script and save them in a file-based version control. This also allows to manage the content of reference data.
When generating the deployment script, TeamWork is aware of the database dependencies, so the script is created in the correct order of all changes.
In addition you can integrate to change management system and link the change to a task and ask TeamWork to generate only changes part of a task.
TeamWork also enables you to compare A to B to C, so you can have more knowledge regarding the origin of the change and if it should or shouldn't be promoted.
Lars Fosdal I would like to know the tool which doesn't have any issues :) We only had a few issues in some of refactoring commands (one refactoring command failed only on MS SQL Server 2012 IIRC) but these issues can be easily avoided by writing custom SQL scripts.
ReplyDeleteUri Margalit dbMaestro does look very interesting, but I worry when a company refuses to publish pricing and instead asks for prospective customers to "Request a Quote". This is usually code for expensive.
ReplyDeleteJust the other day I eliminated a potential vendor from our search because they could not (would not) articulate their pricing in an e-mail. They insisted I call one of their "Customer Empowerment Agents" (I didn't make that up) to discuss our project. How is our project any of their business? I wasn't even asking for a committed price/quote. I just wanted to know how it was priced (servers, CPUs, cores, users, data volume, etc).
dbMaestro seems to do it's own SCM, without integration to f.x. SVN. The whole site smells of "enterprise ware".
ReplyDeleteI am starting to think that the right tool at the right price could make someone filthy rich in this niche.
ReplyDeleteKevin Powick dbMaestro pricing is based on number of PCs the client is installed on. You can manage any number of databases / schema / objects.
ReplyDeleteWe prefer not to publish our pricing over the internet and we do tell it when asked. Please feal the contact us form http://www.dbmaestro.com/support/contactus.aspx?type=quote and the regional sales will come back and will tell you all the information you want.
Lars Fosdal dbMaestro TeamWork is a commercial product target to enterprise, SMB and small startups.
ReplyDeleteWe have customers with 5 seats as well as customers with more than 100 seats.
If you find it interesting, please let me know and I'll make sure some one to contact you
. or if you would like fill up the form http://www.dbmaestro.com/support/contactus.aspx?type=quote and the relevant person will come back to you
Uri Margalit - I perused what was available through the web site, and it did not impress me. The UX exposed in the videos looked like Delphi 5 stuff. It reeks of having had a drinking straw into the Enterprise Oracle world. I honestly stopped buying software that way at the end of last millennium. If you are not willing to expose your product details in public, then my gut feeling is that it is not worth my time. How many use checkout/checkin model VCSs these days?
ReplyDelete