Last Day to Sign Up for SSIS and SQL Replication Online Training
Today is the last day to sign up for the SSIS and the Replication courses taught by Eric Johson, MVP. Your registration grants you access to the 12 courses (six on SSIS and six on Replication) for the entire month of April. Sign up today.
Windows Schduling Using Parallel Schedules
Wrapping up this topic today I wanted to share a simple technique allowing multiple scheduled tasks to work in a cooperative fashion.
Scheduled tasks through Windows Scheduler or SQL Agent don’t have native mechanisms to communicate with each other. If you need tasks to run in parallel and be aware of each other you can use a semaphore in the form of a file.
Simply create a file in a directory accessible to the jobs you wish to integrate. There are lots of ways to confirm the existence of the file, or create the file. Even from TSQL you can view or create a file through master..xp_cmdshell. It isn’t necessary to see the contents of the file. The existence of the file acts as a semaphore communicating some condition of import between two or more jobs. From TSQL executing a dos DIR command or ECHO command you can create a file or retrieve the contents of a directory. Be sure the account on which you run your SQL Agent service has access to the necessary directory.
In TSQL you may choose to have a task sleep by using the TSQL command WAITFOR, and then loop checking for the file existence until it arrives.
A more flexible approach is the use of SSIS packages or PowerShell scripts initiated from either SQL Agent or Windows Scheduler. In these tools you have a richer set of options to view the status from other tasks, including the use of a file based semaphore. SSIS even has a file watcher capability among other things.
Here’s an example of how this might work. I have a backup task that runs on each of 20 databases beginning at 20:00. I have a database defrag task I also run in parallel. The defragment task loops, waiting for notification until a database backup completes. Once a database backup completes the defragment job runs on that database, then sleeps again until another database backup is finished. A semaphore file works nicely. After each individual database backup is completed you execute a command to create a file as a semaphore. This allows both jobs to run concurrently and yet the defragment job will wait until each backup job completes individually.
Happily there are better solutions. I was pleased when I received an Email from Steve Wright at SQL Sentry. This is the kind of problem SQL Sentry handles with ease. Even if all you had was SQL Agent operating on a single machine, it is still difficult to get an overview of what is going on when you have dozens of jobs that run at different times with different repeating schedules.
Steve writes:
I see you’re writing some editorials about scheduling between SQL Agent and Windows Task Scheduler. I’m sure you remember that’s the primary feature set of SQL Sentry Event Manager.
In fact we have a document that specifically addresses why it’s so important to monitor Task Scheduler on your SQL Server machines even if you don’t actively use Task Scheduler due to the built in dependencies that have increased dramatically since Windows Server 2008/Vista. http://www.sqlsentry.net/files/why-watch-windows-task-scheduler.pdf.
Event Manager provides DBAs with "Outlook-style" visibility and functionality for managing SQL Agent jobs, Windows Tasks, and Oracle jobs in increasingly complex cross-platform environments.
Event Manager does not replace SQL Server Agent, or Task Scheduler. In fact, that is a key differentiation between Event Manager and most other scheduling products. With Event Manager there are no agents to install and manage on each server. Event Manager provides a visual display of jobs along with these other events across the enterprise so that the DBA can “see” how they relate to one another and optimize the schedules more efficiently.
Event Manager includes visual schedule management, schedule performance monitoring, alerting and response actions, graphical reporting, and advanced chaining and queuing for these events. The calendar view provides a clear picture of event schedules via an Outlook-style GUI giving unprecedented visibility and control over scheduling issues that can impact server performance.
Do you have any other tips, techniques or solutions you’d like to share with us? Be sure to drop me a note on Facebook, Twitter, or e-mail at btaylor@SSWUG.org.
Cheers,
Ben
Did you Miss SelecTViews with Kevin Kline and Denny Cherry?
Special Edition: If you missed this show earlier, this is a must see. Kevin and Denny cover a wide range of things SQL from optimizing disk performance to Disaster Recovery in light of natural disasters.This wide ranging interview covers tips, experiences…Getting involved with the community, learning SQL, getting involved and so much more.
[Watch the Show] (it’s free)
$$SWYNK$$
Featured Article(s)
Change Data Capture in SQL Server 2008 (Part 1 of 3)
Change Data Capture, or CDC, is a new feature of SQL Server 2008 that allows for incremental changes to a source or base table to be captured and retrieved from staging tables in the database. CDC can help improve incremental data loads, as well as be used for some data tracking. This session will cover how to enable and configure CDC, retrieve data from CDC tables, and will include an example of how to use CDC in the real world.
Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)
Featured Script
Foriegn Key Sequence Report
To list tables in the order you can populate or delete from them without violating Foriegn Key constraints. Sort the #tSeq… (read more)