Every organization has at least one of those guys. You are the guy if you have been with the company long enough that you are very familiar with all of the servers in multiple environments. You can easily translate the SharePoint server into its server name, CL-DB-001-B\SP_2010 and you know all of the DNS aliases pointing to that server as well. Your super power also makes you a magnet for everyone in IT to come and ask you where this database is and what is on that server.
While that guy may look like the nerdy stud that everyone is impressed with, this situation is actually a symptom of a documentation or tooling problem because, someday, that guy will leave the company with everyone else fumbling to find their databases.
As I mentioned, the solution to this problem is documentation, tooling, or both. You could document your server list in something such as an Excel spreadsheet or text file. A method like this can work but only if your team has a high level of discipline. That spreadsheet will only be accurate until the next time something changes, unless it is updated continuously. Maybe adding or removing a server name isn’t very difficult or frequent but I’ve found that databases come, go, and move, more often than people are willing to update documentation. For this reason, I prefer using a tool to help me manage my servers and search for databases.
In this article I will cover my preferred organizational method for setting up a CMS server list, demonstrate a T-SQL search method which can easily be put into a SQL Server Reporting Services report (SSRS is not in scope for this article), and demonstrate a PowerShell search function.
SQL Server Central Management Server
SQL Server has a feature called Central Management Server (CMS). It has a number of useful features such as multiple server querying, a central point of management for SQL Agent jobs, and central administration of policy based management. However, today we are going to focus on the most basic of features, its ability to store a server list.
For the sake of brevity I am going to skip the tutorial on how to set up your CMS and add servers to the list. If you have never used CMS before, check out these instructions.
For those of you who already have a text file or Excel document with a list of servers, here is a PowerShell script which will import them into your CMS to prevent a lot of copy & pasting.
NOTE: Make sure to update the server list source, server name, and CMS folder, highlighted above.
Searching with T-SQL
I have found that there are two common types of searches that an administrator would typically conduct on a server list. The first is a search on various characteristics of the server. For example, you might want to find all servers that are still on SQL Server 2005, servers that support a particular application, or servers owned by a particular analyst or business unit. The second search is when you are looking for the correct server name or connection string for a database.
Metadata search
When you create a registered server in the CMS, you can add a description. This is a great opportunity to store your searchable metadata. Typically I will create a description string similar to this.
SQL Version: SQL Server 2005 – 9.00.1399.06; OS Version: Windows Server 2003 – SP1; Owner: Jimmy Smith; Application(s): SharePoint, HomeGrownApp2; Alias(es): None; Environment: Production;
With this information, plus the required display name and server name, most of the commonly search attributes can be searched by querying just the CMS. Below is a highly generic query for finding your servers with any folder CMS structure. Check out the reference for a more advanced version that is more specific to a particular structure.
Reference: Querying Central Management Servers
Database search
Searching for a database incurs a bit more overhead than searching for metadata. This is because a server’s metadata will not likely change often, which makes manually updating it as part of your change process reasonable. Databases on the other hand, could come and go at a much higher frequency, depending upon how your organization works. Also, keeping a large number of database names in your CMS registered server’s description block is not ideal.
In order to know that you have the most up-to-date information, we will have to query each server in your list to find any database(s) with a particular keyword in its name. Fortunately, CMS comes with a feature to query multiple servers at once.
Simply right click on one of your CMS folders and select New Query to create a query window that is connected to all of the servers in that folder.
Reference: MSDN instructions
Once your window is open you will see that the bar at the bottom of the query window is a different color and there will be an indicator of how many servers have been successfully connected to. From here, you can run this query to a list of the servers with databases that match your keyword.
Searching with PowerShell
I have a natural love for PowerShell. Fortunately, that is not the only reason to use it for this type of searching. When I present the T-SQL solutions, I have received feedback suggesting that the database names get added to the metadata of the server. That way you would not need two queries and you would not need to connect to each server, which can be slow. I don’t like putting database names in the metadata because databases come, go, and get renamed, far more often than the server metadata does. Including the databases in the metadata causes a maintenance burden and the purpose here is to reduce the need for your time and effort.
I, however, agree with it being a hassle to have two queries and you are going to want to maximize your use of asynchronous processes when querying all of the servers on your list for a database name search. PowerShell enables us to do all of this in a single function or cmdlet.
Metadata search
For the metadata search I will leverage a PowerShell function which I first blogged about here. In that post I demonstrated how you can easily retrieve a list of servers based on the group or folder structure of your CMS. Those functions are used in this Search-CMS function which will search the description field of your registered servers.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
Function Search-CMS { param ( [Parameter(Mandatory=$false, ValueFromPipeline=$True, ValueFromPipelineByPropertyName=$True)] [string[]]$Keywords = $null, [Parameter(Mandatory=$false, ValueFromPipeline=$True, ValueFromPipelineByPropertyName=$True)] [string]$cmsServer = '.\sqlexpress', [Parameter(Mandatory=$false, ValueFromPipeline=$True, ValueFromPipelineByPropertyName=$True)] [string]$rootServerGroup = 'DataCenter1' ) begin { Function Get-CMSServerList ([string]$cmsName, [string]$serverGroup, [switch]$recurse) { begin { if(-not (Get-Module | where {$_.Name -eq 'SQLPS'})) { Import-Module SQLPS -DisableNameChecking } Function Parse-CMSServerGroup($serverGroup) { $results = $serverGroup.RegisteredServers; foreach($group in $serverGroup.ServerGroups) { $results += Parse-CMSServerGroup -serverGroup $group; } return $results; } } process { $connectionString = "data source=$cmsName;initial catalog=master;integrated security=sspi;" $sqlConnection = New-Object ("System.Data.SqlClient.SqlConnection") $connectionstring $conn = New-Object ("Microsoft.SQLServer.Management.common.serverconnection") $sqlconnection $cmsStore = New-Object ("Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore") $conn $cmsRootGroup = $cmsStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$serverGroup] if($recurse) { return Parse-CMSServerGroup -serverGroup $cmsRootGroup } else { return $cmsRootGroup.RegisteredServers } } } } process { Trap { $err = $_.Exception while ( $err.InnerException ) { $err = $err.InnerException write-output $err.Message }; continue } if(-not $Keywords) { Write-Warning "No keywords were provided. Dumping the complete server list and descriptions."; $SearchDatabaseName = $false; } $tempResults = @(); $Keywords = ($Keywords | % { [regex]::escape($_) } ) -join "|" $serverList = Get-CMSServerList -cmsName $cmsServer -serverGroup $rootServerGroup -recurse; $serverListOutput = $serverList | Where-Object {$_.Description -match $Keywords} | Select-Object ServerName, Description; foreach($result in $serverListOutput) { $tempResults += $result; } $results = ($tempResults | Select-Object ServerName, Description -Unique) | Format-List; return $results; } } |
To use this function you can pass in a single string or an array of strings for search keywords. I like to default my cmsServer and rootServerGroup parameters, as seen in the example above, but you can also pass them in as I have below.
Here I have searched the metadata for known version indicators. I know that the SQL Server version is in the description and I want to find all servers still running SQL Server 2005. I also want to see all servers which are still operating on Windows Server 2003.
This is already very useful but it has not, yet, met the full requirements. Let’s take a look at what needs to change to incorporate the database search.
Adding the database search
To perform our database search efficiently, we will need to spawn asynchronous threads to query each server in parallel. A typical iterative loop would take too long and our time is very important, especially if your server list is quite large. To achieve this, we will use the Start-Job cmdlet.
The link provided covers the basics if you need a refresher or overview.
Here I will modify the process block of the Search-CMS function shown above and add a new parameter, [switch]$SearchDatabaseName.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
process { Trap { $err = $_.Exception while ( $err.InnerException ) { $err = $err.InnerException write-output $err.Message }; continue } if(-not $Keywords) { Write-Warning "No keywords were provided. Dumping the complete server list and descriptions."; $SearchDatabaseName = $false; } $tempResults = @(); $Keywords = ($Keywords | % { [regex]::escape($_) } ) -join "|" $serverList = Get-CMSServerList -cmsName $cmsServer -serverGroup $rootServerGroup -recurse; if($SearchDatabaseName) { $jobs = @(); foreach($server in $serverList) { $args = @(); $args += $connTimeout; $args += $server.Name; $args += $Keywords; $jobs += Start-Job -ScriptBlock { [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null; $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $args[1] $server.ConnectionContext.ConnectTimeout = $args[0]; if($server.Version.Major -eq $null) { Write-Host "Could not establish connection to $($args[1])." } else { $servers = @(); foreach($database in ($server.Databases).Name) { if($database -match $($args[2])) { $obj = New-Object psobject; Add-Member -InputObject $obj -MemberType NoteProperty -Name Result -Value 'True'; Add-Member -InputObject $obj -MemberType NoteProperty -Name ServerName -Value $($server.Name); $servers += $obj; } } return $servers; } } -ArgumentList $args; } $joboutput = ($jobs | Wait-Job | Receive-Job | Where-Object { $_.Result -eq 'True' } | Select-Object ServerName) $jobs | Remove-Job; foreach($server in $joboutput) { $tempResults += $server; } $results = ($tempResults | Select-Object ServerName -Unique) | Format-Table; } else { $serverListOutput = $serverList | Where-Object {$_.Description -match $Keywords} | Select-Object ServerName, Description; foreach($result in $serverListOutput) { $tempResults += $result; } $results = ($tempResults | Select-Object ServerName, Description -Unique) | Format-List; } return $results; } |
This process:
- Checks to see if you want to search databases or not.
- If database search is selected, a thread will be spawned for each server in your list.
- On each thread, the Microsoft.SqlServer.Management.Smo.Server class is used to query the database list.
- If the database is found, the server name is appended to a temporary variable.
- The temporary variable is then distinct sorted and formatted for output.
- Finally, you have a list of all servers with databases that match your keyword(s).
Wrap-up
In this article I have discussed the challenges of managing multiple servers and a couple of common questions that come up often. I then demonstrated how SQL Server’s Central Management Server can organize your server list and maintain it in a central location which is advantageous in a collaborative environment. There are T-SQL means of searching and sorting your server list, if you prefer to use SQL Server Management Studio. Personally, I prefer to use a single custom PowerShell function to meet those needs.
Download the complete Search-CMS script.
Derik thanks our #sqlfamily for plugging the gaps in his knowledge over the years and actively continues the cycle of learning by sharing his knowledge with all and volunteering as a PASS User Group leader.
View all posts by Derik Hammer
- SQL query performance tuning tips for non-production environments - September 12, 2017
- Synchronizing SQL Server Instance Objects in an Availability Group - September 8, 2017
- Measuring Availability Group synchronization lag - August 9, 2016