Migration scripts -Transaction support/Static Data changes?
ScottA
Posts: 9 Bronze 2
I've been looking at using the migration script functionality instead of using the seperate structure / data comparison tools as this would help us manage the deployment scripts across developers quite nicely - and also be easier to produce. I've found the following drawbacks however which are stopping me from doing this.
The migration scripts created do not support transactions which means I can't (safely) use them for production updates.
The migration scripts don't seem to pick up on any static data changes checked in to Source control where as I thought they should.
I haven't got as far as checking if the migration scripts use the source control comparison settings, as this would also be needed.
Are the things I've mentioned above planned improvements or am I misunderstanding the purpose behind this feature?
The migration scripts created do not support transactions which means I can't (safely) use them for production updates.
The migration scripts don't seem to pick up on any static data changes checked in to Source control where as I thought they should.
I haven't got as far as checking if the migration scripts use the source control comparison settings, as this would also be needed.
Are the things I've mentioned above planned improvements or am I misunderstanding the purpose behind this feature?
Comments
What do you mean 'manage across developers'?
Migration scripts are wrapped in transactions when they are applied (either via a SQL Compare deployment or 'get latest'). Are you finding that this doesn't work for you?
Migration scripts can currently only cover schema transitions, although there's nothing stopping you putting DML in migration scripts should this be appropriate. Can you describe your scenario?
"I haven't got as far as checking if the migration scripts use the source control comparison settings"
I'm not sure I understand. Migration scripts are SQL scripts that are executed on deployment (or 'get latest'), so I'm not sure how they would use comparison settings.
David Atkinson
Red Gate
Product Manager
Redgate Software
I'm sure the migration scripts when applied by SQL Compare or using Source Control's getLatest would be in a transaction my issue is that all of the production systems we deal with aren't able to be hooked up to our development machines and therefore we need to run the scripts manually on the production servers. We could use migration scripts through SQL compare / source control to perform upgrades internally between DEV and QA but I've found it's best to deploy out to QA with the same scripts / process you would use in production as that way any issues with scripts are also picked up in advance.
As the migration scripts are a seperate item in source control it would be ideal if the scripts created were standalone production ready scripts which also included any data changes checked in to source control. Then I suppose any data migration bits could be added to the end of the script and you're good 2 go. This would be a lot cleaner and easier to manage as it's a tab in the source control tool where as currently I'd need to use the two compare tools to generate seperate scripts and then check them in to a random source control folder which people need to know to look in.
What I meant about the comparison settings is the stuff like 'ignore collations' which you can now set in the comparison.xml file for SQL Source control and also set in the options when using SQL compare.
I can see that the migration script functionality doesn't really fit in with what I'm trying ot do at the moment and we can certainly use the other tools to do this, I would have thought that most people would be in the same boat in regards to not being able to hook up production systems to source control, espeially if they are managing products with a large install base.
Is there an easier way to manage this through your toolset that I'm missing?
Cheers,
Production machines should be updated with diligence. What you would do is to generate a deployment script using SQL Compare, which also automatically picked up the migration scripts, save this out and test this against a staging environment, and if this works, apply the same script to production.
The migration script functionality shouldn't change the way you currently work. It's just a feature that allows you to customize the way Red Gate tools generate the full deployment script.
Product Manager
Redgate Software