Check if a server is online before connecting to a linked server

[NilsBevaart]

We are scaling out our large database over multiple servers. For this we are setting up distributed querys to access the data. The performance and scalability is great.

Hovever, we want to keep our availability very high. If one of the servers would be offline, the query would return an error. To bypass this, I want to check if a server is online before executing the query. If not, the query is send to a different server with the same dataset (with help of replication)

When I do this with for example a small select statement and capturing the @@error code, the complete stored procedure is stoped due to the high serverity (16) when the server is not available.
I used the following code:

ALTER  PROCEDURE prc_LB
AS
DECLARE	@Test bit

--	TEST IF SERVER 1 IS ONLINE
SELECT	@Test=Operational
FROM	[UNI-SRV-BI1].DB1.dbo.tblOperational
--	ON ERROR, SET AVAILABLE=1
IF @@ERROR<>0
BEGIN
	SELECT abc FROM MyTable
		UNION ALL
	SELECT abc FROM [UNI-SRV-BI1].DB1.dbo.MyTable
END
ELSE	--USE SERVER 2
BEGIN
	SELECT abc FROM MyTable
		UNION ALL
	SELECT abc FROM [UNI-SRV-BI2].DB1.dbo.MyTable
END

Is there a way to make this work?
Thanks

Nils Bevaart

 

[NilsBevaart]

I think I can use the xp_cmdshell to start the scm utility.
However, I'm having trouble with this tool.

According to the help, I would use

scm -Action 3

to get the current status. When I use this (directly on the commandline to test it) I keep on getting the help list with all available arguments.
Even if I use the servername and password arguments, I'm not getting the result.

Any suggestions or examples?

Nils Bevaart

 

[JamesLean]

You need to specify the service you want to check, eg:

scm -Action 3 -Service mssqlserver

However, that utility pops up a message box to confirm success/failure so you may have problems running it successfully using xp_cmdshell (no user input is allowed).

In any case, adding this kind of check every time you run all your queries is going to add quite a lot of overhead, especially when you would hope 99% of the time the server will be running fine. I would say it is a fairly unusual technique to use.

There are other methods of providing automatic failover and high availability - have you looked at clustering at all?

--James

 

[NilsBevaart]

Well, the commandline works great. As predicted, it does not return the code back to the xp_cmdshell procedue.

Bad luck.

We looked at fail over clustering, but this would require an investment of about twice the amount of using dubble servers and having replication between them. The extermal storage vs. interal storage saves a lot.
If I can check the availability before starting the query, this would be a great work-around for our scenario.

Nils Bevaart