0

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?

  • What version and edition of SQL Server? Have you considered using AlwaysOn where you can use a listener name that doesn't change after failover? – HandyD Mar 4 at 5:58
  • 2008 R2. Availability groups therefore not an option. – Codeheda Apr 27 at 15:07
1

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

| improve this answer | |
  • In my testing, so far, I have noticed SQL server can no longer use statistics with OpenRowset and the end effect is horribly slow queries. – Codeheda Jun 26 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.