How to share redgate database tools settings with Team Foundation Service

In a previous post we’ve seen how to share the SQL Prompt snippet folder to the development team. We’ve used dropbox for sharing and powershell to copy files between the default directory and the new place (changing also the related registry keys). In this post we’ll focus on how to share all the Red-Gate development tools using Team Foundation Server or Team Foundation Service for team sharing (TFService and TFS Express are two free solution).

The requirements are:
- at least a folder, that will be shared to all team members
- the tools must support the customization of the configuration folders
- a script (powershell in the following samples) that can change the configuration folders

Keep in mind that we’re talking about third party tools, plugged in to SQL Server Management Studio. We will speak about:

  • SQL Prompt snippets (we will change snippet folder)
  • SQL Compare filter and project files (we will change filter and project file startup folder, .scp and .scpf files)
  • SQL Data Compare project files (we will change project file startup folder, .sdc files)

We’ll use Team Foundation Service as a Source Control Manager Let’s move deeper step by step:

1) Create a folder into the source control Starting from my demo project $SampleProject, let’s create a folder called SQLTools.

tfservice_SQLTools
Once we execute the checkin operation the new folder will be available for any team member. Each get latest version will get the new folder into the local workspace from the source control.
tfservice_SQLTools_folders

2) Copy of the tool files into the source control Now we can add the files we need into the source control (projects, filters and so on): tfservice_SQLTools_addSC After a checkin, also those files will be syncronized to the team members.

3) Powershell script for configuration folders (you can download here the ps1 file) We’ve got all the things we need to move on. Keep in mind that all the third party tools are working with the default folders now. We need to change those configurations in order to work with the shared version of the projects and filters (and also snippets). The following powershell script does that job (it works only with Red-gate tools). 

The first part of the script reconfigures the snippets folder and copies our own snippets into the new shared folder:
#region PREREQUISITES
#TFS workspace folder
$WorkspacePath = Split-Path $MyInvocation.MyCommand.Definition -Parent
#Snippet destination folder name
$NewSnippetFolder = $WorkspacePath + "\Snippets"
if(!(Test-Path $NewSnippetFolder))
{
    Write-Host "Destination Snippet folder does not exist (" $SnippetFolderName ")" -ForegroundColor "red"
    Read-Host
    Return
}
#endregion

This section changes the pointer into the registry and copies files from default folder to the new one

#region SNIPPETS MANAGEMENT
#ask for the sql prompt folder name
Write-Host
Write-Host "Please enter the name of the SQL Prompt folder located into ProgramFiles/RedGate path" -ForegroundColor "darkcyan"
$SQLPromptFolder = Read-Host
#copies the snippet files into the dropboxfolder
$LocalAppDataSnippetFolder $env:LOCALAPPDATA + "\Red Gate\" + $SQLPromptFolder
if(!(Test-Path $LocalAppDataSnippetFolder))
{
    Write-Host "Snippet folder does not exist, you may not have SQL Prompt installed or you specified a wrong SQL Prompt folder name.." -ForegroundColor "red"
    Read-Host
    Return
}
#snippets are there, so gather the list and copy them all to the new folder (fallback)
$LocalAppDataSnippets = $LocalAppDataSnippetFolder + "\Snippets\*.sqlpromptsnippet"
Write-Host "Copying *.sqlpromptsnippet files from " $LocalAppDataSnippets " to " $NewSnippetFolder ".." -ForegroundColor "gray" -NoNewline
Copy-Item $LocalAppDataSnippets $NewSnippetFolder
Write-Host "Done." -ForegroundColor "green"
#registry property
$SQLpromptRegistryFolder = "HKCU:\Software\Red Gate\" + $SQLPromptFolder
if(!(Test-Path $SQLpromptRegistryFolder) -or !$SQLPromptFolder)
{
    Write-Host "Registry path missing, you may not have Red-gate products installed or you specified a wrong folder name.." -ForegroundColor "red"
    Read-Host
    Return
}
Set-Location $SQLpromptRegistryFolder
Write-Host "Moving to " $SQLpromptRegistryFolder ".." -ForegroundColor "gray"
Write-Host "Changing registry setting for Snippet default folder to " $NewSnippetFolder ".." -ForegroundColor "gray" -NoNewline
Set-ItemProperty . "Snippets Folder" $NewSnippetFolder
Write-Host "Done." -ForegroundColor "green"
#endregion

This section changes the SQL Compare project folder into the registry

