Powershell – Retrieve SQL Server Info for all my Servers

Learning Powershell – Day 2

My aim in implementing powershell has always been for monitoring purposes.. to easily be able to retrieve a snapshot of my SQL Server estate by simply running a powershell script. Regardless of the SQL Query running in the powershell script, i wanted to achieve the following things:

  • Read a list of SQL Servers from a file
      • For each entry – Make a connection to SQL Server in Powershell (Discussed in more detail here)
      • For each entry – Run the same T-SQL query
      • For each entry – Do some error trapping around connectivity
      • Collate all the results into 1 table
      • Do some formatting
      • Either print the results to the screen or to a file

    The idea was that if i can incorporate these things into 1 script, then it would be easily transferrable to other more complex scripts. In essence, the aim was to create a template for future use.

    For the database connection, i am using smo and the get-credential cmdlet. I am feeding in a preset sql authenticated username and asking the user to provide the password.
    #Declare and set the file variables $filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" $inputfile = "$filelocation\ServerProperties.sql" $outputfile = "$filelocation\serveroutput.log" #Reset the arrays $results = @() $resultsout = @() #Preset the default database for invoke-sqlcmd $db = "master" #Supply the username and password (SQL Authentication popup) $server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential -credential "DBA_Monitor" $userName = $credential.UserName -replace("\\","") $pass = $credential.GetNetworkCredential().password #Run the query for each server foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -Database $db -ServerInstance $instanceName -U $username -P $pass -InputFile $inputfile #handle login failures trap { "Oops! $_ on server $instancename"; continue } } # print results $results | format-table -property Host, Instance, Version, Edition, 'Patch Level', SP, ServerType -Autosize #Uncomment below to send to file #$resultsout = $results | format-table -property Server, Instance, Edition, Version, SP -Autosize #$resultsout | out-file -filepath $outputfile

    The script uses the following serverproperties.sql file
            SERVERPROPERTY('MachineName') as Host,
            SERVERPROPERTY('InstanceName') as Instance,
            SUBSTRING(@@VERSION, 1,25) as Version,
            SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
            SERVERPROPERTY('ProductVersion') AS 'Patch Level', 
            SERVERPROPERTY('ProductLevel') as SP, /* RTM or SP1 etc*/
            Case SERVERPROPERTY('IsClustered') 
            when 1     then 'CLUSTERED' else
                      'STANDALONE' end as ServerType

When run using the Windows Powershell ISE, the following happens:

1. Prompted for password


As the screenshot below shows, the login is disabled in 1 of the servers in the list, which creates an error trap. The other 2 servers work successfully and return the data


The formatting is done via the format-table cmdlet. I specify the columns to ensure a table is built, but also to make it easier to add and remove columns from the end result.

Although commented out in the script, there is an option to send the results to file. This is done via the out-file cmdlet.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: