As a developer, when you are working in a project, you tend to have a number of servers which you need to constantly shift between – Local/ DEV/ QA/ MOCK/ PROD.  Again if you are a database administrator, you always have a number of servers to maintain, and most of the time managing a large number of servers can be a challenging task. So the question here is what can be done here to manage or organize these frequently accessed servers in a better way?

The easiest way to save these frequently accessed servers is by storing the connection information of the servers using a feature called ‘Registered Servers‘. For developers and DBAs this is a great help since they need not remember the server names or their connective information. Registering the servers is a one-time activity, but you will leverage the benefits in your day to day work.

To view this window in SSMS, you can navigate to View -> Registered Servers. Then right-click on the ‘Local Server Groups’ and click on ‘New Server Registration’.

Now you can go ahead and register the server by entering the connection details. Note that you can also give a custom name for the registered server.

I have registered all my frequently used servers in my project under ‘Local Server Groups’ folder, as you can see in the screenshot below.

Where are the Registered Server information stored?

Basically it is stored as an XML document and placed in the below path —
C:\Users\Username\AppData\Roaming\Microsoft\Microsoft SQL Server\130\Tools\Shell

When you open the file ‘RegSrvr.xml‘, you will find all the names of the servers which you have registered and other details.

To summarize, 3 reasons why I prefer to register all my SQL server instances —

• Registering the servers (storing the connection information ) is a one-time activity but saves a good amount of time on a day to day basis.
• There is no need to remember the server names or the connection information any more – hence it makes server management easier.
• You can create new Server Groups to group the SQL Server instances, as per your requirement.

To learn more SQL Tips and Tricks & other Productivity Enhancements in SQL Server Management Studio, please refer my article links below —

SQL Server Management Studio 2016 Productivity Enhancements

Increase your Coding Speed in SQL Server Management Studio

10 SSMS Tips and Tricks to boost your Productivity

Categories: SQL Server, SQL Tips