We have a number of different SQL Server databases, and multiple people contribute to the schemas with new tables, views, stored procs, etc…
I’d like to monitor these databases and kick off a Continua project that performs unit and integration tests to ensure that nothing has broken after the DB schema changes. If the tests are successful, the schema changes should then be committed to our VCS.
I can do all of these things today, except automatically triggering the project. Any suggestions for how this can be made to happen?
We don’t have any triggers which detect SQL schema changes, although this would be a nice feature to have in the future.
As you have noted, you could use a Periodic Trigger to frequently start a build containing actions to download the SQL Schema. The actions could include the new SQL Package Extract action, or if you have FinalBuilder (and Redgate SQL Compare), you can use the Redgate SQL Compare - Make Scripts action, or use PowerShell.
To detect whether the schema has changed, you could use the FinalBuilder File Compare action to compare with a previous version of the schema (stored on a shared folder). Then use Flow Control actions to decide whether to stop the build, continue the build, or trigger a new build via a Build Completed Trigger.
You could also commit the schema files to a version control repository using FinalBuilder actions or the via a Run Dos Command / Batch File action. If there are changes then a new changset will be created and this can be used to trigger another build via a Repository and Repository Trigger.
Another possibility would be to set up a DDL trigger in SQL Server which writes to file when the schema changes. The file could be picked up by a File Repository and used to trigger a build via a Repository Trigger.