SQL Server Registered Servers
Developers, DBAs, QAs, or even accidental DBAs everyone of us works with SSMS (SQL Server Management Studio). We tend to spend a huge part of our work time and it can be hard to remember the names of all the servers or instances.
If we deal with more that one number of SQL Servers. It can be very difficult to "jump" between these servers every time. Connection details, machine names, SQL login, Windows authentication, environments they are part of... It's can be a nightmare.
Today's article will be about SSMS Hidden Gem...
As you already understand, that future comes into a game and help us to order all this mess. We can set up all information, like server names and logins only once, and then use it every time by one (or two) clicks.
More of that, it can be a list of servers, hierarchy, or groups of the servers. We can set up an alias for each server - this is useful if the server has a long name and we want a shorter alias displayed.
As well as storing of persisting server information and saving our time. There are some other advantages of using either. For example, you can run a query, health check, or even maintenance against multiple servers at once.
I recommend to group servers together by clients (if you have more that one), environments, or type of the servers (production, test and etc.). But it's your choice and you can play with it.
• Connection data stored locally on our machine in the XML file.
• Passwords encrypted, so it is for the local user only.
• Supports SQL Server Authentication or Windows Authentication.
• Useful for the database engine, SSAS, SSIS, and SSRS connections.
How it works:
By default, the Registered Servers window is not shown in SSMS. To see that, just hit Ctrl+Alt+G on a keyboard or show it up by View menu, and choose the needed option.
In this case, I've chosen to configure database servers, but you can also work with Analysis Services, Integration Services, and Reporting Services servers. We can change our choices, by icons on the top.
Now, we must decide where are the details will placed.
Local Server Groups - on your local system (an actual system that is running SSMS). When you work from a different system, you won't able to use those connection details. But there is an option to Import/Export these details. This at least allows you to move details between systems.
Instead of local storing, we can configure a Central Management Server. This is a server that will store for us all connection details. It seems a great idea - everything in a single place, but not all is so pink. The big down-sides of this arrangement is that only Windows authentication can used. Unlike Local Server Groups, that also works with SQL logins.
In any case, after we choose storage, we can create a Server Group or just a Single Server Registration.
Let's create a new Server Group. In this case, we will asked to choose a name and an optional description.
Done, we have a Group, easy!
After that, just right-click and choose New Server Registration. From here, it's a regular SSMS connection.
In additional tabs, we can configure other connection properties.
In the connection tab, we can make more and set different colors for each server or group. That will help and avoid confusion between environments when we use a lot of tabs.
After a first server is added, we create a second server, third, fourth, and so on.