I have previously used FlyWay to perform database migrations, and in fact there is even a course on Pluralsight for this.
I am currently generating scripts via Visual Studio Database Projects, but there appear to be a few problems with this. Firstly, it doesn't seem to scale well. It seems ok with a single database, but once you have multiple databases in an environment operating at different versions, there doesn't seem to be a way of knowing what version a database is at, and therefore know which scripts to run to get it up to date. There is also no automated way to get the database up to date by running a batch of the right scripts. I'm also not convinced that the scripts run transactionally. I seem to recall database scripts failing but leaving behind database objects - the only solution of which was to either restore the database from backup and apply a fixed script, or rerun Sql Compare to give me the new diff script. I would have loved to use FlyWay and put the scripts generated by Visual Studio Sql Compare into FlyWay versioned scripts, but that doesn't work, as FlyWay doesn't run the scripts in SqlCmd mode, and therefore the variables generated in the script all fail. Yes, I can go to some effort to modify those scripts, but I'd rather not have to make too many changes to the generated scripts (I am already reviewing those scripts every time it gives the "data may be changed..." error). I will take a look at DbUp. Does anyone know if that can handle SqlCmd mode scripts? On Mon, Nov 2, 2015 at 3:22 PM, Craig van Nieuwkerk <[email protected]> wrote: > I do this as well with projects. It works well if you only have to deploy > to a couple of databases. If you have many databases (>3) then I find the > migrations approach described by Grant works very well. > > On Mon, Nov 2, 2015 at 3:15 PM, David Burstin <[email protected]> > wrote: > >> We use SQL Server projects for patching, version controlled with git. >> >> For schema changes, we run a compare on the project and the dev database >> (assuming that is where the schema changes are) and create an upgrade >> script from that. >> >> For actual data changes to be applied, we create separate scripts in the >> sql project specifically for those. >> >> This seems to work pretty well for us. YMMV. >> >> Cheers >> Dave >> >> On 2 November 2015 at 14:53, Grant Castner <[email protected]> >> wrote: >> >>> Hi Tony, >>> We use dbup (https://dbup.github.io/) - it allows you to create a small >>> visual studio project so that you can track scripts as well as check them >>> in. >>> >>> Cheers, >>> Grant >>> >>> >>> Grant Castner >>> Phone: 0458 770 749 >>> Twitter: https://twitter.com/grantcastner >>> LinkedIn: au.linkedin.com/pub/grant-castner >>> >>> >>> ------------------------------ >>> Date: Mon, 2 Nov 2015 14:48:56 +1100 >>> Subject: Sql Server Patch Scripts >>> From: [email protected] >>> To: [email protected] >>> >>> >>> Hi all, >>> >>> Almost every system I have developed has been against a SQL Server >>> database, and every environment has needed to be patched as greenfields >>> projects introduce new changes. >>> >>> The reality is that I have found providing patch scripts for sql server >>> quite problematic and have never been very happy about what we do to apply >>> patch scripts. >>> >>> I want to know what scripts have been applied to a database, and I also >>> want to know that scripts applied are transactional (that is, if a script >>> "breaks" when applied, nothing has changed.) I also want to be able to add >>> data to be inserted into tables to my scripts. >>> >>> What are people currently doing to solve these issues? >>> >>> Warm regards, >>> Tony >>> >> >> >
