Migration Scripts and Build
We are just getting started with the SQL Automation pack and I've been following the tutorial / worked examples in the documentation, using the TeamCity build runners.
I've been able to Build a database package and then Sync it to a blank database, including static data, which is great.
What I've not grasped yet is, how to migration scripts fit into this pattern, if at all?
Currently, we have a number of web-app projects where the databases are under SQL Source Control. We use migration scripts frequently to manage certain changes (such as adding a non-nullable column etc.) and have a manual process for deploying database changes alongside code changes, which we want to automate.
When setting up the "Redgate CI Build" runner, there's only the option for the database repo path, and nothing for the migration scripts folder, which for our projects is always in a seperate SVN "project" (same repo, different trunk from the database itself).
How do migration scripts fit into the Build --> Test --> Sync model? It looks more like snapshotting to me - or am I missing an option?
Do we need to simply work up our own SQLCompare scripts manually to achieve the results we want? Which include:
* Automatically build and integrate changes from source control to integration / development database.
* Create "release" script (or package etc.) from a certain point in SVN, bundling a set of changes.
* Deploy this against our QA database.
* If QA passed, deploy the same package / script against our production database.
I've been able to Build a database package and then Sync it to a blank database, including static data, which is great.
What I've not grasped yet is, how to migration scripts fit into this pattern, if at all?
Currently, we have a number of web-app projects where the databases are under SQL Source Control. We use migration scripts frequently to manage certain changes (such as adding a non-nullable column etc.) and have a manual process for deploying database changes alongside code changes, which we want to automate.
When setting up the "Redgate CI Build" runner, there's only the option for the database repo path, and nothing for the migration scripts folder, which for our projects is always in a seperate SVN "project" (same repo, different trunk from the database itself).
How do migration scripts fit into the Build --> Test --> Sync model? It looks more like snapshotting to me - or am I missing an option?
Do we need to simply work up our own SQLCompare scripts manually to achieve the results we want? Which include:
* Automatically build and integrate changes from source control to integration / development database.
* Create "release" script (or package etc.) from a certain point in SVN, bundling a set of changes.
* Deploy this against our QA database.
* If QA passed, deploy the same package / script against our production database.
Comments
SQL CI/ Team City plugin only supports Migration v2 and not Migrations v1. You can read more about Migrations v2 here.
If you have any more questions then please post on the forum.
Thanks,
Priya
Project Manager
Red Gate Software
For our process, I'm a little unhappy with the deployment pattern - we like to genrate a script which we apply to QA then test. If all is fine, we then apply the same script to Production (which was at the same state as QA was previously), knowing that the exact set of changes in the script have been tested.
The new v2 migrations talks about the "remainder" script being created, and implies that, essentially, every deployment will create a deployment script on the fly, so there's no way to tell exactly what it's going to do on your Prod server?
I'm a product manager at Red Gate. Your post asks a really good question, one that we're working on at the moment. We're working on how to help automate production deployments. Specifically giving the opportunity to review the and 'lock in' the SQL script that will be run during your release process. If you're interested in finding out more, or would be interested in joining our beta program please could you send me a note on jonathan.hickford@red-gate.com?
Regards,
Jon