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.

Connecting to SQL Server through Powershell

Being a newbie to powershell, ive been researching and testing different connection options for powershell scripts. Ive come across the following ways:

      • Invoke-sqlcmd Windows Authentication
      • Invoke-Sqlcmd SQL Authentication
      • Invoke-Sqlcmd with Authentication Prompt
      • SQLConnection.connectionstring property (.NET)
      • SMO Connection with Authentication prompt
      • SMO Connection with hardcoded details

Invoke-sqlcmd Windows Authentication

Takes current windows connection details to try to do pass through authentication onto SQL Server

#Uses windows Authentication to traverse through instance list and return db count $filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" $results = @() foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd ` -Query "select SERVERPROPERTY('ServerName') as Server, count(*) as 'DB Count' from sys.databases" ` -ServerInstance $instanceName } # print results $results | Format-Table -autosize

Invoke-Sqlcmd SQL Authentication

#Uses sql Authentication to traverse through instance list and return db count $filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" $results = @() foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd ` -Query "select SERVERPROPERTY('ServerName') as Server, count(*) as 'DB Count' from sys.databases" ` -ServerInstance $instanceName ` -U "MyUsername" -P "password" } # print results $results | Format-Table -autosize

Invoke-Sqlcmd with Authentication prompt

This option uses the get-credentials cmdlet to bring up a prompt whereby connection details can be entered. These are then passed through to a connection method.

$filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" #resets the variable $results = @() # login using SQL authentication, which means we supply the username # and password $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential #the backslash is regular expression to remove \ (prompt will give it as default for domain) $userName = $credential.UserName -replace("\\","") #getnetworkcredential gives the passport unencrypted $pass = $credential.GetNetworkCredential().password $db = "master" foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -U $userName -P $pass -Database $db -ServerInstance $instancename ` -Query "select SERVERPROPERTY('ServerName') as Server, count(*) as 'DB Count' from sys.databases" } # print results $results | format-table -Autosize

When the script is run, a popup is presented to the user:


If the credentials are correct, then the script returns how many databases are in each server in the instancelist.

SQLConnection.ConnectionString Property

The connection string and the query are fed into a SQLClient.SQLDataAdapter and the output formatted

foreach ($svr in get-content "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\serverlist.txt") { $con = "server=$svr;database=master;Integrated Security=sspi" $cmd = "SELECT SERVERPROPERTY('ServerName') AS Instance, SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP" $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con) $dt = new-object System.Data.DataTable $da.fill($dt) | out-null $dt | Format-Table -autosize }

SMO Connection with Authentication Prompt

# load assemblies [Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, ` Version=, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, ` Version=, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, ` Version=, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, ` Version=, Culture=neutral, `PublicKeyToken=89845dcd8080cc91") # connect to SQL Server named instance # use server\instancename $serverName = "Server\INSTANCE" $server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName" # login using SQL authentication, which means we supply the username # and password $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential $userName = $credential.UserName -replace("\\","") $server.ConnectionContext.set_Login($userName) $server.ConnectionContext.set_SecurePassword($credential.Password) # clear the screen cls # list connection string Write-Host "--------------------------------------------------------" Write-Host "Connection String : " Write-Host $server.ConnectionContext.ConnectionString Write-Host "--------------------------------------------------------"

Powershell: Invoke-Sqlcmd command not recognized

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, 
script file, or operable program. Check the spelling of the name, or if a path 
was included, verify that the path is correct and try again.

When trying to run the Invoke-sqlcmd command within the powershell utility, an error is returned suggesting the command is not recognized.

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "mysqlserver"


This happens because the Invoke-sqlcmd cmdlet is not included as part of PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). Before using invoke-sqlcmd you should install SSMS or the SQL Server 2008 Feature Pack

When sqlps has been installed, you may then load invoke-sqlcmd into powershell.exe by using the below script from Michiel Wories blog http://blogs.msdn.com/b/mwories/archive/2008/06/14/sql2008_5f00_powershell.aspx

Im still a novice at powershell so Ive ended up building the initiliaze into all my basic scripts using dot-source

# Use Dot-Source to Call the “Initialize-SQLPSEnvironment” Script. .{."\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\Initialize-SqlPsEnvironment.ps1"} $instanceNameList = Get-Content "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\ServerList.txt" foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -Query "select SERVERPROPERTY('ServerName') As ServerName, Name, crdate from sysdatabases" -ServerInstance $instanceName } # print results $results

The above script calls the initialize script, and then does a basic SQL Query

Cannot detach a suspect or recovery pending database

After losing connectivity to the data and log volume for one of our SQL servers, we received the following error:

