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?
Best Answer
Well, I haven't tested but how about openrowset? Native client is deprecated so there might be a bit of work needed to get it running, depending on what SQL server version youre're running, but then again so is mirroring I suppose. How about something like this:
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Create view MyView
AS
SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=Server1;FailoverPartner=Server2;Trusted_Connection=yes;', AdventureWorks2012.HumanResources.Department) AS a;
GO