Running ETL on AlwaysOn Primary Replica
Scenario: Consider a SQL Server configured for AlwaysOn Availability Groups where a set of databases can fail over to another node. You have SSIS jobs and you created them on both nodes. However, you need to check if the current node on which the job is running is the primary node. If you don’t do this, the job fails because the database isn’t accessible.
Solution: In every SQL Server Agent Job, add a first step to check if one of the replicated databases in the AlwaysOn availability group is the primary replica:
IF
sys.fn_hadr_is_primary_replica
(‘<replicated dataset name here>’)
= 0
BEGIN
EXEC msdb..sp_stop_job
N’DW Daily Load’;
END
This script checks if the database is a primary replica. If this is not the case, the script stops the next step, which in this case is the “DW Daily Load” step.
