checks/Databasev5.Tests.ps1
|
# So the v5 files need to be handled differently. # Ww will start with a BeforeDiscovery , $Filename which for the Database Checks will need to gather the Instances up front BeforeDiscovery { # Gather the instances we know are not contactable [string[]]$NotContactable = (Get-PSFConfig -Module dbachecks -Name global.notcontactable).Value # Get all the tags in use in this run $Tags = Get-CheckInformation -Check $Check -Group Database -AllChecks $AllChecks -ExcludeCheck $ChecksToExclude $InstancesToTest = @(Get-Instance).ForEach{ # just add it to the Not Contactable list if ($NotContactable -notcontains $psitem) { $Instance = $psitem try { $InstanceSMO = Connect-DbaInstance -SqlInstance $Instance -ErrorAction SilentlyContinue -ErrorVariable errorvar } catch { $NotContactable += $Instance } if ($NotContactable -notcontains $psitem) { if ($null -eq $InstanceSMO.version) { $NotContactable += $Instance } else { # Get the relevant information for the checks in one go to save repeated trips to the instance and set values for Not Contactable tests if required Get-AllDatabaseInfo -Instance $InstanceSMO -Tags $Tags } } } } Write-PSFMessage -Message "Instances = $($InstancesToTest.Name)" -Level Verbose Set-PSFConfig -Module dbachecks -Name global.notcontactable -Value $NotContactable # Get-DbcConfig is expensive so we call it once $__dbcconfig = Get-DbcConfig } # Each Test will have a -ForEach for the Instances and the InstancesToTest object will have a # lot of information gathered up front to reduce trips and connections to the database <# - copy in test - add skip after describe $skip = Get-DbcConfigValue skip.database.databasecollation add to IT -Skip:$skip #> Describe "Suspect Page" -Tag SuspectPage, High , Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.suspectpage' }).Value Context "Testing suspect pages on <_.Name>" { It "Database <_.Name> should return 0 suspect pages on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.suspectpageexclude -notcontains $PsItem.Name } } { $psitem.SuspectPage | Should -Be 0 -Because "You do not want any suspect pages" } } } Describe "Database Collation" -Tag DatabaseCollation, High, Database -ForEach $InstancesToTest { #TODO: Should we have a skip option for each IT block? $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.databasecollation' }).Value Context "Testing database collation on <_.Name>" { It "Database <_.Name> collation <_.Collation> should match server collation <_.ServerCollation> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.wrongcollation -notcontains $PsItem.Name } } { $psitem.ServerCollation | Should -Be $psitem.Collation -Because "You will get collation conflict errors in tempdb" } # wrong collation set It "Database <_.Name> collation <_.Collation> should not match server collation <_.ServerCollation> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ $_.Name -in $psitem.ConfigValues.wrongcollation } { $psitem.ServerCollation | Should -Not -Be $psitem.Collation -Because "You have defined the database to have another collation then the server. You will get collation conflict errors in tempdb" } } } Describe "Valid Database Owner" -Tag ValidDatabaseOwner, Medium, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.validdatabaseowner' }).Value Context "Testing Database Owners on <_.Name>" { #TODO fix the it text - needs commas --> should be in this list ( sqladmin sa ) ) It "Database <_.Name> - owner '<_.Owner>' should be in this list ( <_.ConfigValues.validdbownername> ) ) on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.validdbownerexclude -notcontains $PsItem.Name } } { $psitem.Owner | Should -BeIn $psitem.ConfigValues.validdbownername -Because "The account that is the database owner is not what was expected" } } } Describe "Invalid Database Owner" -Tag InvalidDatabaseOwner, Medium, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.invaliddatabaseowner' }).Value Context "Testing Database Owners on <_.Name>" { It "Database <_.Name> - owner '<_.Owner>' should not be in this list ( <_.ConfigValues.invaliddbownername> ) ) on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.invaliddbownerexclude -notcontains $PsItem.Name } } { $psitem.Owner | Should -Not -BeIn $psitem.ConfigValues.invaliddbownername -Because "The database owner was one specified as incorrect" } } } Describe "AsymmetricKeySize" -Tag AsymmetricKeySize, CIS, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.security.asymmetrickeysize' }).Value Context "Testing Asymmetric Key Size is 2048 or higher on <_.Name>" { It "Database <_.Name> asymmetric key size should be at least 2048 on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.asymmetrickeysizeexclude -notcontains $PsItem.Name } } { $psitem.AsymmetricKeySize | Should -Be 0 -Because "Asymmetric keys should have a key length greater than or equal to 2048" #$psitem.AsymmetricKeySize | Should -BeGreaterOrEqual 2048 -Because "Asymmetric keys should have a key length greater than or equal to 2048" } } } Describe "Auto Close" -Tag AutoClose, High, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.autoclose' }).Value Context "Testing Auto Close on <_.Name>" { It "Database <_.Name> should have Auto Close set to <_.ConfigValues.autoclose> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.autocloseexclude -notcontains $PsItem.Name } } { $psitem.AutoClose | Should -Be $psitem.ConfigValues.autoclose -Because "Because!" } } } Describe "Auto Shrink" -Tag AutoShrink, High, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.autoshrink' }).Value Context "Testing Auto Shrink on <_.Name>" { It "Database <_.Name> should have Auto Shrink set to <_.ConfigValues.autoshrink> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.autoshrinkexclude -notcontains $PsItem.Name } } { $psitem.AutoShrink | Should -Be $psitem.ConfigValues.autoshrink -Because "Shrinking databases causes fragmentation and performance issues" } } } Describe "Virtual Log Files" -Tag VirtualLogFile, Medium, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.vlf' }).Value Context "Testing Database VLFs on <_.Name>" { It "Database <_.Name> VLF count should be less than <_.ConfigValues.maxvlf> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.vlfexclude -notcontains $PsItem.Name } } { $psitem.VLF | Should -BeLessThan $psitem.ConfigValues.maxvlf -Because "Too many VLFs can impact performance and slow down backup/restore" } } } Describe "Log File Count Checks" -Tag LogfileCount, Medium, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.logfilecounttest' }).Value Context "Testing Log File count for <_.Name>" { It "Database <_.Name> should have <_.ConfigValues.logfilecount> or less log files on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.logfilecountexclude -notcontains $PsItem.Name } } { $psitem.LogFileCount | Should -BeLessOrEqual $psitem.ConfigValues.logfilecount -Because "You want the correct number of log files" } } } Describe "Auto Create Statistics" -Tag AutoCreateStatistics, Low, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.autocreatestatistics' }).Value Context "Testing Auto Create Statistics for <_.Name>" { It "Database <_.Name> should have Auto Create Statistics set to <_.ConfigValues.autocreatestats> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.autocreatestatsexclude -notcontains $PsItem.Name } } { $psitem.AutoCreateStatistics | Should -Be $psitem.ConfigValues.autocreatestats -Because "This value is expected for autocreate statistics" } } } Describe "Auto Update Statistics" -Tag AutoUpdateStatistics, Low, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.autoupdatestatistics' }).Value Context "Testing Auto Update Statistics on <_.Name>" { It "Database <_.Name> should have Auto Update Statistics set to <_.ConfigValues.autoupdatestats> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.autoupdatestatsexclude -notcontains $PsItem.Name } } { $psitem.AutoUpdateStatistics | Should -Be $psitem.ConfigValues.autoupdatestats -Because "This value is expected for autoupdate statistics" } } } Describe "Auto Update Statistics Asynchronously" -Tag AutoUpdateStatisticsAsynchronously, Low, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.autoupdatestatisticsasynchronously' }).Value Context "Testing Auto Update Statistics Asynchronously on <_.Name>" { It "Database <_.Name> should have Auto Update Statistics Asynchronously set to <_.ConfigValues.autoupdatestatsasync> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.autoupdatestatsasyncexclude -notcontains $PsItem.Name } } { $psitem.AutoUpdateStatisticsAsync | Should -Be $psitem.ConfigValues.autoupdatestatsasync -Because "This value is expected for autoupdate statistics asynchronously" } } } Describe "Trustworthy Option" -Tag Trustworthy, DISA, Varied, CIS, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.trustworthy' }).Value Context "Testing database trustworthy option on <_.Name>" { It "Database <_.Name> should have Trustworthy set to false on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.trustworthyexclude -notcontains $PsItem.Name } } { $psitem.Trustworthy | Should -BeFalse -Because "Trustworthy has security implications and may expose your SQL Server to additional risk" } } } Describe "Database Status" -Tag DatabaseStatus, High, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.database.status' }).Value Context "Database status is correct on <_.Name>" { It "Database <_.Name> has the expected status on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.statusexclude -notcontains $PsItem.Name } } { $psitem.Where{ $_.Name -notin $psitem.ConfigValues.excludereadonly -and $psitem.IsDatabaseSnapshot -eq $false }.Readonly | Should -Not -Contain True -Because "We expect that there will be no Read-Only databases except for those specified" $psitem.Where{ $_.Name -notin $psitem.ConfigValues.excludeoffline }.Status | Should -Not -Match 'Offline' -Because "We expect that there will be no offline databases except for those specified" $psitem.Where{ $_.Name -notin $psitem.ConfigValues.excluderestoring }.Status | Should -Not -Match 'Restoring' -Because "We expect that there will be no databases in a restoring state except for those specified" $psitem.Where{ $_.Name -notin $psitem.ConfigValues.excludeoffline }.Status | Should -Not -Match 'AutoClosed' -Because "We expect that there will be no databases that have been auto closed" $psitem.Status | Should -Not -Match 'Recover' -Because "We expect that there will be no databases going through the recovery process or in a recovery pending state" $psitem.Status | Should -Not -Match 'Emergency' -Because "We expect that there will be no databases in EmergencyMode" $psitem.Status | Should -Not -Match 'Standby' -Because "We expect that there will be no databases in Standby" $psitem.Status | Should -Not -Match 'Suspect' -Because "We expect that there will be no databases in a Suspect state" } } } Describe "Query Store Enabled" -Tag QueryStoreEnabled, Medium, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.security.querystoreenabled' }).Value Context "Testing to see if Query Store is enabled on <_.Name>" { It "Database <_.Name> should have Query Store enabled on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.qsenabledexclude -notcontains $PsItem.Name } } { $psitem.QueryStore | Should -Not -BeIn ('OFF', 'ERROR') -Because "We expect the Query Store to be enabled" } } } Describe "Query Store Disabled" -Tag QueryStoreDisabled, Medium, Database -ForEach $InstancesToTest { $skip = ($__dbcconfig | Where-Object { $_.Name -eq 'skip.security.querystoredisabled' }).Value Context "Testing to see if Query Store is disabled on <_.Name>" { It "Database <_.Name> should have Query Store disabled on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.qsdisabledexclude -notcontains $PsItem.Name } } { $psitem.QueryStore | Should -Not -Be 'OFF' -Because "We expect the Query Store to be disabled" } } } Describe "Compatibility Level" -Tag CompatibilityLevel, High, Database -ForEach $InstancesToTest { $Skip = ($__dbcconfig | Where-Object Name -EQ 'skip.database.compatibilitylevel').Value Context "Compatibility level matches server compatibility level on <_.Name>" { It "Database <_.Name> has the expected compatibility level on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.compatexclude -notcontains $psitem.Name } } { $psitem.CompatibilityLevel | Should -Be $psitem.ServerLevel -Because "it means you are on the appropriate compatibility level for your SQL Server version to use all available features." } } } Describe "Guest User" -Tag GuestUserConnect, Security, CIS, Medium, Database -ForEach $InstancesToTest { $Skip = ($__dbcconfig | Where-Object Name -EQ 'skip.security.guestuserconnect').Value Context "Testing Guest user has CONNECT permission on <_.Name>" { It "Database Guest user should return no CONNECT permissions in <_.Name> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.guestuserexclude -notcontains $psitem.Name } } { $psitem.GuestUserConnect | Should -BeFalse -Because "we don't want the guest user to have connect access to our database." } } } Describe "Recovery Model" -Tag RecoveryModel, DISA, Medium, Database -ForEach $InstancesToTest { $Skip = ($__dbcconfig | Where-Object Name -EQ 'skip.database.recoverymodel').Value Context "Testing Recovery Model on <_.Name>" { It "Database <_.Name> should be set to <_.ConfigValues.recoverymodeltype> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.recoverymodelexclude -notcontains $psitem.Name } } { $psitem.RecoveryModel | Should -Be $psitem.ConfigValues.recoverymodeltype -Because "You expect this recovery model." } } } Describe "PseudoSimple Recovery Model" -Tag PseudoSimple, Medium, Database -ForEach $InstancesToTest { $Skip = ($__dbcconfig | Where-Object Name -EQ 'skip.database.pseudosimple').Value Context "Testing database is not in PseudoSimple recovery model on <_.Name>" { It "Database <_.Name> has PseudoSimple recovery model equal false on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database -and $_.RecoveryModel -eq 'Full' } else { $psitem.ConfigValues.pseudosimpleexclude -notcontains $psitem.Name -and $_.RecoveryModel -eq 'Full' } } { $psitem.PseudoSimple | Should -BeFalse -Because "PseudoSimple means that a FULL backup has not been taken and the database is still effectively in SIMPLE mode" } } } Describe "Contained Database Auto Close" -Tag ContainedDBAutoClose, CIS, Database -ForEach $InstancesToTest { $Skip = ($__dbcconfig | Where-Object Name -EQ 'skip.security.containedbautoclose').Value Context "Testing contained database auto close option on <_.Name>" { It "Database <_.Name> should have auto close set to false on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database -and $_.ContainmentType -ne "NONE" } else { $psitem.ConfigValues.contdbautocloseexclude -notcontains $psitem.Name -and $_.ContainmentType -ne "NONE" } } { $psitem.ContainedDbAutoClose | Should -BeFalse -Because "Contained Databases should have auto close set to false for CIS compliance." } } } Describe "Contained Database SQL Authenticated Users" -Tag ContainedDBSQLAuth, CIS, Database -ForEach $InstancesToTest { $Skip = ($__dbcconfig | Where-Object Name -EQ 'skip.security.ContainedDBSQLAuth').Value #TODO: something with this? #if ($version -lt 13 ) { $skip = $true } Context "Testing contained database to see if sql authenticated users exist on <_.Name>" { It "Database <_.Name> should have no sql authenticated users on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database -and $_.ContainmentType -ne "NONE" } else { $psitem.ConfigValues.contdbsqlauthexclude -notcontains $psitem.Name -and $_.ContainmentType -ne "NONE" } } { $psitem.ContainedDbSqlAuthUsers | Should -Be 0 -Because "We expect there to be no sql authenticated users in contained database." } } } Describe "Page Verify" -Tag PageVerify, Medium, Database -ForEach $InstancesToTest { $Skip = ($__dbcconfig | Where-Object Name -EQ 'skip.database.pageverify').Value Context "Testing page verify on <_.Name>" { # handle differently depending on major version - not available at all in SQL 2000. 2005 not available on tempdb. if($psitem.MajorVersion -eq 8) { It "Database Page verify is not available on SQL 2000 on <_.SqlInstance>" { $true | Should -BeTrue } } elseif ($psitem.MajorVersion -eq 9) { It "Database <_.Name> should have page verify set to <_.ConfigValues.pageverify> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.pageverifyexclude -notcontains $psitem.Name } } { if($psitem.Name -ne 'tempdb') { $psitem.PageVerify | Should -Be $psitem.ConfigValues.PageVerify -Because "Page verify helps SQL Server to detect corruption" } else { $true | Should -BeTrue } } } else { It "Database <_.Name> should have page verify set to <_.ConfigValues.pageverify> on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.pageverifyexclude -notcontains $psitem.Name -and $_.Name -ne 'tempdb'} } { $psitem.PageVerify | Should -Be $psitem.ConfigValues.PageVerify -Because "Page verify helps SQL Server to detect corruption." } #tempdb handled like v4 It "Database Page verify is not available on tempdb on SQL 2005 on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ $_.Name -eq 'tempdb' } { $psitem.PageVerify | Should -Be $psitem.ConfigValues.PageVerify -Because "Page verify helps SQL Server to detect corruption." } } } } Describe "Foreign keys and check constraints not trusted" -Tag FKCKTrusted, Low, Database -ForEach $InstancesToTest { $Skip = ($__dbcconfig | Where-Object Name -EQ 'skip.database.fkcktrusted').Value Context "Testing Foreign Keys and Check Constraints are not trusted <_.Name>" { It "Database <_.Database> Foreign Key <_.Name> on table <_.Parent> should be trusted on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.fkcktrustedexclude -notcontains $psitem.Name } }.ForeignKeys { $psitem.IsChecked | Should -Be $true -Because "This can have a huge performance impact on queries. SQL Server won't use untrusted constraints to build better execution plans. It will also avoid data violation" } It "Database <_.Database> Foreign Key <_.Name> on table <_.Parent> should be trusted on <_.SqlInstance>" -Skip:$skip -ForEach $psitem.Databases.Where{ if ($Database) { $_.Name -in $Database } else { $psitem.ConfigValues.fkcktrustedexclude -notcontains $psitem.Name } }.Constraints { $psitem.IsChecked | Should -Be $true -Because "This can have a huge performance impact on queries. SQL Server won't use untrusted constraints to build better execution plans. It will also avoid data violation" } } } |