Could anyone point me to a good walkthrough of Datadude (Visual Studio Database project) and the do's and dont's, with focus on remote deployment? We've been using it for versioning and updating our own test environment. My question is now, what to do when having to upgrade the customers test and production environment? I've been doing a litte scripting and using vsdbcmd and the dbschema and creating the update script directly on the production environment, but I'm not quite satisfied with this approach. Ideally, from my point of view, I need to package the database upgrade as an 'installer' (or part of an installation, but let's not get into that right now - WiX sigh*), perform the database compare 'on-site', generate update script, invoke and rollback on errors.
So any ideas, good blog posts I've might have missed, walkthroughs I need to read?
Thanks in advance!
/Jasper
I might be wrong, but I don't think there's anything beyond vsdbcmd to achieve this. Is it possible to include vsdbcmd in your WiX installer?
You might consider Red Gate SQL Packager, although this can only read from an actual database source, not a VS database project. It generates a .exe or a C# project that you can integrate into an existing installer. However this doesn't do the comparison 'on site', so probably doesn't meet your requirements.
Your best bet may be to use Red Gate's SQL Comparison SDK to insert code in your installer to do the comparison and deployment 'on site'. You can run the upgrade script in a transaction, so rollback would occur if the script fails (but of course you must run a backup before enagaging in any database update!). Again, the 'source' can't be a VS database project, but you could instead use a schema a schema snapshot, generated using SQL Compare or the free SQL Snapper tool.