Say I have a set of servers with mirrored application databases and a set of statistics servers where a couple of the tables are exposed from the application database as views (with some tenant specific filtering). The view looks something like
... as select * from [server].[table] where tenant_id = xyz
Is there a way to create the view such that it doesn't depend on which of the applications server is primary. The view stops working after every fail-over and someone has to fail-back the DB.
What is the best practice when it comes to situations like this? Any not-so-best solution?