LogWriter: Operating system error 2
(failed to retrieve text for this error. Reason: 15100) encountered.

Which resulted in the database on that volume showing as being suspect.

After gaining connection back to the volume, I tried to detach the database, but received the following error:

Cannot detach a suspect or recovery pending database. 
It must be repaired or dropped. (Microsoft SQL Server, Error: 3707) 

To resolve i did the following:


EMERGENCY mode marks the database as READ_ONLY, disabled logging, and access is limited to sysadmins. Marking the database in this mode is a first step for resolving log corruption.

ALTER DATABASE emergencydemo set single_user
ALTER DATABASE emergencydemo set multi_user

This should resolve any corruption and bring the database online. The database will come out of EMEREGENCY mode automatically.

Auditing Basics

The following example creates an Audit which will write to a file on the C:\ drive to record any Inserts or Selects against the AdventureWorks2008 sales.customer table

Create the Audit.

GUI: Instance | Security | Audits | New Audit

USE [master]

/****** Object:  Audit [ServerAuditDB]    Script Date: 01/19/2012 12:30:19 ******/
(    FILEPATH = N'C:\AuditSQL2008\'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
(    QUEUE_DELAY = 1000
    ,AUDIT_GUID = '8383f3a0-3f76-4d11-943d-5a504f7f7698'

Then create a Database level Audit to monitor the Sales.Customer table for Select or Inserts

GUI: Instance | Databases | AdventureWorks2008 | Security | Database Audit Specifications | New ..

USE [AdventureWorks2008]

ADD (INSERT ON OBJECT::[Sales].[Customer] BY [dbo]),
ADD (SELECT ON OBJECT::[Sales].[Customer] BY [dbo])

Make sure the Audits are enabled and then Test:

select * from sales.customer

View the Audit either by specifically selecting from the Audit file:

SELECT * FROM sys.fn_get_audit_file ('C:\AuditSQL2008\ServerAuditDB_8383F3A0-3F76-4D11-943D-5A504F7F7698_0_129714493641530000.sqlaudit',default,default);

Or by Doing the following to select from all Audit files in the location:

SELECT * FROM sys.fn_get_audit_file ('C:\AuditSQL2008\*',default,default);
statement FROM 
sys.fn_get_audit_file ('C:\AuditSQL2008\*',default,default);


Enable/Disable Audit Error–Cannot alter a server audit from a user database

Cannot alter a server audit from a user database. 
This operation must be performed in the master database. 
(Microsoft SQL Server, Error: 33074)


This error occurs when enabling/disabling both Audits and Server Audit Specifications via the GUI SSMS. It happens because the admin who is editing the audit does not have the default database set to Master.

To resolve  either change the login to have a default database of master or run via T-SQL.

USE [master]

This has been recognised as an issue in SQL Server 2008 GUI and is resolved in Denali

Ora-00494: Enqueue [Cf] Held For Too Long

Applies to Oracle Single Instance running on OEL 5 X86_64

Problem: One of our QA Databases crashed due to the following error:

Tue Jan 17 12:53:12 2012
Errors in file /u01/app/oracle/diag/rdbms/myQAdb/MYQADB/trace/MYQADB_lgwr_616.trc  (incident=203762):
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 796'
Incident details in: /u01/app/oracle/diag/rdbms/myQAdb/MYQADB/incident/incdir_203762/MYQADB_lgwr_616_i203762.trc
Killing enqueue blocker (pid=796) on resource CF-00000000-00000000
by killing session 1086.1
Tue Jan 17 12:53:16 2012
Sweep Incident[203762]: completed

Prior to the database crash we were seeing errors from OEM

Message=1 distinct types of incidents have been found in the alert log.

Metric=Generic Incident Status

Having looked into the errors in the alert log and the subsequent database crash at 12:53, it seems the issue was caused by the database running out of memory

Full details are given here: Ora-00494: Enqueue [Cf] Held For Too Long Causing Database To Crash [ID 1101862.1]

within MYQADB_lgwr_616_i203762.trc trace file

loadavg : 0.12 0.11 0.09

memory info: free memory = 0.00M

swap info:   free = 0.00M alloc = 0.00M total = 0.00M


0 S oracle     796     1  0  75   0 – 1696141 -    Jan10 ?        00:00:28 ora_arc2_MYQADB

We bounced both the database and the server to clear memory which seemed to resolve the issue. We have not had the Enqueue error since then.

The solution being offered on the metalink note is If RAM > 8192MB then swap should be .75 times RAM, however the memory on the server is already correctly set.

This is one we will just be keeping an eye on…