Managing Server Config Data, And Then Some
We used PowerShell to get information on a single server. This time, how to get info on several of your servers.
- By Joshua Jones
- 10/26/2009
Last time, we built a PowerShell script to get server information. It used SQL SMO and WMI. Now, let's look at upgrading the script to handle multiple servers. First, check out the code here (in full color coding):
 |
[Click image to view larger version.] |
You might recognize that "inner block" of code. Yep, it's the script from the last column. Notice the new line just above the block; here, we're declaring a loop that will take a list of servers to work through:
#loop through each server from our list of servers
foreach ($servername in get-content "C:\Projects\MyServers.txt")
{
Obviously, you need to point the call to get-content to the correct directory and file in your environment. By doing this, you're simultaneously declaring the variable $servername and assigning it a value for each iteration. And note the curly bracket that denotes the start of the block of looped code.
Also, notice the lack of a variable assignment for $servername. This is because in the code chunk, we're declaring and assigning values to $servername in one shot. This is an easy way to iterate through a server list.
Finally, don't forget the final curly bracket to end the code block.
While we're at it, let me introduce you to the get-content cmdlet. This is handy for doing exactly what we're doing here: opening a file and retrieving values, one line at a time. It can take some simple parameters, such as credentials, exclusions, and transaction control. Want to see the entire syntax? Run this:
PS SQLSERVER:\>get-help Get-Content -examples
While I created this by hand, you could easily use PowerShell to get a list of servers out of a table and create this file for you daily. The command would look something like this:
Invoke-Sqlcmd -Query "SELECT [NAME] FROM Admin.dbo.ServerInfo;" | Out-File -filePath "C:\Projects\MyServers.txt"
However you generate the file, make sure that it's a consistent format, and that it is reliable. Once that's done, you can use this code to go through your environment and store SQL Server configurations.
Next time, we'll look at the more advanced configuration management features of SQL Server 2008: policy-based management. We'll also use PowerShell to review and update tables as needed, building on the objects used in the last few posts. Until then, have fun!
About the Author
Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.