SQL Source Control and Git–Getting Started

It seems as though Git is taking the world by storm as the Version Control System (VCS) of choice. TFS is widely used in the MS world, but Git is growing, Subversion is shrinking, as are most of the other platforms.

As a result, I wanted to do a quick setup using SQL Source Control (SOC) and Git, showing you how this works. SOC supports Git in a few ways, so this is the primary way I’d see most people getting started.

Update: Since this was published, the SQL Source Control team released an updated version (v4.1) with support for Git that allows push/pull within the client. I’ve got an updated post here.

Scenario

Here’s the scenario that I’ll use. I’ve got a database, WindowDemo, that has a few tables, some data, and a few procs. As you can see below this isn’t linked to a VCS.

2015-09-24 16_34_56-Cortana

I want to store my DDL code in c:\git\WindowDemo\trunk. I’ve got that folder created, but it’s empty. I’ll keep related database stuff (docs, scripts, etc) in c:\git\WindowDemo if I need it.

2015-09-24 16_37_36-Photos

Git Setup

The first thing you need to do is get your Git repository setup. There are many ways to do this, but I’ll use the command line because I like doing that. The commands in the various client GUIs will be very similar.

I’m going to set the git repository here at c:\windowdemo to keep all my database stuff in one place. To setup the repository, I run a git init in the command prompt. This initializes my repository.

2015-09-24 16_41_02-Photos

Now I have a git VCS, I need to get code in there.

SQL Source Control Setup

Now I move to SSMS to link my database to the repository. In SSMS, I right click my database and select “link database to source control”.

2015-09-24 16_42_27-Start

This will open the SOC plugin on the setup tab. I’ve filled in the path to the place in the repository I want the code to go. This is the trunk folder. I’ve also selected Git, using the “Custom” selection on the left and Git in the dropdown.

2015-09-24 16_43_54-Link to source control

Once I click the link button, I’ll get a dialog showing progress and then return to the setup tab.

2015-09-24 16_44_15-Start

Notice the balloon near the top. This lets me know the link is active and I have changes in my database that aren’t in the VCS. There’s a pointer to the “Commit changes” tab, so I’ll click that.

2015-09-24 16_48_00-New notification

In the image above, I see I have a number of “new” objects from the perspective of the VCS. I can see the name, and the type of object in the middle. At the bottom, I see the version in my database (highlighted code) on the left and the version in my VCS (blank) on the right.

This is where I commit my changes. I enter a comment at the top and click the “commit” button on the right (not shown). When I do that, I’ll get a clean “commit tab” that shows that my VCS is in sync with my database DDL.

2015-09-24 16_50_18-SQL Source Control - Microsoft SQL Server Management Studio

Inside Git

What’s happened in my VCS? Let’s look in the file system. Here I see my trunk folder.

2015-09-24 16_51_49-Photos

SOC has created a structure for my DDL code and included some meta data. If I look in one of these folders, such as Stored Procedures, I see

2015-09-24 16_58_56-Photos

This is the .SQL code that matches what’s compiled in my database. SOC stores the current CREATE statement for all my objects so that they can easily be examined.

Inside Git, I see a clean status with all my files as committed objects.

2015-09-24 17_02_57-Start

This is what I want. Now I can continue on with database development, tracking all my changes. I’ll look at the flow and tracking changes in another post.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , . Bookmark the permalink.

12 Responses to SQL Source Control and Git–Getting Started

  1. Lori says:

    Thanks for the info! What version of SQL/SSMS are you using? I tried it in SQL 2014 and less and am not seeing the icon for “Link DB to source control.”

    • Eddie says:

      Hi Lori, I think Steve has forgotten to mention (or assumes everyone has it) that this is a function of the RedGate tools and not native to SSMS.

    • way0utwest says:

      I’m using SQL Source Control from Redgate as a plugin to SSMS. There is’t a native way to do this in SSMS.

  2. Why “trunk”? That’s a Subversion convention that doesn’t really apply to git.

    • way0utwest says:

      trunk is a term I’ve used for years. I’ve also had the main branch called master (lately in git), or mainline. There isn’t a standard I’ve seen, and it seems to vary across time and with systems.

    • I think you are asking why the folder was named “trunk”, right? The folder should just be the project / database name. Git handles branches internally – there are no separate folders for each branch – that’s the power of Git.

  3. Jason says:

    To expound on Lori and Brian’s comments, I’d be more interested in seeing a blog / demo / solution that doesn’t rely on a paid 3rd party tool.

    • way0utwest says:

      There isn’t a way to do this in SSMS without a third party tool. There are ways outside of SSMS, using the file system, and there are other third party products, but nothing in SSMS.

    • way0utwest says:

      Ah, I use trunk as I need a place to stick the database code here, separate from somewhere that I have other database stuff. At Redgate, they’ve used a folder called “ScriptsFolder” below the database name.

      So if I want to have a VCS projects called SalesDB, I have this:

      -SalesDB
      |- ScriptsFolder
      |- Test data
      |- etc.

      Scripts folder seems misnamed to me, and I’ve usually put the code in trunk, so that’s what I named it. I could have this as DDL Code or something else, for sure. That does make sense as I will have different branches in there, but perhaps not.

      Part of the issue with Git is that if I branch the db, I can lose control of where the true code is if I switch back and forth between branches and a database. This is because the database can’t just be “refreshed” with new code as a C# project can. I’ve got to maintain state in code.

      I did start experimenting with checking out branches to a separate folder with a separate database to combat this.

  4. Pingback: Git Push in SQL Source Control | Voice of the DBA

  5. What’s your source(s) for your opening statement? I’d be interested in some data to support those claims as I’m keen to promote TFS and GIT.

Comments are closed.