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

Reference :http://technet.microsoft.com/en-us/library/hh245202.aspx

About these ads

About Prashanth Jayaram

I’m a Database technologist having 8+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
This entry was posted in PowerShell, SQL and tagged , . Bookmark the permalink.

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

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