Hi,
Is it possible to create a Datasource that will cope with a MS SQL database failing over onto the Slave server?

It’s not supported out of the box, but the query runner code is very straighforward and you can change it to handle the fallback logic very easily.