In Microsoft SQL Server, I have some older databases set to "Torn Page Detection" rather than "Checksum" page verify.
Do I need to determine if a given SQL Server database is 'compatible' with the "Checksum" page verify setting, or is it something that can be applied to any SQL database?
Are the databases even aware what their setting is? It is a setting that is handled transparently by SQL Server correct?
I realize that the change will only affect pages changed after the config takes effect.
Short Answer: No
You don't need to check compatibility; yes, it is something that can be applied to any database. It will be compatible, even if it is an 80 (SQL Server 2000) compatibility mode database, you can enable Checksum, and likely should.
This is transparent in that your tables don't look or act differently, your apps don't know about it, etc. As for the "database knowing" it depends on how you mean it. From the app connecting? No. But the SQL Server storage engine that interacts with the files knows it, so it does the checks on write, but again that is transparent to your app and users.
One more consideration:
You are correct, only pages changed after it takes effect is a good thing to keep in mind. Doing index rebuilds at some point after doing this is one good way to touch a whole lot of pages, and not a bad idea.
And watch performance/CPU overhead. There is a "cost" associated with Checksum, though that cost isn't as high as you might fear. This article, though written for 2005, talks about some of those concerns, but again it is rare to have an issue from this there.
