SQL Server
SQL Server
SSH Parallel Remote Commands on Multiple Servers
See more SSH Examples
Demonstrates running the same command on several servers simultaneously. It is simply a matter of using one Ssh object per server, starting the command on each with QuickCmdSend, and then polling each connection with QuickCmdCheck until all have finished.
Background: This is the fan-out pattern behind configuration-management and monitoring tools: query or update a whole fleet in roughly the time the slowest single host takes, rather than the sum of all of them. Each server needs its own object because each has its own connection and authentication state. Note that results come back in whatever order the servers finish, which is why the example tracks completion per connection. Production code would keep the objects in arrays instead of repeating the logic per server.
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- Demonstrates running the same command on several servers simultaneously. It is simply a
-- matter of using one Ssh object per server.
DECLARE @ssh1 int
EXEC @hr = sp_OACreate 'Chilkat.Ssh', @ssh1 OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @ssh2 int
EXEC @hr = sp_OACreate 'Chilkat.Ssh', @ssh2 OUT
DECLARE @ssh3 int
EXEC @hr = sp_OACreate 'Chilkat.Ssh', @ssh3 OUT
DECLARE @port int
SELECT @port = 22
-- Normally you would not hard-code the password in source. You should instead obtain it
-- from an interactive prompt, environment variable, or a secrets vault.
DECLARE @password nvarchar(4000)
SELECT @password = 'mySshPassword'
EXEC sp_OAMethod @ssh1, 'Connect', @success OUT, 'ssh-server1.example.com', @port
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @ssh1, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
EXEC sp_OAMethod @ssh1, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @ssh1, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
EXEC sp_OAMethod @ssh2, 'Connect', @success OUT, 'ssh-server2.example.com', @port
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @ssh2, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
EXEC sp_OAMethod @ssh2, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @ssh2, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
EXEC sp_OAMethod @ssh3, 'Connect', @success OUT, 'ssh-server3.example.com', @port
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @ssh3, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
EXEC sp_OAMethod @ssh3, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @ssh3, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
-- This command sleeps for 5 seconds and then prints the system date/time, making the parallel
-- execution easy to observe.
DECLARE @cmd nvarchar(4000)
SELECT @cmd = 'sleep 5; date'
-- Start the command on each server. QuickCmdSend returns immediately.
DECLARE @channel1 int
EXEC sp_OAMethod @ssh1, 'QuickCmdSend', @channel1 OUT, @cmd
IF @channel1 < 0
BEGIN
EXEC sp_OAGetProperty @ssh1, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
DECLARE @channel2 int
EXEC sp_OAMethod @ssh2, 'QuickCmdSend', @channel2 OUT, @cmd
IF @channel2 < 0
BEGIN
EXEC sp_OAGetProperty @ssh2, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
DECLARE @channel3 int
EXEC sp_OAMethod @ssh3, 'QuickCmdSend', @channel3 OUT, @cmd
IF @channel3 < 0
BEGIN
EXEC sp_OAGetProperty @ssh3, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
-- The command is now running on all three servers at once. Poll each connection until every
-- command has finished. (Production code would keep the objects in arrays rather than
-- repeating the logic.)
DECLARE @pollTimeoutMs int
SELECT @pollTimeoutMs = 50
DECLARE @numFinished int
SELECT @numFinished = 0
DECLARE @finished1 int
SELECT @finished1 = 0
DECLARE @finished2 int
SELECT @finished2 = 0
DECLARE @finished3 int
SELECT @finished3 = 0
WHILE @numFinished < 3
BEGIN
IF Not @finished1
BEGIN
DECLARE @ch1 int
EXEC sp_OAMethod @ssh1, 'QuickCmdCheck', @ch1 OUT, @pollTimeoutMs
IF @ch1 = -2
BEGIN
EXEC sp_OAGetProperty @ssh1, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
IF @ch1 >= 0
BEGIN
PRINT '---- ssh1 channel ' + @ch1 + ' finished ----'
DECLARE @out1 nvarchar(4000)
EXEC sp_OAMethod @ssh1, 'GetReceivedText', @out1 OUT, @ch1, 'utf-8'
EXEC sp_OAGetProperty @ssh1, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @ssh1, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
PRINT @out1
SELECT @finished1 = 1
SELECT @numFinished = @numFinished + 1
END
END
IF Not @finished2
BEGIN
DECLARE @ch2 int
EXEC sp_OAMethod @ssh2, 'QuickCmdCheck', @ch2 OUT, @pollTimeoutMs
IF @ch2 = -2
BEGIN
EXEC sp_OAGetProperty @ssh2, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
IF @ch2 >= 0
BEGIN
PRINT '---- ssh2 channel ' + @ch2 + ' finished ----'
DECLARE @out2 nvarchar(4000)
EXEC sp_OAMethod @ssh2, 'GetReceivedText', @out2 OUT, @ch2, 'utf-8'
EXEC sp_OAGetProperty @ssh2, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @ssh2, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
PRINT @out2
SELECT @finished2 = 1
SELECT @numFinished = @numFinished + 1
END
END
IF Not @finished3
BEGIN
DECLARE @ch3 int
EXEC sp_OAMethod @ssh3, 'QuickCmdCheck', @ch3 OUT, @pollTimeoutMs
IF @ch3 = -2
BEGIN
EXEC sp_OAGetProperty @ssh3, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
IF @ch3 >= 0
BEGIN
PRINT '---- ssh3 channel ' + @ch3 + ' finished ----'
DECLARE @out3 nvarchar(4000)
EXEC sp_OAMethod @ssh3, 'GetReceivedText', @out3 OUT, @ch3, 'utf-8'
EXEC sp_OAGetProperty @ssh3, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @ssh3, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
RETURN
END
PRINT @out3
SELECT @finished3 = 1
SELECT @numFinished = @numFinished + 1
END
END
END
EXEC sp_OAMethod @ssh1, 'Disconnect', NULL
EXEC sp_OAMethod @ssh2, 'Disconnect', NULL
EXEC sp_OAMethod @ssh3, 'Disconnect', NULL
EXEC @hr = sp_OADestroy @ssh1
EXEC @hr = sp_OADestroy @ssh2
EXEC @hr = sp_OADestroy @ssh3
END
GO