June 29, 2015 at 9:35 pm
Comments posted to this topic are about the item Trusting Source Control
June 30, 2015 at 1:20 am
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.
June 30, 2015 at 1:24 am
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
June 30, 2015 at 2:14 am
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
June 30, 2015 at 2:53 am
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.
June 30, 2015 at 4:15 am
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);June 30, 2015 at 4:18 am
That sounds awesome, are you going to share it?
June 30, 2015 at 4:50 am
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);June 30, 2015 at 5:46 am
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.
June 30, 2015 at 7:20 am
Good article. But even with source control, keep putting in those comments in the stored procedures.
June 30, 2015 at 12:57 pm
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.
June 30, 2015 at 12:59 pm
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.
June 30, 2015 at 1:03 pm
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!
July 2, 2015 at 12:36 am
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.
July 2, 2015 at 12:39 am
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 15 total)
You must be logged in to reply to this topic. Login to reply