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
Reblogged this on Prashanth Jayaram .