Importing Registered Servers from SSMS into Azure Data Studio

What’s the definition of insanity?

In case you didn’t notice, I’m already in love with SQL Operations Studio Azure Data Studio. After downloading the preview and poking around, I’m pretty excited for what this application can (and eventually) do. I view the preview as a furnace, where a new developer or management interface for SQL Server will eventually be forged.

Much like I tell people writing PowerShell to switch from the PowerShell ISE to VS Code, I’ll probably eventually push people towards this application. It’s a smaller install, it’s pretty snappy, and the interface is way cleaner and easier to manage (and officially supports dark themes). There’s built in source control and support for extensions. So much to explore and learn! Not yet, though; there’s still a lot to work out, like the fact that it can’t give you actual execution plans (just estimates) and other things. I’m sure that with enough community engagement, it’ll continue to evolve. It’s too early to write it off, but at the same time, you can’t go whole-hog using it either.

One barrier to entry is that the initial setup can be a little daunting, especially if you use a local connection groups or central management servers to keep track of registered connections in SQL Server Management Studio. You’d be in for a lot of manual clicking and typing of connections if you have a lot of saved connections. But there’s a better way: you can import all that saved information right into Azure Data Studio, and it’s pretty painless, too. Buckle up, because this involves a little knowledge of how settings are saved in Azure Data Studio, and how we can quickly get saved connection information out of SSMS and into your new application. Spoiler alert, we’re going to use PowerShell.

JSON: “At least it’s not XML”

Like I mentioned in my last post, changing settings in Azure Data Studio requires you to modify a text file. No fancy GUIs here. To pull up your settings, either click “File -> Preferences -> Settings” or use Control-Comma (,) on your keyboard (Note: keyboard shortcuts are king in both VS Code and SQL Ops Studio, I’d recommend practicing).

When you do, after a fresh install, you should see something that looks like this:

The panel on the left is the default settings for the application. Go ahead and try to edit them. What’s that, you can’t? That’s on porpoise. In this shell, just like VS Code, when you want to change or add a setting, you don’t do it in the defaults, you set them in your personal settings file, which is the panel on the right. Essentially, if you want to override a setting, you find it in the left panel, and copy/paste it into your settings, which trumps the defaults. Anything additional just gets added, since it won’t technically override anything. Your settings file is stored if your local roaming application data folder (C:\Users\<your user name>\AppData\Roaming\sqlops\User ) when you first run the application.

The settings are saved in JSON format, which is nice and easy to work with as a key:value pair. If you were to add connections through the UI, they get added to your settings file and here’s a sample of what it would look like:

{
    "datasource.connectionGroups": [
        {
            "name": "ROOT",
            "id": "C777F06B-202E-4480-B475-FA416154D458"
        }
    ],
    "datasource.connections": [
        {
            "options": {
                "server": "someservername.boatmurder.net",
                "database": "master",
                "authenticationType": "SqlLogin",
                "user": "drew",
                "password": "",
                "connectTimeout": 30,
                "applicationName": "sqlops",
                "groupId": "C777F06B-202E-4480-B475-FA416154D458",
                "databaseDisplayName": "master"
            },
            "groupId": "C777F06B-202E-4480-B475-FA416154D458",
            "providerName": "MSSQL",
            "savePassword": true,
            "id": "7d3ace26-f270-40c0-a6f3-6019894366dc"
        }
    ]
}

Inside your settings file, two new sections get created. First, the “datasource.connectionGroups” key, which contains information about the groups (or folders) that you connections get stored in. By default, they get added to the “ROOT” section of the list (i.e. no group). If this group doesn’t exist when you create a server, Azure Data Studio creates it for you and assigns it a unique GUID.

Next, details about the connection get listed here: the “datasource.connections” key saves all your server connection info. The nested “options” key contains all the details about the server connection, such as which server it wants to connect to, what kind of authentication, username, etc. The groupId field is what helps order it in the group hierarchy (here, we see it is just off the ROOT group).

So, now that we know this, importing the server info can be pretty straightforward: we just need to capture the saved server info on our local machine, create the neccessary JSON, and paste it into our settings file. Even then, if we could aggregate it, that’s still a lot of manual work. I want to automate the entire process. The question then becomes, how? PowerShell, that’s how!

The plan

On paper, PowerShell seems like the ideal tool to get our info out of SSMS and into Azure Data Studio:

• PowerShell has great JSON support. You can read JSON from a file, which gets converted into an object that you can then interact with. That makes taking your existing settings file and parsing through it pretty easy, as we’ll see.
• Thanks to the SQL Server PowerShell module, we have an easy and convenient way to get our local saved SQL registration info. When you load the module, as part of the paths that get created, you can browse the SQLSERVER:\SQLRegistration  path to get all your saved connection information, including any central management servers you have registered.
• Once we have the settings file loaded, and have recursively scanned your SQLRegistration folder, we just need to construct “datasource.connectiongroup” and “datasource.connection” objects, append them, and save the appended JSON to a new configuration file.

