8 Registered Servers
Our favorite way to organize servers within a SQL Server estate is by using Registered Servers (http://dbatools.io/registerservers), which is an instance inventory system introduced in SQL Server 2005. Registered Servers is our preferred inventory system because it has all of the basic features we need, such as grouping, authentication, and aliases, and it’s included in SQL Server at no additional cost. This means we don’t have to procure an inventory system each time we begin working in a new environment.
Registered Servers supports SQL Server 2000 instances and later 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, because it enables us to pipe servers from Get-DbaRegServer into almost any dbatools command. This ability makes it astoundingly easy to execute a command against every single instance in our SQL estate.
If you’ve got a lot of SQL Server instances, keeping track of them can be a daunting task. In the old days, we used custom web applications, Excel, or even Notepad to keep track of each instance. Registered Servers can help monitor your SQL Server instances, but they can still be rather tedious because they require a ton of clicking. Fortunately, dbatools turns those clicks into commands that make management fast and easy.
“Registered Servers” is the overarching name for three different types of repositories that store server connection information and enable multiserver queries (http:// dbatools.io/execregisterserv), which allow you to execute T-SQL queries against many servers at once. dbatools supports all three of the following types of repositories:
The ability to work with Local Server Groups was actually added just for this book! An entire chapter about Central Management Server alone was unexciting, so we added support for Local Server Groups and Azure Data Studio. What’s even cooler is that this also changed the way we fundamentally connect to SQL Server, which allowed us to add support for all authentication types that are supported by SQL Server Management Studio.
Figure 8.1 shows an example of what the Registered Servers window looks like in our environments. You’ll see that we do not use a flat list but, rather, divide our servers into groups such as containers, cloud, onprem, Production, and Test.
Imagine being able to select all servers or just a specific group and run one query or command against all of them, as shown in the following listing. This is useful on a day-to-day basis, but it can be a lifesaver during audits that require DBAs to prove their systems are secure by running prebuilt queries in front of an auditor!
Listing 8.1 Running a query against every server in our inventory
Let’s take a closer look at each of the three available Registered Server repositories. Note that because of the graphical nature of Registered Servers, this chapter will have several screenshots to help orient you with the way it works.
8.1 Local Server Groups
The first type of Registered Server we’ll look at are Local Server Groups. We like using Local Server Groups because they are flexible and allow us to securely store connection credentials. The downside, however, is that the inventory list cannot be shared with other database administrators. Local Server Groups support a variety of credentials, including SQL Server authentication and Azure Active Directory, as shown in figure 8.2.
Credential passwords are encrypted and stored within RegSrvr.xml.
8.1.1 Version-specific RegSrvr.xml files
All data in Local Server Groups is stored locally on your hard drive in a file called RegSrvr.xml, deep in the $homeAppDataRoamingMicrosoft directory. Several instances of RegSrvr.xml can exist on one machine, as shown in figure 8.3.
Until SSMS 18, Microsoft used version-specific locations for the .xml file. This meant that each version of SSMS had a different collection of SQL Servers. Understandably, this led to a bit of confusion when we modified our Registered Servers using dbatools but did not see them in older versions of SSMS.
Now, with SSMS 18 and, presumably, future versions, Microsoft has standardized the location of RegSrvr.xml. If you aren’t seeing your Registered Servers in SSMS, please upgrade to the latest version of SQL Server Management Studio. You can see in the next listing how dbatools can access servers that are stored in Local Server Groups.
Listing 8.2 Accessing local Registered Servers with dbatools
PS> Get-DbaRegServer Name ServerName Group Description Source ---- ---------- ----- ----------- ------ dockersql1 macmini Node 1 test AG Local... dockersql2 macmini,14333 Node 2 test AG Local... sql01 sql01 onprem Azure... azure sql db dbatools.database.windows.net cloud Azure...
8.2 Azure Data Studio
Azure Data Studio (http://dbatools.io/ads), or ADS, is a cross-platform database management tool aimed at developers. Although the name suggests “cloud-only,” Azure now means both on-premises and cloud, and, similarly, ADS works with both on-premises SQL Servers and SQL Servers that are in the cloud, as can be seen in figure 8.4.
Unlike SSMS, ADS runs on macOS and Linux. So, if your primary workstation is not Windows, this is the SQL management tool for you. Even if you’re on Windows, ADS has some really cool features that you’ll enjoy, primarily, Jupyter Notebooks, which managers and conference audiences love. Like SSMS, ADS also supports Registered Servers.
If you’ve installed ADS and registered a server connection, a file called settings.json will be created in the $homeAppDataazuredatastudio directory. SSMS 18 will pick up on this and add it to your Registered Server list.
If this file does not exist, then Azure Data Studio will not appear in your SSMS Registered Servers tab. Azure Data Studio stores encrypted credentials (http://dbatools.io/adsencrycreds) in the Credential Manager, which is tied to both a specific user and a specific computer.
Our Registered Server commands provide valid -SqlInstance sources, as can be seen in the next code snippet.
Listing 8.3 Accessing local Registered Servers
PS> Get-DbaRegServer -Name sql01 | Get-DbaDatabase | Backup-DbaDatabase SqlInstance Database Type TotalSize DeviceType Start Dura... ----------- -------- ---- --------- ---------- ----- ----... sql01 master Full 3.71 MB Disk 2019-11-25 11:27:38 00:0... sql01 model Full 2.45 MB Disk 2019-11-25 11:27:39 00:0... sql01 msdb Full 14.08 MB Disk 2019-11-25 11:27:40 00:0... sql01 test Full 2.45 MB Disk 2019-11-25 11:27:40 00:0...
Although ADS supports MFA (multifactor authentication), dbatools does not support multifactor authentication with Azure Data Studio Registered Servers. The servers will appear in the list, but when you attempt to connect, the connection will be made using basic Windows Authentication. This is because ADS implements MFA with interactive GUI elements that we do not currently support.
Using the Get-DbaRegisteredServer alias A couple commands in dbatools have an associated alias, including Get-DbaRegServer. You can also use Get-DbaRegisteredServer, which is an alias of Get-DbaRegServer.
To use MFA within dbatools, check out the examples for Connect-DbaInstance.
8.3 Central Management Server
The Central Management Server, or CMS, was introduced in SQL Server 2008 and is intended to keep a centralized repository of SQL Servers for multiple DBAs within an organization. Unlike Local Server Groups, CMS supports only the Database Engine. The Central Management Server shines most when your organization uses Windows Authentication and has multiple DBAs to share the inventory. The Central Management Server is highly integrated with SSMS but ultimately resides in msdb, so it’s also part of SQL Server itself, as can be seen in figure 8.5.
Creating groups and servers is straightforward (http://dbatools.io/ssmscms), though you may find yourself frustrated by the lack of authentication options, which include only Active Directory Integrated and Windows Authentication, as shown in figure 8.6. This is likely because storing SQL login credentials is pretty much credential sharing, which is an ill-advised security practice.
Currently, if you’d like to use alternative credentials or login methods within SSMS, as shown in the next code sample, you will have to stick with regular local Registered Servers or Azure Data Studio. dbatools is not impacted by this limitation because we offer the option to attach a -SqlCredential to any command for any server.
Listing 8.4 Using alternative credentials to connect to the remote machine
PS> $cred = Get-Credential sqladmin PS> Get-DbaRegserver -SqlInstance dbainstance | Backup-DbaDatabase -SqlCredential $cred
In listing 8.4, servers are gathered from the CMS instance dbainstance, and all of their databases are backed up by an administrator who has logged in as the SQL login sqladmin.
CMS is available in all editions of SQL Server, including the freely available SQL Server Express. This allows you to centralize your inventory for all of your DBAs without incurring additional costs.
To connect to the Central Management Server using dbatools, use the -SqlInstance parameter with Get-DbaRegServer as shown here.
Listing 8.5 Connecting to the CMS
PS> Get-DbaRegServer -SqlInstance dbainstance Name ServerName Group Description ---- ---------- ----- ----------- Dynamics dynsql01 ProductionHR Dedicated to Dynami... System Center scsql01 ProductionInfra SCCM clustered inst... vCenter vcsql01 ProductionInfra Contact Frank for m... VDI Cluster vdisql01vdi ProductionInfra SQL 2008 cluster ... SharePoint Cluster spsql01 ProductionWeb Nodes: spsql01n1, s... sql2012cluster sql2012cluster TestSQL 2012 Developer edition ... sql2014 sql2014 TestSQL 2014 Enterprise Edition ... sql2016 sql2016 TestSQL 2016 SP1, so the good st... sql2017exp sql2017exp TestSQL 2017 Express test for mi... sql2017 sql2017 TestSQL 2017 Enterprise edition ...
In listing 8.5, dbainstance is the SQL Server that holds the CMS repository.
8.4 Inventory organization
If you are new to Registered Servers and wonder how to organize your inventory, there is no set standard, so the decision is all yours. Some examples of inventory group design that we’ve seen or used at some point follow:
Even within the same organization, servers may be organized into different groups at different times, depending on the company’s needs. In particular, we often find that organizing by Version and Edition can be especially useful during migrations. You can even include one SQL Server in multiple groups, so adding one server to both the Version and Edition groups is possible. Once the migrations are complete, we then move those servers back their Department folder.
8.4.1 Importing advanced environment folder structures
Already have a CSV file with a list of servers? You can easily import it using Add-DbaRegServer, as shown in the following code listing. You need only the ServerName column, but you can also import the Name, Description, and Group columns, as well as others. Group even supports subgroups.
Listing 8.6 Importing complex structures using CSV
PS> Import-Csv -Path C: emp egservers.csv ServerName Name Description Group ---------- ---- ----------- ----- sql2017 SQL Server 2017 Older version TestDevOld sql2019 SQL Server 2019 The newest SQL TestDev sqlcluster SQL Server Cluster Prod FCI Prod PS> Import-Csv -Path C: emp egservers.csv | Import-DbaRegServer -SqlInstance sql2016 Name ServerName Group Description Source ---- ---------- ----- ----------- ------ SQL Server 2017 sql2017 TestDevOld Older version Central Manag... SQL Server 2019 sql2019 TestDev The newest SQL Central Manag... SQL Server Cluster sqlcluster Prod Prod FCI Central Manag...
We try to be as flexible as possible and offer a number of different ways to import and manage new servers and even server groups, which will be detailed shortly.
Once you’ve created your estate, which likely includes groups, we also offer ways to filter by these groups, as can be seen in the next code listing. This allows to you execute commands against a limited number of specific servers. For example, you may want to run resource-intensive queries during the day and limit them just to the servers where the local time is not within business hours.
Listing 8.7 Filtering Registered Servers by group
PS> Get-DbaRegServer -SqlInstance sql2016 -Group TestDev Name ServerName Group Description Source ---- ---------- ----- ----------- ------ SQL Server 2017 sql2017 TestDev Older version Central Management S... SQL Server 2019 sql2019 TestDev The newest SQL Central Management S... PS> Get-DbaRegServer -SqlInstance sql2016 -ExcludeGroup TestDev Name ServerName Group Description Source ---- ---------- ----- ----------- ------ SQL Server Cluster sqlcluster Prod Prod FCI Central Management Servers
Get-DbaRegServer accepts an array of strings that you can use to include or exclude as many groups as you wish.
Getting Registered Servers in both Local Server Groups and CMS
To replicate the results seen in figure 8.1, add both -SqlInstance and -IncludeLocal, as shown here.
Listing 8.8 Getting a list of all Registered Servers
This command gets all Registered Servers found in the Central Management Server on dbainstance, as well as the servers registered in Local Server Groups and Azure Data Studio. You can then use this information to run commands or queries against the resulting servers.
8.5 Further integration
dbatools has more than 10 commands that help you easily manage Registered Servers and Registered Server groups, as shown in table 8.1.
Table 8.1 Registered Server commands
We’ve covered the Get-DbaRegServer command in depth and will now touch on Add-DbaRegServer and other available commands a bit more.
8.5.1 Adding new Registered Servers
If you’re setting up your inventory for the first time, or adding a new server to your estate, you’ll also want to add it as a Registered Server. Adding new Registered Servers is supported by Local Server Groups and the Central Management Server. To add a new connection to Azure Data Studio, you must use ADS itself.
We tried to make adding a Registered Server as simple as possible. In SSMS, all you need to add is a server name, and the same is true with dbatools, as shown next.
Listing 8.9 Adding a Registered Server to Local Server groups
Like SSMS, we automatically fill in the Registered Server Name field when it is not provided by -Name. Because -SqlInstance was not specified, listing 8.9 adds a Registered Server to the Local Server Groups. See figure 8.7 for a visualization of this process.
Adding to the Central Management Server is just as straightforward, but this time, we must specify the CMS server using -SqlInstance, as shown in the next code sample. Both Local Server Groups and the CMS support Groups and will automatically create them if they do not exist.
Listing 8.10 Adding a Central Management Server in the OnPrem group
PS> $splatRegServer = @{ SqlInstance = "sqldb01" Group = "OnPrem" ServerName = "sql01" } PS> Add-DbaRegServer @splatRegServer
Groups are useful in helping you organize larger estates but are not required. If you do not add a Registered Server to a group, it will just go to the root of Local Server Groups or the CMS.
If you’d like to organize by groups, you may have noticed in earlier examples that we support nested groups. To add nested groups (think of them like subfolders), use a backslash (), as shown here.
Listing 8.11 Adding a Central Management Server to a nested group
PS> $splatRegServer = @{ SqlInstance = "sqldb01" Group = "OnPremAccounting" ServerName = "sql01" } PS> Add-DbaRegServer @splatRegServer
The command in listing 8.11 automatically creates both the OnPrem and the Accounting groups if they do not exist. It will then add sql01 to the Accounting folder.
We tried to make adding Registered Servers as flexible as possible, so you have a number of ways you can do it. One of the coolest ways is to use Connect-DbaInstance, as shown next. This allows you to keep credentials for servers that require alternative authentication.
Listing 8.12 Registering SQL Server on a Linux instance
PS> $splatConnect = @{ SqlInstance = "dockersql1,14333" SqlCredential = "sqladmin" } PS> Connect-DbaInstance @splatConnect | Add-DbaRegServer ➥ -Description = "Container for AG tests"
In listing 8.12, a connection attempt will be made to the Docker instance, and once established, the Docker instance will then be added to Local Server Groups, complete with credentials for SQL authentication. See the result in figure 8.8.
8.5.2 Copy, Export, Import
Our Copy, Export, and Import commands currently support only the Central Management Server. Copying servers is useful when migrating to a new server. Imports and exports are useful for disaster recovery, where the original server is no longer available and Registered Servers must be restored from disk.
Listing 8.13 Using Copy, Export, and Import
# Copy from one server to another PS> Copy-DbaRegServer -Source sql2008 -Destination sql01 # Export CMS list to an XML file PS> $splatExportRegServer = @{ SqlInstance = "sql2008" Path = "C: emp" OutVariable = file } PS> Export-DbaRegServer @splatExportRegServer # Import CMS list from an XML file PS> Import-DbaRegServer -SqlInstance sql01 -Path $file
These examples show various ways to migrate data from sql2008 to sql01. You may already be familiar with the export/import function, because it was one of the most popular ways to migrate CMS Registered Servers in SSMS, as shown in figure 8.9.
8.5.3 Moving Registered Servers
In the event that you need to move things around, perhaps after a migration or due to an organizational change, you can easily move registered servers from group to group using Move-DbaRegServer, as shown in the next listing.
Listing 8.14 Moving a Registered Server to a new group
In listing 8.14, one Registered Server, Web SQL Cluster, is moved from its current group (it can be any group) to the Prod group within the HR group.
You may also want to move your servers in batches, perhaps because you’ve changed the layout of your Registered Server groups. Doing this in SSMS is a mulitistep, exhausting process. But with dbatools, you can execute a single command and pipe in multiple servers to move them all at once, as seen next.
Listing 8.15 Moving multiple Registered Servers to a new group
In this listing, all Registered Servers matching the phrase “HR” will be moved to the Prod group within the HR group.
8.5.4 Removing Registered Servers
In the event that you decommission servers and remove them from your Registered Server groups, dbatools makes it easy to remove Registered Servers as well, as shown in the following code sample.
Listing 8.16 Removing a single Registered Server
In listing 8.16, the Registered Server sql01 has been removed. You can also remove multiple servers using piping. If you do not wish to confirm each removal, use the PowerShell convention -Confirm:$false, as shown here.
Listing 8.17 Removing multiple Registered Servers
8.6 Registered Server groups
Similarly, Registered Server groups are also supported by dbatools, as shown in table 8.2.
Table 8.2 Registered Server group commands
|
Move a group of servers to the root of your Registered Servers or within another group. | ||
|
Helps delete groups, including the Registered Servers they contain. |
As we mentioned earlier, managing Registered Servers (and Registered Server groups) can be rather tedious because it requires a ton of clicking, but dbatools makes it easy.
8.7 Hands-on lab
Let’s use what you have read about in this chapter to accomplish the following tasks: