Introduction
The growing adoption of Continuous Integration development practice implies that developers have to work in a collaborative manner by storing and sharing their source code into a central repository. For SQL Server Business Intelligence (SQL Server BI) developers, the collaboration is usually made possible by the Team Explorer plug-in for Visual Studio which provides an interface for integrating SSRS, SSIS and SSAS solutions into source control environments such as Team Foundation Version Control (TFVC) or Git. However, several aspects of SQL Server BI solutions involve T-SQL development (i.e. defining of SSRS datasets, SSAS data source views, as well as SSIS source data flow tasks).
For most SQL Server BI developers, SQL Server Management Studio (SSMS) is their preferred IDE for T-SQL development instead of Visual Studio. Unfortunately, the integration of SSMS with source control repositories is often not straight forward and often requires several prerequisite software (i.e. MSSCCI Provider, Visual Studio, Team Explorer) to be installed prior to configuring source control. Furthermore, some of the prerequisite software is not compatible with all versions of SSMS and thus you may discover that the prerequisite software itself is dependent on other programs, as is the case in Figure 1 wherein an installation of MSSCCI Provider was unsuccessful because Team Explorer 2013 was not installed.
Some of the documented frustrations relating to the integration of SSMS with source control are expressed in various professional community platforms including here. Not surprisingly, considering all the hindrances and probable frustrating experience, the integration of SSMS with source control is often not worth the effort and consequently the practice of Continuous Integration as it relates to T-SQL development is sometimes abandoned.
Fortunately for SQL Server developers, ApexSQL Source Control is one product that seems to alleviate most of the aforementioned hindrances through a seamless integration of source control with SSMS. In this article, we take a look at the basic features of ApexSQL Source Control and demonstrate the ease at which it integrates with SSMS.
Challenge
Even if you were able to satisfy all requirements for integrating SSMS with source control, Team Explorer requires you to script database files prior to checking them into source control. Such a practice could be time-consuming and there is a greater chance that some changes could be missed. To demonstrate the challenge at hand, we will attempt to store a T-SQL function script used to replace ASCII special characters into TFVC:
-
Connect to TFVC using the Team menu in SSMS
Figure 2: Connect to Source Control -
Assuming that you have successfully configured a TFS workspace, script the dbo.ReplaceASCII function using SSMS and save it as a file into your TFS workspace as shown in Figure 3
Figure 3: Script Function steps -
In Team Explorer tab, navigate to Pending Changes option and Check In the newly scripted function, as shown in Figure 4
Figure 4: Check-in pending changes
Next time changes are made to the function, you will have to script it out again and check it in using Team Explorer. The obvious challenge with this approach is that once you have altered the function in SQL Server, you may forget to script those changes out so they could be versioned controlled using Team Explorer.
Solution
The ApexSQL Source Control add-in introduces, amongst other things, an IDE called the Action center tab allows you to maintain your source control repository without necessarily having to first script out database objects! Usually the Action center tab will automatically be opened as soon as you finish linking a database into a source control repository but you can still manually open it through keyboard combination keys Ctrl + Alt + A or by right clicking the source control-linked database and navigating to the Action center option as shown in Figure 5. Or from a ApexSQL Source Control toolbar
A preview of the Action center tab is shown in Figure 6, which can be docked and pinned like you would any other SQL Server query window.
In addition to taking away the need to use Team Explorer, the Action center tab allows for the tracking of database changes by simply clicking the Refresh button on the top right corner of the Action center tab. Figure 7 shows a pending change containing a SampleASCIIReplace function that has easily been detected by simply clicking the Refresh button. Looking at the Differences section at the bottom of the window you will notice that the Repository side is blank which means the detected function doesn’t exist on source control repository. To add the detected change into source control repository, developers need to just tick the checkbox next to the function Type and proceed to click the Apply button.
Once you have added an object into source control, ApexSQL Source Control provides additional options that you can utilize to further maintain that object including viewing Object history, Check-in or Undo check out/lock. Some of these options are dependent on the database Development model (Dedicated or Shared).
The Action center tab and all related ApexSQL Source Control options can only appear once you have installed ApexSQL Source Control add-in, and linked a given database into source control. The installation and configuration of ApexSQL Source Control is simple and wizard-based:
-
Link database to source control
The first thing that you need to do after installing ApexSQL Source Control would be to link a database to source control.
Figure 9: Link database to source control -
Specify connection type
The next part involves specifying connection type and choosing source control system – for the purposes of this demonstration, Team Foundation Server will be used
Figure 10: Choose source control system -
Choose development model
You will then be prompted to choose a development model – for the purposes of this demonstration, the Shared development model will be used
Figure 11: Choose development model When prompted, click Yes to create an ApexSQL database, where all Framework objects will be saved. For this demo, the default settings under the Object filtering tab and the Script options tab remained unchanged.
-
Source Control Authentication
The final part involves logging into your source control server and specifying a Team project (this is similar to Manage Connections in Team Explorer shown in Figure 2).
Figure 12: Connect to source control server As soon as you click the Finish button after configuring source control server, you will be redirected to the Action center tab wherein you can start tracking database changes and updating them into a source control repository.
- Continuous integration
- Work in Team Explorer
- Team Foundation Server
- SQL Server Source control – Part I – understanding source control basics
- SQL Server Source control – Part II – Integration
- Integrate source control with SQL Server to reduce database development time
- How to commit database objects to SQL Server database Source control
- Getting a database under version control: How to link a database to source control and initially commit objects
Summary
The ApexSQL Source Control add-in conveniently allows for the integration of SQL Server Management Studio with source control. Through its Action center tab, SQL Server developers can detect and update database changes in real time thereby reinforcing the principle of Continuous Integration.
References
September 25, 2017
