Problem
Within the last 15+ years of working as an MSSQL DBA and Developer for small, medium and large corporations the simple task of working as a team on the same T-SQL code like procedures, functions and scripts has always been a challenge. In this tip I will show how you can easily integrate source control to help developers share their code and integrate into SSMS.
Solution
In this tip I will show how source control (I will be using Microsoft Team Foundation Server 2012) can be integrated and used to share code development in SQL Server Management Studio (SSMS). There are many source control applications available on the market and this tip will only focus on the Microsoft Team Foundation Server product.
First of all, we need to have a corporate Source Control System in place. In my example, I will use Microsoft Team Foundation Server 2012, but there are also other options. Please note, this tip does not intend to explain what a source control is and how to it works.
My starting point is a folder created by the Source Control Admin that is used to store our code. This is just a Windows folder that is under source control.
Adding Source Control Plug-in to SSMS
To integrate into SSMS, click on Tools > Options… and select Source Control as shown below to see if source control has been setup.
As we can see from the image above, no Source Control Plug-in has been installed in SSMS.
Step 1: Install Source Control Plug-in
The plug-in depends on the type of source control used. In my case I have downloaded the Team Foundation Server 2012 plug-in from Microsoft. To install it, double click on the .msi file and follow the instructions.
Step 2: Post Installation Step
In order to add the newly installed plug-in, in SSMS click on Tools > Options…
Under Source Control, select the newly installed Team Foundation Server MSSCCI Provider plug-in from the dropdown.
Step 3: Open a T-SQL File
There are essentially two ways to open a T-SQL file that has its access managed by a source control application. The first way consists of using a source control free GUI like Eclipse or Visual Studio 2012 Shell Integrated tool to check in and out code. The second, is to use SSMS. This tip will focus on the first method (using Visual Studio 2012 Shell Integrated) while the next tip will show full SSMS source control integration.
When installing Visual Studio 2012 Shell Integrated on your local machine, you will have to map the Source Control Folder (access to this folder is given by the Source Control Admin) to a local folder on your local box. Please note that all files stored on your local folder mapped to the Source Control Server are read-only by default.
The below picture shows the local folder c:\mySpace mapped to the Source Control folder.
First, let’s open a T-SQL file from this source control folder using SSMS to see what happens.
If we open the file in SSMS we can see a lock icon that indicates that the file is open in read-only mode.
If we try to save this file in SSMS, we get the following warning.
In order to modify our script we need to check the file out from Source Control and open it again using SSMS. Please note that the following screenshots are from Visual Studio 2012 Shell Integrated that is used as front end GUI to manage (check-in / check-out) the code. Please note that you check out the file for edit using the Source Control GUI, however once the file has been checked out it has to be opened using SSMS to make edits.
To check out a file, right click on it and select Check Out for Edit.
Once the file has been checked-out, we can modify it. Please note that while the file is in a check-out status, other developers will have read-only access to the file.
Step 4: Check-in T-SQL File
Once the file has been modified and saved, the check-in procedure takes place using the Source Control GUI by using the Check In Pending Changes option.
The check-in procedure requires a comment to be created. Such comments are used to keep track of changes made to the script.
Step 5: Check Change History
To see what changes have been made to the T-SQL file, click View History from the Source Control GUI.
The screenshot below shows, the last 3 changes applied to this file.
Conclusion
The main advantages of using Source Control are:
- Share code development
- Track history of changes
- Ability to restore code for a specific change in time
- Automatically create a backup by the Source Control Application
Next Steps
Check out these links to learn more:
