The topic for this month’s T-SQL Tuesday #177 hosted by Mala Mahadevan (Blog) is regarding how we manage our database repository. Read on to see my journey of using different repositories and source control for my code.
What is Version Control / Source Control / Repository?
I use 3 different terms above but they’re all fundamentally the same thing – Version Control Systems (VCS). They keep track of your database code and changes to it by yourself or another developer. It’s a central “source of truth” for your stored procedure definitions, your table schema, UDFs, etc. It’s where all the code lives and is managed. It is a protected place where authorized changes to the code are chronicled.
Why Version Control is Necessary
Using source control for your T-SQL code is something that you better grasp as you go along your development career working with other developers. When you’re in a college dorm hacking away or isolated on a passion project, you may not see the necessity of having your database code all nicely stored in a central location with metadata categorizing it along with your changes. Once you start working on a team of developers you quickly see the pitfalls of not using some kind of database code repository.
Pitfalls of Not Using a Repository / Source Control / Version Control
As you grow your software project and collaborate with other developers, there are pain points which arise:
- How do we ensure that all developers are working with the same good copy of the code?
- How do we handle changes to the code?
- How can we
blameaudit and track changes of who did what to what and when? - Where do we centralize the storage and backup of this critical data i.e. our code?
- How do we bundle and deploy the code that we have into some dev/test/qa/staging/prod environment?
Some specific stories I can tell during my career of the perils of not using a VCS or database repository are:
- Many years ago I worked at a place that wanted to use SharePoint as our repository for SQL and SSRS RDL files. While SharePoint does advertise and offer some level of document version control and collaborative features, it isn’t appropriate for a development team.
- With older VCS systems that didn’t work like Git in the sense that the whole thing is decentralized and shared, you’d have one developer with a totally different set of code than another. Good luck merging that mess together especially if they cannot see the conflicts.
- Rollbacks to a serious bug become a quest of searching on someone’s desktop to find their committed change and the previous state which worked before a change was introduced.
What Repositories Have I Used
The first source control I can recall using is Subversion. There was also some product that HP had but I forget it’s name. Following that was Visual Source Safe and then TFS (Team Foundation Server) which became the modern Azure DevOps we have today. TFS is where most of my working experience lies. Currently I use Git.
Conclusion
So there you have it. Source control is your friend. You should have a repository of your code which constructs your database. If you have any questions let me know in the comments!
Thanks for reading!