Sql-server – Completion of SQL Job triggers start of job on a different SQL Server

I would say consider logon triggers to accomplish this. This way you aren't using a server side trace. You can also audit all sucessful logins in the SQL Server error log (right click on instance, properties, security and then choose both successful and failed logons.. I don't believe this shows you the DB context info, though)

Also - I would seriously consider upgrading to a later version of SQL if possible. SQL 2005 is two (three if you count 2008 R2) versions back. I know you probably know that but I'd feel bad if I didn't call it out :-)

I would also highly discourage the use of SA for any logons. SA is a highly privileged account. It is -the- highly privileged account in SQL Server. Everyone knows there is an account named SA and it can be prone to hack attempts. I tend to push for windows authentication only and ensure a group is added that the proper DBAs team can properly and securely added to. I often disable the SA account in mixed mode and will create another account with SA rights but a non descript name. If, for some reason, the SA account needs to stay around, I try and give that a horrible password and store it someplace really safe and not use it.

By not having an SA account used by so many people that you need to audit its usage, you can give more granular permissions to do the required activities in SQL and no more. Least privilege and tight access lists will take you much further than watching SA account activity.

At any rate, Logon triggers may be the best bet. Trace would work but there is a cost associated with that (there is with logon triggers also but my guess is the cost of trace will be more expensive for you)

I'd say the easiest possible way to do this is through PowerShell and SMO. Take the following code for instance:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$SqlServerNames = "Server1", "Server2"
$SqlJobName = "YourJob"

foreach ($SqlServerName in $SqlServerNames) {
    $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

    $SqlServer.JobServer.Jobs[$SqlJobName].Start()
}

All this does it loop through a list of servers (explicitly stated at as string array variable, but can easily be obtained also from a SQL Server database table, a text file, etc.), connects to the current server, and then starts the job with the job named whatever you set the $SqlJobName variable to.

It's also worth noting that the Start() function will not wait for the job to complete before continuing code execution.

As you can see, in very few lines of PowerShell code you are able to accomplish this task. But I'd take it a step further, such as error handling. You don't want this to bomb out and not know what failed, when it failed, and what servers it did/didn't run on.

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlServer.JobServer.Jobs[$SqlJobName].Start()
    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

And, as always with any code, test this out in a non-production environment to ensure that it does what you think it will and should do. Re-work it for your needs/environment.

As per RoKa's comment:

Can this be edited to check for whether the job is currently running, error handling?

Great point, and definitely something to check for (as well as if the job actually exists by checking for a non-null value):

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlJob = $SqlServer.JobServer.Jobs[$SqlJobName]

        if (($SqlJob) -and ($SqlJob.CurrentRunStatus -eq [Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle)) {
            $SqlJob.Start()
        }

        else {
            # handle/log accordingly if the job doesn't exist or it's not idle
        }

    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

EDIT: While re-reading your question, I see SQL Server 2000. Are those going to be the target instances? If that is the case, and you do decide to go with the PowerShell/SMO answer I provided, then definitely definitely definitely test and ensure it is going to do what you want. Test this out very far from production and be 100% sure it does what you think it will.

EDIT: It looks like SMO versions before SQL Server 2012 (i.e. 2005, 2008, and 2008 R2) support managing SQL Server 2000 instances. I'm finding this information through this BOL reference.