Enterprise SQL Job Activity Reporting

  • Craig Benson

    SSC Veteran

    Points: 253

    Comments posted to this topic are about the item Enterprise SQL Job Activity Reporting

  • dave-dj

    SSChampion

    Points: 11292

    Hi,

    This is a great idea and I've just done something similar, but more focused on producing server documentation of jobs, backup devices etc etc.

    What I'd like to suggest is a slightly different alternative to acheive the same result.

    I would create an SSRS report with a data source for each of your severs.

    Create a report a separate report for each server to view the job history of jobs on the server.

    Then using SSRS create a report which includes a sub report, point to each of the server reports.

    This would remove the need for SSIS and storing data in SQL (unless of course you need a bigger than you keep for your job history..

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • Craig Benson

    SSC Veteran

    Points: 253

    I see great minds think alike! I actually set out using a pure SSRS solution, but the report generation took too long because I have so many servers, so I opted to off-load the data gathering portion using a SSIS solution and a single table that gets truncated before each run.

    Nevertheless, for those out there that don't have too many servers, using a pure SSRS solution is definitely the way to go!

  • Duude

    SSC Rookie

    Points: 39

    Hi!

    Would you extend the article with the "howto" of the "For Each Loop Container".

    Thanks!!

  • Andy Warren

    SSC Guru

    Points: 119676

    I think it would be less maintenance to host the SSIS package on a single server, have it poll all the other servers, and then base the report on the compiled data.

  • Craig Benson

    SSC Veteran

    Points: 253

    I didn’t actually use the For Each Loop container, as I don’t connect to all my SQL Servers using Windows credentials.

    But, if you do, here’s how you would use one:

    1. In your SSIS package, you would need a VB script task that would itself have a For Each loop routine to iterate through all the SQL Servers on your network. All this routine would do is build a string list of all the SQL Server names.

    2. Save the string of server names to a SSIS variable.

    3. Create a For Each Loop container in the SSIS package and inside the loop iterate through the SSIS server name variable and update another SSIS variable that contains the connection string that is used by your OLE DB Source connection manager.

    4. Then just follow the article steps to run the SQL Script to save the output to the designated OLE destination database.

    Hope this helps!

  • Andy Leonard

    SSCrazy Eights

    Points: 9950

    Andy Warren (12/7/2009)


    I think it would be less maintenance to host the SSIS package on a single server, have it poll all the other servers, and then base the report on the compiled data.

    Good point Andy,

    I've built solutions like this as well, and I find architecture drives the distribution of the workload. My current environment, for example, has firewalls between Application Lifecycle tiers (Dev, Systems Integration, Production, etc.). In that case, I've opted to deploy a version of the tool to each tier individually - but if I wanted, I could pump CSV files around the enterprise and aggregate the information.

    Great article Craig!

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • Duude

    SSC Rookie

    Points: 39

    Thank you Craig.

    The only problem that I see in your good script, is to enable 'Ad Hoc Distributed Queries'.

  • Craig Benson

    SSC Veteran

    Points: 253

    Yes, there is a small security concern with having it continuously enabled. Optionally, you could enable it before the script and disable it after. Or, you could create a linked server before the script and drop it after. The cat, as they say, can be skinned a few different ways! 8^)

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    this is pretty good

    i just finished debugging a backup report system that uses SSRS to display the data and monitoring jobs is next on the list

    http://www.sqlservercentral.com/articles/SQL+Server+2005/61887/

    i found this article that i think i was going to base my system on, but i was going to customize it a lot. going to look at yours in more detail and probably take parts of each one.

    One shortcoming I see is that in the report there is no server column and there is no filtering for failed jobs that i would like to see first instead of looking through a lot of data.

  • iBar

    SSCertifiable

    Points: 5872

    Hello,

    Getting following error if i create an OLE DB soruce to remote DB

    OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

    Msg 2, Level 16, State 1, Line 0

    Named Pipes Provider: Could not open a connection to SQL Server [2].

    If i remove the OPENROWSET it works fine.

    Is there any alternate to OPENROWSET, which will eventually remove the problem of "Ad-Hoc Remote Queries"

    OR

    How to fix the above error ?

    Cheers

  • dave-dj

    SSChampion

    Points: 11292

    Check that you have

    a) remote connections enable for named pipes in is enabled in SQL Server Surface Area Manager > Surface Area Configuration for Services and Connections

    b) OPENROWSET is enabled in SQL Server Surface Area Manager > Surface Area Configuration & Features

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • smthembu

    Hall of Fame

    Points: 3451

    Great article Craig, just to acknowledge your work i also modified the reports to include a graph to get a quick glance before scanning the report to check for failed jobs.

    Tx once more:-)

    It's better to fail while trying, rather than fail without trying!!!

  • D-SQL

    SSCrazy

    Points: 2265

    This post is pretty old but I have a question i have the report all set up but Im having issues getting one of my reports to show up one server. I added one more server to the report but the in tables if I select still cant get the job activity. This is a instance sql server thats the only difference from the other 9 servers.

    I have 9 working and set up exaclty the same.

    Any help would be appreciated

  • D-SQL

    SSCrazy

    Points: 2265

    This is a great. I have a question now that I have this all set up I have added a SQL instance can you point me in the right direction in the SQL to use the instance ion the server?

    Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply