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).
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.
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): 
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).
#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. 
On the “Tools –> Application Options..” we expect the same folder. 
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. 
Snippet manager Start SQL Server Management Studio, open the “SQL Prompt” menu and select “Snippet Manager”
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): 
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.
