May 21, 2011 at 10:34 pm
Is there any way to check if a connection between 2 SQL Servers exists.
For instance I don't want a sproc to be run unless a connection exists (i.e it could be broken by resetting the computer etc). Is there any way I can do this within code so I can deal
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
May 22, 2011 at 5:30 am
The only real test is to try to connect. So I'd try a really simple select on the remote server and do a try / catch to get the error.
The only other option I have in mind is to do a ping with cmd_shell
May 22, 2011 at 6:13 pm
bijarcity (5/21/2011)
Is there any way to check if a connection between 2 SQL Servers exists.For instance I don't want a sproc to be run unless a connection exists (i.e it could be broken by resetting the computer etc). Is there any way I can do this within code so I can deal
I remember implementing hearthbeat monitoring based in a simple procedure running every nn seconds to check the health of the partner server. Procedure is supposed to page you if hearthbeat fails.
Depending on criticality you may want to have a third server checking the two you are interested otherwise you may find yourself thinking everything is Okay while actually your two servers are down 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 23, 2011 at 12:34 am
PaulB-TheOneAndOnly (5/22/2011)
bijarcity (5/21/2011)
Is there any way to check if a connection between 2 SQL Servers exists.For instance I don't want a sproc to be run unless a connection exists (i.e it could be broken by resetting the computer etc). Is there any way I can do this within code so I can deal
I remember implementing hearthbeat monitoring based in a simple procedure running every nn seconds to check the health of the partner server. Procedure is supposed to page you if hearthbeat fails.
Depending on criticality you may want to have a third server checking the two you are interested otherwise you may find yourself thinking everything is Okay while actually your two servers are down 😀
--------------------
thanks but the problem is ,when you select a table from other servers this server that you write the query on it can't access the table from onother severs so you will time out
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
May 23, 2011 at 12:39 am
Ninja's_RGR'us (5/22/2011)
The only real test is to try to connect. So I'd try a really simple select on the remote server and do a try / catch to get the error.The only other option I have in mind is to do a ping with cmd_shell
//---------------------
i found that (cmd_shell)
here is the code of that:
01 -----------------------------------------------
02 --Ping Scanner using T-SQL (C) Duong Thanh ( knightvn AT gmail.com)
03 --Detect which host are online on LAN by pinging from SQL Server
04 --Usage: EXEC spPingScan '192.168.1.0-254'
05 -------------------------------------------------
06 CREATE PROCEDURE spPingScan @ip_range varchar(200)
07 AS
08 BEGIN
09 DECLARE @stpos int, @i int, @ip_start varchar(200), @start int
10 ,@head varchar(200), @pos_dash int, @end int
11
12 SET @ip_range = LTRIM(RTRIM(@ip_range))
13 --ex: @ip_range = '192.168.1.1-10'
14 SET @pos_dash = CHARINDEX('-', @ip_range) -- dash position
15 SET @end = SUBSTRING(@ip_range, @pos_dash + 1, LEN(@ip_range) - @pos_dash) -- 233
16 SET @ip_start = SUBSTRING (@ip_range, 1, LEN(@ip_range) - LEN(@end) - 1) -- 192.168.101.20
17
18 SET @stpos = 1
19
20 -- Get final . index
21 SET @i = 1
22
23 WHILE @i < 4
24 BEGIN
25 SET @stpos= CHARINDEX('.', @ip_start, @stpos+1)
26 SET @i = @i + 1
27 END
28
29 SET @start = CAST(SUBSTRING(@ip_start, @stpos+1, LEN(@ip_start) - @stpos) AS int) -- 10
30 SET @head = SUBSTRING(@ip_start, 1, LEN(@ip_start) - LEN(@start)) -- 192.168.1.
31
32 --tmp tables
33 CREATE TABLE #tmpPingResult
34 ( [ID] int identity NOT NULL, [content] varchar(400) NULL )
35
36 CREATE TABLE #tmpHostsUp
37 ( [ID] int identity NOT NULL, [Host] varchar(50) NULL)
38
39 DECLARE @j-2 int, @cmd varchar(200), @host varchar(50)
40 SET @cmd = ''
41 SET @j-2 = @start
42
43 WHILE (@j <= @end)
44 BEGIN
45 SET @host = @head + LTRIM(RTRIM(CAST(@j as varchar(3))))
46 SET @cmd = 'ping -n 1 ' + @host
47 INSERT INTO #tmpPingResult EXEC master..xp_cmdshell @cmd
48
49 IF( (SELECT COUNT(*) FROM #tmpPingResult) > 8 )
50 BEGIN
51 INSERT INTO #tmpHostsUp VALUES (@host) --insert into host table if find a host up
52 END
53
54 TRUNCATE TABLE #tmpPingResult
56 END
57
58 --return
59 SELECT * FROM #tmpHostsUp
60
61 END
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
May 24, 2011 at 5:18 am
bijarcity (5/23/2011)
PaulB-TheOneAndOnly (5/22/2011)
bijarcity (5/21/2011)
Is there any way to check if a connection between 2 SQL Servers exists.For instance I don't want a sproc to be run unless a connection exists (i.e it could be broken by resetting the computer etc). Is there any way I can do this within code so I can deal
I remember implementing hearthbeat monitoring based in a simple procedure running every nn seconds to check the health of the partner server. Procedure is supposed to page you if hearthbeat fails.
Depending on criticality you may want to have a third server checking the two you are interested otherwise you may find yourself thinking everything is Okay while actually your two servers are down 😀
--------------------
thanks but the problem is ,when you select a table from other servers this server that you write the query on it can't access the table from onother severs so you will time out
Exactly. You trap the error and page oncall DBA; that's the idea.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply