September 13, 2016 at 11:24 am
xsevensinzx (9/13/2016)
dave.farmer (9/13/2016)
xsevensinzx (9/12/2016)Can't you just do that in code documentation? I think that's the biggest hurdle for me on justification. In all my stored procs for example, you have a boilerplate at the top with a history of changes ontop of code comments with change descriptions and dates.
Using source control makes the whole picture so much easier to see. We use TFS here for all code (and have done since the very beginning when I and two others started up the dev team here), so we have change history, work items, changesets etc. across the whole dev department.
A sproc may have a comment in the top saying what changed, who by and if you're lucky, why... but when that change is part of a changeset along with the 3 other sprocs, 2 tables and a view that all changed, and that changeset is associated with a work item that also details the overall piece of work, and also has tasks for the front end and back end changes that were made to achieve it, you have all the information in one place.
Oh, I get that, but you also have project management software that does that for you too. I'll use JIRA in my example because that's what I use. Every change to the database has some type of task assigned to you. That task is tied to a sprint that is tied to a project. If the task is part of a bigger set of tasks, then you have the parent and child hierarchy that shows you all other changes--front and back--that relate to that change.
All of that has nothing to do specifically with VC. Your VC only enhances that tool because you can then have features like what JIRA has and connect your VC to JIRA directly where those same tasks can be used to branch in your VC repository in one location as well attach the task to the repository log itself.
Outside of the added benefit of branching from a project management or task management solution, tracking changes is easily accomplished outside of version control. If you're pure goal is to track changes, then get JIRA. Version Control does track those changes, but the primary goal is to reduce conflict when two or more people are working on the same lines of code.
That's all I'm saying.
Besides merging conflicts, VC systems provide history of changes. This is especially useful when a feature is started in development and then you have to shelf it due to some crisis and then come back to your work a week later and can't recall what you've done so far. With VCS, it is fairly quick to look through all the changes in the project to see what was done on the entire project, not just a single file. Also, if the builds that go to QA are not pulled from a VCS repo then troubles are abound :). [ie: It works on my system! :w00t::-P].
I don't want to get into all the benefits of a VCS, but it is a must tool to use for development projects that has code/scripts written by developers. That includes SQL scripts. Even our Dev Ops are using Git to store all their admin (T-SQL) and automation (powershell) scripts. I would encourage everyone to use one and store ALL code in there.
Remember...
"The only measure of progress is working code in source control."
working being the operative word there.
September 13, 2016 at 11:53 am
xsevensinzx (9/12/2016)
Chiefly Confused (9/12/2016)
Having the schema's and sprocs in a VCS helps immeasurably when you are trying to figure out what, where, why a change was made. Even just knowing who did it can make a big difference when trying to decide if it is code you need to change, or keep.Can't you just do that in code documentation? I think that's the biggest hurdle for me on justification. In all my stored procs for example, you have a boilerplate at the top with a history of changes ontop of code comments with change descriptions and dates. You match that with something like JIRA tasks that also keeps a paper trail of that change, then what exactly is VC doing for you?
Things that happen with the schema are a bit more difficult, but again, there is still that project management software sitting on top that has the log too.
You could do this in a header, but there's no enforcement. People make changes to procs, test them, make more changes, test, fix things, test, and by then they may not update the header. A checkin to a VCS, requiring a comment, is a good way to try and force someone to write a comment at the time the code is working, and it's being checked in. Not at the first attempt to change.
Jira, PM, any system can work, but none of them connect to the time when a change is actually completed in code.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 13, 2016 at 11:54 am
qbrt (9/12/2016)
That brings up a question for Mr. Jones :)..."Why has RedGate steered clear of the DACPAC?"
Fundamental choice made a long time ago. Could have supported DACPAC or used the version they do now in Nuget. We debated it, not sure why we went with nuget.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 13, 2016 at 11:56 am
peter.row (9/13/2016)
Coming predominantly from a software dev' side where we obviously do use a source control system.We don't use an ORM and so what we do for DB stuff is we:
# have all SPs, Views, UDFs in hand written individual files which contain existence checks, drop, create, set permissions.
# We create individual script files for each task that changes schema, migrates data etc... named after the task ID in our task tracking system.
# Part of the build for our product generates an upgrade script that concatenates all the task scripts and all DB object scripts that have been changed since the last release into a single script that contains a version check at the start so it can only run on a database that is version X-1 (i.e. the previous version), the end of the script then updates the version field in the DB. This uses a template script file so in preparation for a new release the template config is reset, the SCS revision number in it is updated to the last revision so it starts including changes from that point on.
The above system is custom written and we've been using it for the last 5 years without issues.
That's the process most people follow, keeping the code in a VCS as it changes. This is essentially what we've tried to formalize at Redgate with the DLM products. Just just written to work with any system, not a specific one.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 13, 2016 at 12:21 pm
Steve Jones - SSC Editor (9/13/2016)
You could do this in a header, but there's no enforcement. People make changes to procs, test them, make more changes, test, fix things, test, and by then they may not update the header. A checkin to a VCS, requiring a comment, is a good way to try and force someone to write a comment at the time the code is working, and it's being checked in. Not at the first attempt to change.
Jira, PM, any system can work, but none of them connect to the time when a change is actually completed in code.
If you're not enforcing proper code standards and styles then you are not committing your code correctly either. Committing in this context means not properly committing your code to SQL Server without the proper documentation, not just the VC.
If you can enforce people to commit and use VC, you should be forcing them to comment and document too. VC usage or not, everyone should be documenting inside their code under the same guidelines and enforcement.
But yeah, I get that a commit message means it's done. If someone completes a task in JIRA. Why would it not be done? Do you frequently run into cases when someone was not done? That they lied to you? 😛
September 13, 2016 at 3:33 pm
Another approach I have seen is used in frameworks such as Yii and laravel is something called a migration.
A migration is a special class containing a deployment and rollback method that changes something in the DB. There is a tracking table so that when a migration is run successfully its name and the deployment timestamp is recorded.
Only migrations created after the last one will deploy unless you specifically tell the system to rollback or redo migrations. Yes, you keep these things in source control but once committed to shared source control the migrations get treated as immutable. Because we use BitBucket which is a git type VCS there are local commits and pushes up to the shared repository.
September 14, 2016 at 1:17 am
funbi (9/13/2016)
xsevensinzx (9/12/2016)Can't you just do that in code documentation? I think that's the biggest hurdle for me on justification. In all my stored procs for example, you have a boilerplate at the top with a history of changes ontop of code comments with change descriptions and dates. You match that with something like JIRA tasks that also keeps a paper trail of that change, then what exactly is VC doing for you?
It's easier to do diffs on versions to see what changed than sift through a pile of comments that may or may not be up-to-date. With TFS you can associate DB changes with a code changeset which references the task so it's very easy to see what changed, when and by whom. It's also easier to roll back certain changes like stored procs & views. In your scenario stored procs would get very cluttered over time if you made multiple changes to a single line etc.
Basically once you take the human element out things become a lot more maintainable 🙂
This a million times this!
Like other comments have said as well over time changes documented in comments this way end up with the comment being many factors larger than the actual T-SQL itself in some cases - that is just silly.
For us the individual DB object script files have a comment at the top briefly saying what its for and in the case of SPs and UDFs what any parameters are for and that's it. If I want to know what happened between releases I just "show log" on the SP/UDF/View script file in question where I can see all the times that it changed and by whom and diff between one commit with any other. This is 100% more reliable than a human remembering to put a comment at the top of a file.
All commits must have the task ID at the start of the commit comment and a brief explanation of the change. Because this is easily logged and accessed then anybody that doesn't add a adequate commit comment gets a good talking to. This is not the same as remembering to put a change at the top of each SP/UDF/view because you are committing a task not a file so it is a more holistic approach.
September 14, 2016 at 12:12 pm
Eric M Russell (9/12/2016)
... Everyone needs to be on the same page reagarding which projects go in which repositories and how the folders and various branches are structured. So, first you need a dedicated VCS manager who is responsible for the overseeing the version control process and repositories; someone who RTFM and can serve as the go-to person for technical questions about how to use the system and providing guidance about how to follow the process. In a small organization this could be project manager, SCRUM master, or just any team member willing to volunteer, but somebody needs to own the overall process.
Absolutely. It does no good if the company says to use VCS, but then it's not enforced.
September 14, 2016 at 1:40 pm
xsevensinzx (9/13/2016)
Steve Jones - SSC Editor (9/13/2016)
You could do this in a header, but there's no enforcement. People make changes to procs, test them, make more changes, test, fix things, test, and by then they may not update the header. A checkin to a VCS, requiring a comment, is a good way to try and force someone to write a comment at the time the code is working, and it's being checked in. Not at the first attempt to change.
Jira, PM, any system can work, but none of them connect to the time when a change is actually completed in code.
If you're not enforcing proper code standards and styles then you are not committing your code correctly either. Committing in this context means not properly committing your code to SQL Server without the proper documentation, not just the VC.
If you can enforce people to commit and use VC, you should be forcing them to comment and document too. VC usage or not, everyone should be documenting inside their code under the same guidelines and enforcement.
But yeah, I get that a commit message means it's done. If someone completes a task in JIRA. Why would it not be done? Do you frequently run into cases when someone was not done? That they lied to you? 😛
This was baked into our (non-date) developer review criteria years ago, so when it was mandated that all data changes had to use VCS - it automagically was incorporated into the data dev profile as well. They weren't happy that they lost their access past dev, but that feeling passes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply