This time, the zip file for Invoke-SQLQuery contains two functions. The first function is Query-ServerCatalog. This function builds a simple SELECT statement and then uses Invoke-SQL Query to run it. This is useful as an example of the sort of thing that you can do with Invoke-SQLQuery. Query-ServerCatalog will return a result set full of servernames, assuming that you have a particular table in a particular database on a particular server that holds these names. Setting that up and keeping it current is a bit of work and probably is not worth doing unless you work in a large environment with a group of DBAs.
The more interesting function is Get-SQLServerList. Some of the highlights of Get-SQLServerList are:
- It reads a text file to find the names of servers and matches them against the $computer parameter.
- There is simplistic support for 'comments' in this text file. A '#' at the start of a line indicates that the remainder of the line should be ignored. This allows commentary information to be placed in the text file and provides a simple way to temporarily ignore a particular server.
- If the text file isn't found, Get-SQLServerList tries to grab a list of servers from a table using Query-SQLServerCatalog. I don't use this function now. Instead, I rely entirely on text files to store the server names. Text files are very easy to manage and change. I am leaving the feature alone because it's not broken, as far as i know, and it might be useful to me or someone else in the future.
- If a value is specified for -ProductVersion, Get-SQLServerList actually connects to all of the servers that match the given value of $computer and then looks for matching ProductVersion information. Even though it connects to all of the servers it still runs in just a few seconds.
With careful naming of servers, I can quickly select subsets of the entire server population. For our purposes, a seven character long name might consist of the following 'fields', along with some made-up examples:
Three characters indicating location of server: PHL (Philadelphia), NYC (New York City), SEA (Seattle)
Two characters indicating environment: DE (Development), "QA" (Quality Assurance), "PR" (Production)
Two characters indicating a 'server number': 01, 02, ..., 10, ..., 20, ..., 99
Using this scheme, the contents of the text file might look like this:
PHLQA05
PHLPR06
PHLPR88
NYCDE11
NYCPR11
NYCPR44
SEADE04
SEADE05
SEAPR33
SEAQA33
# end of file
I normally alias Get-SQLServerList to just "ssl", to cut down on typing. Here are some quick examples, using my made-up server names, followed with some made-up output.
# Show me all of the servers
PS>ssl
PHLQA05
PHLPR06
PHLPR88
NYCDE11
NYCPR11
NYCPR44
SEADE04
SEADE05
SEAPR33
SEAQA33
# Show me all of the QA servers. Note that the function does not use capitalization-sensitive comparisons:
PS>ssl qa
PHLQA05
SEAQA33
# Show me all of the Philly servers:
PS>ssl phl
PHLQA05
PHLPR06
PHLPR88
# Show me a particular server:
PS>ssl phlpr88
PHLPR88
And so on. By providing a -productversion value, you can run more intricate queries:
# Show me all of the sql2005 servers:
PS>ssl -ProductVersion 2005
PHLQA05
PHLPR06
PHLPR88
NYCDE11
NYCPR11
# Show me all of the sql2005 servers in Philly
PS>ssl phl 2005
PHLQA05
PHLPR06
PHLPR88
# Show me all of the servers with build 3054
PS>ssl -ProductVersion 3054
PHLPR06
So, we can easily find servers that are interesting to us. Note that both the -Computer and -ProductVersion are using Powershell's matching semantics. This means that you can create fairly complicated searches to answer questions like "show me the Philly servers with even-numbered server numbers".
That's all for now. In my next post, I'll show you some more of the commands that leverage Get-SQLServerList and Invoke-SQLQuery.
No comments:
Post a Comment