When working with SQL Server systems, it can be hard to remember the names of all the servers, to remember connection details for the ones that need SQL logins (instead of Windows authentication), and to remember other details of those servers, such as which environments they are part of (eg: production, UAT, test)
SQL Server Management Studio (SSMS) has a facility to help you to do this. It allows you to register server details in a single place.
By default, the window isn't shown, but from the View menu, you can choose Registered Servers.
When the window opens, you can see this:
Note the toolbar near the top of the window. It is showing that we're configuring database servers but the other icons let you know that you can also work with Analysis Services, Integration Services, and Reporting Services servers.
The first decision that you need to take is to decide where the details will be stored. Local Server Groups are stored on your local system ie: the system that is running SSMS. If you move to a different system to work, you won't have those connection details. Alternately, a Central Management Server can be configured. This is a server that agrees to hold connection details. While this seems a great idea (because the details would be held in a single place), one of the down-sides of this arrangement is that only Windows authentication can then be used. Local Server Groups can also work with SQL logins.
Let's create a server group as an example. If I right-click Local Server Groups, here are the available options:
Note that there is an option to Import (and Export) these details. This at least allows you to move details between systems.
Let's create a new Server Group:
It just needs a name and an optional description, then OK. When it's created, right-click it, and choose New Server Registration:
I've connected to the server SDUPROD and I've given the registered server the same name. Note that you don't need to do that. I could have called it PayrollServer or some other more meaningful name. You'll also notice that there are tabs for configuring other connection properties.
I've then created a second server called HRServer and under the covers, I've pointed it to another server.
Now I have all my servers in groups, in an appropriate location. I can right-click them to open new queries to them, and to do much more.
This is an awesome feature.
Is there a way to have the view open by default when I open Management Studio or do I have to add the view every time I open again?
Not that I know of sorry.
Regards,
Greg
Yes, it's possible to have the Registered Servers pane visible when SSMS starts up, in v18.3 anyway.
Here's how:
1. Press Ctrl+Alt+G to open the Registered Servers pane.
2. DO NOT try to drag-position the Registered Servers pane anywhere else!
(In my SSMS at least, trying to drag-position this pane crashes SSMS.)
3. Click the dropdown arrow in the Registered Servers pane title bar, then click Dock.
(If Dock is greyed out, the pane is already docked.)
4. Close and re-open SSMS; verify that the Registered Servers pane is visible and docked.
That said, if you close the Registered Servers pane before you close SSMS, it will NOT 're-appear' when you next open SSMS: you'll have to press Ctrl+Alt+G to open it again.
Thanks. Not sure if this is a relevant question, but Can I get the version and productversion of a linked server with this method. I created the Group and put a server in it, ran the query and it produced the correct result.
But I need to be able to find the correct server version and productversion for multiple servers in one script. Does this allow me to do this?
Many thanks
Yes, if you right-click the group, and say New Query, you open up a query for the whole group. SSMS will run the query on each server in the group, and return you a single set of results.