Our favorite way to organize servers within a SQL Server estate is by using Registered Servers, which were first introduced in SQL Server 2005. Registered Servers support SQL Server 2000 and above and can support the Database Engine, Analysis Services, Integration Services, and Reporting Services. We prefer to use this feature as a simplified inventory tool for the Database Engine.
"Registered Servers" is the overarching name for three different types of repositories that store server connection information and enable multi-server queries. dbatools supports all three types of repositories.
- Local Server Groups
- Azure Data Studio
- Central Management Studio
Azure Data Studio is a new addition which was introduced in SQL Server Management Studio 18, and only appears when Azure Data Studio is installed on the computer running SSMS.
If you aren’t familiar with Registered Servers, you can take a look by opening SQL Server Management Studio, then clicking on View → Registered Servers. Figure 8.1 shows an example of what this window looks like in our environments.
Figure 8.1. Registered Servers in Management Studio 18
Imagine being able to select all servers or just a specific group and running one query or command against all of them. This is useful on a day-to-day basis, but it can be a lifesaver during audits which require DBAs to prove their systems are secure by running pre-built queries in front of an auditor!