10

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?

0

3 Answers 3

Reset to default
1

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.

1
  • In number 4) are you stating that prod will have the changes but dev will not? Your source control should be where you store a particular version. Not the database server itself. It appears your need a hot-fix branch and/or database in your Dev environment. Otherwise I do not see how you can protect against overwriting current changes and fixes that are in progress.
    – SoftwareCarpenter
    Commented Apr 26, 2012 at 18:07
1

Do you have a hot fix branch based off your production mainline? If so then you could merge the hotfix updated back into Dev once they have been applied to your production environment. 

|---Mailine 
V
| 
|------ Hot Fixes (code merged from current prod) | Ready for QA
|  ^    -----QA :
|  |            :
|  |            : QA approved and ready for Prod
|  |          V
^-------Prod    __ Hotfixes approved push back to mainline
-1

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?

1
  • 1
    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.
    – TetonSig
    Commented Jan 30, 2012 at 14:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.