Technical Article

Execution of Code on Multiple Servers Remotely

,

Do you have more than 10 sql servers you must manage?
Most DBA s do, I have over 120 sql instances to manage. Before I used to use ISQL/OSQL in a batch to execute a command on all these instances. It works but not very nice. Below is the code I use to collect information of all my servers from T-SQL. No more batch files for me!

Basically, this code will create a table that houses your instance names and 'sa' passwords, create linked connection, execute your code, drop the link connection and finally delete the temp table. To add more instances, just  add another line to the "insert into" section of the code. My first usage of this code is to connect to all my production servers and report back the job  status on a daily basis. I only see the failed jobs and start my day from there.

You must execute this from a SQL 2005 connection as the linked server code is slightly different on SQL 2000 than SQL 2005. But that's ok as I connect to SQL 7.0, SQL 2000 and SQL 2005 servers.

Read the comments at the beginning of the code which shows you how to modify it for your enironment.

I hope that this code will make you life easier as it has done for me.

------------------------------------------------------------------------------------------------------------
--  Stored Procedure/Script Name: RemoteExec.sql 
--  
--  Purpose: This code will allow you to link to other 
-- servers, execute and retrieve the output all form one
-- location. EXECUTE THIS FROM SQL 2005
--
--  A few steps must be completed before you start.
--
-- 1.  Create a database on a srver that the table can be created can called it: MYCONNECTDB
--     If you don't like that name just to a search/replace for that name
--
-- 2.  Modify the INSERT INTO statements to place your instance names, sa-password
--
-- 3.  Change 'Domain\yourname' to your domain and user name
-- 
-- 4.  Search for 'YourCode' and change it to your code which is to be executed on the remote/linked server
--
-- That's it. Only have to modify once for your enviroment.
--
-- Currently, I'm using it to connect to 120+ servers!
--
-- Enjoy

------------------------------------------------------------------------------------------------------------


USE MYCONNECTDB
go

drop table [dbo].[LinkedInstanceTable]
go
 
USE MYCONNECTDB
go

CREATE TABLE [dbo].[LinkedInstanceTable]
 ( 
 [NamedInstance] VARCHAR(125), 
 [LinkedPsswd] VARCHAR(35)
 )
GO

INSERT INTO [MYCONNECTDB].[dbo].[LinkedInstanceTable]([NamedInstance],[LinkedPsswd]) VALUES ('InstanceName1','sa-password1')
INSERT INTO [MYCONNECTDB].[dbo].[LinkedInstanceTable]([NamedInstance],[LinkedPsswd]) VALUES ('InstanceName2','sa-password2')
INSERT INTO [MYCONNECTDB].[dbo].[LinkedInstanceTable]([NamedInstance],[LinkedPsswd]) VALUES ('InstanceName3','sa-password3')
GO

USE master
GO

PRINT ' '
PRINT '                 Linked Server Remoted Execution '
PRINT ' '
PRINT '                          Version 1.1'
PRINT ' '
PRINT '   Usage: This script is used to remotely execute T-SQL commands'
PRINT '   on remote linked servers. Thus eliminating the need to'
PRINT '   manually connect to each server.'
PRINT ' '
PRINT '   Edit this script and enter the servers to link to and enter'
PRINT '   the sa password. Once completed this script will delete the'
PRINT '   created table and remove all linked connections.'
PRINT ' '
PRINT '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -'
PRINT ' '

-- Stop unnecessary SQL ouput
SET NOCOUNT ON


DECLARE @InstanceName VARCHAR (125),
@psswd VARCHAR (15),
@mySQL VARCHAR (250),
@mySQL2 VARCHAR (250),
@Error VARCHAR (150)
        
-- Set up cursor to load instances names

DECLARE myInstances CURSOR FOR SELECT NamedInstance, LinkedPsswd FROM MYCONNECTDB.dbo.LinkedInstanceTable

OPEN myInstances
FETCH NEXT FROM myInstances INTO @InstanceName, @psswd

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Create/Connect/Execute/Drop Linked Server for Instance: [' + @InstanceName + ']'

---------------------------------------------------------------------------------------------------
-- Connect to the remote/linked instance

EXEC sp_addlinkedserver @InstanceName , N'SQL Server'
IF @@Error <> 0
 BEGIN
PRINT 'WARNING! sp_addlinkedserver error for instance: [' + @InstanceName + '], please review'
 END
ELSE 
PRINT 'Adding Linked Server for instance: [' + @InstanceName + ']'
EXEC sp_addlinkedsrvlogin @InstanceName, 'FALSE', 'Domain\yourname', 'sa', @psswd

IF @@Error <> 0
 BEGIN
PRINT 'WARNING! sp_addlinkedsrvlogin error for instance: [' + @InstanceName + '], please review'
 END
ELSE 
PRINT 'Adding Linked Server Login for instance: [' + @InstanceName + ']'


--  Execute your code on the remote/linked server

   PRINT 'Executing Command on Instance: [' + @InstanceName + ']'
--   PRINT ' '

-- ------------------------------------------------------------------------------
-- Edit this section with the T_SQL query to be executed on all linked servers 
-- ------------------------------------------------------------------------------
-- ******************************************************************************


exec ('YourCode')


-- ******************************************************************************
-- ------------------------------------------------------------------------------  

IF @@Error <> 0
 BEGIN
PRINT 'WARNING! CAN NOT EXECUTE REMOTE CODE ON INSTANCE: [' + @InstanceName + '], PLEASE REVIEW'
 END
ELSE 
PRINT ' '
PRINT 'Exeuted remote code on instance: [' + @InstanceName + ']'
PRINT ' '
PRINT ' '
PRINT ' '


-- Drop the remote/linked server connection

PRINT 'Dropping Linked Server Instance: [' + @InstanceName + ']'
EXEC sp_dropserver @InstanceName, 'droplogins' 

IF @@Error <> 0
 BEGIN
PRINT 'WARNING! CAN NOT DROP CONNECTION FOR INSTANCE: [' + @InstanceName + '] PLEASE REVIEW'
 END
ELSE 
PRINT 'Dropped connection on instance: [' + @InstanceName + ']'

PRINT ' '
PRINT '*********************************************'
FETCH NEXT FROM myInstances INTO @InstanceName, @psswd
END

CLOSE myInstances
DEALLOCATE myInstances

-- Drop work table. This will ensure better security as it contains you 'sa' passwords!

drop table  [MYCONNECTDB].[dbo].[LinkedInstanceTable]

IF @@Error <> 0
 BEGIN
PRINT 'WARNING! CAN NOT DROP TABLE: MYCONNECT, PLEASE REVIEW'
 END
ELSE 
PRINT 'Dropped Table: MYCONNECT'

PRINT ' '
PRINT '*********************************************'
PRINT ' '
PRINT ' End of Query - Completed '

Rate

5 (1)

Share

Share

Rate

5 (1)