How to Get Started with Version Control for Databases

  • Kendra Little

    SSC Enthusiast

    Points: 117

    Comments posted to this topic are about the item How to Get Started with Version Control for Databases

  • phegedusich

    Ten Centuries

    Points: 1353

    I implemented version control in TFS via SSMS a few years ago. Now I’m struggling with migrating the codebase to the new methods (VS/Github/etc). There is no migration path, so I have to start from scratch and break the version history chain. I understand the need for this evolution, but it would be helpful to have some kind of migration tool/process other than start from scratch.

  • Jason-

    SSCrazy

    Points: 2584

    Version control is one of those basics that if you don't know how it works, how to leverage it, stop any current learning you're doing and focus on this. Depending how much time is available for learning, in two weeks one should be able to pickup SSDT and Git/Azure DevOps. Red-Gate Version Control can be simpler (go dedicated environment) though I can't speak to deployments since other than a training class I have no real world exposure.

    As data professionals we should always follow these two principals:

    • Version Control First - No database object or code will exist in any shared environment that does not first exist in Version Control.
    • Independent Deployability - All database objects and code must be independently deployable to various environments in various states such as development, staging, QA, production, customer sites etc.

    Remember, truly fast, repeatable actions must be smooth. The above two principals will lead us to smooth and as any athlete that has put forth effort to learn and improve can tell you (and my old boss), "Slow is smooth, smooth is fast"

    -

  • skeleton567

    SSCertifiable

    Points: 5047

    In my last position we didn't need change control.

    We weren't allowed to make any changes...

    Rick

    The only thing worse than being an influencer
    is believing one.

  • IowaDave

    SSCommitted

    Points: 1639

    I'm interested to learn if anyone is using Visual Studio and GIT for database version control and if so, how is it working?

  • Rod at work

    SSC-Dedicated

    Points: 33376

    Rick (skeleton567), I like your answer. Probably the majority of our databases are related to vendor applications, so they control the database schema and we're not allowed to change anything.

    But we do have some homegrown apps and databases. Coincidentally I was asking one of the DBAs here if they have any of our homegrown databases under version control. He doesn't believe so, although he is new to his position, so he might not be aware of what's going on concerning version control of our homegrown databases.

    Kendra, I've seen some folks who will try something once. Then if anything goes wrong, they will swear it off and never try it again for the rest of their careers.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Ralph Hightower

    SSCrazy

    Points: 2804

    At my current employer, there has been talk about having the databases in source control for quite a while (years); but that hasn't happened. I support a web application that was written in C# and uses SQL Server with extensive use of stored procedures; there are no embedded SQL statements in the C# code. When analyzing the code in Visual Studio, eventually I track down the code calling a stored procedure; which means that I need to crank up SSMS and locate the stored procedure to see what the code is doing. That process is a major PIA!

    Since the DBAs aren't using version control, I scripted everything in the database as individual files and added the SQL files to the Visual Studio project so I can look at what the stored procedure does from within Visual Studio instead of switching to SSMS. As a side result of scripting the database and adding the files to the project, the database has also been added to source control.

  • roger.plowman

    SSChampion

    Points: 10243

    Call me old fashioned but I'm still using TortoiseSVN tied into both Visual Studio (for the front end) and Windows Explorer for the SQL scripts.

    Which, to be honest is only the working copy. The master copy of the SQL scripts are kept in a SQL database as components assembled by a homebrew script generator. Makes keeping script level execution permissions straight so much simpler! Actually scales pretty well, there's over 2,000 scripts in the inventory. Changes happen to the components in the database, the script generator makes the actual script to create the SP/function along with execution rights, which move to the working directory and are swept up by SVN.

    Belt and suspenders! 🙂

  • skeleton567

    SSCertifiable

    Points: 5047

    Rod at work wrote:

    Rick (skeleton567), I like your answer. Probably the majority of our databases are related to vendor applications, so they control the database schema and we're not allowed to change anything.

    The position I refer to was pretty much the opposite.  The company had originally contracted with Microsoft to provide DBA and developer support for several applications using replication to a nationwide chain of dealers and provide consolidated national account billing and reporting.  I was one of the original in-house DBA's just before the roll-out.

    Nearly all of our database design and application code was custom developed so there was little issue with software vendors.  Once we took the project in-house, there was no outside development.

    As DBA's, we developed hundreds of stored procedures as the back-end of the applications.  In those days we were using Microsoft Visual Source Safe and kept very strict control of schema versions, upgrade scripts, and stored procedure code.  We performed essentially all upgrades to remote servers by logging in and running upgrade scripts ourselves, normally on weekend nights, so we had very tight control over the process.  As I recall, since I had little to do with the front-end, the developers kept control of their own code in their own Visual Source Safe, and put out upgrade packages for the local support folks at the remote locations to apply to their machines.

    So version control was pretty tight.  The issues arose in being able to get needed updates such as bug fixes and performance issues pushed through the QC testers and included in upgrade releases that were controlled by often non-technical Project Managers.  This created problems for version control because we would have code 'checked out' with modifications that never got implemented but might then need to make further modification that would not include our fixes.  Modificationswere often made to development servers which had never been implemented in production so we always had to be able to roll them back and keep track of them manually.

    Rick

    The only thing worse than being an influencer
    is believing one.

  • nepromM

    SSC Enthusiast

    Points: 139

    We have managed to implement a combination of Visual Studio and GIT for the CI/CD process.

    This was performed by using SQL Server Data Tools (SSDT) for Visual Studio.

    The biggest obstacle was importing the databases into Visual Studio (Sql Server Database Projects).

    We have encountered and resolved several issues, including the "complex database project difficulties".

    The problem was described on sqlservercentral, by Hakim Ali, back in 2015. (link to the article)

  • This was removed by the editor as SPAM

  • Rod at work

    SSC-Dedicated

    Points: 33376

    I'm envious, Rick. You're environment sounds like one I'd like to work in. I understand my management's decision, made long before I came here, to adopt lots of vendors software solutions. Millions of Federal dollars are involved, so you can't screw things up. On the other hand, I've help write applications that also handled millions of Federal dollars. Still, I do understand my management's decision.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • skeleton567

    SSCertifiable

    Points: 5047

    Rod at work wrote:

    I'm envious, Rick. You're environment sounds like one I'd like to work in. I understand my management's decision, made long before I came here, to adopt lots of vendors software solutions. Millions of Federal dollars are involved, so you can't screw things up. On the other hand, I've help write applications that also handled millions of Federal dollars. Still, I do understand my management's decision.

    There were definitely good and bad effects.  We had few emergency problems with new releases, but on the other hand had to live with bugs and faults that could have been fixed if the project managers had cared more about end users and validity of data.

     

    Rick

    The only thing worse than being an influencer
    is believing one.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply