Trusting Source Control

  • ed.elliott

    SSC Rookie

    Points: 40

    Comments posted to this topic are about the item Trusting Source Control

  • Alex Friedman

    Right there with Babe

    Points: 755

    The problem with relying on source control is that you have to actually enforce check-ins somehow. For example, in the cases described in the article where a DBA does some on-the-spot performance tuning, I'm willing to bet that DBA often will not remember to check-in his changes.

    It would be great if there was some control process that checked who missed check-ins -- but that process would need to know who made the changes and when, and the default trace is being rolled over too fast to rely on (which is why Red Gate's Source Control is useless in my opinion).

    Eventually nothing beats good old DDL triggers writing to some kind of DDL audit table.

  • Ed Elliott

    Ten Centuries

    Points: 1365

    Hey Alex,

    You are right, everything needs to go into source control - if your deployment process means that upgrade scripts are created from source control then it fixes that issue.

    If you only ever deploy from source control then everything must be checked in.

    Ed

  • Greg M. Lucas

    Right there with Babe

    Points: 782

    Alex Friedman (6/30/2015)


    ...It would be great if there was some control process that checked who missed check-ins -- but that process would need to know who made the changes and when....

    There is - RedGate's DLM Dashboard can be used to monitor for source drift. So the team responsible for database change management can get email notifications of changes made to any DB environment.

    Alex Friedman (6/30/2015)


    ...Eventually nothing beats good old DDL triggers writing to some kind of DDL audit table.

    And this is exactly what DLM Dashboard uses under the hood. But DDL triggers on their own will not solve the problem presented in this article.

    The point ED makes is valid. The starting point for good database change management is source control. The next stage is the DBA as gatekeeper who will only release database changes from source control (nu-get packages or checked-in change scripts generated and tested against pre-prod). The DBA can use DLM Dashboard to be able to gain assurance that PROD and PRE-PROD environments are the same and that PROD hasn't changed since the change script was generated and tested against PRE-PROD.

    None of this is possible without having the code in source control in the first place.

    Greg M Lucas
    "Your mind is like a parachute, it has to be open to work" - Frank Zappa

  • Alex Friedman

    Right there with Babe

    Points: 755

    Hmm, Red Gate's DLM dashboard is indeed an interesting development -- but it's still in beta, and does not integrate with source control. It simply compares databases.

    I've actually been doing the same using Red Gate's SQL Compare and my own DDL triggers, but it's nice that there's a proper tool for it now.

    They do list integration with source control in the future on their roadmap, so that will be interesting.

  • mister.magoo

    SSC-Forever

    Points: 47068

    I personally use a custom built ssms addin that flags up databases with changes that have not been committed to source control and can perform that commit.

    I just need to automate that as a windows service or SQL agent job and my life would be stress free.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Ed Elliott

    Ten Centuries

    Points: 1365

    That sounds awesome, are you going to share it?

  • mister.magoo

    SSC-Forever

    Points: 47068

    Ed Elliott (6/30/2015)


    That sounds awesome, are you going to share it?

    Been thinking about it, but not yet.

    I might stick it on github at some point, but it's not production ready yet.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • David Atkinson

    Ten Centuries

    Points: 1128

    Alex Friedman (6/30/2015)


    Hmm, Red Gate's DLM dashboard is indeed an interesting development -- but it's still in beta, and does not integrate with source control. It simply compares databases.

    I've actually been doing the same using Red Gate's SQL Compare and my own DDL triggers, but it's nice that there's a proper tool for it now.

    They do list integration with source control in the future on their roadmap, so that will be interesting.

    Alex- It's actually out of beta now. To get it, you can visit the check-for-updates feature in SQL Compare and it will appear as an option in the installer. It pairs well with SQL Compare or any deployment process as it not only fires alerts when schema changes happen, but it also maintains a comprehensive log of past deployments. It can be downloaded from its product page and is free to use.

    How would you like it to integrate with source control? This is something the team is pondering and we're very open to ideas from the SQL Server community.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good article. But even with source control, keep putting in those comments in the stored procedures.

  • cdesmarais 49673

    SSCommitted

    Points: 1863

    If you are going to have trustworthy source control you should only deploy from source control. I can't overstate how important that is. Backfills never get kept up to date. That can be annoying, especially keeping track of multiple environments. But the payoff is repeatable stable deployments and always knowing the state of your systems. We use git and have automated and manual teamcity deployments triggered off of checkins. Generally we do automated deployments for "code" objects, but still trigger builds manually for schema and no trivial data updates.

  • icastellanos

    SSC Rookie

    Points: 44

    Iwas Bornready (6/30/2015)


    Good article. But even with source control, keep putting in those comments in the stored procedures.

    Absolutely. I was just making that point yesterday that even with source control, having comments in the code (sp's, views, etc.) makes troubleshooting much easier sometimes.

  • Ed Elliott

    Ten Centuries

    Points: 1365

    cdesmarais 49673 (6/30/2015)


    If you are going to have trustworthy source control you should only deploy from source control. I can't overstate how important that is. Backfills never get kept up to date. That can be annoying, especially keeping track of multiple environments. But the payoff is repeatable stable deployments and always knowing the state of your systems. We use git and have automated and manual teamcity deployments triggered off of checkins. Generally we do automated deployments for "code" objects, but still trigger builds manually for schema and no trivial data updates.

    Beautiful!

  • Yet Another DBA

    SSCarpal Tunnel

    Points: 4299

    icastellanos (6/30/2015)


    Iwas Bornready (6/30/2015)


    Good article. But even with source control, keep putting in those comments in the stored procedures.

    Absolutely. I was just making that point yesterday that even with source control, having comments in the code (sp's, views, etc.) makes troubleshooting much easier sometimes.

    Some comments will help, other will be a plain distraction or even misleading. I know some people who only ever put comments in at the top, which isn't too useful when you have many screens of code to go through. I have also seen some of the comments that belong to an early version being changed so that the only way of seeing what the comments are is to go back into the history of the source control system. Too many examples of the comments being wrong to trust them.

  • Ed Elliott

    Ten Centuries

    Points: 1365

    Yet Another DBA (7/2/2015)


    Too many examples of the comments being wrong to trust them.

    This is so true, we have had years and years of bad or incorrect comments, lets try a new approach!

  • Viewing 15 posts - 1 through 15 (of 16 total)

    You must be logged in to reply to this topic. Login to reply