Options
Integrating Production Hot Fixes into Shared database model
TetonSig
Posts: 27
We are using SQL Source Control 3, SQL Compare, SQL Data Compare from RedGate, Mercurial repositories, TeamCity and a set of 4 environments including production.
I am working on getting us to a dedicated environment per developer, but for at least the next 6 months we are stuck with a shared model.
To summarize our current system, we have a DEV SQL server where developers first make changes/additions. They commit their changes through SQL Source Control to a local hgdev repository. When they execute an hg push to the main repository, TeamCity listens for that and then (among other things) pushes hgdev repository to hgrc. Another TeamCity process listens for that and does a pull from hgrc and deploys the latest to a QA SQL Server where regression and integration tests are run. When those are passed a push from hgrc to hgprod occurs. We do a compare of hgprod to our PREPROD SQL Server and generate deployment/rollback scripts for our production release.
Separate from the above we have database Hot Fixes that will need to be applied in between releases. The process there is for our Operations team make changes on the PreProd database, and then after testing, to use SQL Source Control to commit their hot fix changes to hgprod from the PREPROD database, and then do a compare from hgprod to PRODUCTION, create deployment scripts and run them on PRODUCTION.
If we were in a dedicated database per developer model, we could simply automatically push hgprod back to hgdev and merge in the hot fix change (through TeamCity monitoring for hgprod checkins) and then developers would pick it up and merge it to their local repository and database periodically.
However, given that with a shared model the DEV database itself is the source of all changes, this won't work. Pushing hotfixes back to hgdev will show up in SQL Source Control as being different than DEV SQL Server and therefore we need to overwrite the reposistory with the "change" from the DEV SQL Server.
My only workaround so far is to just have OPS assign a developer the hotfix ticket with a script attached and then we run their hotfixes against DEV ourselves to merge them back in.
I'm not happy with that solution. Other than working faster to get to dedicated environment, are they other ways to keep this loop going automatically?
I am working on getting us to a dedicated environment per developer, but for at least the next 6 months we are stuck with a shared model.
To summarize our current system, we have a DEV SQL server where developers first make changes/additions. They commit their changes through SQL Source Control to a local hgdev repository. When they execute an hg push to the main repository, TeamCity listens for that and then (among other things) pushes hgdev repository to hgrc. Another TeamCity process listens for that and does a pull from hgrc and deploys the latest to a QA SQL Server where regression and integration tests are run. When those are passed a push from hgrc to hgprod occurs. We do a compare of hgprod to our PREPROD SQL Server and generate deployment/rollback scripts for our production release.
Separate from the above we have database Hot Fixes that will need to be applied in between releases. The process there is for our Operations team make changes on the PreProd database, and then after testing, to use SQL Source Control to commit their hot fix changes to hgprod from the PREPROD database, and then do a compare from hgprod to PRODUCTION, create deployment scripts and run them on PRODUCTION.
If we were in a dedicated database per developer model, we could simply automatically push hgprod back to hgdev and merge in the hot fix change (through TeamCity monitoring for hgprod checkins) and then developers would pick it up and merge it to their local repository and database periodically.
However, given that with a shared model the DEV database itself is the source of all changes, this won't work. Pushing hotfixes back to hgdev will show up in SQL Source Control as being different than DEV SQL Server and therefore we need to overwrite the reposistory with the "change" from the DEV SQL Server.
My only workaround so far is to just have OPS assign a developer the hotfix ticket with a script attached and then we run their hotfixes against DEV ourselves to merge them back in.
I'm not happy with that solution. Other than working faster to get to dedicated environment, are they other ways to keep this loop going automatically?
Comments
1) push changes from hgprod to hgdev as you suggest 2) Also auto apply the deployment scripts (created between hgprod and PREPROD SQL) to DEV SQL
This should hopefully mean that the 'change' doesn't appear as a difference on the DEV SQL server.
Would this work?
David Atkinson
Red Gate
Product Manager
Redgate Software
(1) Make hot fix in production
(2) Commit through SSC, push change to prod mercurial repository
(3) Terminal Service into shared DEV SQL Server
(4) pull prod repository into dev and merge changes
(5) Go into SSMS where SSC is setup as a dedicated link to mercurial
(6) Do a get latest and
(a) hopefully just push the new hotfix into dev because nobody has changed the objects in the hotfix in DEV since the last release
(b) manually merge the changes in if someone has modified the objects in the hotfix since the last release
(7) commit from SSC, push to hg dev
This is still awfully manual, especially if step 6 (b) is necessary, but it does establish a process and it does work.
What would be GREAT is if SSC included some basic merge functionality!
What's the chance of that anytime soon?
Redgate Software
Voted up and commented on this item
http://redgate.uservoice.com/forums/390 ... bject-inst
Redgate Software