Incremental database update with SQL Compare
professor_k
Posts: 2
Hello,
I’m build engineer in the small software company, and currently I’m investigating ways of automated database deployment for one of our current project.
I heared lot about your toolkit, and after reading some information on you site and some playing with trial version, it seems to be similar to thing I’m looking for.
Our current database is legacy, and it’s poorly designed. So we have to make database refactoring within application that is up and running. Moreover we’d like to have this completely automated – with continuous integration on the development server and one-click deployment to the other stages.
I was supposing to hold scripted version of the database under the source control, and during build process - synchronise dev/test/stage/live/etc. database with current script.
But there are still couple of questions.
E.g. if developer is renaming column – will the application detect that this is rename, not the removal of one column, and then – addition of another? Vice versa? As far as I understand it's impossible to distinguish such cases - and them has to be handled manually.
One more example, as for me - main one. Let’s store colors of something in column A of some table. Imagine following list of operations. Column A was renamed to B. Scripted, checked in. That was written script, that updates all red items to be pink, in the column B. Againg checked in. And then, on some stage that column was renamed again to be C. (I know this sounds quite unrealistic, but this is just example of what I’m afraid of - changes of structure that correlates with manual update scripts).
If then we are going to update database that still has A column, it seems that my solution will deploy all changes to database structure in single batch. We would be able to execute script neither before nor after modification of database structure, only somewhere in between… And this is what seems to me impossible in such solution. If all sources are stored as “CREATE†scripts, not “ALTERâ€, and deployment is committed by creating new database from scratch and then synchronizing between them, it seems to me to be impossible to find out proper sequence of scripts to execute.
That's why I'm asking about incremental database update - when all scripts are stored as "ALTER", except some initial list of "CREATE"s. As far as I discovered, I can easily generate that update scripts with SQL Compare - but can I execute them? Can the application distinguish what was the last script executed on the specific database?
Can you please suggest me, is it possible to use your software for such purposes?
I’m build engineer in the small software company, and currently I’m investigating ways of automated database deployment for one of our current project.
I heared lot about your toolkit, and after reading some information on you site and some playing with trial version, it seems to be similar to thing I’m looking for.
Our current database is legacy, and it’s poorly designed. So we have to make database refactoring within application that is up and running. Moreover we’d like to have this completely automated – with continuous integration on the development server and one-click deployment to the other stages.
I was supposing to hold scripted version of the database under the source control, and during build process - synchronise dev/test/stage/live/etc. database with current script.
But there are still couple of questions.
E.g. if developer is renaming column – will the application detect that this is rename, not the removal of one column, and then – addition of another? Vice versa? As far as I understand it's impossible to distinguish such cases - and them has to be handled manually.
One more example, as for me - main one. Let’s store colors of something in column A of some table. Imagine following list of operations. Column A was renamed to B. Scripted, checked in. That was written script, that updates all red items to be pink, in the column B. Againg checked in. And then, on some stage that column was renamed again to be C. (I know this sounds quite unrealistic, but this is just example of what I’m afraid of - changes of structure that correlates with manual update scripts).
If then we are going to update database that still has A column, it seems that my solution will deploy all changes to database structure in single batch. We would be able to execute script neither before nor after modification of database structure, only somewhere in between… And this is what seems to me impossible in such solution. If all sources are stored as “CREATE†scripts, not “ALTERâ€, and deployment is committed by creating new database from scratch and then synchronizing between them, it seems to me to be impossible to find out proper sequence of scripts to execute.
That's why I'm asking about incremental database update - when all scripts are stored as "ALTER", except some initial list of "CREATE"s. As far as I discovered, I can easily generate that update scripts with SQL Compare - but can I execute them? Can the application distinguish what was the last script executed on the specific database?
Can you please suggest me, is it possible to use your software for such purposes?
Andriy Kvasnytsya
Comments
Well it is quite easy to run SQL Compare from the cmd line that syncs your live database with the source controlled scripts folder. use the /sync arg to have SQL Compare run a sync.
Therefore all you have to do is have your build script pull down the labeled version of the scripts folder and run a sync between that and your live DB.
Currently, I have platform that does all of the above and than some using SQL Compare, TFS, TFS Deployer and Powershell.
Let me know if this helps
BJHop
We have some built in heuristics to identify renamed columns. In most cases this will work, but sometimes it will not. If it cannot identify the renamed column it will drop the old one and create a new one. After this you will need to push over the missing data using SQL Data Compare.