#region COMPARE AND FILTERS MANAGEMENT
#destination folder (the same as the Workspace tool folder)
$NewCompareFolder = $WorkspacePath
#ask for the sql compare folder name
Write-Host
Write-Host "Please enter the name of the SQL Compare folder located into ProgramFiles/RedGate path" -ForegroundColor "darkcyan"
$SQLCompareFolder = Read-Host
#registry property
$SQLCompareRegistryFolder = "HKCU:\Software\Red Gate\" + $SQLCompareFolder + "\UI\"
if(!(Test-Path $SQLCompareRegistryFolder) -or !$SQLCompareFolder)
{
    Write-Host "Registry path missing, you may not have Red-gate products installed or you specified a wrong folder name.." -ForegroundColor "red"
    Read-Host
    Return
}
Set-Location $SQLCompareRegistryFolder
Write-Host "Moving to " $SQLCompareRegistryFolder ".." -ForegroundColor "gray"
Write-Host "Changing registry setting for Compare default project folder to " $NewCompareFolder ".." -ForegroundColor "gray" -NoNewline
Set-ItemProperty . "SharedProjectDirectory" $NewCompareFolder
Write-Host "Done." -ForegroundColor "green"
Write-Host "Changing registry setting for Compare Filters default folder to " $NewCompareFolder ".." -ForegroundColor "gray" -NoNewline
Set-ItemProperty . "DefaultFilterFolder" $NewCompareFolder
Write-Host "Done." -ForegroundColor "green"
#endregion

This section changes the SQL Data Compare project folder into the registry

#region DATA COMPARE AND FILTERS MANAGEMENT
#destination folder (the same as the Workspace tool folder)
$NewDataCompareFolder = $WorkspacePath
#ask for the sql data compare folder name
Write-Host
Write-Host "Please enter the name of the SQL Data Compare folder located into ProgramFiles/RedGate path" -ForegroundColor "darkcyan"
$SQLDataCompareFolder = Read-Host
#registry property
$SQLDataCompareRegistryFolder = "HKCU:\Software\Red Gate\" + $SQLDataCompareFolder + "\UI\"
if(!(Test-Path $SQLDataCompareRegistryFolder) -or !$SQLDataCompareFolder)
{
    Write-Host "Registry path missing, you may not have Red-gate products installed or you specified a wrong folder name.." -ForegroundColor "red"
    Read-Host
    Return
}
Set-Location $SQLDataCompareRegistryFolder
Write-Host "Moving to " $SQLDataCompareRegistryFolder ".." -ForegroundColor "gray"
Write-Host "Changing registry setting for Data Compare default project folder to " $NewDataCompareFolder ".." -ForegroundColor "gray" -NoNewline
Set-ItemProperty . "SharedProjectDirectory" $NewDataCompareFolder
Write-Host "Done." -ForegroundColor "green"
Write-Host "Changing registry setting for Data Compare Filters default folder to " $NewDataCompareFolder ".." -ForegroundColor "gray" -NoNewline
Set-ItemProperty . "DefaultFilterFolder" $NewDataCompareFolder
Write-Host "Done." -ForegroundColor "green"
#endregion
Set-Location "C:"
Write-Host "Process completed successfully." -ForegroundColor "yellow"
Write-Host "Restart SQL Server Management Studio and Red-Gate product to apply the changes.." -ForegroundColor "yellow"
 

The powershell script must be shared. A good practice is to put it into the folder that we’re sharing into TFS source control. Now we can check if the script worked for each tool: Compare project and filters Start SQL Compare and open the project pressing the “open project..” button. In this example we expect “C:\TFS_Workspace\SampleProject\SQLTools” as a default folder. tfservice_SQLTools_prjC On the “Tools –> Application Options..” we expect the same folder. tfservice_SQLTools_filter Data compare project Start SQL Compare and open the project pressing the “open project..” button. In this example we expect “C:\TFS_Workspace\SampleProject\SQLTools” as a default folder. tfservice_SQLTools_prjD Snippet manager Start SQL Server Management Studio, open the “SQL Prompt” menu and select “Snippet Manager

tfservice_SQLTools_sni
Pressing the “Locate on Disk” button will open the folder where you read the snippet from. We will expect the /Snippet folder as a source.

Every change (changes on filter file below) on the file we are speaking for will generate a pending change into the Team Explorer (Visual Studio): tfservice_SQLTools_PC

In case we would like to add new files, like with new snippets, we need to manage the addition using the Team Explorer (detected add section). Suppose to add the snippet as described below:
tfservice_SQLTools_sniNew
This operation will create a file with the name PRV.sqlpromptsnippet.
The new file is located into the Detected Add section of the Team Explorer:
tfservice_SQLTools_Det
After the promotion the file can be checked in and it’s available into the source control.

Conclusions Sharing settings to the team is a good practice to follow. With a simple get and a simple script all the team members can be synchronized in a click or two. This is very useful when both new memebers starts to work with us (they get, let’s say, the “welcome kit”) and everyone in the team changes data. Compare products are very important during the deploy process. With the above sample, each project file is reflected to all the developers, and everyone can get always the latest version from source control. In addition everyone can get the useful snippets when writing t-sql. If you follow these kinds of procedures, you will take a great productivity enhancement.

Stay Tuned!
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s