View from linked servers? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

View from linked servers?

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-18 : 05:40:43

I have a problem that i'm not sure how to approach exactly.

We have a sql server that needs data from 3 other sql servers.
i want something like
create view myWiev
as
select .. from server1
union all
select .. from server2
union all
select .. from server3

but each of these 3 servers can be offline for whatever reason and i'd like to just not return data from there but my select to still work.

unfortunatly a stored procedure is NOT an option here.

any ideas?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 06:05:44

Can you have a JOIN, such that the second part of the Join won't get acted on AT ALL if the first part is False?

You might need some scheduled task running, say, once a minute to update a local table as to whether Server1,2,3 are On/Off line ...

... which will mean that for up to 59 seconds after a server goes Offline users will get Connectivity Errors, and for up to 59 seconds after it comes back Online they won't get any data from it.

If something like that is acceptable I'm thinking of:


select *
(
   SELECT ...
   FROM LocalFlagTable AS LFT
        -- Hopefully this join will not be exercised if LFT.Status = 'OFFline'
        JOIN server1
             ON 1 = 1
   WHERE LFT.ServerName = 'server1' AND LFT.Status = 'online'
) AS S1
union all
...

If that STILL exercises the Join then maybe there is some more "dynamic" way of generating the syntax for only those servers that ARE online.

Failing that replicate the data locally and VIEW on a UNION of those, local, tables

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-18 : 07:53:07

hmm... interesting idea... i'll see what we decide in the end...
any more bright ideas anyone? can't have kristen take all the glory!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 08:21:10

Or...

A stored procedure with


  SELECT * FROM Table1 UNION ALL
  SELECT * FROM Table2 UNION ALL
  SELECT * FROM Table3

IF @@ERROR <> 0
  SELECT * FROM Table1 UNION ALL
  SELECT * FROM Table2

IF @@ERROR <> 0
  SELECT * FROM Table1 UNION ALL
  SELECT * FROM Table3

IF @@ERROR <> 0
  SELECT * FROM Table2 UNION ALL
  SELECT * FROM Table3

IF @@ERROR <> 0
  SELECT * FROM Table1

IF @@ERROR <> 0
  SELECT * FROM Table2

IF @@ERROR <> 0
  SELECT * FROM Table3

And in the VIEW

SELECT	*
FROM	OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'EXEC {sp_name above}')

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 08:23:12

~~Probably without the "INTO #Temp" ??~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 08:23:44

Dang! You saw it before I edited it

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-18 : 08:39:52

hmm... not a bad idea Peter.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 08:47:15

I have no idea of what timeout times (if any) that apply to your environment with linked server failure.
Is it instant? Or a 30 second "grace period"? Meaning every "failed" takes 30 seconds to complete, before moving on to next IF statement.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 09:14:50

If there is a grace period, try the FAST query option hint.

SELECT ...
...
OPTION (FAST 1)

This might be helpful even if there is not a grace period.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-18 : 09:34:08

no idea on that one yet... i think it should be instant.
it's all still in planning stage... i'm pushing replication of needed tables to our main server. it's not that many data.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 09:40:38

Hrmm.. I did some test with OPTION (FAST 1) with a complicated view.
Here are the results

1,542,216 records returned	CPU	DURATION	READS
				-----	--------	----------
No OPTION FAST			32499	46140		    70,387
With OPTION FAST 1		31496	39373		12,481,166
With OPTION FAST 2000000	31872	44985		    70,891

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-18 : 09:41:21

those are some heavy reads

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-18 : 09:42:21

oh and we're not talking about millions of rows.
we're talking in 200 - 500 rows range max.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 06:33:55

Any progress yet?

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-09 : 07:48:54

not yet... we' scraped this part for future... but we're porbably going to have a webservice access for each db...
which isn't that bad at all.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources