AutoMon

Drop server into Automon DBA SPs – AM02

Advertisements

Dropping a server from the Automon CMS. Again, use SQL linked server or other source linked server SP, both are scripted here.

This script will delete the entry from table and drop linked server automatically.

Drop servers as other data source  SP:

alter PROCEDURE [dbo].[USP_DBA_DROPSERVER_FOR_MONITOR]
/*
Summary:     Drop server into AutoMon
Contact:     Muthukkumaran Kaliyamoorthy SQL DBA
Description: Drop server into AutoMon

ChangeLog:
Date         Coder                          Description
2013-jan-21  Muthukkumaran Kaliyamoorthy     Updated the 2012 functionality                   


*/

@P_LINK_SERVER SYSNAME,
@P_SERVER SYSNAME,
@P_VERSION SYSNAME,
@P_DESC VARCHAR(50)
--@P_USERNAME SYSNAME,
--@P_PWD VARCHAR(100)
WITH ENCRYPTION

AS 
 BEGIN

DECLARE @LINK_SERVER SYSNAME
DECLARE @SERVER SYSNAME
DECLARE @VERSION SYSNAME
DECLARE @DESC VARCHAR(50)
--DECLARE @USERNAME SYSNAME
--DECLARE @PWD VARCHAR(100)
DECLARE @droplogins VARCHAR(100)

SET @LINK_SERVER=@P_LINK_SERVER
SET @SERVER=@P_SERVER
SET @VERSION=@P_VERSION
SET @DESC=@P_DESC
--SET @USERNAME=@P_USERNAME
--SET @PWD=@P_PWD

DECLARE @TABSQL VARCHAR(500)
DECLARE @PROCSQL VARCHAR(500)
SET @PROCSQL='
USE [MASTER];
DROP TABLE [DBO].[TEMPSPACE]'
SET @TABSQL='DROP PROCEDURE [DBO].[USP_TEMPSPACE_POP]'

 
BEGIN
BEGIN TRY


--SELECT LEN(@PROCSQL)
EXEC ('EXEC ['+ @LINK_SERVER + '].MASTER.DBO.SP_EXECUTESQL N'''+ @PROCSQL+'''')
PRINT 'TABLE DROPED'
EXEC ('EXEC ['+ @LINK_SERVER + '].MASTER.DBO.SP_EXECUTESQL N'''+ @TABSQL+'''')
PRINT 'PROCEDURE DROPED'

END TRY


BEGIN CATCH

begin
--SELECT * FROM [DBADATA].[DBO].[DBA_ALL_SERVERS]
DELETE FROM [DBADATA].[DBO].[DBA_ALL_SERVERS]
WHERE [SERVERNAME]=@LINK_SERVER AND [DESCRIPTION]=@DESC
PRINT 'DELETE THE SERVER NAME FROM AUTOMATION TABLE'
end
END CATCH
end

EXEC SP_DROPSERVER @SERVER=@LINK_SERVER,@droplogins='droplogins'
--EXEC SP_DROPLINKEDSRVLOGIN @RMTSRVNAME='DBA_SNIPPETUHC',@LOCALLOGIN=NULL--,@RMTUSER=@USERNAME
--EXEC SP_DROPLINKEDSRVLOGIN @RMTSRVNAME=@LINK_SERVER,@locallogin=@USERNAME
PRINT 'LINKED SERVER DROPED'



END

Drop servers as SQL server  SP:

alter PROCEDURE [dbo].[USP_DBA_DROPSERVER_FOR_MONITOR]
/*
Summary:     Drop server into AutoMon
Contact:     Muthukkumaran Kaliyamoorthy SQL DBA
Description: Drop server into AutoMon

ChangeLog:
Date         Coder                          Description
2013-jan-21  Muthukkumaran Kaliyamoorthy     Updated the 2012 functionality                   


*/

@P_SERVER SYSNAME,
@P_VERSION SYSNAME,
@P_DESC VARCHAR(50)
--@P_USERNAME SYSNAME,
--@P_PWD VARCHAR(100)
WITH ENCRYPTION

AS 
 BEGIN

DECLARE @SERVER SYSNAME
DECLARE @VERSION SYSNAME
DECLARE @DESC VARCHAR(50)
--DECLARE @USERNAME SYSNAME
--DECLARE @PWD VARCHAR(100)
DECLARE @droplogins VARCHAR(100)


SET @SERVER=@P_SERVER
SET @VERSION=@P_VERSION
SET @DESC=@P_DESC
--SET @USERNAME=@P_USERNAME
--SET @PWD=@P_PWD

DECLARE @TABSQL VARCHAR(500)
DECLARE @PROCSQL VARCHAR(500)
SET @PROCSQL='
USE [MASTER];
DROP TABLE [DBO].[TEMPSPACE]'
SET @TABSQL='DROP PROCEDURE [DBO].[USP_TEMPSPACE_POP]'

 
BEGIN
BEGIN TRY


--SELECT LEN(@PROCSQL)
EXEC ('EXEC ['+ @SERVER + '].MASTER.DBO.SP_EXECUTESQL N'''+ @PROCSQL+'''')
PRINT 'TABLE DROPED'
EXEC ('EXEC ['+ @SERVER + '].MASTER.DBO.SP_EXECUTESQL N'''+ @TABSQL+'''')
PRINT 'PROCEDURE DROPED'

END TRY


BEGIN CATCH

begin
--SELECT * FROM [DBADATA].[DBO].[DBA_ALL_SERVERS]
DELETE FROM [DBADATA].[DBO].[DBA_ALL_SERVERS]
WHERE [SERVERNAME]=@SERVER AND [DESCRIPTION]=@DESC
PRINT 'DELETE THE SERVER NAME FROM AUTOMATION TABLE'
end
END CATCH
end

EXEC SP_DROPSERVER @SERVER=@SERVER,@droplogins='droplogins'
--EXEC SP_DROPLINKEDSRVLOGIN @RMTSRVNAME='DBA_SNIPPETUHC',@LOCALLOGIN=NULL--,@RMTUSER=@USERNAME
--EXEC SP_DROPLINKEDSRVLOGIN @RMTSRVNAME=@SERVER,@locallogin=@USERNAME
PRINT 'LINKED SERVER DROPED'



END

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 17 = 26