dataplat / dbachecks Goto Github PK

With a warning that credentials cant be imported on another comp.

Is the current result set

better than this ?

I have added the context for each Server so each one is seperate and altered the ordering of the test title

When scripting out the creation of an AG on SQL Server 2012+ (that I've tried it does this on any version) it does not (1) enable the AlwaysOn_health XE session nor (2) set that session to start on start-up.

This will happen whether you use SMO (what dbatools will have soon) or if you use SQLSERVER provider via the sqlserver module.

mmm that exists, so the check is probably outdated.

Tests Passed: 1312, Failed: 0, Skipped: 1, Pending: 0, Inconclusive: 0


    Directory: C:\Windows\temp


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d-----       12/27/2017   4:11 PM                dbachecks
[16:11:32][Update-DbcPowerBiDataSource] Wrote results to C:\Windows\temp\dbachecks\dbachecks_1_SuspectPage_FailedJob_LastGoodCheckdb_LastBackup_VirtualLogFile_MemoryDump_Default.json


PS C:\Users\> Start-DbcPowerBi
WARNING: [16:21:10][Start-DbcPowerBi] json file not found. Run Update-DbcPowerBiDataSource to auto generate

When I use Pester I like to have different types of Tags

So for example
my drive size, services checks are also tagged Server
my SQL Admins, Adhoc, Tmpdb checks are also tagged Instance
my dbowner, identity, dbcc checks are also tagged database
my SQL Admins, dbowner, job owner tests are also tagged security

We should ensure we have some consistency in this right now before it gets real messy :-)

Discuss

The current test is attempting to run against databases that are in an offline state.

Can't get this to work

Describe "Supported Build" -Tags SupportedBuild, $filename {
	(Get-SqlInstance).ForEach{
		Context "Ensuring $psitem's build is still supported" {
			It "still in support" {
				(Get-DbaSqlBuildReference -SqlInstance $psitem).SupportedUntil | Should BeGreaterThan (Get-Date)
			}
		}
	}
}

Keeps saying

Anyone know any vendors that would like to add their tests? Like.. Argenis or someone at SentryOne or RedGate?

I imagine it'd be like RedGate.Tests.ps1 or PureStorage.Tests.ps1

 It "$_ AUth Scheme should be NTLM" {
                $connection.AuthScheme | Should Be "NTLM"
            }

Explore other options that perhaps should be configurable this way. I know we'll have a lot of configurable things but it'll be easy to mod using our Import and Export JSON thingies.

Argenis is reaching out to his PureStorage PoSH folks. Anyone else have a vendor in mind?

you are also welcomed to give the intro page an update. the ccurrent file sizes are too small. ill make a logo for us one day.

It appears the $dac variable used for the test is not getting populated.

We need a plan to avoid the number of files growing and giving us this

when just running one tag

My suggestion is to reduce the number of Tests.ps1 files and move the Describe blocks with the tags inside them

I suggest

Server.Tests.ps1
Instance.Tests.ps1
Database.Tests.ps1

as a starter for ten

Discus

What do yall think of the way I'm skipping (using the config?)

We have many automated checks in place using dbatools and we use the registered servers. Could you please add intelligence to the checks that if the database is part of availability group and not readable then it automatically skips the steps.

Otherwise our monitoring shows a lot of errors like the below sample:
APPLICATION_NAME: "dbatools PowerShell module - dbatools.io"
ERROR NUMBER: 976.
ERROR MESSAGE: The target database, 'DB_NAME', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

SQL_TEXT:
use [DB_NAME] SELECT (select value from sys.database_scoped_configurations as dsc where dsc.name = 'MAXDOP') AS [MaxDop], (select value_for_secondary from sys.database_scoped_configurations as dsc where dsc.name = 'MAXDOP') AS [MaxDopForSecondary], (select value from sys.database_scoped_configurations as dsc where dsc.name = 'LEGACY_CARDINALITY_ESTIMATION') AS [LegacyCardinalityEstimation], (select ISNULL(value_for_secondary, 2) from sys.database_scoped_configurations as dsc where dsc.name = 'LEGACY_CARDINALITY_ESTIMATION') AS [LegacyCardinalityEstimationForSecondary], (select value from sys.database_scoped_configurations as dsc where dsc.name = 'PARAMETER_SNIFFING') AS [ParameterSniffing], (select ISNULL(value_for_secondary, 2) from sys.database_scoped_configurations as dsc where dsc.name = 'PARAMETER_SNIFFING') AS [ParameterSniffingForSecondary], (select value from sys.database_scoped_configurations as dsc where dsc.name = 'QUERY_OPTIMIZER_HOTFIXES') AS [QueryOptimizerHotfixes], (select ISNULL(value_for_secondary, 2) from sys.database_scoped_configurations as dsc where dsc.name = 'QUERY_OPTIMIZER_HOTFIXES') AS [QueryOptimizerHotfixesForSecondary]

Should it be tests-external? or the other way around? We will, at some point, add pester tests to test our commands so.. im up for a rename, not sure i like tests-external anyway.

I enabled the skip.backuplogcheck to true and attempted to run my invoke again and it still checks logs.

When setting config values if $false or $true is not used for a boolean option it causes it to be a string and has issues.

Notes from @SQLDBAWithABeard :
yes thats a tricky one - could you create an issue and we might try and see if we can code for that (if "false") {$false} etc

To make it easier to change it would be useful to be able to tab them ;-)

Should have pertinent examples for this project and then referrals for ppl to look at Pester's help if they wanna know the deets

Also our description and everything is all Pester, should be more from our readme.md

Each test uses Get-ComputerName or Get-SqlInstance - @JanMulkens said, and it's true, that this info from the readme is not adequate:

