How to Share SQL Server Management Studio Templates with Teams
Whenever I present on the subject of SQL Server Management Studio (SSMS) templates I inevitably get the question: “how do I share these with others on my team or between my laptops and jump servers?”
Microsoft doesn’t make that task easy. The template folder exists buried deep in the C:\Users path structure of every server and workstation that has SSMS installed on it. To be more precise it’s located in:
C:\Users\
To top it off AppData is a system folder that’s hidden unless you specify in Folder Options in Windows Explorer otherwise and even then the Templates folder doesn’t get created until you view the Template Explorer in SSMS for the first time.
It seemed like it was time for me to finally solve this issue for both the sake of being able to share how to do this with those I stand in front of when discussing the template subject as well as for the benefit of myself and my team. I put a great deal of effort into building a toolkit of templates over the last 7 or 8 years and I was really getting tired of using thumb drives and Dropbox to keep all my workstations in sync.
It’s Dropbox and OneDrive that most people I speak with bring to my attention when discussing this. However each of those tools only sync their proprietary folder (and any subfolders in their hierarchy.) Since it’s still not an option for changing the template folder location as a customizable path in SSMS – even in vNext as far as I’ve been able to determine – I was left with coming up with a good “missing link” application that is lightweight and would work with SSMS in order to fill the gap between the C:\Users…\Templates path and either Dropbox or OneDrive. Keep in mind when I walk through this solution I’ve settled on a free version of an application called Allway Sync and Dropbox. While I have a free version of OneDrive I had made a decision quite a few years ago to pay for the Pro version of Dropbox that I use on a daily basis and have installed on all my laptops. Eventually I’ll make the shift to OneDrive since it’s a Microsoft product and is integrated into the modern versions of the Office suite of tools as well as all supported versions/editions of the Windows operating system. (It’s therefore a bit more acceptable to use when syncing to jump boxes where I use SSMS against production when working through VPNs rather than Dropbox.)
There is a bit of a sweet spot when identifying the tool I was going to use to keep the Templates folder in sync with Dropbox: depending on the third-party tool it “broke” the Template Explorer if a proprietary file was placed into the sync’d template folder. I had made a first attempt using a tool called SyncToy, which is a Microsoft product, but because of the file that is placed into the source folder for synchronizing it caused Template Explorer to toss an exception error and would not display any of the template folders.
Now that I’ve dipped into the background at how I arrived with the SSMS ß à Allway Sync ß à Dropbox solution let’s take a look on the settings needed to make this work for you and your teams.
Configuring SSMS to Synchronize to Dropbox/OneDrive
The first statement I want to make is that I don’t recommend synchronizing all of the subfolders in the Templates folder. Microsoft ships a plethora of existing templates with SSMS. What I prefer to do is create a folder for all of my custom templates leaving the Microsoft-shipped templates intact. In the case of this example I’ve created a subfolder in the Templates folder called “Sync Templates”. I’ll not go into the differences between creating template folders in the SSMS Template Explorer versus doing so in Windows Explorer and their pros/cons. I’ve provided links to the two articles I’ve done for SQL Server Pro to date earlier in this article if you’d like to review the ins and outs of templates in SSMS: something I’d state as required reading before moving onto this article.
As we proceed I’ll be using my work laptop as the initial source of the templates and my personal laptop as the destination for synchronizing but what you’ll see is a two-way communication structure that will allow changes to flow each direction allowing teams to make changes and keep all workstations identical for sake of sharing templates.
The process for getting the synchronizing architecture in place is simple:
1. Download Allway Sync from https://allwaysync.com/
You’ll be prompted to either save or run the install executable. Since you’re going to need to repeat this install on any workstation you plan to include in the sync family select save. Once you’ve downloaded the file copy it to a folder of your choice in Dropbox/OneDrive so you have access to it everywhere you plan to do the install. The install is very much a standard “click-next-click-next-finish” process and you’re all very intelligent and beautiful people (your welcome) so I’ll not go into that process ad nauseum.
2. Create the necessary target and source folders
I make life easy on myself and map a drive to the Template folder so I don’t have to consistently remember the full path, let alone type it. The Z:\ mapping you’ll see if screen shots in this article maps to C:\Users\
3. Launch Allway Sync.
You’ll be greeted with a screen that looks a bit like this (I’ve already taken the liberty of creating the job when I realized I’d not snapped the photo first):
4. Customize the Job - Basics
- Job Name: Push Templates to Cloud
- Left Synchronization Path: Z:\Sync Templates
- Right Synchronization Path: C:\Users\
\Dropbox\Sync Templates - Synchronization Path (arrow graphic labeled “Change”): By default this is bi-directional. Leave it as such.
5. Advanced Job Customization
Now it’s time for the more detailed aspects of synchronizing the contents of these folders. In Allway Sync either select Job then Properties from the menu or right click the job tab and select Properties from the popup context menu. An option form appears. We are interested in just two areas of this form: Synchronization Rules and Automatic Synchronization. Under the job name select Synchronization Rules and make sure you set your options to match the screen shot below:
This ensures your changes will ignore any lag due to Daylight Savings Time, will sync both directions based upon timestamp of the modification date of the file(s) involved, and will propogate both updates and deletes. That last one is up to you to decide to implement but I have a saying of Trust the Team when it comes to such things… until they no longer deserve your trust that is.
Next select Automatic Synchronization and make sure you click the following options from the screen shot below:
This ensures that modifications are synchronized in (near) real time. If you’d rather inject a lag you can do so here as well.
6. Launch it
The proof is in the pudding they say. Click Analyze in the main Allway Sync form for the initial analysis. Don’t be surprised to get a warning about the sheer number of changes to sync as this is expected the first time the job is run. That done go ahead and click Synchronize and let the magic happen.
Configure Additional Workstations/Laptops
At this point you’ve now been able to synchronize a non-Dropbox/OneDrive folder to Dropbox/OneDrive. Now, using the functionality of Dropbox/OneDrive your “Sync Templates” folder is being replicated to any workstation you’re employing those commercial tools’ capabilities. You need to repeat what you did here on those other workstations in order to replicate changes between Dropbox/OneDrive and their Templates folder.
- Install Allway Sync, launch and create the job.
- Add the correct folders for synchronizing.
- Change the Synchronization Rules and Automatic Synchronization settings.
- Analyze and Synchronize
Proceed At Your Own Risk
While there may be other tools that work just as well as Allway Sync this is what I settled on for my needs. Results may vary and other options may exist for you. As always take extreme caution when installing any new tool onto any workstation or (particularly) any server. Do so at your own risk! When I had this finally configured (in less than 10 minutes for both laptops mind you) I was able to create a template on my work laptop called “Work Template” and another on my personal laptop called “Personal Template” and have both Template folders look like this almost immediately through a VPN and on two different wireless networks:
Bonus Synchronization
In many cases you may find yourself with multiple versions of SSMS installed on a laptop or test VM perhaps. The nice thing about using Allway Sync is that the tool is extensible to allow you to synchronize more than two folders. In this case I’ve added another folder that points to a Y: drive I have mapped to my SSMS 2016 install (Z: maps to my SSMS 2014 install).
All I need to do after adding the additional sync folder is run through another cycle of Analyze and Synchronize and the three folders are now kept in sync.
Conclusion
While you may come up with other solutions using different synchronization software what I’ve shown you here does work with SSMS. You will end up with a system folder embedded in the synchronized folders with a single .xml file in that folder but it does play friendly with the Template Explorer unlike what I saw with my first attempt with SyncToy. Now when I’m asked the question about sharing templates I can point to a working solution. Goes to show that oftentimes it’s simply up to us to create a solution where one isn’t readily available: and of course to share these minor victories with others.
