Integrating Production Hot Fixes into Shared database model

TetonSigTetonSig 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?


  • Why not:

    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
    David Atkinson
    Product Manager
    Redgate Software
  • The problem with 2) is that we may already be working in DEV on future changes to the objects we are deploying to PROD. So auto deploying sp_ReadCustomers from Wednesday's deployment to PROD may mean I overwrite Tuesday's changes to sp_ReadCustomers in the shared DB environment.
  • so here's my latest (last?) attempt:

    (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?
  • I'm not aware of any immediate plans for the kind of functionality that you describe - what's probably best is if you head on over to our Uservoice forum and either create your own suggestion or add your vote to any others there that sound like they would help you out - popular suggestions are much more likely to be investigated by the SQL Source Control team for inclusion in a future release.
    Systems Software Engineer

    Redgate Software

  • Thanks, it's helpful to know where things can be improved!
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.