...however, you can set a permanent list of servers. "Servers" include both SQL Server instances and Windows servers. Checks that access Windows Server (e.g. disk space checks) will utilize -ComputerName parameter. A pure SQL Server command(s) (such as the backup check) utilizes the -SqlInstance parameter.

Since we'll be using a lot of $_, let's please use $psitem in lieu.

I'm fixing them now

server name is just written out as "1"

So what happens if you have an embedded foreach? You lose track of the original $_ or $psitem it seems. I propose that we do this - assign the first psitem to $servername

Describe "Duplicate Index" -Tag Index, $filename {
	(Get-SqlInstance).ForEach{
		$servername = $psitem
		Context "Testing $servername for duplicate indexes" {
            (Get-DbaDatabase -SqlInstance $servername).ForEach{
				It "$psitem should not have duplicate indexes" {
					Find-SqlDuplicateIndex -SqlInstance $servername -Database $psitem.Name | Should Be $null
				}
			}
		}
	}
}

I get this error each time I run Invoke-DbcCheck.

Should we set a global EnableException to true var? Or can pester fail without doing that?

Similar to the other checks

Add Find-DbaDatabaseGrowthEvent, Find-DbaUnusedIndex to Database.Tests

Scenario

I'm writing a test to test

• PageVerify
• AutoShrink
• AutoClose

All of those can be checked from Get-DbaDatabase dbatools command.

Question

Should we have 1 .tests.ps1 file for each check?
Or everything in a single .tests.ps1 file where we do the validation for the skip like $autoclose = Get-DbcConfigValue skip.AutoClose ?

Context Testing backups on PRDPRSDBS11A,5510 [-] Error occurred in Context block 2.32s PSInvalidCastException: Cannot convert value "System.String" to type "System.Management.Automation.SwitchParameter". Boolean parameters accept only Boolean values and numbers, such as $True, $False, 1 or 0. ArgumentTransformationMetadataException: Cannot convert value "System.String" to type "System.Management.Automation.SwitchParameter". Boolean parameters accept only Boolean values and numbers, such as $True, $False, 1 or 0. ParameterBindingArgumentTransformationException: Cannot process argument transformation on parameter 'Skip'. Cannot convert value "System.String" to type "System.Management.Automation.SwitchParameter". Boolean parameters accept o nly Boolean values and numbers, such as $True, $False, 1 or 0. at <ScriptBlock>, C:\GitHub\dbachecks\checks\lastbackup.Tests.ps1: line 18 at DescribeImpl, C:\Program Files\WindowsPowerShell\Modules\Pester\4.1.1\Functions\Describe.ps1: line 161

Should we have Performance.Tests.ps1?

In my environment, I treat all of my servers the same. but what if ppl have diff RPOs for diff farms?

How would we tell people to use this? Create a script where they repeatedly reset Set-DbcConfig -Name Setup.SqlInstance -Value sql2016, sql2017, sql2008, sql2008\express

Is that healthy/acceptable, @FriedrichWeinmann ?

mmmm, i guess this should be save.ps1 since we're requiring prereqs in the module manifest. save all required modules or do we just show ppl how to install PowerShell Gallery?

Each test uses Get-ComputerName or Get-SqlInstance, this info from the readme is not adequate:

...however, you can set a permanent list of servers. "Servers" include both SQL Server instances and Windows servers. Checks that access Windows Server (e.g. disk space checks) will utilize -ComputerName parameter. A pure SQL Server command(s) (such as the backup check) utilizes the -SqlInstance parameter.

Preferable would be to see the type you can use behind or in front of the group in get-dbccheck

I am not sure how the test is connecting, but we do not have the latency output by the test.

`Executing script C:\GitHub\dbachecks\checks\latency.Tests.ps1

Describing Testing network latency
[-] network latency for PRDPRSDBS11A,5510 should be less than 40 ms 299ms
Expected: {True}
But was: {False}
7: $results.Average.TotalMilliseconds -lt $max | Should be $true
at Invoke-LegacyAssertion, C:\Program Files\WindowsPowerShell\Modules\Pester\4.1.1\Functions\Assertions\Should.ps1: line 190
at , C:\GitHub\dbachecks\checks\latency.Tests.ps1: line 7`

Can we create a .pbit?

Usage

# Run tests and export its json
New-DbcPowerBiJson

# Launch PowerBi then hit refresh
Show-DbcPowerBi

New-DbcPowerBiJson

Basically is:

$results = Invoke-DbcCheck -Show Summary -PassThru
$results.TestResult | ConvertTo-Json -Depth 5 | Out-File "$env:windir\temp\dbachecks.json"

Show-DbcPowerBi

Launches the included pbix file - all you gotta do is hit refresh ♻️

When importing the psd1 I get this error

Import-Module .\dbachecks.psd1
Exception calling "InvokeScript" with "4" argument(s): "You cannot call a method on a null-valued expression."At C:\Users\mrrob\OneDrive\Documents\GitHub\dbachecks\dbachecks.psm1:17 char:9

• else { $ExecutionContext.InvokeCommand.InvokeScript($false, ([scr ...
  

•        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : RuntimeException

Need to add Test-DbaSuspectPage to Database.Tests

Executed Update-DbcPowerBiDataSource and got the following error.

should we make a command to make it easy to import config shit enmasse? I think the answer is no, we'd just be duplicating work that should be done within Set-DbcConfig

for instance

$skips = Import-CliXml skips.xml
foreach ($skip in $skips) { Set-DbcConfig ....

CIS.pdf

DISA: https://github.com/sqlcollaborative/disa-docs/blob/master/sql2014.md