PowerSQL – List SQL Server instance configuration details and sending an automated email

This article walks through how to list SQL Server configurable and non-configurable instance settings using PowerShell and sending an automated email to a listed users. The Function SendEmail has MailServerName as one of its parameter. Please change accordingly.The following code loads the SMO(SMO Server Objects) assemblies.
Import the SQLPS module, and create a new SMO Server object and Specify the DisableNameChecking parameter if you want to suppress the warning about Encode-Sqlname and Decode-Sqlname.

#import SQL Server module
PS:\>Import-Module SQLPS -DisableNameChecking

ErrorMessage -
Import-Module : The specified module ‘SQLPS’ was not loaded because no valid module file was found in any module directory.

Checkout for available modules using below code

PS:\>Get-Module -ListAvailable

If SQLPS is not there, Load the assemblies Manually using below given code.

Copy the code and load into PowerShell-ISE.exe and execute, If some of the modules are loaded already you may encounter some error. You can ignore those errors.

# Loads the SQL Server Management Objects (SMO)

********************************************************************
$ErrorActionPreference = “Stop”
$sqlpsreg=”HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps”
if (Get-ChildItem $sqlpsreg -ErrorAction “SilentlyContinue”)
{
throw “SQL Server Provider for Windows PowerShell is not installed.”
}
else
{
$item = Get-ItemProperty $sqlpsreg
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}

$assemblylist = “Microsoft.SqlServer.Management.Common”,”Microsoft.SqlServer.Smo”,”Microsoft.SqlServer.Dmf “,”Microsoft.SqlServer.Instapi “,
“Microsoft.SqlServer.SqlWmiManagement “,”Microsoft.SqlServer.ConnectionInfo “,”Microsoft.SqlServer.SmoExtended “,”Microsoft.SqlServer.SqlTDiagM “,
“Microsoft.SqlServer.SString “,”Microsoft.SqlServer.Management.RegisteredServers “,”Microsoft.SqlServer.Management.Sdk.Sfc “,”Microsoft.SqlServer.SqlEnum “,
“Microsoft.SqlServer.RegSvrEnum “,”Microsoft.SqlServer.WmiEnum “,”Microsoft.SqlServer.ServiceBrokerEnum “,”Microsoft.SqlServer.ConnectionInfoExtended “,
“Microsoft.SqlServer.Management.Collector “,”Microsoft.SqlServer.Management.CollectorEnum”,”Microsoft.SqlServer.Management.Dac”,”Microsoft.SqlServer.Management.DacEnum”,
“Microsoft.SqlServer.Management.Utility”

foreach ($asm in $assemblylist)
{
$asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

Push-Location
cd $sqlpsPath
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location

***********************************************

FUNCTION SendEmail
{
param($from,$to,$subject,$htmlFileName,$smtpServerName)
[string]$receipients=”$to”
$body = Get-Content $htmlFileName
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body
$body.isBodyhtml = $true
$smtpServer = $smtpServerName
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
}

# Change sendEmail data as per your configuration

FUNCTION Load-ServerConfiguration
{
Param ([String]$instanceName)
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$a = “<style>”
$a = $a + “BODY{background-color:peachpuff;}”
$a = $a + “TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}”
$a = $a + “TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}”
$a = $a + “TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}”
$a = $a + “</style>”

$server.Information.Properties |Select Name, Value | ConvertTo-HTML -head $a -body “<H2>Server Information</H2>” | Out-File -append F:\PowerSQL\Test.htm
$server.Settings.Properties |Select Name, Value | ConvertTo-HTML -head $a -body “<H2>Server Setting Information</H2>” | Out-File -append F:\PowerSQL\Test.htm
$server.UserOptions.Properties |Select Name, Value |ConvertTo-HTML -head $a -body “<H2>UserOption Information</H2>” | Out-File -append F:\PowerSQL\Test.htm
$server.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue | ConvertTo-HTML -head $a -body “<H2>Configuration Information</H2>” | Out-File -append F:\PowerSQL\Test.htm

SendEmail prashanth@abc.com prashanth@abc.com “Server Configuration Report – $InstanceName” f:\powersql\test.htm mail01.abc.com
}

Note: Change Output file path in the above code as per your requirement.

Load-ServerConfiguration AQDB001

Output:

Server Information

Name	Value
BuildNumber	2766
Edition	Standard Edition (64-bit)
ErrorLogPath	F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log
HasNullSaPassword
IsCaseSensitive	False
IsFullTextInstalled	True
Language	English (United States)
MasterDBLogPath	F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
MasterDBPath	F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
MaxPrecision	38
NetName	AQDB001
OSVersion	5.2 (3790)
PhysicalMemory	6143
Platform	NT x64
Processors	2
Product	Microsoft SQL Server
RootDirectory	E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL
VersionMajor	10
VersionMinor	0
VersionString	10.0.2766.0
Collation	SQL_Latin1_General_CP1_CI_AS
EngineEdition	2
IsClustered	False
IsSingleUser	False
ProductLevel	SP1
BuildClrVersionString	v2.0.50727
CollationID	872468488
ComparisonStyle	196609
ComputerNamePhysicalNetBIOS	AQDB001
ResourceLastUpdateDateTime	2/25/2010 4:37:39 PM
ResourceVersionString	10.00.2766
SqlCharSet	1
SqlCharSetName	iso_1
SqlSortOrder	52
SqlSortOrderName	nocase_iso