Is it as easy as it sounds, though?

Digging a pit of success

I wrote a function called Import-RegisteredServers that you can snag from my Github repository. The code is listed out there. I won’t paste the entire thing here, since it’s a living repository that is going to change as 1) my code gets better and 2) the application changes. Go ahead and download it take a look.

All done? Cool, let’s move on.

In addition to the points listed above, I wanted to include some quality of life stuff in the script too, like checking for duplicate server registrations within a group and making sure the user has the required settings areas defined. Let’s take a look at some of the code that makes this work

Reading the settings file

$UserSettings = (get-content -Path $PathToSettingsFile -Raw | ConvertFrom-Json)

The line above is what gets things started for us. We’re using Get-Content to read the contents of the settings file (which is stored in the $PathToSettingsFile  variable defined a bit earlier in the code. Then, we take that content and pipe it to ConvertFrom-JSON  which does exactly as advertised: it takes your input variable and attempts to parse the values as JSON data. Once it does, your content that was stored in JSON becomes an object:

Which means, now can we compare, add, or remove things we see fit. This functionality makes it easy to add things into your existing settings once you have them loaded.

Getting your saved registration info

Now we need to get our registered servers from SSMS. We’re going to do that with the help of the SQL Server PowerShell module. Here’s a quick snippet to demonstrate how it works:

import-module sqlserver
Get-ChildItem -Path SQLSERVER:\SQLRegistration -Recurse

This will look at ever folder you have saved as part of your registered servers in SSMS. The object you get back, though, has some quirks

• You will get back server registration objects, which contain connection info like server name, authentication type, and other various properties.
• You will also get back registration directory objects, which are containers for your servers. They have properties that identify their own unique GUID, as well as a “parent” object that tells SSMS how to display it as a child of another group, for example.

The trick then is to make sure you work with both objects. My function is basically broken up into two sections: the first section iterates over groups, determines if they are a child of another group, and writes a new connection group key:value pair to the settings object we read in ahead of time. The second part does the same thing, except for the servers.

Each section also checks to see if the parts of the settings file has areas defined for these settings as well, and adds them in if they aren’t there.

Saving your registrations

Once the parsing is done, we now need to write the file. The final part of the file does this pretty elegantly, if I do say so myself:

$UserSettings | ConvertTo-Json -Depth 99 | Out-File -FilePath $PathToSettingsFile -Encoding "UTF8"

Here, I’m taking the $UserSettings  object which contains all the info we’ve added during our parsing process, and piping it to the ConvertTo-Json  cmdlet. Note the -Depth 99  parameter here: by default, when you pipe content to ConvertTo-Json , it only goes two levels deep. Our server registrations are three levels deep, so it won’t be saved correctly unless you account for that. With a value of 99 here, I’m overcompensating, but it wouldn’t be the first (or last) time.

Once the object has been recombobulated into JSON, we pipe it again to Out-File, overwriting our file we imported at the start of the script. We’re specifying UTF-8 encoding, too, since Azure Data Studio is very picky about file encoding.

Odds and Ends

I’m pretty pleased with the functionality I wrote here, and I hope you find it useful. Before you download it and try it yourself though, just a few quick notes about the function:

• It’s mentioned above, but bears repeating: this requires the SQL Server PowerShell module. Go download it already!
• If you run the script and you see servers show up in your settings, but not in SQL Ops Studio UI, try restarting the application. It can be a little finicky about that.
• The function can take two optional parameters:
  • -PathToSettingsFile  will allow you to specify a different user settings file. By default, the script will read your $env:AppData  variable to determine the default location, but if it can’t find it there, you can override where it looks with this parameter.
  • -SaveTo  is similar, except your have your output saved somewhere else instead of overwriting your existing file
• Oh, before you panic about that last bit: the script will take your existing file, whether it be the default or one you specify and make a copy with it a “.old” extension at the end.
• The script will check for duplicates when you run it. Meaning: if you run it once and import your stuff, it won’t duplicate all your folders and servers again. However, it you were to add a new registration to your SSMS registered servers, and then re-run the script, you should only get the new stuff. It makes the script a little more complicated with some checking, but in the end I didn’t want people creating an endless stream of servers.
• Try running it with -Verbose  for some updates. I wrote this hopped up on prescription cough medicine, so it’ll be fun to re-read these messages later.
• A note on authentication methods: if you have SQL Server Authentication for any existing registered servers in SSMS, your credentials will be pulled and saved in plain text in your settings file. I know, I know, but that’s how I pull them from PowerShell (it’s plain text there, too). I haven’t found a neat way to save those credentials encrypted, yet.

Your turn

For roughly 100 lines of code, this turned out pretty well I think. Can it be better? Absolutely it can; there’s people who can code better than me out there, and I bet you’re one of them. I encourage you to submit a PR once you come up with another solution. I’d love to see your work! And if you liked this script and want to see more, drop me a comment. I’d love to keep contributing if you tell me where I can help.

Here’s the script in action (click for an larger, easier to watch version):