September 11, 2016 at 11:49 am
Comments posted to this topic are about the item Ignoring Database Version Control
September 12, 2016 at 12:54 am
However, the reason I think version control is important is that database development is often disconnected between the database and application. Changes might occur in the database long before they are used in code, or they may need to be undone in development, perhaps in a destructive way that isn't possible in production. The history of changes, and understanding of what needs to be changed, is much easier with a record in a VCS.
Actually, I kind of disagree with that statement. Not because it's false, but not really a good statement that hits true to why version control is important to databases as they are to applications.
The reason I say that is because I feel the reasons are pretty simple. History of change can happen outside of version control. Version control aims to reduce the conflict we always run into when two or more members work on the same lines of code. It also aims to provide you with better avenues to rollback or roll forward change-by-change without having to do more than you want with full or incremental backups of said code. This applies to database, applications and even word documents.
The counter is that you're a one-man shop or have a small team where conflict rarely if never happens. Then maybe version control does not make sense or sell you entirely on adoption. However, having that ability to easily roll backwards or fowards without having to constantly backup after every change applies to all, but not as much to databases when data is involved because of its weight.
September 12, 2016 at 3:40 am
Experience has taught me that any development discipline must be practised continually, otherwise the discipline will be lost. Using source control is one of these disciplines.
I have adopted a couple of disciplines with regard to source code commits on the recommendation of a developer
There are short cuts you can take when you are a one man band or in a very small team that you may get away with for a while. As the team grows these short-cuts will start to cause problems and lead to technical debt.
If the approach to disciplines is sloppy then when the need for tighter disciplines arises it won't be just you that needs to pull your socks up, it will be the entire team and therefore you will be facing "cultural change". Cultural change is not a 10 minute fix and can be very painful for all sorts of reasons.
There is a reason that short-cuts are called short-cuts and not called "the main route".
Using source control won't hurt you but failing to use it might.
September 12, 2016 at 7:09 am
Even when a VCS has been installed within the organization, I guess some teams are reluctant to checkin code, because they are not sure where exactly where to checkin or even how to use the system. 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.
Also, remember that the VCS itself is essentially database, and it needs to be included in backups just like any other database. Your VCS essentially contains the family jewels. In a previous job, the VCS was on-premises but managed outside the DBA team, and they weren't performing backups. Then one day the SAN partially crashed and the repository databases were lost. It was an epic disaster. Trust me, you don't want to go there. Again, it helps if someone owns the process and insures such sageguards are in place while other team members focus on the job of writing and checking-in code.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 12, 2016 at 10:12 am
Steve Jones - SSC Editor (9/11/2016)
...Don't spend your time maintaining software scripts when you need to be developing other software...
That is done far too often.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
September 12, 2016 at 2:13 pm
We swear by it. We used to use sourcesafe but now use subversion. How quick and easy it is to see what took place in the history of a piece of code. I can't imagine not using version control.
September 12, 2016 at 3:27 pm
We use TFS here in my shop. The web developers had been using it for several years and finally this year the database team set up and started using TFS. True it wasn't simple. It took about 3 weeks work for all the database schemas and sproc's to be pulled into TFS. Then there was time needed to train everyone to use it (even longer to get used to using it). But finally we are rolling smoothly.
Probably the biggest factor was bridging the gap between "why do I have to do this, they should to if for me" and "I'm too busy to worry about this now, I go work to do." (which means never getting to it). Now, We (both Dev's and DBAs') are working together to help each other keep the changes up to date.
September 12, 2016 at 3:33 pm
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.
September 12, 2016 at 4:04 pm
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.
September 12, 2016 at 4:12 pm
hmmmm, read a long time ago...can't recall who to attribute it to....
"The only measure of progress is working code in source control."
I've been managing all my coding projects with this philosophy since I read that. And yes, I have this quote posted on my wall. This approach is fantastic managing code progress and being able to envision what has been done and what else needs to get done by the team. All source *pun intended* of truth is always in the source control. If it is not there, then it is on the developers shoulders to get it in there properly. This includes all SQL scripts. In our case database projects (SSDT or DACPAC) in Visual Studio. Distributed source control tool is a must. For us it is: Git. Yes, there is a learning curve. But that's why devs make the big bucks, right? 😛
This is doubly enforced on projects that are outsourced. It is the only true way I can confirm how the project is advancing. Being able to clone, build and test a repo at anytime cannot be underestimated.
Also, using pull requests for code review is awesome as well. Especially for database (SQL) changes.
That brings up a question for Mr. Jones :)...
"Why has RedGate steered clear of the DACPAC?"
September 12, 2016 at 4:22 pm
Yes, and sometimes the comments in that "Boilerplate" comment section entails many times the size of the the working code. More importantly, it provides a "paper trail" when a sproc gets deleted. Yes, old no longer functional, sproc's can, and sometimes should, get deleted. Previously, an old sproc was renamed "x_old name here_date deleted_who did it" and we had hundreds of "X'ed" out sprocs in the databases. Now it is easy to find the working sproc's and look back to find the reason.
Way back when I started working with code and data the common way to handle this was to take a "snapshot" of everything, code, sprocs, schema, and yes the data. I think that this is better. I may be wrong, but that is my opinion.
September 13, 2016 at 1:53 am
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.
September 13, 2016 at 4:53 am
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 🙂
September 13, 2016 at 5:07 am
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.
You can see when it was done, who did it, who did the other aspects, the large-scale feature, the small scale tasks etc. that your change is part of as well as not just who did the work on your sproc, but who worked on the other items, and who owns the feature and asked for the work and why they needed it.
My database doesn't exist in isolation, it works with 20 other databases, a large codebase and delivers 20 or so websites, manages data imports and exports to and from customers etc. Having all the information relating to a change is essential in bug-fixing, tracking and in understanding the architecture for future development.
Not only that, but at the end of a sprint TFS generates a change script to apply to QA (we prepare QA as a copy of the live environment first), and that script once fully tested, corrected if necessary with additional TFS-generated scripts and passed for release then gets applied to the other environments (UAT etc.) up to and including the live deployment.
That way we work on individual items in development, but we have no manual intervention (and hence no ability to introduce any extra errors) in the TFS-generated deployment scripts, and the script that is tested is the script that gets run on live - again eliminating all manual steps along the way where we could introduce new errors.
I set this up because we were a small team, and there was just me doing DB work initially. My previous job had a team of development DBAs collating changes and manually producing scripts to send to the team of production DBAs for eventual deployment - I didn't have time to do all that, so I looked for a way to automate the deployment process while ensuring maximum error-elimination.
The benefits of source control became apparent later, but as a database team of 1 (and for the last 2 or 3 years, 2) source control saved me huge amounts of time, gained in data security, error detection, efficiency, understanding and the ability to see a change at the immediate level (the view, or sproc etc.) or any higher level (the set of DB changes it was part of, the overall task they belonged to and the overarching feature the task fitted into). There really is no downside as far as I can see.
The main arguments against using source control seem to be "we can replicate some of that without using it"... but it's easier and quicker when you do use source control, and it brings massive other benefits too.
September 13, 2016 at 10:37 am
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.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply