Introduction
This will be a step-by-step guide on how to create your own SQL Server Management Studio 18 extension (SSMS from now on). It will cover only the basic setup and steps required to get started. It’s sort of an update to the Create Your Own SQL Server Management Studio 17 (SSMS) Extension tutorial and if you’re already familiar with that one, there’s no need to go through this one as well because most of the stuff there still applies. Even though this is an update, it will cover all the steps again in full so the people who haven’t read that one can still get started with creating their own extensions from this tutorial alone. So if you’re new here, you should skip the next section and go to the Background or straight to the Requirements section if you don’t want to be bothered with the reasons why I wrote this tutorial. If you read the previous tutorial and just want to catch up on what’s changed, then you should only read the next section covering the differences between developing an extension for the previous version of SSMS and this one.
If You Already Read the Create Your Own SQL Server Management Studio 17 (SSMS) Extension Tutorial
There are only a few things that changed from back then so if you’re already familiar with the previous tutorial, you should just skim through this list and you’re good to go.
- The default install location for the SSMS executable (required for running and debugging the extension) now is:
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\
- The location of the extensions folder now is:
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Extensions\
- The location for the log file now is:
C:\Users\Username\AppData\Roaming\Microsoft\AppEnv\15.0\
- There’s no more need for the workarounds (rendering half of the previous tutorial completely useless)
The SSMS team at Microsoft finally decided to remove the constraint that allowed only extensions with whitelisted package ids to be loaded into SSMS. Now you just develop your extension and it just works.
- Will be using Visual Studio 2017 this time around
Because the updated SSMS 18 is now based on the VS 2017 Isolated Shell.
One more thing to note on this final point is that extensions developed with Visual Studio 2015 might not work properly on SSMS 18 due to assembly version incompatibilities. The simplest way to work around this is to create a new project in VS 2017, reference the new assemblies (i.e., the newer versions of the same assemblies) and move all of your extension code into the new solution and try running it from there. I recently had issues with running and debugging an extension from Visual Studio 2015 on SSMS 18. Moving the code and debugging from a new project (with new references to the newer versions of the same assemblies) created and run in VS 2017 solved the problem.
Background
With SSMS 18 around the corner (RC1 available at the time of writing), I decided to have a look at the changelog for this version (available here) hoping to see “Dark theme fully supported” (no luck there) or something else interesting when this note caught my eye:
Quote:
Package IDs no longer needed to develop SSMS Extensions
You see, everyone that tried to develop an SSMS extension up to this point knows that it was a pain to start. The biggest reason was this constraint set by the SSMS team that wouldn’t allow any non whitelisted extension to be loaded into SSMS when starting. As a result, we had to do a bunch of unpleasant hacks to get around this restriction and to get our extensions to work. Making things even harder was the complete lack of support and documentation on extending SSMS which to my knowledge still hasn’t changed. The last mention of extensibility for SSMS in the official documentation I could find is in the pages for SQL Server 2014 (available here) which state:
Quote:
SQL Server Management Studio is built upon the Visual Studio Isolated Shell, which inherently supports extensibility (add-ins/plug-ins). It is possible to tap into the Visual Studio extensibility services to surface custom capabilities within SQL Server Management Studio; however, such extensibility is not supported.
Even though the decision to remove the whitelist check makes things easier, as far as I know extensions are still not officially supported and we’re still left to dig around the web for resources on extending SSMS which are few and far between. Luckily, because they share the same isolated shell, tutorials for creating Visual Studio extensions can sometimes help and there are plenty of these around (official documentation available here). The lack of official documentation and online resources are the reasons for creating this tutorial.
Requirements
Because SSMS 18 is based on the VS 2017 Isolated Shell, I recommend this version of Visual Studio when creating your extensions. Due to assembly version compatibility, using other versions of Visual Studio (even newer versions) is a one way trip to headache town and I highly discourage this. For this tutorial, I’ll be using a freshly installed Visual Studio 2017 and SSMS 18 RC1 on a fresh install of Windows 10. So if these steps work for me, I can pretty much guarantee they’ll work for you as long as you use the software and the versions listed above. There’s not much else to say in this section except to make sure that you install the Visual Studio extension development toolset when installing Visual Studio because you need this installed to get the Extensibility section and the VSIX project type when creating new projects.
If you don’t have this toolset installed, you can still add it to Visual Studio later with running the Visual Studio Installer located at:
C:\Program Files (x86)\Microsoft Visual Studio\Installer\vs_installer.exe
and hitting Modify on your existing installation.
File > New Project
Our SSMS extension is going to start its life as a Visual Studio extension that would later be moved to SSMS. To start our project, in Visual Studio, we first go to File > New > Project… and select the VSIX Project template located in the Installed > Visual C# > Extensibility section.
Let’s name our project HelloWorldSsms18Extension and hit OK.
To make our extension actually do something, we’re going to add a command in the Tools menu by right-clicking our project in the Solution Explorer and selecting Add > New Item...
From here, we need to select the Custom Command item from the Installed > Visual C# Items > Extensibility > VSPackage section. Let’s name our command HelloWorldCommand and hit Add.
So, yeah… that’s pretty much it. Our extension is done. To test that it’s actually working and that we can debug it, we’re going to add a breakpoint in the Execute method of the HelloWorldCommand class (located in the HelloWorldCommand.cs file) and hit the Play/Start (now named Current Instance...) button in Visual Studio.
If everything went well up to this point, hitting the Start button should start a new Visual Studio instance (called the Experimental Instance) in which we should see our command in the Tools menu. You might have to wait a while if this is your first time running the Experimental Instance, but once it’s started, we can find our command in it.
If we run the command (by clicking on it), we can see that our breakpoint is hit (in the development instance of Visual Studio) and if we hit Continue, our Experimental Instance will come back into view displaying a message box which is exactly what one would expect to see given the code in the Execute method of our command.
I guess Microsoft forgot to update the text of this message because the method is no longer called MenuItemCallback like it was in the previous versions of the Custom Command (it’s now called Execute). You can see this in the screenshots of the previous tutorial.
We can now close the message box and the Experimental Instance, go back to our development instance of Visual Studio, change the message that the message box should display and try to run it again just to make sure that everything works as expected.
After we verified that our extension loads and works just fine and that we can debug it without issues, we can finally move our extension from Visual Studio to SSMS.
Moving to SSMS
There’re couple of things we need to do if we want to see our extension running in SSMS. Firstly, we want SSMS to load when we hit the Start button instead of another Visual Studio. To fix this, we need to go to our project’s properties by right-clicking on it in the Solution Explorer and selecting Properties.
In the Properties window, we need to go to the Debug tab and change the "Start external program" property to
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe
to make Visual Studio starts SSMS when debugging the extension. This is the default install location for SSMS 18. If this is not where your SSMS is installed, just hit the Browse button, locate the Ssms.exe executable on your computer and hit Open. While we’re here, we might as well change the "Command line arguments" field as well and put the /log argument in it. This will make SSMS log its activity when running and it could help us with troubleshooting down the line if something goes wrong with our extension. The location of the log file is:
C:\Users\Username\AppData\Roaming\Microsoft\AppEnv\15.0\ActivityLog.xml
After we’ve made the changes and hit Save, we can hit the now appropriately named Start button and see if SSMS starts with our extension loaded in it.
Ok, SSMS did start. Let’s close the Connect to Server window and check if our extension is in the Tools menu.
Hmmm… we got SSMS, but our extension is nowhere to be found. The reason for this is that our extension is not in the folder where SSMS usually looks for extensions to load when starting up. The location of that folder is:
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Extensions\
To fix this, we need to tell Visual Studio to copy the extension files to that location when building the project. We can do this in the VSIX tab of the project’s properties window by checking the "Copy VSIX content to the following location" checkbox and entering the SSMS extensions folder location (plus a folder name for our extension) in the textbox below. Let’s say the name of the folder to be created will be HelloWorldSsms18Extension and with that, the full path in the textbox should be:
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\
Common7\IDE\Extensions\HelloWorldSsms18Extension
Save the updated properties and hit Start again.
Ok, something went wrong because now Visual Studio won’t even build the project.
If we hit No and inspect the errors in the Error List window, we can see that Visual Studio was unable to copy the extension files to the provided path. This can be easily solved by just running Visual Studio as an administrator, so close Visual Studio and run it again but this time as an administrator.
Once Visual Studio loads, hit Start again. This time, the project builds without errors and SSMS is started. If we close the Connect to Server window and check in the Tools menu, we can see that our extension is there, right where it should be.
If we click on our menu item, we can see that our breakpoint is hit in Visual Studio (which means debugging works) and if we hit Continue, we can see our message box displayed in SSMS (which means that our extension works).
And that’s pretty much it really. This extension doesn’t do much but it’s a good starting point when trying to create your own. Our development environment and project are finally set-up for developing SSMS extensions. So…
Where to Go From Here?
Well, I don’t know really. Your best bet is to look for extension projects on GitHub to get some ideas and to see some code samples which can really help when creating your own. Unfortunately, there aren’t many of these around but there’re a few (some mentioned in the previous tutorial at the bottom) and these can really help. Aside from that, just Google around I guess, but as I mentioned a few times before, there isn’t much to find about SSMS extensions on the web. Check out tutorials on Visual Studio extensions because they are quite similar and they can provide much value when trying to figure out how to achieve something specific like putting the command button in a different place other then the Tools menu (e.g., a right-click context menu) or getting the current active editor window.
Good luck...
Note: Unlike the previous tutorial, this one won’t include any downloadable code or project files because the process of starting a project like this doesn’t require any code to be written and you can replicate the results above just by following the steps and looking at the images provided.
