Store Optional Connection Parameters in SQL Server Management Studio

About six months ago I wrote about the challenges of using the Always-On Availability Group Listeners with SSMS. At the time, I had presented three problems and only solutions for two of them. At this time, I am happy to announce that Microsoft has responded to our connect ticket and provided a current version work around as well as a promise for feature implementation in vNext.

The challenge

The problem was that, with Availability Group Listeners (AGLs), you often needed to connect with the MultiSubnetFailover parameter, the ApplicationIntent parameter, or both. If you use SSMS for these connections, it has been very inconvenient. You would be required to perform several clicks and retype the parameters every time that you connected to the AGL. The best solution that I was able to present was to up vote the Microsoft connect ticket.

The workaround

IMPORTANT NOTE: This will not work for Central Management Server, local registered servers only.

1. Open SSMS.

2. Browse to the Registered Servers by going to view > registered servers.

3. Right-click on Local Server Groups.

4. Select New Server Registration.

5. Fill out the connection information for your Availability Group Listener. Don’t forget to set your default database to be within the Availability Group, if applicable.

ssms-availability-group-listener-workaround-1ssms-availability-group-listener-workaround-2
6. Click Save.

7. Close SSMS.

8. Open %APPDATA%\Microsoft\Microsoft SQL Server\$(Ver)\Tools\Shell\RegSrvr.xml (where $(Ver) is the SQL version such as 110, 120, 130 of your SSMS install).
ssms-availability-group-listener-workaround-4
9. Find the <RegisteredServers:Name> node with the name of the server you just created.
ssms-availability-group-listener-workaround-5
10. Edit the <RegisteredServers:ConnectionStringWithEncryptedPassword> node and add any other parameters you want to the end (such as MultiSubnetFailover=true).
ssms-availability-group-listener-workaround-6

11. Save the file.

12. Open SSMS.

13. Attempt to connect to your registered server by double-clicking.

ssms-availability-group-listener-workaround-3

ANOTHER IMPORTANT NOTE: If you ever update this registered server, you will need to repeat the XML edit steps because it will be overwritten.

This article has 5 comments

  1. Hi,
    sorry but unable to find a shell directory or RegSrvr.xml
    my environnement is Windows Server 2012 enterprise ans SQL Server 2012 Enterprise Edition .

    thanks

    Eric

  2. hmmm…. tried on latest CTP3 for 2016. Doesn’t appear to work. The string looks correct. Still frustrated that MS haven’t fixed SSMS like they said they would.

  3. Great post! This recently came in handy for me.

    FYI, I discovered that you can go to Object Explorer, connect to a server using your additional connection properties of choice, then right-click this new server connection in Object Explorer and select “Register…”. The newly registered server will inherit the connection parameters you set, although you still can’t edit/view the parameters in the registered server. But at least you won’t have to use Notepad. 🙂

    I’ve tried this with the December build of SSMS 2016.

Leave a Reply

%d bloggers like this: