Sql-server – I’m using the SQL Server Agent to schedule even non-database tasks – is this a bad idea

I have only done this a couple of times and quite a while ago. At that time I used PSEXEC.EXE from the Windows Internals. You can read the instructions here:

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

For Powershell remote execution there was a discussion in StackOverflow that satisfied the questioner. Powershell is probably the way to go these days.

https://stackoverflow.com/questions/9535515/powershell-execute-remote-exe-with-command-line-arguments-on-remote-computer

However you run the remote job, you can schedule it in the SQL Server Agent process that runs the other jobs. If you think it would work for you, you could also set the job to run on a defined period of 'idleness'. (I don't think that is what you want, but you can look into it under the SQL Server Agent discussion on Agent schedules at http://technet.microsoft.com/en-us/library/ms189065.aspx.)

Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job. If anybody sees any potential problem with this I'd be interested in knowing!

-- Check if this server is the preferred replica for backups.
    If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
    BEGIN

        -- Check whether the script is running from SQL Agent.  If it is not then raise an error so that the user knows that the backup hasn't completed.
        -- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
        declare @AgentAccount varchar(100)
        set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
                                AND value_name = 'ObjectName'
                            )


        if @AgentAccount = SYSTEM_USER
        begin

            declare @jobSource tinyint
            SELECT  @jobSource = ja.run_requested_source
                    --WHEN 1 THEN 'SOURCE_SCHEDULER'
                    --WHEN 2 THEN 'SOURCE_ALERTER'
                    --WHEN 3 THEN 'SOURCE_BOOT'
                    --WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
                    --WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                    --ELSE 'UNKNOWN' END
            FROM msdb.dbo.sysjobactivity ja
            WHERE ja.session_id = @@SPID
            AND start_execution_date is not null
            AND stop_execution_date is null;

            if @jobSource = 4   -- Job was run manually.
            begin
                RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
                return 1
            end
            else
            begin
                print 'No backup produced as this server is not the preferred replica for backups.'
                return 0
            end
        end
        else
        begin
            RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
            return 1
        end
    END