Erik Porter (gravatar)

Keeping a Database in sync

Friday, March 19, 2004
Filed under

I was thinking about developing a little (or maybe not so little, haven't thought it all through yet) app to show you the differences between two databases and allow you to then sync those differences.  The reason being, is that we usually have a Development Database and a Production Database.  Currently, we just keep track of the changes we make to the development db and once we upload the new version of the app using it, we change the database on production manually to match the development db.  It's a pain and easy to screw up or forget something, so I was thinking about writing a DBDiff or something like that.

Before doing that however, I want to make sure there's nothing out there already that rocks and would be ready to go for us for cheap.  One of our main problems is that our development server sits internally on our network and our production server sits somewhere else on the internet and we can't run an app that can look at them both simultaneously.

Has anyone seen anything out there that would help with synchronizing databases across networks (i.e. build a schema file, then compare another db to the schema file, etc)?  If not, maybe I could write an initial version, then stick it up on GDN Workspaces and have it expanded by myself and others.

Just curious!  :)

20 Comments

  • http:// (gravatar)

    http:// said
    March 19, 2004

    I like SQLDiff from Apex Software. http://www.apexsql.com/ It only does MS SQL Server, though. Mike

  • I'd love something like that, I'd be glad to help out in anyway if you started writing one.

  • The tool I use for our CMS has in its DAL a complete call logging system. (It's stored procedure/VB6/C++/ADO/XML based). So when a developer changes something in a website definition on the development box, the calls to the stored proc api are recorded. When the developer is done, the tool allows the developer to export 'changes', which in fact exports the calls to XML. Another tool imports that XML file and can 'replay' the logged change calls. All kinds of checks are build in of course so you don't run a call twice. Works pretty well. :) Requires some effort though to get it right. The problem is often that meta data which describes the semantic value of other data is stored inside the same database/catalog. This means that if you change metadata (like you alter the type of an item from '5' to '6' on the dev box) on the devbox, it's very hard to propagate that change to a production box.

  • http:// (gravatar)

    http:// said
    March 19, 2004

    We've been using SqlDelta ( http://www.sqldelta.com/ ) and are pretty happy about it.

  • Another tool which compares two MS SQL db's: http://www.adeptsql.com/index.htm We use this at work and it's been helpful. I'd be happy to see what you come up with, though. These tools aren't cheap.

  • Jon, very true. All these tools look great, but a little pricey (rightfully so, but still). I may play around this weekend with some ideas. If I come up with something of any use I may start up a workspace and see how much interest/ideas/work can be thought up and see if it would be a worthy cause or not. I'll blog about it of course if I do. Thanks everyone for your comments and links! :)

  • I use and love Red Gate's SQL Compare. It's new version is really speedy and makes updating a database a breeze.

  • Hey Ryan! :) Thanks for pointing SQL Compare out...looks great, I'll definitely have to give it a try. I'm just wondering if it would be easy to write a tool that covers 75% of people's needs that could be a free tool...anywho, thanks for point it out (and Hurricane Blog)

  • There is an open source project made in vb.net for this on SourceForge: http://sourceforge.net/projects/dabcos/

  • Count me in Erik. I've got some good database abstraction code. It shouldn't be too hard with the schema and using standard SQL to update.

  • Schema changes only? Or data too? Lookup data and the like can often get out of sync. I did something similar to this in VB6 (using DMO) at my last employer. Haven't had the chance to move any of the ideas over to .NET though. And I'm working with Oracle at the moment. If you have DBAs then this sort of thing is often not looked favourably upon :)

  • I actually started working on something after this post...it's still not that great, but yah, I used DMO as well. I maybe put up the code if I get it working well. It's schema only, no data checking. Supports comparing servers on different networks other stuff

  • I'll be keeping an eye on this too then :)

  • I'll let you know where you'll have the most problems, right now... just so you know... Adding a column, if it's not at the end of the list. My advise? Don't even try it. Always add new columns at the end. If you don't, then you have to create a whole new table, extract the data from the original (we used BCP), add the new table, copy the data back in, and remove the old one. Especially a pain in the A, if you've got constraints, fks etc. Biggest mistake I ever made in my development career was supporting adding columns in the middle of a table. And for what? Field order isn't important anyway. It caused us all sorts of issues.

  • :o Haven't gotten there yet, so thanks so much for warning me about that! I'll keep that in mind...

  • If you get to it, let me know and I'll drop you the code (VB6 though). recipher at hotmail dot com.

  • http:// (gravatar)

    http:// said
    May 12, 2004

    Can i get the VB6 code Johnny Hall??

  • http:// (gravatar)

    http:// said
    May 12, 2004

    vbguru613@hotmail.com

  • Came across this discussion through a Google search. Did you ever find a good solution?

    I found these two pages that talk about using a Windows based app called SQLyog. I am not sure if it is still free though.

    http://www.sitepoint.com/article/mysql-data-sqlyog-job-agent

    http://www.databasejournal.com/features/mysql/article.php/1584401

  • http:// (gravatar)

    http:// said
    January 27, 2008

    hello, I was wondering if you guys came up with a solution for this. Please let me know by email me at amit@georgiaaqaurium.org

    thanks

Your Information
Mrs. Gravatar (gravatar)

<-- It's a gravatar

your comment