Page Verify - False Flag on <= 2005 TempDB

[lowlydba]

Issue

The check does not skip over tempdb in 2005, where checksum is available overall but not able to be set on TempDB, and does not skip over 2000 where it is not available at all. For 2000, torn page detection should be the best practice option.

Note: Compatibility level does not affect this option, it is only dictated by the instance version

Bug Report

General Troubleshooting steps

• Verified running the latest release of dbachecks?

Does (Find-Module dbachecks).Version match (Get-Module dbachecks).Version.ToString()
Y

• Verified errors are not related to permissions?
• Can duplicate in new/clean PowerShell session (clean = powershell -NoProfile)

[michalporeba]

I've been working on much faster database tests which are not using SMO, this issue presents very interesting challenge for me, especially that I haven't seen a 2000 or even 2005 instance for quite a few years now. I will try to make the newer version work with older versions, but I will need some help testing.

I don't suppose we have an VMs in VSTS hosting 2000 @SQLDBAWithABeard? ;)
I don't think it is possible to get a developer edition of anything older than 2012 now, or am I wrong?

[lowlydba]

Definitely wish I didn't need to make this bug, sigh. I'm quite happy to be a guinea pig if you can get something working, though I know those versions are far from priorities.

[michalporeba]

what a nice surprise, we still have sys.sysdatabases for backwards compatibility. I will be able to use that (I think) to develop version for SQL 2000. I'll have something ready over the weekend.

[niphlod]

I can be a guinea pig for 2005, not for 2000, but I don't see why it'd be that hard.
notes:

• page_verify_option is there on >= 2005 sys.databases
• you need to combine also the compat level. on 80 even on 2005 I'm not sure you can enable checksum
• if page_verify_option is not there, there's always DATABASEPROPERTYEX

last but not least, I think @potatoqualitee has a 2000 around for quick testing

[lowlydba]

@niphlod

you need to combine also the compat level. on 80 even on 2005 I'm not sure you can enable checksum

I was able to turn it on despite compatibility level 80, as I was working through some old servers. Sort of surprised it worked.

[wsmelton]

The sys schema itself @michalporeba does not exist in SQL Server 2000.

Overall I think @potatoqualitee should decide whether we want to even try and support 2000 because I don't think we should for this module. We already require PowerShell 4.0, which dbatools goes back to 3.0. So considering this module is for newer "tech" I would draw the line somewhere and not have the same backward support we do with dbatools because this module does not serve the same purpose.

The documentation itself for SQL Server 2000....is only available if you still have the installation media. Microsoft has already removed it from BOL and it was not ( and is not ) migrated to the new docs formatted site.

[wsmelton]

Compatibility 80 is another beast to deal with as well because certain DMVs and system views will not work the same either. Most things will but we have bugs in dbatools functions (recall a few issues got submitted) because of this as well.

[SQLDBAWithABeard]

I am happy to not support 2000 for everything. Some will work some won’t. That’s ok.

[michalporeba]

I would like to give it a go if somebody can help me.

select d.name                           [Database]
    ,''														                    DatabaseCollation
    ,''																            Owner
    ,''															                RecoveryModel
    ,convert(bit, case when 1 = 1 & [status] then 1 else 0 end)				    AutoClose
    ,convert(bit, 0)													        AutoCreateStatistics
    ,convert(bit, case when 4194304 = 4194304 & [status] then 1 else 0 end)		AutoShrink
    ,convert(bit, 0)														    AutoUpdateStatistics
    ,convert(bit, 0)														    AutoUpdateStatisticsAsynchronously
    ,case
		when 16 = 16 & [status] then 'TORN_PAGE_DETECTION'
		when 65536 = 65536 & [status] then 'CHECKSUM'
		else 'NONE'
	end 																		PageVerify
    ,0																			SuspectPages
    ,convert(bit, 0)															Status
    ,''															                Trustworthy   
    ,'80'															            CompatibilityLevel
    ,''																            UserAccess
    ,convert(bit, case when 1024 = 1024 & [status] then 1 else 0 end)           IsReadOnly
    ,0																			DataFilesWithoutBackup
from sysdatabases d

@lowlydba any chance you could complete that query so that it returns as much of this information as possible? compare it to the one i have here https://github.com/michalporeba/dbachecks/blob/316-assert-functions-2000/internal/functions/Get-DatabaseInfo.ps1

[lowlydba]

@michalporeba Got most of the data:

select [d].[name]                                                                    [Database]
    ,databasepropertyex([d].[name], 'Collation')                                         [DatabaseCollation]
    ,suser_sname([d].[sid])                                                      [Owner]
    ,databasepropertyex([d].[name], 'Recovery')                                      [RecoveryModel]
    ,convert(bit, (databasepropertyex([d].[name], 'IsAutoClose')))                       [AutoClose]
    ,convert(bit, (databasepropertyex([d].[name], 'IsAutoCreateStatistics')))                [AutoCreateStatistics]
    ,convert(bit, (databasepropertyex([d].[name], 'IsAutoShrink')))                          [AutoShrink]
    ,convert(bit, (databasepropertyex([d].[name], 'IsAutoUpdateStatistics')))                    [AutoUpdateStatistics]
    ,convert(bit, 0)                                                             [AutoUpdateStatisticsAsynchronously]
    ,case
        when 16 = 16 & [d].[status] then 'TORN_PAGE_DETECTION'
        else 'NONE'
    end                                                                          [PageVerify]
    ,convert(bit, 0)                                                                         [SuspectPages]
    ,case
        when 32 = 32 & [d].[status] then 'LOADING'
        when 64 = 64 & [d].[status] then 'PRE_RECOVERY'
        when 128 = 128 & [d].[status] then 'RECOVERING'
        when 128 = 128 & [d].[status] then 'NOT_RECOVERED'
        when 512 = 512 & [d].[status] then 'OFFLINE'
        when 32768 = 32768 & [d].[status] then 'EMERGENCY'
        when 1073741824 = 1073741824 & [d].[status] then 'CLEANLY_SHUT_DOWN'
        else 'ONLINE'
    end                                                                          [Status]
    ,convert(bit, 0)                                                             [Trustworthy]
    ,[d].[cmptlevel]                                                              [CompatibilityLevel]                                                           
    ,databasepropertyex([d].[name], 'UserAccess')                                        [UserAccess]
    ,convert(bit, case when 1024 = 1024 & [d].[status] then 1 else 0 end)                    [IsReadOnly]
    ,convert(bit, 0)                                                             [DataFilesWithoutBackup]
from sysdatabases d

[michalporeba]

Thanks, that will be very useful, but implementing it (the way I want) depends now on ability to skip tests from inside the It block, so we can skip rather than fail tests which are not support in the specific version, and that we can unit test that behaviour. I've got a PR with pester already, I'm now waiting for it to hopefully get approved soon but it seems every body is on conferences these days ;)

pester/Pester#1026

[ashdar]

Any chance of this moving forward? The above-linked Pester issue was last commented-on in July.

I run over 31,000 tests (in total) every day. This issue explains 3 of my 17 failures. I would like to get those 3 items out of the list of "I just need to ignore that one" that I keep in my head.

I can test against 1 development and 2 production SQL 2005 instances, which will be up and running for another 6 to 12 months.