Any thoughts on keeping it simple and use SSIS to make an Execute SQL Task for each database you want to poll to check if it is alive?

The idea is to run a SQL query against any table (e.g. select count(*) from <table>) on any schema of the target server and set the Execute SQL Task to if-fail, then put a subsequent action to send an e-mail with a short text of your choice.

Start somewhere simple and get the setup running to see if the architecture works. You can refine and add bells and whistles later.

With this path, you don't need to do much coding and testing and get the job done in a day or two.

Stored procedures is not always the answer and it's rather localized to one database server. Even with Linked Servers, the code is on one server rather than an independent SSIS server (potentially) overseeing all the SQL Servers.