Tidal Workload Automation Adapter for Microsoft SQL Server

Adapter Overview

Adding SQL Server Processes into Tidal Workload Automation Workflows

When businesses require an enterprise-wide, heterogeneous automated job scheduling solution that incorporates SQL Server workflows, Workload Automation, combined with Tidal Workload Automation Adapter for Microsoft SQL Server, is the right choice. This powerful combination brings the reliability and flexibility of Tidal’s Workload Automation enterprise workflow automation solution to this industry-standard database.

The adapter enables scheduling automation of new or existing SQL Server database job or job steps to be added to an enterprise-wide scheduling environment. SQL Server jobs can now be integrated into a larger business process workflow environment managed through a single pane of glass. Database dependencies that affect job steps can now be managed across the entire enterprise, and IT staff can quickly and easily pass variables to and from SQL Server database jobs. As a result, application and database automation and management can be consolidated in one solution, reducing staff effort and expense, while increasing productivity.

How Tidal Workload Automation Adapter for SQL Server Works

The adapter offers advanced scheduling and monitoring capabilities without scripting, manual intervention, or changes to SQL Server configuration parameters. Once the adapter is configured, SQL Server jobs are defined and maintained through the Workload Automation web user interface. This heterogeneous automation solution provides detailed control of SQL Server jobs through extensive alerts about job abnormalities, resource availability, load balancing, and machine functions. It also provides real-time status information from a single console and gives IT operations staff the ability to interact with current and future jobs.

Features and Benefits

The adapter allows SQL Server jobs to be handled through the Tidal Workload Automation user interface. Users can access SQL Server job information and build workflows through a visual interface that allows them to define dependencies among SQL Server jobs alone or SQL Server jobs and jobs running on other systems and applications

Important scheduling capabilities for SQL Server users include:

  • Database Job Definition and Scheduling – The adapter enables three types of SQL Server jobs to be defined in Workload Automation:
  • Direct SQL Jobs – Makes SQL Server calls using T-SQL statements
  • Database Jobs – Includes one or more steps of various database task types, including the following:
    • ActiveX

    • Operating system command

    • Replication distributor, merge, queue reader, snapshot, and transaction log reader

    • SQL Server analysis services command and analysis services query

  • Existing Database Jobs – Adds, imports, or links to jobs already defined to run under the SQL Server agent
  • Detailed Job Monitoring and Controls – Once database jobs are defined and scheduled, the solution provides real- time status information and gives IT staff the ability to interact with current and future jobs. Users can access detailed job step controls—hold job instances, abort, delete, re-run, and make ad hoc changes to a job instance before it has run. In addition, IT staff has constant communication with SQL Server workflows through the use of extensive alert capabilities which can be defined to send detailed messages regarding job abnormalities, resource availability, load balancing, and machine functions. In addition, Tidal Workload Automation offers different options for displaying job output, including aligned columns, CSV, raw, and XML. Users can also re-direct the output to a local file or log it to a database table.
  • Defining and Running SQL Server Events – Tidal Workload Automation Adapter for SQL Server simplifies processing database events and does not require scripting or managing database triggers. The adapter gives users the ability to capture and respond to database activities, such as table modification, index creation, or database row activity. And Workload Automation permits views of all run time database events, which users can then leverage to create events- based job dependencies quickly and efficiently.

The adapter can easily pass variables from Tidal Workload Automation to any SQL Server database job, pass job information across the entire batch job scheduling environment, and pass completed SQL Server jobs to other downstream workflow operations, such as extract, transform, and load (ETL) report creation. In addition, specific events—the arrival of a file from a trading partner, for example—can be used to initiate a new SQL Server action or job step automatically. Users can also assign alert actions to an event, making it possible for administrators to know when that event has been triggered. Users can assign alerts to the database user who triggered an event, the database object that triggered it, and the output of the event selected.

Features Summary

  • Links SQL Server with other RDMBS and business applications and consolidates the automation of those jobs through a single console
  • Provides management functions that eliminate scripting, thereby allowing unlimited dependencies, integrated workflows, and scalability
  • Creates, schedules, and runs T-SQL queries and SQL Server jobs and monitors events without requiring database triggers
  • Defines SQL Server jobs or imports existing jobs and runs them across multiple SQL Server instances
  • Supports jobs that include steps in various database tasks, including ActiveX scripts, OS commands, and direct inclusion of T-SQL statements
  • Monitors Table Create, Modify, and Delete; Index Create, Modify, and Delete; Row Add, Modify, and Delete
  • Creates a pre-processor step for any SQL Server job that supports passing parameter values, such as SQL selection criteria, to a job before running it
  • Launches SQL Server jobs based on external events, such as the arrival of a file
  • Generates output of T-SQL queries in XML and other formats without additional coding
  • Provides Workload Automation calendaring features with more extensive options than those offered by Microsoft tools

Benefits Summary

  • Leverages an investment in SQL Server database to create a comprehensive business automation solution
  • Supports greater efficiency and productivity through a single enterprise-wide scheduling automation environment
  • Reduces the margin for error and enables better resource utilization through fewer steps
  • Enables SLAs to be met faster, by providing access to pre- tested steps
  • Eliminates expensive script generation and ongoing script management by supporting extensive calls
  • Reduces manual intervention through alert and even trigger automation
  • Save time with automated out-of-the-box processing steps
  • Create an enterprise-wide workflow solution through upstream and downstream job handoffs
  • Helps administrators correct errors quickly and resolve job step bottlenecks via historic logs
  • Aligns workflows to user demands without expensive manual calendar manipulations

Tidal Workload Automation Adapter for Microsoft SQL Server helps IT staff deliver maximum database job throughput, rock-solid reliability, and comprehensive coordination of database activities as part of an enterprise- wide business process automation solution. This powerful solution accomplishes these objectives by providing a single integrated enterprise job scheduler that balances the service requirements of batch or on demand workloads, whether they were created from within SQL Server or exist in support of other applications and technologies across the enterprise.