Sql-server – Run PS-Script after job is done on 3 instances – SQL Server

instancejobspowershellsql server

I have backup jobs running on a server with 3 instances.

Link to script: https://ola.hallengren.com/sql-server-backup.html

The solution I found for the other 2 servers with only once instance was to create a job which has the backup scripts as steps via TSQL and then simply runs the PowerShell script.

Is there a way to do this on a multi-instance server?

Server is running SQL Server 2012

Best Answer

You have a few options on how you could handle this...

Create a Scheduled Task on that server that contains a PowerShell script. The first part of that script contains a workflow that would iterate over each instance and call the backup job on each instance. Use a foreach -parallel would allow all the jobs to start at one time. You would then need to script out a while loop to wait and see that all jobs have completed. Then the end of that PowerShell script just call the code you need to run.
Pick an instance and use it as a "central" location to actually run the backups against each instance. You could utilize linked servers to remotely call Ola' scripts. This would offer a bit more control and ease of management because you would know once the command finished the backup for that instance are complete. Your last step would just be your PowerShell script.
Leave each backup job on the given instance. Add a scheduled task that calls your PowerShell script, but at the start of that script check to make sure a recent backup has been done. If not, just stop the script. Schedule that task to run between an the average end time of the backup jobs until a given a few hours later. This would ensure at some point it will find that the backups on all instances are current, then execute your code.

Related Solutions

SQL Server – Running PowerShell Script on Different Server

If you are within a domain environment you can setup a proxy account that has permissions on SQLBILON1. However, using remote PowerShell is a bit of overkill and adds an unneeded layer to troubleshoot. If you do not have remote PowerShell enabled on both servers and firewall access between the servers configured properly it will have issues.

I would simply use UNC paths to the server. Whether you use the admin share like \\SQLBILON1\H$\SQLBackups or create share directly to \\SQLBILON1\SQLBackups. Unless you want to give the SQL Agent service on MYSQLSERVER1 permissions to that backup directory you would need to create a proxy account that has the appropriate permissions.

Your script will be much more simply if you just go this route:

#-------------------------------------------------------------------------------
# Script to delete old SSAS backup files
#
# Marcelo Miorelli
# 
# 19-novembre-2014 Wed
#-------------------------------------------------------------------------------


#-- set the Path where the backup files (.abf) are located
#
$path = '\\SQLBILON1\H$\SQLBackups' # OR \\SQLBILON1\SQLBackups

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem $Path -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item

Sql-server – SQL Agent doesn’t have perms to run a PowerShell script

I had a similar problem when running powershell scripts from a sql server job.

If you do not have remote PowerShell enabled on both servers and firewall access between the servers configured properly it will have issues.

Run a PowerShell script on a different server - from inside a SQL Server job

the way I have solved this problem is through a proxy account.

I have created a proxy account in sql server, that had all the relevat permissions to the remote server, folders and files that I needed and run the sql job as that account.

a proxy account in my case was a domain account, and I have granted this account the privileges on the remote server.

this might help with proxy: How to get the AD account associated to a Proxy?

From this link below you can see:

powershell problem when running on a remote server - access is denied

As far as workarounds, there is this answer on SU. you can read the article in detail that the answer points to fully understand, but this is the main command to adjust permissions:

If you decide you want to allow others, what you do is run the command:

Set-PSSessionConfiguration -Name Microsoft.PowerShell -showSecurityDescriptorUI

Notice that this action could have a serious impact on your system so we ask you to confirm that you really want to do this.

Best Answer

Related Solutions

SQL Server – Running PowerShell Script on Different Server

Sql-server – SQL Agent doesn’t have perms to run a PowerShell script

Related Question