Server Setting Information

Name	Value
AuditLevel	Failure
BackupDirectory	F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup
DefaultFile
DefaultLog	G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data
LoginMode	Mixed
MailProfile
NumberOfLogFiles	-1
PerfMonMode	None
TapeLoadWaitTime	-1

UserOption Information

Name	Value
AbortOnArithmeticErrors	False
AbortTransactionOnError	False
AnsiNullDefaultOff	False
AnsiNullDefaultOn	False
AnsiNulls	False
AnsiPadding	False
AnsiWarnings	False
ConcatenateNullYieldsNull	False
CursorCloseOnCommit	False
DisableDefaultConstraintCheck	False
IgnoreArithmeticErrors	False
ImplicitTransactions	False
NoCount	False
NumericRoundAbort	False
QuotedIdentifier	False

Configuration Information

DisplayName	Description	RunValue	ConfigValue
recovery interval (min)	Maximum recovery interval in minutes	5	5
allow updates	Allow updates to system tables	0	0
user connections	Number of user connections allowed	0	0
locks	Number of locks for all users	0	0
open objects	Number of open database objects	0	0
fill factor (%)	Default fill factor percentage	0	0
disallow results from triggers	Disallow returning results from triggers	0	0
nested triggers	Allow triggers to be invoked within triggers	1	1
server trigger recursion	Allow recursion for server level triggers	1	1
remote access	Allow remote access	1	1
default language	default language	0	0
cross db ownership chaining	Allow cross db ownership chaining	0	0
max worker threads	Maximum worker threads	0	0
network packet size (B)	Network packet size	4096	4096
show advanced options	show advanced options	1	1
remote proc trans	Create DTC transaction for remote procedures	0	0
c2 audit mode	c2 audit mode	0	0
default full-text language	default full-text language	1033	1033
two digit year cutoff	two digit year cutoff	2049	2049
index create memory (KB)	Memory for index create sorts (kBytes)	0	0
priority boost	Priority boost	0	0
remote login timeout (s)	remote login timeout	20	20
remote query timeout (s)	remote query timeout	600	600
cursor threshold	cursor threshold	-1	-1
set working set size	set working set size	0	0
user options	user options	0	0
affinity mask	affinity mask	0	0
max text repl size (B)	Maximum size of a text field in replication.	65536	65536
media retention	Tape retention period in days	0	0
cost threshold for parallelism	cost threshold for parallelism	5	5
max degree of parallelism	maximum degree of parallelism	0	0
min memory per query (KB)	minimum memory per query (kBytes)	1024	1024
query wait (s)	maximum time to wait for query memory (s)	-1	-1
min server memory (MB)	Minimum size of server memory (MB)	500	500
max server memory (MB)	Maximum size of server memory (MB)	5000	5000
query governor cost limit	Maximum estimated cost allowed by query governor	0	0
lightweight pooling	User mode scheduler uses lightweight pooling	0	0
scan for startup procs	scan for startup stored procedures	1	1
awe enabled	AWE enabled in the server	0	0
affinity64 mask	affinity64 mask	0	0
affinity I/O mask	affinity I/O mask	0	0
affinity64 I/O mask	affinity64 I/O mask	0	0
transform noise words	Transform noise words for full-text query	0	0
precompute rank	Use precomputed rank for full-text query	0	0
PH timeout (s)	DB connection timeout for full-text protocol handler (s)	60	60
clr enabled	CLR user code execution enabled in the server	0	0
max full-text crawl range	Maximum crawl ranges allowed in full-text indexing	4	4
ft notify bandwidth (min)	Number of reserved full-text notifications buffers	0	0
ft notify bandwidth (max)	Max number of full-text notifications buffers	100	100
ft crawl bandwidth (min)	Number of reserved full-text crawl buffers	0	0
ft crawl bandwidth (max)	Max number of full-text crawl buffers	100	100
default trace enabled	Enable or disable the default trace	1	1
blocked process threshold (s)	Blocked process reporting threshold	0	0
in-doubt xact resolution	Recovery policy for DTC transactions with unknown outcome	0	0
remote admin connections	Dedicated Admin Connections are allowed from remote clients	1	1
filestream access level	Sets the FILESTREAM access level	0	0
optimize for ad hoc workloads	When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.	0	0
access check cache bucket count	Default hash bucket count for the access check result security cache	0	0
access check cache quota	Default quota for the access check result security cache	0	0
Agent XPs	Enable or disable Agent XPs	1	1
SQL Mail XPs	Enable or disable SQL Mail XPs	0	0
Database Mail XPs	Enable or disable Database Mail XPs	1	1
SMO and DMO XPs	Enable or disable SMO and DMO XPs	1	1
Ole Automation Procedures	Enable or disable Ole Automation Procedures	1	1
xp_cmdshell	Enable or disable command shell	1	1
Ad Hoc Distributed Queries	Enable or disable Ad Hoc Distributed Queries	1	1
Replication XPs	Enable or disable Replication XPs	0	0