A while ago I had discussed with Redgate about how to allow sql source control to create scripts for releases to deploy db changes.
This mostly works, except when there are changes that require more logic to properly handle existing data when certain db object changes are made.
For instance, adding a new column to a table (that has data in it) that is required, but does not have a default.
The current way of handling this is to create a custom script for that change at the time of check in. The problem with this is that it requires you to check in all the changes you have made up to that point, and it basically now requires your prod db to go through steps of the versions of the db you checked into source control. I get why you did it this way, but one of the things that we like about the diff tool and script changes, is that it takes the end result of what we checked in, and makes a script to make those changes happen. This method of custom scripts, makes your prod db go through these “versions” that you checked in. We check in code all the time, and this would be several versions. On top of that, when I want to check in the one table change, I may not have everything else in the db done yet (other sp’s or views that use this table) so I can’t really check in the db “version” I just want to check in these table changes. Then on top of that, requrements may change, and make it so that I don’t need this field at all, or something else that makes me not want the prod DB to ever go to this “version” that I checked in. We would rather it use the end result of the objects we have checked in, compare to prod, and make a script to move the differences.
Well, before I did not have a good idea of what to do about this, but now I do. Not 100% on all the details, but here is the idea.
Instead of allowing a custom script to move a batch of changes, you allow the user to edit a script for a single change. You may even restrict this further to only table changes, and maybe even further to only changes in the population of the temp table that is created (where it normally copies over the data), as that is normally where this issue happens. Anyway, it generates the sql to make the change, then the user will edit the script to put in the correct data, then the app would compare this against the original, and keep track of the differences, as a search and replace sort of thing. Then when the final script is created, apply the search and replace to the new script for that one object change.
Or if you could just allow a more generic set of search and replace elements that will be stored and executed when the script is generated.
For example I am adding a new column with no default that is required, so when the script generates the output, I want to replace this:
INSERT INTO [dbo].[tmprgxxinstalclienttypelku]([instalclienttypeid], [desctext], [actflag], [lastupdatedtm], [lastupdateusername]) SELECT [instalclienttypeid], [desctext], [actflag], [lastupdatedtm], [lastupdateusername] FROM [dbo].[instalclienttype_lku]
DECLARE @codelst VARCHAR(1000)
SET @codelst = 'CANS, CON, CRPES, DS, ES, IS, IB, LAS, M, R, USA, LAA, CA, IA, RFA, EEA, RJBA, VC, VCC, FS, GBL, OTH, ESFE, SYN'
INSERT INTO [dbo].[tmprgxxinstalclienttypelku]
([instalclienttypeid], [desctext], [actflag], [lastupdatedtm],
SELECT ictl.[instalclienttypeid], ictl.[desctext], ictl.[actflag], ictl.[lastupdatedtm],
ictl.[lastupdateusername], ISNULL(psf.value, 'NA')
FROM [dbo].[instalclienttypelku] as ictl
LEFT JOIN dbo.parsestringfn(@codelst, ',') AS psf
ON ictl.instalclienttypeid = psf.order_id
Not sure exactly how doable that is….
Thanks for sharing this idea with us. The team has already started some work on a different approach to solve Migrations that doesn’t require version control information. An early access release of SQL Source Control with this new functionality is available to download here: http://documentation.red-gate.com/display/MV2
Our approach is not quite the way you’ve explained above, but you should be able to handle the ‘adding a new column without a default’ scenario to handle existing data without having to check-in all the changes. The gist of Migrations v2 is that all the migration scripts will be run upfront during deployment. There are quite a few articles explaining this and a fair amount of discussion already going on in our Steering group. You’re welcome to join our discussions here: https://groups.google.com/group/red-gate-migrations
I hope this helps.
SQL Source Control Development Team