Programmable Object update causes error in shadow DB for drift analysis
Hi,
I'm having trouble with a TFS build creating a Preview Report and Drift Analysis Report - the creation of the shadow database to drive these fails. My situation is this:
1. ReadyRoll (1.14.12.4663) project in Visual Studio (2015 update 3) with Programmable Objects enabled. Semantic versioning also enabled.
2. Update a table in a new migration to add a new column.
3. Update a stored procedure to make use of the new column - Programmable Object file for this sproc updated.
4. Local target DB and ReadyRoll project nicely in sync after these changes.
5. Check in changes to TFS and kick off a build - this has TargetServer and ShadowServer properties defined, so attempts to generate a Preview Report and a Drift Analysis Report.
6. Build creates patch which correctly identifies the new migration and updated stored procedure.
7. Build starts creating Preview Report - to do this it appears to try to re-deploy the shadow database at the previous version - it deploys all the migrations and Programmable Objects up to the previous version BUT it also tries to deploy the updated Stored Procedure which is referencing the new column which has not been migrated into the shadow database! Consequently the shadow database deploy fails in adding the altered stored procedure with an 'Invalid Column name...' error.
Of course, I can disable the Preview Report and Drift Report generation with appropriate parameters to the build, but I'm concerned I might be mis-understanding something about how ReadyRoll works - this is my first experience of using it for real.
From my reading so far, my scenario seems normal - add a column and update a stored procedure. Could anyone comment on this? I would like to keep the generation of drift analysis reports in the build, if possible.
Thanks,
Steve.
I'm having trouble with a TFS build creating a Preview Report and Drift Analysis Report - the creation of the shadow database to drive these fails. My situation is this:
1. ReadyRoll (1.14.12.4663) project in Visual Studio (2015 update 3) with Programmable Objects enabled. Semantic versioning also enabled.
2. Update a table in a new migration to add a new column.
3. Update a stored procedure to make use of the new column - Programmable Object file for this sproc updated.
4. Local target DB and ReadyRoll project nicely in sync after these changes.
5. Check in changes to TFS and kick off a build - this has TargetServer and ShadowServer properties defined, so attempts to generate a Preview Report and a Drift Analysis Report.
6. Build creates patch which correctly identifies the new migration and updated stored procedure.
7. Build starts creating Preview Report - to do this it appears to try to re-deploy the shadow database at the previous version - it deploys all the migrations and Programmable Objects up to the previous version BUT it also tries to deploy the updated Stored Procedure which is referencing the new column which has not been migrated into the shadow database! Consequently the shadow database deploy fails in adding the altered stored procedure with an 'Invalid Column name...' error.
Of course, I can disable the Preview Report and Drift Report generation with appropriate parameters to the build, but I'm concerned I might be mis-understanding something about how ReadyRoll works - this is my first experience of using it for real.
From my reading so far, my scenario seems normal - add a column and update a stored procedure. Could anyone comment on this? I would like to keep the generation of drift analysis reports in the build, if possible.
Thanks,
Steve.
Tagged:
Comments
Thanks for reporting this issue. I can confirm that this is a bug that occurs as a result of a limitation in the way that programmable objects are handled during deployment preview and drift correction.
We are currently working on a fix for this and I will post here when there is an update. Incidentally would you be open to trying a hotfix when it becomes available?
Product Manager
Redgate Software
Many thanks for the update and confirming it wasn't me misunderstanding something!
I'd be more than happy to try a hotfix.
Best Regards,
Steve
A quick update: the team plans to resolve this issue in stages.
A fix for the deployment preview reports was released today in ReadyRoll 1.14.17. Upon upgrading, the fix will take effect once the first deployment with this version has been performed: a snapshot of the project schema will be stored within the database, which will then be used by ReadyRoll to generate subsequent previews reports.
A fix for the drift analysis report will be shipped in a later release. It will also utilise the schema snapshot as part of the report (and resync script) generation process. So upgrading to 1.14.17 will help your target environments get ready for the later fix as well.
I'll provide a further update once the release is available.
Product Manager
Redgate Software
Thanks for the update and work on this.
I'll keep an eye out for the drift analysis fix!
Regards,
Steve.
A further update: a fix for the drift analysis report has just shipped today in ReadyRoll 1.14.18.
As with the deploy preview fix in the previous release, upon upgrading your build agent, the fix for the drift report will take effect once the first deployment has been performed: a snapshot of the project schema will be stored within the database, which will then be used by ReadyRoll to generate subsequent previews reports (prior to the first deployment with this version of ReadyRoll, the current error will continue to be logged, except it will be reported as a warning that will no longer cause build failure).
Any questions or further issues please let me know.
Product Manager
Redgate Software