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 .
LikeLike