Switching between multiple DB servers in SQL Server Management Studio can be a pain. Registered Servers in SQL Server Management Studio (SSMS) helps by allowing us to store servers for access later.
Example 1 below (click for a larger view in a new window) shows some servers I’ve registered and organised by app but you can organise them how you like and you can nest groups too.
A cool feature with SSMS Registered Servers is the ability to query multiple DBs at once. In example 2 above SSMS has combined results from multiple servers and included ‘Server Name’ in the output.
Another neat feature is that SQL Server Management Studio Registered Servers can be exported and imported. This is really handy for developer on-boarding as you can check the XML export file into source control or put it onto the team Wiki etc… windows authentication works well here as storing passwords is likely a no-go even for DEV DBs but check your company policy!!! … storing server names could be disallowed too.
You can see from the image above that there are two kinds of registered servers, local server groups and central management servers. The main differences are:
Local Server Groups
Stored locally so usable by local user only.
Can be used with SQL Server Authentication or Windows Authentication.
Can be used for the DB engine, SSAS, SSIS and SSRS.
Central Management Servers
Stored in msdb so can be used by other users.
Can be used with Windows Authentication only.
Can be used for the DB engine only.
One thought on “Use Registered Servers in SQL Server Management Studio (SSMS) for easy access to multiple DB servers”