New Check - Schema changed

[michalporeba]

Inspired by the two sessions by @SQLDBAWithABeard about the devops process and dbachecks I thought it's strange that there isn't there a check already that would alert that when we were out, or perhaps on holidays, somebody changed the schema and didn't put the change to the repo.

The repo part would be rather difficult to implement but a simply hash of sys.sql_definition, perhaps last modify_date from sys.all_objects should be enough to quickly see if the schema was changed or not.

This check as well as the #300 (suspended checks) would require some state persistence. Perhaps more tests would benefit from it? What would be the best way to do it? json files or tables? I supposed it would depend whether the checks are supposed to be personal per dba or shared by a team.

[SQLDBAWithABeard]

I like this (Reason it is not here is because I would use DLM Dashboard from Redgate to do it)

A configuration item for "days since change" or "date since last change"

a test for Has any object changed since that date?

[wsmelton]

Something to consider on checking dates is converting them to local time. In situations where instances are on UTC or some other offset would have to be accounted for when comparing that date to what is in your versioning system.

Configuration wise, you could simply make a configuration that held the date value. If you are aware when the last change was submitted to your versioning system for a given database, just keep that value updated in your configuration prior to running the check(s).

[SQLDBAWithABeard]

All date comparisons should be using UTC - It will probably be easier in the first instance to display and configure in UTC as well. As long as we make it clear that that is what we are doing