Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Getting started with source control for SQL Server


By:   |   Last Updated: 2017-09-20   |   Comments (2)   |   Related Tips: More > DBA Best Practices

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.

Adding Source Control Plug-in to SSMS

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.

Art4-1.JPGMicrosoft Team Foundation Server Plug-in InstallationMicrosoft Team Foundation Server Plug-in Installation License AgreementMicrosoft Team Foundation Server Plug-in Installation FolderMicrosoft Team Foundation Server Plug-in Confirm InstallationMicrosoft Team Foundation Server Plug-in Installation Complete

Step 2: Post Installation Step

In order to add the newly installed plug-in, in SSMS click on Tools > Options...

Check Plug-in installation in SSMS at Tools then Options

Under Source Control, select the newly installed Team Foundation Server MSSCCI Provider plug-in from the dropdown.

Select the Team Foundation Server Plug-in in SSMS

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.

source control folders mapped in Visual Studio 2012 Shell

First, letís open a T-SQL file from this source control folder using SSMS to see what happens. 

open file from source control folder in SSMS

If we open the file in SSMS we can see a lock icon that indicates that the file is open in read-only mode.

Lock indicate the file is read only in SSMS

 If we try to save this file in SSMS, we get the following warning.

Warnings related to saving a write protected file in SSMS

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.

Check Out a File 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.

Check in Code Changes

The check-in procedure requires a comment to be created. Such comments are used to keep track of changes made to the script.

Adding comments in for the file

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.

Select View History to see the file changes

The screenshot below shows, the last 3 changes applied to this file.

View Change History

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:



Last Updated: 2017-09-20


next webcast button


next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, November 16, 2017 - 7:30:16 PM - chad Back To Top

But Source control option has been deprecated in SSMS 2016, is there another workaround for this?

 

 

*** NOTE *** - If you want vto include code from SQL Server Management Studio (SSMS) in vcyoc post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to removve thve SSMS formatxng.


Monday, September 25, 2017 - 9:21:03 AM - Bob Back To Top

 Has Source Control been removed from SSMS 2017.2 ?

 

 


Learn more about SQL Server tools