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.
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.
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.
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”.
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.
Once I click the link button, I’ll get a dialog showing progress and then return to the setup tab.
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.
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.
Inside Git
What’s happened in my VCS? Let’s look in the file system. Here I see my trunk folder.
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
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.
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.
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.”
LikeLike
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.
LikeLike
I’m using SQL Source Control from Redgate as a plugin to SSMS. There is’t a native way to do this in SSMS.
LikeLike
Why “trunk”? That’s a Subversion convention that doesn’t really apply to git.
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
Pingback: Git Push in SQL Source Control | Voice of the DBA
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.
LikeLike
Some are surveys and media, mostly a feeling. However, here are a few items I did find:
https://github.com/sunny256/openhub-repositories/blob/master/graph/relative.svg
http://programmers.stackexchange.com/questions/136079/are-there-any-statistics-that-show-the-popularity-of-git-versus-svn
There aren’t a lot of stats for TFS as it’s closed source. It’s also expensive. However when I teach and survey, it seems most of the people working in Windows exclusively do use TFS for their work. However, since TFS Online allows the use of Git, there’s a bit of a trend towards MS adopting Git as well as TFS, since there’s a call here.
Ultimately I like Git and committing offline, because I work remotely. I can’t always easily get to a server.
LikeLike