Whitepaper : 3 Tips for Managing Large Numbers of SQL Server Jobs

Managing a large number of SQL Server jobs across a large enterprise environment can be difficult and frustrating. Database administrators are often asked to schedule jobs for a variety of SQL processes and SQL Server Integration Services (SSIS) packages with little or no insight into what the processes or packages will be doing. Scheduling packages blindly can often yield performance problems from jobs that try to use the same resources or access the same data structures at the same time. Job overlap can be difficult to find and can cause a lot of problems.

This whitepaper shares the author’s three top tips for managing a large number of SQL jobs and SSIS packages. These tips will make administering the jobs easier and prevent common problems that are inherent in a large SQL job environment. (1) Centralize the SQL job servers onto dedicated servers. (2) Utilizing the new SSIS catalog in SQL 2012 and newer. (3) Use a third party tool that can give you the big picture and let you look at your SQL job environment as a whole. These tips will make administering a large number of jobs easier, alleviate some common resource issues with executing many SQL jobs, simply troubleshooting SSIS package failures, and bring the problems in your environment into a single view.

If you have a lot of SQL jobs and SSIS packages that you have to manage, you have a difficult task. The tips provided in this white paper will help ease your burden by simplifying the administration of the jobs and packages.

Using centralized job servers will bring your servers into focus while alleviating a lot of the persistent performance issues that accompany the execution of SQL jobs and SSIS packages on production servers. It also eases the configuration and administrations of many availability and disaster recovery scenarios.

The SSIS catalog in SQL Server 2012 and newer provides some new features that make administering and troubleshooting SSIS pages easier. This tool automatically performs extensive logging of the internal steps of an SSIS package that can tell you exactly what failed and why. There is no longer a need to rig custom logging or step through a package to troubleshoot package failures.

Lastly, look at the big picture. Use one of the tools available for managing and viewing jobs across your enterprise, like one of Idera’s job management tools. Whether one chooses to go with a free option or a paid option, one will undoubtedly get a tremendous amount of value out of these tools and be able to quickly identify and resolve SQL job collisions.