Test-SQLDefaults.ps1
|
<#PSScriptInfo
.VERSION 1.0 .GUID 730f1621-25a7-4503-886d-625695f1dd06 .AUTHOR Rob Sewell .DESCRIPTION Function to run a series of Pester tests for SQL Defaults against a server or array of servers .COMPANYNAME .COPYRIGHT .TAGS SQL, Pester, Defaults, SQL Server .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .Synopsis Function to run a series of Pester tests for SQL Defaults against a server or array of servers .DESCRIPTION Runs a number of Pester tests to check default values for many options against a server or array of servers .EXAMPLE Test-SQLDefault -Servers 'SQLServer1' -SQLAdmins 'THEBEARD\Rob'` -BackupDirectory 'C:\MSSQL\Backup' -DataDirectory 'C:\MSSQL\Data\'` -LogDirectory 'C:\MSSQL\Logs\' -MaxMemMb '4096' -Collation 'Latin1_General_CI_AS'` -TempFiles 4 -OlaSysFullFrequency 'Daily' -OlaSysFullStartTime '21:00:00'` -OlaUserFullSchedule 'Weekly' -OlaUserFullFrequency 1 `## 1 for Sunday -OlaUserFullStartTime '22:00:00' -OlaUserDiffSchedule 'Weekly'` -OlaUserDiffFrequency 126` ## 126 for every day except Sunday -OlaUserDiffStartTime '22:00:00' -OlaUserLogSubDayInterval 15` -OlaUserLoginterval 'Minute' -HasSPBlitz $true -HasSPBlitzCache $True -HasSPBlitzIndex $True -HasSPAskBrent $true -HASSPBlitzTrace $true` -HasSPWhoisActive $true -LogWhoIsActiveToTable $true -LogSPBlitzToTable $true` -LogSPBlitzToTableEnabled $true -LogSPBlitzToTableScheduled $true` -LogSPBlitzToTableSchedule 'Weekly' -LogSPBlitzToTableFrequency 2 ` # 2 means Monday -LogSPBlitzToTableStartTime '03:00:00' This will run Pester tests against SQLServer1 instance and check using all the variables .EXAMPLE $Parms = @{ Servers = 'SQLServer1','SQLServer2','SQLServer2\Instance1','SQLServer3'; SQLAdmins = 'THEBEARD\Rob','THEBEARD\SQLAdmins'; BackupDirectory = 'C:\MSSQL\Backup'; DataDirectory = 'C:\MSSQL\Data\'; LogDirectory = 'C:\MSSQL\Logs\'; MaxMemMb = '4096'; Collation = 'Latin1_General_CI_AS'; TempFiles = 4 ; OlaSysFullFrequency = 'Daily'; OlaSysFullStartTime = '21:00:00'; OlaUserFullSchedule = 'Weekly'; OlaUserFullFrequency = 1 ;## 1 for Sunday OlaUserFullStartTime = '22:00:00'; OlaUserDiffSchedule = 'Weekly'; OlaUserDiffFrequency = 126; ## 126 for every day except Sunday OlaUserDiffStartTime = '22:00:00'; OlaUserLogSubDayInterval = 15; OlaUserLoginterval = 'Minute'; HasSPBlitz = $true; HasSPBlitzCache = $True; HasSPBlitzIndex = $True; HasSPAskBrent = $true; HASSPBlitzTrace = $true; HasSPWhoisActive = $true; LogWhoIsActiveToTable = $true; LogSPBlitzToTable = $true; LogSPBlitzToTableEnabled = $true; LogSPBlitzToTableScheduled = $true; LogSPBlitzToTableSchedule = 'Weekly'; LogSPBlitzToTableFrequency = 2 ; # 2 means Monday LogSPBlitzToTableStartTime = '03:00:00'} Test-SQLDefault @Parms This example uses splatting to hold the parameters and will run the tests against SQLServer1, SQLServer2, SQLServer2\Instance1 and SQLServer3 .NOTES AUTHOR : Rob Sewell http://sqldbawithabeard.com Initial 12/05/2016 #> function Test-SQLDefault { [CmdletBinding()] param( # Server Name or ServerName\InstanceName or an array of server names and/or servername\instancenames [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [array]$Servers , # Expected SQL Admin Account or an array of accounts [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [array]$SQLAdmins , # Default Backup Directory - Needs to match exactly including trailing slash if applicable [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$BackupDirectory , # Default Data Directory - Needs to match exactly including trailing slash if applicable [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$DataDirectory , # Default Log Directory - Needs to match exactly including trailing slash if applicable [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$LogDirectory , # Maximum Memory [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [int32]$MaxMemMb , # Collation [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$Collation, # Maximum Memory [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [int32]$TempFiles, # The frequency of the Ola Hallengrens System backups - Weekly, Daily [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$OlaSysFullFrequency , # The start time of the Ola Hallengrens System backups - '21:00:00' [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$OlaSysFullStartTime , # The frequency of the Ola Hallengrens User Full backups - Weekly, Daily [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$OlaUserFullSchedule , # The frequency of the Ola Hallengrens User Full backups # See https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.jobschedule.frequencyinterval.aspx # for full options # 1 for Sunday 127 for every day [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$OlaUserFullFrequency , # The start time of the Ola Hallengrens User Full backups - '21:00:00' [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$OlaUserFullStartTime , # The frequency of the Ola Hallengrens User Differential backups - Weekly, Daily [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$OlaUserDiffSchedule , # The frequency of the Ola Hallengrens User Differential backups # See https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.jobschedule.frequencyinterval.aspx # for full options # 1 for Sunday 127 for every day [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$OlaUserDiffFrequency , ## 126 for every day except Sunday # The start time of the Ola Hallengrens User Differential backups - '21:00:00' [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$OlaUserDiffStartTime , # The interval between the Ola Hallengrens Log Backups # If 15 minutes this will be 15 if 3 hours this will be 3 [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [int32]$OlaUserLogSubDayInterval , # The unit of time for the Ola Hallengrens Log Backups interval # If 15 minutes this will be Minute if 3 hours this will be Hour [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$OlaUserLoginterval , # Boolean value for existence of sp_Blitz [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$HasSPBlitz, # Boolean value for existence of sp_BlitzCache [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$HasSPBlitzCache, # Boolean value for existence of sp_BlitzIndex [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$HasSPBlitzIndex, # Boolean value for existence of sp_AskBrent [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$HasSPAskBrent, # Boolean value for existence of sp_BlitzTrace [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$HASSPBlitzTrace, # Boolean value for existence of sp_WhoIsActive [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$HasSPWhoisActive, # Boolean value for existence of Agent Job to Log sp_WhoIsActive to table [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$LogWhoIsActiveToTable, # Boolean value for existence of Agent Job to log sp_Blitz to table [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$LogSPBlitzToTable, # Boolean value for Agent Job to log sp_Blitz to table enabled [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$LogSPBlitzToTableEnabled, # Boolean value for Agent Job to log sp_Blitz to table scheduled [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [boolean]$LogSPBlitzToTableScheduled, # The frequency of the Agent Job to log sp_Blitz to table - Weekly, Daily [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$LogSPBlitzToTableSchedule, # The frequency of the Agent Job to log sp_Blitz to table # See https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.jobschedule.frequencyinterval.aspx # for full options # 1 for Sunday 127 for every day [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$LogSPBlitzToTableFrequency, # The start time of the Agent Job to log sp_Blitz to table - '21:00:00' [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] [string]$LogSPBlitzToTableStartTime ) foreach($Server in $Servers) { if($Server.Contains('\')) { $ServerName = $Server.Split('\')[0] $Instance = $Server.Split('\')[1] } else { $Servername = $Server $Instance = 'MSSQLSERVER' } ## Check for connectivity if((Test-Connection $ServerName -count 1 -Quiet) -eq $false){ Write-Error "Could not connect to $ServerName" $_ continue } if ([bool](Test-WSMan -ComputerName $ServerName -ErrorAction SilentlyContinue)) {} else {Write-Error "PSRemoting is not enabled on $ServerName Please enable and retry" continue} Describe "$Server" { BeforeAll { $Scriptblock = { [pscustomobject]$Return = @{} $srv = '' $Server = $Using:Server $SQLAdmins = $Using:SQLAdmins [void][reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo'); $srv = New-Object Microsoft.SQLServer.Management.SMO.Server $Server $Return.SQLRegKey = (Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$Instance" -ErrorAction SilentlyContinue) $Return.DBAAdminDb = $Srv.Databases.Name.Contains('DBA-Admin') $Logins = $srv.Logins.Where{$_.IsSystemObject -eq $false}.Name $Return.SQLAdmins = @(Compare-Object $Logins $SQLAdmins -SyncWindow 0).Length - $Logins.count -eq $SQLAdmins.Count $SysAdmins = $Srv.Roles['sysadmin'].EnumMemberNames() $Return.SQLAdmin = @(Compare-Object $SysAdmins $SQLAdmins -SyncWindow 0).Length - $SysAdmins.count -eq $SQLAdmins.Count $Return.BackupDirectory = $srv.BackupDirectory $Return.DataDirectory = $srv.DefaultFile $Return.LogDirectory = $srv.DefaultLog $Return.MaxMemMb = $srv.Configuration.MaxServerMemory.RunValue $Return.TempFiles = $srv.Databases['tempdb'].FileGroups['PRIMARY'].Files.Count $Return.Collation = $srv.Collation $Return.DatabasesStatus = $srv.Databases.Where{$_.Status -ne 'Normal'}.count $Return.AgentJobs = $srv.JobServer.Jobs.Count $OlaDbs = 'CommandExecute','DatabaseBackup','DatabaseIntegrityCheck','IndexOptimize' $Sps = $srv.Databases['DBA-Admin'].StoredProcedures.Where{$_.Schema -eq 'dbo'}.Name $Return.OlaProcs = $sps.count - @(Compare-Object $sps $oladbs -SyncWindow 0).Length -eq 4 $Return.RestoreProc = $Sps -contains 'RestoreCommand' $Return.OlaSysFullEnabled = $srv.JobServer.jobs['DatabaseBackup - SYSTEM_DATABASES - FULL'].IsEnabled $Return.OlaSysFullScheduled = $srv.JobServer.jobs['DatabaseBackup - SYSTEM_DATABASES - FULL'].HasSchedule $Return.OlaSysFullFrequency = $srv.JobServer.jobs['DatabaseBackup - SYSTEM_DATABASES - FULL'].JobSchedules.FrequencyTypes $Return.OlaSysFullStartTime = $srv.JobServer.jobs['DatabaseBackup - SYSTEM_DATABASES - FULL'].JobSchedules.ActiveStartTimeOfDay $Return.OlaUserFullEnabled = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - FULL'].IsEnabled $Return.OlaUserFullScheduled = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - FULL'].HasSchedule $Return.OlaUserFullSchedule = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - FULL'].JobSchedules.FrequencyTypes $Return.OlaUserFullFrequency = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - FULL'].JobSchedules.FrequencyInterval $Return.OlaUserFullStartTime = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - FULL'].JobSchedules.ActiveStartTimeOfDay $Return.OlaUserDiffEnabled = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - DIFF'].IsEnabled $Return.OlaUserDiffScheduled = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - DIFF'].HasSchedule $Return.OlaUserDiffSchedule = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - DIFF'].JobSchedules.FrequencyTypes $Return.OlaUserDiffFrequency = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - DIFF'].JobSchedules.FrequencyInterval $Return.OlaUserDiffStartTime = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - DIFF'].JobSchedules.ActiveStartTimeOfDay $Return.OlaUserLogEnabled = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - Log'].IsEnabled $Return.OlaUserLogScheduled = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - Log'].HasSchedule $Return.OlaUserLogSchedule = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - Log'].JobSchedules.FrequencyTypes $Return.OlaUserLogFrequency = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - Log'].JobSchedules.FrequencyInterval $Return.OlaUserLogSubDayInterval = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - Log'].JobSchedules.FrequencySubDayInterval $Return.OlaUserLoginterval = $srv.JobServer.jobs['DatabaseBackup - USER_DATABASES - Log'].JobSchedules.FrequencySubDayTypes $Return.HasSPBlitz = $Srv.Databases['master'].StoredProcedures.Name -contains 'sp_blitz' $Return.HasSPBlitzCache = $Srv.Databases['master'].StoredProcedures.Name -contains 'sp_blitzCache' $Return.HasSPBlitzIndex = $Srv.Databases['master'].StoredProcedures.Name -contains 'sp_blitzIndex' $Return.HasSPAskBrent = $Srv.Databases['master'].StoredProcedures.Name -contains 'sp_AskBrent' $Return.HASSPBlitzTrace = $Srv.Databases['master'].StoredProcedures.Name -contains 'sp_BlitzTrace' $Return.HasSPWhoisActive = $Srv.Databases['master'].StoredProcedures.Name -contains 'sp_WhoIsActive' $Return.LogWhoIsActiveToTable = $srv.JobServer.jobs.name.Contains('Log SP_WhoisActive to Table') $Return.LogSPBlitzToTable = $srv.JobServer.jobs.name.Contains('Log SP_Blitz to table') $Return.LogSPBlitzToTableEnabled = $srv.JobServer.jobs['Log SP_Blitz to table'].IsEnabled $Return.LogSPBlitzToTableScheduled = $srv.JobServer.jobs['log SP_Blitz to table'].HasSchedule $Return.LogSPBlitzToTableSchedule = $srv.JobServer.jobs['Log SP_Blitz to table'].JobSchedules.FrequencyTypes $Return.LogSPBlitzToTableFrequency = $srv.JobServer.jobs['Log SP_Blitz to table'].JobSchedules.FrequencyInterval $Return.LogSPBlitzToTableStartTime = $srv.JobServer.jobs['Log SP_Blitz to table'].JobSchedules.ActiveStartTimeOfDay $Return.Alerts20SeverityPlusExist = $srv.JobServer.Alerts.Where{$_.Severity -ge 20}.Count $Return.Alerts20SeverityPlusEnabled = $srv.JobServer.Alerts.Where{$_.Severity -ge 20 -and $_.IsEnabled -eq $true}.Count $Return.Alerts82345Exist = ($srv.JobServer.Alerts |Where {$_.Messageid -eq 823 -or $_.Messageid -eq 824 -or $_.Messageid -eq 825}).Count $Return.Alerts82345Enabled = ($srv.JobServer.Alerts |Where {$_.Messageid -eq 823 -or $_.Messageid -eq 824 -or $_.Messageid -eq 825 -and $_.IsEnabled -eq $true}).Count $Return.SysDatabasesFullBackupToday = $srv.Databases.Where{$_.IsSystemObject -eq $true -and $_.Name -ne 'tempdb' -and $_.LastBackupDate -lt (Get-Date).AddDays(-1)}.Count Return $Return } $Return = Invoke-Command -ScriptBlock $Scriptblock -ComputerName $ServerName } Context 'Server' { It 'Should Exist and respond to ping' { $connect = Test-Connection $ServerName -count 1 -Quiet $Connect|Should Be $true } if($connect -eq $false){break} It 'Should have SQL Server Installed' { $Return.SQLRegKey | Should Be $true } } # End Context Context 'Services'{ BeforeAll { If($Instance -eq 'MSSQLSERVER') { $SQLService = $Instance $AgentService = 'SQLSERVERAGENT' } else { $SQLService = "MSSQL$" + $Instance $AgentService = "SQLAgent$" + $Instance } $MSSQLService = (Get-CimInstance -ClassName Win32_Service -Filter "Name = '$SQLService'" -CimSession $ServerName) $SQLAgentService = (Get-CimInstance -ClassName Win32_Service -Filter "Name = '$AgentService'" -CimSession $ServerName) } It 'SQL DB Engine should be running' { $MSSQLService.State | Should Be 'Running' } It 'SQL Db Engine should be Automatic Start' { $MSSQLService.StartMode |should be 'Auto' } It 'SQL Agent should be running' { $SQLAgentService.State | Should Be 'Running' } It 'SQL Agent should be Automatic Start' { $SQLAgentService.StartMode |should be 'Auto' } } # End Context <# Context 'FireWall' { It 'Should have a Firewall connection for SQL Browser' { $Scriptblock = {Get-NetFirewallRule -Name 'SQL Browser Service - Allow'} $State = Invoke-Command -ComputerName $ServerName -ScriptBlock $Scriptblock $State | Should Be $true } It 'Firewall connection for SQL Browser should be enabled' { $Scriptblock = {(Get-NetFirewallRule -Name 'SQL Browser Service - Allow').Enabled} $State = Invoke-Command -ComputerName $ServerName -ScriptBlock $Scriptblock $State | Should Be $true } It 'SQL Browser Firewall Action Should Be Allow' { $Scriptblock = {(Get-NetFirewallRule -Name 'SQL Browser Service - Allow').Action} $State = Invoke-Command -ComputerName $ServerName -ScriptBlock $Scriptblock $State.value | Should Be 'Allow' } It 'SQL Browser Firewall Application should be the SQLBrowser.exe' { $Scriptblock = {(Get-NetFirewallRule -Name 'SQL Browser Service - Allow'|Get-NetFirewallApplicationFilter).Program} $State = Invoke-Command -ComputerName $ServerName -ScriptBlock $Scriptblock $State | Should Be 'C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe' } It 'Should have a Firewall connection for SQL DB Engine' { $Scriptblock = {Get-NetFirewallRule -Name 'SQL Database Engine - Allow'} $State = Invoke-Command -ComputerName $ServerName -ScriptBlock $Scriptblock $State | Should Be $true } It 'Firewall connection for SQL DB Engine should be enabled' { $Scriptblock = {(Get-NetFirewallRule -Name 'SQL Database Engine - Allow').Enabled} $State = Invoke-Command -ComputerName $ServerName -ScriptBlock $Scriptblock $State | Should Be $true } It 'DB EngineFirewall Action Should Be Allow' { $Scriptblock = {(Get-NetFirewallRule -Name 'SQL Database Engine - Allow').Action} $State = Invoke-Command -ComputerName $ServerName -ScriptBlock $Scriptblock $State.value | Should Be 'Allow' } It 'DB EngineFirewall Application should be the SQLBrowaser.exe' { $Scriptblock = {(Get-NetFirewallRule -Name 'SQL Database Engine - Allow'|Get-NetFirewallApplicationFilter).Program} $State = Invoke-Command -ComputerName $ServerName -ScriptBlock $Scriptblock $State | Should Be 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' } } # End Context Firewall #> Context 'Databases' { It 'Should have a DBA-Admin Database' { $Return.DbaAdminDB |Should Be $true } It 'Databases should have a normal Status - No Restoring, Recovery Pending etc' { $Return.DatabasesStatus |Should Be 0 } It 'System Databases Shol dhave been backed up within the last 24 hours' { $Return.SysDatabasesFullBackupToday | SHould be 0 } } # End Context Context 'Users' { It "Should have $SQLAdmins as a login" { $Return.SQLAdmins | Should Be $True } It "$SQLAdmins Should be sysadmin" { $Return.SQLAdmin|Should Be $true } } # End Context Context 'Defaults'{ It "Should have a default Backup Directory of $BackupDirectory" { $Return.BackupDirectory |Should Be $BackupDirectory } It "Should have a default Data Directory of $DataDirectory" { $Return.DataDirectory |Should Be $DataDirectory } It "Should have a default Log Directory of $LogDirectory " { $Return.LogDirectory |Should Be $LogDirectory } It "Should have a Max Memory Setting of $MaxMemMb" { $Return.MaxMemMb |Should Be $MaxMemMb } It "Should have a Collation of $Collation" { $Return.Collation |Should Be $Collation } it "Should have $tempFiles tempdb files" { $Return.tempFiles| Should be $tempFiles } It 'Should have Alerts for Severity 20 and above' { $Return.Alerts20SeverityPlusExist | Should Be 6 } It 'Severity 20 and above Alerts should be enabled' { $Return.Alerts20SeverityPlusEnabled | Should Be 6 } It 'Should have alerts for 823,824 and 825' { $Return.Alerts82345Exist |Should Be 3 } } # End Context Context 'Agent Jobs' { It 'Should have Agent Jobs' { $Return.AgentJobs |Should BeGreaterthan 0 } It 'Should have Ola Hallengrens maintenance Solution' { $Return.OlaProcs | Should Be $True } It 'Should have Restore Proc for Ola Hallengrens Maintenance Solution' { $Return.RestoreProc | Should Be $True } It 'The Full System Database Backup should be enabled' { $Return.OlaSysFullEnabled | Should Be $True } It 'The Full System Database Backup should be scheduled' { $Return.OlaSysFullScheduled | Should Be $True } It "The Full System Database Backup should be scheduled $OlaSysFullFrequency" { $Return.OlaSysFullFrequency.value| Should Be $OlaSysFullFrequency } It "The Full System Database Backup should be scheduled at $OlaSysFullStartTime" { $Return.OlaSysFullStartTime| Should Be $OlaSysFullStartTime } It 'The Full User Database Backup should be enabled' { $Return.OlaUserFullEnabled| Should Be $True } It 'The Full User Database Backup should be scheduled' { $Return.OlaUserFullScheduled | Should Be $True } It "The Full User Database Backup should be scheduled Weekly $OlaUserFullSchedule" { $Return.OlaUserFullSchedule.value | Should Be $OlaUserFullSchedule } It "The Full user Database Backup should be scheduled Weekly on a $OlaUserFullFrequency" { $Return.OlaUserFullFrequency| Should Be $OlaUserFullFrequency } It "The Full User Database Backup should be scheduled at $OlaUserFullStartTime" { $return.OlaUserFullStartTime| Should Be $OlaUserFullStartTime } It 'The Diff User Database Backup should be enabled' { $Return.OlaUserDiffEnabled| Should Be $True } It 'The Diff User Database Backup should be scheduled' { $Return.OlaUserDiffScheduled| Should Be $True } It "The Diff User Database Backup should be scheduled Daily Except Sunday = $OlaUserDiffSchedule" { $Return.OlaUserDiffSchedule.Value| Should Be $OlaUserDiffSchedule } It "The Diff User Database Backup should be scheduled Daily Except Sunday = $OlaUserDiffFrequency" { $Return.OlaUserDiffFrequency| Should Be $OlaUserDiffFrequency } It "The Diff User Database Backup should be scheduled at $OlaUserDiffStartTime" { $Return.OlaUserDiffStartTime| Should Be $OlaUserDiffStartTime } It 'The Log User Database Backup should be enabled' { $Return.OlaUserLogEnabled| Should Be $true } It 'The Log User Database Backup should be scheduled' { $Return.OlaUserLogScheduled| Should Be $True } It 'The Log User Database Backup should be scheduled Daily' { $Return.OlaUserLogSchedule.Value | Should Be 'Daily' } It 'The Log User Database Backup should be scheduled Daily' { $Return.OlaUserLogFrequency| Should Be 1 } It "The Log User Database Backup should be scheduled for every $OlaUserLogSubDayInterval" { $Return.OlaUserLogSubDayInterval| Should Be $OlaUserLogSubDayInterval } It "The Log User Database Backup should be scheduled for every $OlaUserLoginterval" { $Return.OlaUserLoginterval.Value| Should Be $OlaUserLoginterval } It "Should have the Log SP_WhoisActive to Table Agent Job $LogWhoIsActiveToTable" { $Return.LogWhoIsActiveToTable| Should Be $LogWhoIsActiveToTable } It "Should have the Log SP_WhoisActive to Table Agent Job $LogSPBlitzToTable" { $Return.LogSPBlitzToTable| Should Be $LogSPBlitzToTable } It "Log SP_Blitz to Table Agent Job Should Be Enabled" { $Return.LogSPBlitzToTableEnabled| Should Be $LogSPBlitzToTableEnabled } It "Log SP_Blitz to Table Agent Job Should Be Scheduled" { $Return.LogSPBlitzToTableScheduled| Should Be $LogSPBlitzToTableScheduled } It "Log SP_Blitz to Table Agent Job Should Be Scheduled $LogSPBlitzToTableSchedule" { $Return.LogSPBlitzToTableSchedule.Value| Should Be $LogSPBlitzToTableSchedule } It "Log SP_Blitz to Table Agent Job Should Be Scheduled Weekly on a $LogSPBlitzToTableFrequency" { $Return.LogSPBlitzToTableFrequency| Should Be $LogSPBlitzToTableFrequency } It "Log SP_WhoisActive to Table Agent Job Should Be Scheduled at $LogSPBlitzToTableStartTime" { $Return.LogSPBlitzToTableStartTime| Should Be $LogSPBlitzToTableStartTime } } # End Context Agent Jobs Context 'DBA Scripts' { It "Should Have sp_Blitz $HasSPBlitz"{ $Return.HasSPBlitz |Should Be $HasSPBlitz } It "Should Have sp_BlitzCache $HasSPBlitzCache" { $Return.HasSPBlitzCache | Should Be $HasSPBlitzCache } It "Should Have sp_BlitzIndex $HasSPBlitzIndex" { $Return.HasSPBlitzIndex | Should Be $HasSPBlitzIndex } It "Should Have sp_AskBrent $HasSPAskBrent" { $Return.HasSPAskBrent | Should Be $HasSPAskBrent } It "Should Have sp_BlitzTrace $HASSPBlitzTrace" { $Return.HASSPBlitzTrace | Should Be $HASSPBlitzTrace } It "Should Have sp_WhoIsActive $HasSPWhoisActive" { $Return.HasSPWhoisActive | Should Be $HasSPWhoisActive } } } # End Describe $Server } } |