(SQL Server) SSH Parallel Remote Commands on Multiple Servers

Shows how to execute a command in parallel on multiple servers.

Note: This example requires Chilkat v9.5.0.65 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

// Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
//
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes Chilkat SSH/SFTP to have been previously unlocked.
    -- See Unlock SSH for sample code.

    -- Executing a command on multiple servers simultaneously is straightforward.
    -- It's just a matter of using one SSH object per server..
    DECLARE @ssh1 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Ssh', @ssh1 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @ssh2 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Ssh', @ssh2 OUT

    DECLARE @ssh3 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Ssh', @ssh3 OUT

    DECLARE @port int
    SELECT @port = 22
    DECLARE @success int
    EXEC sp_OAMethod @ssh1, 'Connect', @success OUT, 'ssh-server1.com', @port
    IF @success <> 1
      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

    -- Authenticate using login/password:
    EXEC sp_OAMethod @ssh1, 'AuthenticatePw', @success OUT, 'sshLogin1', 'sshPassword1'
    IF @success <> 1
      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

    -- Connect and authenticate with 2 more servers.
    -- For brevity, the success/failure won't be checked...
    EXEC sp_OAMethod @ssh2, 'Connect', @success OUT, 'ssh-server2.com', @port
    EXEC sp_OAMethod @ssh2, 'AuthenticatePw', @success OUT, 'sshLogin2', 'sshPassword2'
    EXEC sp_OAMethod @ssh3, 'Connect', @success OUT, 'ssh-server3.com', @port
    EXEC sp_OAMethod @ssh3, 'AuthenticatePw', @success OUT, 'sshLogin3', 'sshPassword3'

    -- Note: If we wanted, we could've used ConnectAsync and AuthenticatePwAsync
    -- to do the connecting and authenticating in parallel...

    -- The command to be run on each SSH server will sleep for 5 seconds,
    -- and then show the current system date/time.
    DECLARE @cmd nvarchar(4000)
    SELECT @cmd = 'sleep 5; date'

    -- Start each command
    DECLARE @ssh1Channel int
    EXEC sp_OAMethod @ssh1, 'QuickCmdSend', @ssh1Channel OUT, @cmd
    IF @ssh1Channel < 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
    -- For brevity, we're not checking the return values here:
    DECLARE @ssh2Channel int
    EXEC sp_OAMethod @ssh2, 'QuickCmdSend', @ssh2Channel OUT, @cmd
    DECLARE @ssh3Channel int
    EXEC sp_OAMethod @ssh3, 'QuickCmdSend', @ssh3Channel OUT, @cmd

    -- OK, at this point the command is running simultaneously on each server.

    -- Now collect the results of each command.
    DECLARE @pollTimeoutMs int
    SELECT @pollTimeoutMs = 50
    DECLARE @numFinished int
    SELECT @numFinished = 0
    DECLARE @channel int

    -- Note: You would rewrite this code to use arrays.
    DECLARE @ssh1Finished int
    SELECT @ssh1Finished = 0
    DECLARE @ssh2Finished int
    SELECT @ssh2Finished = 0
    DECLARE @ssh3Finished int
    SELECT @ssh3Finished = 0
    WHILE @numFinished < 3
      BEGIN
        -- Check to see if anything has finished.
        -- QuickCmdCheck returns -1 if there are no errors and nothing else finished
        -- QuickCmdCheck returns -2 if there was an error (such as a lost connection)
        -- QuickCmdCheck returns a channel number if a channel finished.
        IF @ssh1Finished <> 1
          BEGIN
            EXEC sp_OAMethod @ssh1, 'QuickCmdCheck', @channel OUT, @pollTimeoutMs
            IF @channel = -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 @channel = @ssh1Channel
              BEGIN


                PRINT '---- ssh1 channel ' + @channel + ' finished ----'
                EXEC sp_OAMethod @ssh1, 'GetReceivedText', @sTmp0 OUT, @channel, 'ansi'
                PRINT @sTmp0
                SELECT @numFinished = @numFinished + 1
                SELECT @ssh1Finished = 1
              END
          END
        IF @ssh2Finished <> 1
          BEGIN
            EXEC sp_OAMethod @ssh2, 'QuickCmdCheck', @channel OUT, @pollTimeoutMs
            IF @channel = -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 @channel = @ssh2Channel
              BEGIN


                PRINT '---- ssh2 channel ' + @channel + ' finished ----'
                EXEC sp_OAMethod @ssh2, 'GetReceivedText', @sTmp0 OUT, @channel, 'ansi'
                PRINT @sTmp0
                SELECT @numFinished = @numFinished + 1
                SELECT @ssh2Finished = 1
              END
          END
        IF @ssh3Finished <> 1
          BEGIN
            EXEC sp_OAMethod @ssh3, 'QuickCmdCheck', @channel OUT, @pollTimeoutMs
            IF @channel = -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 @channel = @ssh3Channel
              BEGIN


                PRINT '---- ssh3 channel ' + @channel + ' finished ----'
                EXEC sp_OAMethod @ssh3, 'GetReceivedText', @sTmp0 OUT, @channel, 'ansi'
                PRINT @sTmp0
                SELECT @numFinished = @numFinished + 1
                SELECT @ssh3Finished = 1
              END
          END

      END

    -- --------------
    -- Sample output:

    -- 	---- ssh2 channel 101 finished ----
    -- 	Fri Dec 23 00:25:48 UTC 2016
    -- 
    -- 	---- ssh3 channel 102 finished ----
    -- 	Thu Dec 22 18:25:12 CST 2016
    -- 
    -- 	---- ssh1 channel 100 finished ----
    -- 	Thu Dec 22 18:25:48 CST 2016

    EXEC @hr = sp_OADestroy @ssh1
    EXEC @hr = sp_OADestroy @ssh2
    EXEC @hr = sp_OADestroy @ssh3


END
GO