SQL Server PowerShell : How to Audit Your SQL Server Instance Properties Using PowerShell and SMO

Here is a simple script to audit your SQL Server Instance Properties.

Note that the property names are not hardcoded. We query each of these properties, and use those to display the property values.

If you prefer, you can also query directly each of the properties of an instance. If this is the case, just specify your server object and then the property name. For example:

$serverObject.BackupDirectory

The list of properties can be found at the end of the post.

Script to do Basic Audit of SQL Server Instance Properties

[ps collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
#============================================================
#Audit Script using SMO and PowerShell
#Script below enumerates all properties of a SQL Server instance,
#and exports them to a text, csv and XML file
#Author: Donabel Santos
#============================================================

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$sqlserver = "(local)"
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver

#let’s capture all properties into an array
$objColl = $srv | gm | where {$_.MemberType -eq "Property" } | select Name

#let’s create an empty text file where we will store the server properties
$filepath = "C:serveraudit.txt"
New-Item -itemType file $filepath -force | Out-Null

#let’s loop through each property
foreach($obj in $objColl)
{
#catch any exceptions
trap [Exception]
{
$obj.Name + " : " + "Trapped $($_.Exception.Message)" | Out-File -append -filePath $filepath
continue
}

#display property on screen; we’re just curious
" " + $obj.Name

#we’re not going to print out SystemMessages, currently there’s 97526!
if($obj.Name -ne "SystemMessages")
{
$obj.Name + " : " + $srv.($obj.Name) `
| Out-File -append -filePath $filepath
}
}

#Alternative to the text file above are csv and XML files.
#Below is how you can do it
#Notice the syntax is a little bit more cryptic – each of these is a one liner!
#Looks messy, eh?

#Export all properties to a CSV file – one liner
$srv | gm | where {$_.MemberType -eq "Property" -and `
$_.Name -ne "SystemMessages"} `
| select Name, @{Name="Value";Expression={[string]$srv.($_.Name)}} `
| Export-Csv -path "C:serveraudit.csv" -noTypeInformation

#Export all properties to an XML file – one liner
$srv | gm | where {$_.MemberType -eq "Property" -and `
$_.Name -ne "SystemMessages"} `
| select Name, @{Name="Value";Expression={[string]$srv.($_.Name)}} `
| Export-Clixml -path "C:serveraudit.xml" -encoding "UTF8"

[/ps]

List of SQL Server Instance Properties ..

that you can query from PowerShell

  ActiveDirectory
  AuditLevel
  Audits
  BackupDevices
  BackupDirectory
  BrowserServiceAccount
  BrowserStartMode
  BuildClrVersion
  BuildClrVersionString
  BuildNumber
  Collation
  CollationID
  ComparisonStyle
  ComputerNamePhysicalNetBI
  Configuration
  ConnectionContext
  Credentials
  CryptographicProviders
  Databases
  DefaultFile
  DefaultLog
  DefaultTextMode
  Edition
  Endpoints
  EngineEdition
  ErrorLogPath
  Events
  FilestreamLevel
  FilestreamShareName
  FullTextService
  Information
  InstallDataDirectory
  InstallSharedDirectory
  InstanceName
  IsCaseSensitive
  IsClustered
  IsFullTextInstalled
  IsSingleUser
  JobServer
  Language
  Languages
  LinkedServers
  LoginMode
  Logins
  Mail
  MailProfile
  MasterDBLogPath
  MasterDBPath
  MaxPrecision
  Name
  NamedPipesEnabled
  NetName
  NumberOfLogFiles
  OleDbProviderSettings
  OSVersion
  PerfMonMode
  PhysicalMemory
  Platform
  Processors
  Product
  ProductLevel
  Properties
  ProxyAccount
  ResourceGovernor
  ResourceLastUpdateDateTim
  ResourceVersion
  ResourceVersionString
  Roles
  RootDirectory
  ServerAuditSpecifications
  ServerType
  ServiceAccount
  ServiceInstanceId
  ServiceMasterKey
  ServiceName
  ServiceStartMode
  Settings
  SqlCharSet
  SqlCharSetName
  SqlDomainGroup
  SqlSortOrder
  SqlSortOrderName
  State
  Status
  SystemDataTypes
  SystemMessages
  TapeLoadWaitTime
  TcpEnabled
  Triggers
  Urn
  UserData
  UserDefinedMessages
  UserOptions
  Version
  VersionMajor
  VersionMinor
  VersionString
Advertisements

One comment

  • Hey

    first let me say thank you!
    Awesome tutorials, really!

    Do you think it is possible to “monitor” the log file size of a Database? And maybe also the Database size?

    I have around 40 Servers out there where i want to check if the size is ok…
    Would be cool if you could do something like this in the future!

    Thanks so far!

    Like

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