Triggers in SQL Server are a powerful feature that allows you to automate the execution of pipelines or tasks. Whether you are coming from an SSIS background or new to SQL Server, understanding how triggers behave in different scenarios is crucial for successful implementation.

Schedule Triggers

Schedule triggers in SQL Server are similar to creating a SQL Server Agent job. You can specify the properties such as frequency and start time to schedule the execution of a pipeline. However, it is important to note what happens when the execution duration overlaps with the schedule.

For example, let’s say you have a pipeline that ingests data from an on-premise SQL Server to Azure Data Lake Store (ADLS) every hour. If the ingestion process completes within an hour, the trigger will start the next execution as per the schedule. However, if the ingestion duration exceeds an hour, you may encounter issues.

In some cases, you may notice that two instances of the same pipeline are running in parallel, ingesting the same set of tables. This can lead to resource level locks and failures, as both instances are trying to create a file with the same name at the same location.

Tumbling Window Triggers

Tumbling window triggers in SQL Server provide a different approach to scheduling pipeline executions. These triggers allow you to set dependencies and control the execution based on the status of another trigger or itself.

When using a tumbling window trigger, you can specify the recurrence of the execution, such as once an hour. By setting the “Add dependencies” property, you can make the trigger execution dependent on the status of another trigger or itself.

With tumbling window triggers, you can avoid parallel execution of the same pipeline and prevent resource deadlocks. However, it is important to note that if one of the executions fails, the next execution will not start until the root cause of the failure is fixed.

Additionally, it is worth mentioning that tumbling window triggers can accumulate execution windows in a queue. This means that even if the trigger is set to have an end date, the accumulated executions may go beyond this date if there are failures in the pipeline.

Conclusion

Understanding how triggers behave in SQL Server is essential for successful pipeline automation. Schedule triggers provide a straightforward way to schedule pipeline executions, but you need to be aware of potential issues when the execution duration overlaps with the schedule.

Tumbling window triggers offer more control and flexibility, allowing you to set dependencies and avoid parallel execution. However, it is important to monitor and address any failures to ensure the smooth execution of your pipelines.

By familiarizing yourself with the behavior of triggers in SQL Server, you can optimize your pipeline automation and ensure the efficient processing of data.