Trusting Source Control

Question

Trusting Source Control

ed.elliott

SSC Rookie

Points: 43
More actions
June 29, 2015 at 9:35 pm

#299530

Comments posted to this topic are about the item Trusting Source Control
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]

Alex Friedman Right there with Babe Points: 755 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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: 47174 More actions · Answer 5

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);

Ed Elliott Ten Centuries Points: 1365 More actions · Answer 6

Ed Elliott

Ten Centuries

Points: 1365

June 30, 2015 at 4:18 am

#1809029

That sounds awesome, are you going to share it?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

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);

David Atkinson Ten Centuries Points: 1128 More actions · Answer 8

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 More actions · Answer 9

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

cdesmarais 49673 SSCommitted Points: 1873 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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!

Trusting Source Control

Cookies on SQLServerCentral