August 13, 2015 at 2:46 am
Hi there,
I have two servers. One that holds the DWH-databases and one that is executing the ETLs (including scheduling).
The ETL-Server is executing ETLs all day and we have usually a maintenance window in the evening (22h - 24h).
However I want to be more flexible about the maintenance (e.g. in case of an urgent update) but do not want to shut down all running ETLs (brute force) as they may be doing an updating, inserting that will be hard to restart at that particular point and leave a inconsistency.
I was thinking about this solution:
Every scheduled Agent Job checks in the first step if "maintenance mode" is active and will stop its further execution (e.g. by rising an error)
This maintenance mode will be a flag in a table that can be pulled by a stored procedure from the agent job.
This means already running jobs can finish and no new job will be startet and through the raised errors I see which job I will have to restart after the mainenance (+ Jobs that where scheduled in the downtime).
By doing this I have clean end of jobs and do not have to fix jobs after mainenance.
Before I implement this, how did you solve your ability for maintenance or is there e.g. already something in SQL Server that would exactly do what I want to build?
Cheers,
Christian
August 14, 2015 at 2:46 am
Hi,
Well, there is the "CPU idle condition" configuration in the SQL Server Agent service configuration but it is almost useless. An interesting option that could suits you is enabling or disabling the schedule objects associated to your maintenance tasks and with the job step retry options.
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply