Verificare linked servers

Situatie

Pentru simplificarea administrarii serverelor de sql am facut o procedura care, rulata pe un server de sql, verifica toate LinkedServer-ele de pe acel server si le testeaza conexiunea\functionalitatea.
Ea poate fi pusa sa ruleze si intr-un job, si poate trimite alerte pe email , alerte care sa contina linkedserver-ele ce nu mai sunt functionale.

Solutie

— exec CheckLinkedServers ‘Profilul de email de pe sql server’, ‘adresa email unde sa se trimita alertele’

CREATE PROCEDURE CheckLinkedServers @profile VARCHAR(255),@email VARCHAR(2000)

BEGIN

DECLARE @test_linkedserver BIT
DECLARE @servername SYSNAME

select ROW_NUMBER() Over(Order by server_id) as Seq,name as LinkerServer
into #LinkedServers
from sys.servers (nolock) where is_linked=1

declare @i as int = 1
declare @j as int = (select count(*) from #LinkedServers)

WHILE (@i<=@j)
BEGIN

BEGIN TRY
select @servername=LinkerServer from #LinkedServers where Seq=@i

EXEC @test_linkedserver= sys.sp_testlinkedserver @servername

PRINT ‘Sucessfully connected to ‘ + CAST(@servername as VARCHAR(30))
END TRY

BEGIN CATCH
PRINT ‘Failed to connect to ‘ + CAST(@servername as VARCHAR(30))

IF (@profile <> ”) AND (@email <> ”)
BEGIN
DECLARE @emailSubject VARCHAR(255)
DECLARE @emailBody VARCHAR(8000)
SET @emailSubject = ‘Linked server connnection problem : ‘ + @servername + ‘ linked server cannot be accessed from ‘ + @@SERVERNAME
SET @emailBody = @emailSubject

EXEC msdb.dbo.sp_send_dbmail
@profile_name= @profile
, @recipients=@email
, @body=@emailBody
, @subject=@emailSubject
, @importance=’High’
END

RAISERROR (‘Linked Server Failure’, 16, 1, @emailSubject)

END CATCH

set @i = @i + 1
END
END

Tip solutie

Permanent

Follow Us