Once Upon a Time
In a recent article, I described a solution to the joint problem of inability to customize the default location of the template folder for SQL Server Management Studio and sharing of templates across machines or between teams. I invite you to read how I developed that solution before moving onto this article.
I’ll wait…
I received a few inquiries as to why I didn’t just use a symbolic link for synchronization; favoring third party software instead. Rather than write a lengthy reply in the comments section of the original article it seemed appropriate to take some time and consideration into developing a companion article around why symbolic links are not a substitute but how they can accomplish some of the functionality of what we’re attempting to do by sharing templates between team members and multiple devices.
Symbolic Link
Now that you’ve (hopefully) read the source article and have seen the approach I took using third-party software incorporated with a cloud service such as Dropbox or OneDrive I want to show you how you can construct a similar approach using a symbolic link – removing the requirement for using third-party software beyond those cloud services that are vital for the core sharing portion of this solution. I also want to show you why this is not a viable alternative to keeping template folders in sync – but only provides the ability to share out your templates through a cloud sharing service.
We build this solution using a symbolic link to create a shortcut in the local sync folder for your cloud service of choice. Unlike a standard shortcut which is just a pointer to the target you’ve created the shortcut to; a symbolic link will look and behave as though the target folder/file truly exists where the shortcut is located.
That means the key here is to create a symbolic link in the cloud service’s local folder (in this case I use Dropbox but only because that is the cloud share service I’ve subscribed to – any cloud share service should act accordingly.) This symbolic link should point to the template folder for your version of SQL Server Management Studio:
C:\users\
Building the Solution: Part One – Identifying the Target
You’ll notice the ellipsis (…) at the end of that path. Since you have options here let’s explore each of those options and how to configure the symbolic link for your implementation:
OPTION 1: You want to keep all templates in sync with other machines/users including templates for SQL Data Services as well as Analysis Services.
This is the easiest solution as it keeps all templates in sync – both those that you create and any of the Microsoft-supplied templates that ship with Management Studio. This also ensures that no matter the folder structure/organization you use for your templates you’ll be ensured to catch those in the sync process. In that case use the following as the target for your symbolic link:
C:\users\
This is because that folder, “Templates” is the parent folder of subfolders covering templates for multiple SQL-centric disciplines. When building the symbolic link – and having the target for that symbolic link being a directory/folder the link includes all child/sub folders.
OPTION 2: You want to keep only the SQL Data Services templates in sync with other machines/users.
Under this mandate you build the symbolic link to the “sql” folder one level lower than the one presented above. It will still synchronize all templates related to the Data Services including those Microsoft-shipped ones but any other templates (such as SSAS templates) are not synchronized:
C:\users\
OPTION 3: You want to keep only non-Microsoft-shipped templates in sync with other machines/users.
This will likely be the most common implementation of synchronizing templates. It allows you to share templates you customize and create within a dedicated folder to keep custom and Microsoft-shipped templates separated. It ensures and provides a “soft force” to you and your team to use a dedicated folder/subfolder structure for any personalized templates. Updates to SQL Server Management Studio may include changes to templates provided with the installation of SSMS. However, any custom folders remain untouched. To accomplish this, you’ll need to first create a new folder in the C:\users\
C:\users\
Building the Solution: Part Two – Creating the Local Symbolic Link
The command to create a symbolic link is run from the command line. Open up a Command window (Window Key + R) and then type the following command (referencing the three options above appropriately.) The syntax to accomplish the creation of the symbolic link is structured as such:
mklink /D
The /D flag tells the command that this will be a directory (aka folder) symbolic link. Omitting that flag would switch to a default behavior that would attempt to create a symbolic link based upon a single file.
The mklink commands for each of the three options would look like this:
Option 1
mklink /D \ “C:\users\\AppData\Roaming\Microsoft \SQL Server Management Studio\\Templates"
Option 2
mklink /D \ “C:\users\\AppData\Roaming\Microsoft \SQL Server Management Studio\\Templates\Sql"
Option 3
mklink /D \ “C:\users\\AppData\Roaming\Microsoft \SQL Server Management Studio\\Templates\Sql\"
The instant you create the symbolic link it begins to synchronize.
Building the Solution: Part Three – Creating the Remote Symbolic Link
At this point you’ll observe that the folder linked to in the templates directory appears to exist in the cloud service’s local folder. This is as far as a symbolic link can take you without breaking the functionality of the Template Explorer in the remote instance of SQL Server Management Studio.
Yes, I said “break”.
Regardless of which of the three options you subscribed to above I want to show you why getting your templates into the cloud sharing service is as far as you can go. From here, to pull your template files into the remote instance of SSMS you’ll need to manually copy files from the cloud share service’s local folder to the necessary folder in that long path in the C:\users directory that pertains to the option you chose above.
For the sake of this example I’ve decided to use Option 3 which is the sync of a single custom folder in the templates directory. All work that follows is to be performed on the remote laptop/PC – not the laptop/PC where you set up the original link.
Setting up the Symbolic Link on the Remote Device – Do Not Do This at Home!
Before we continue I want you to first read through the remainder of the article completely. As always I strongly encourage you to never use code obtained through the internet, even mine, without first vetting in a non-production “sandbox” environment. In this case, I’d go so far as to even say not to run the following code at all – anywhere. You’ll see why soon enough just through reading along.
Since I’ve decided to show how a symbolic link breaks SSMS’s Template Explorer functionality using the single folder sync process I need to create (essentially) the opposite of the Option 3 link described above to take the custom folder in my local Dropbox home folder and “push” the contents to the folder that we would want to exist in the templates directory. Using cmd.exe running as Administrator type the following command replacing those items in <> with the correct values for your instance of SSMS and your user profile name:
mklink /D "C:\users\\AppData\Roaming\Microsoft \SQL Server Management Studio\\Templates\Sql\ " \
At this point you’ll be notified you’ve successfully created the symbolic link:
If you look in the templates folder, you’ll see that custom subfolder has been created:
Unlike the example in the source article, this folder is created as a shortcut and not as what the operating system thinks is a “real” folder. Does this cause trouble for SSMS?
The answer is a definitive: “Yes”.
Now you can see why I had to forgo the use of a symbolic link for solving the sharing problem and why I told you not to run this code yourself. You also should be aware that deleting the symbolic link from that template directory does not fix the issue you’ve created either. You will find yourself needing to uninstall and reinstall SSMS to gain use of the Template Explorer again.
Conclusion
I am not a fan of all the hoops needed to be jumped through to solve the sharing issue (or lack of sharing issue I should say) in SSMS when it comes to templates. The SQL Server Program Group could solve this by allowing customization of the source of the templates to allow that to be placed on a volume that can be accessed by multiple devices. I have seen firsthand that sounds easier than it is. Every hour of developer time has a cost and there are a limited number of developers in the SQL Server PG. Until this issue is prioritized higher it will not be addressed. Until then you have two options: third party software combined with Cloud sharing services to automatically solve the sharing of templates or using a symbolic link to at least get template files into your Cloud sharing service of choice and then performing periodic manual copies of files from the local Cloud service folder to your templates folder.
