Sql-server – Collect results of many sql server jobs

sql server

I intend to create a simple job on a bunch (100+) sql servers (of differing flavors) that does a simple automated query periodically (like weekly). These jobs then need to push the result out to a central location. What is going to be a reliable, minimal config way to push those results?

I was thinking of converting the result set to text/xml and posting to a url. It seems that doing anything http from tsql is not simple, or standard. I think a CLR stored proc is probably my best option. I've been shying away from it because of the multi-step deployment.

(I also considered using a script that connects to every server and executes the query. I rejected this because of security concerns in introducing a shared account across all our servers.)

Can anyone suggest better options?

Thanks

Best Answer

I've created an SSIS package that connects to multiple servers and runs a query and copies that data to a central SQL server that I report against. Just took a while to get the query to contain all the information I needed but the SSIS package is pretty straight forward, so may be an easier option than txt/xml

Related Solutions

Sql-server – Collect performance information on high CPU condition in SQL Server

I think we might need to dip into sys.dm_os_ring_buffers and sys.dm_os_sys_info to get the CPU utilization data.

Please find below the script of Glenn Berry

-- Recent CPU Utilization History (SQL 2008/2008 R2 Only)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 

SELECT TOP(144) SQLProcessUtilization AS [SQLServerProcessCPUUtilization], 
               SystemIdle AS [SystemIdleProcess], 
               100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPUUtilization], 
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime] 
FROM ( 
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
            AS [SystemIdle], 
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 
            'int') 
            AS [SQLProcessUtilization], [timestamp] 
      FROM ( 
            SELECT [timestamp], CONVERT(xml, record) AS [record] 
            FROM sys.dm_os_ring_buffers 
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
            AND record LIKE '%<SystemHealth>%') AS x 
      ) AS y 
ORDER BY record_id DESC;

Source : Glenn Berry's Script

Sql-server – History from SQL Agent Jobs (SQL Server 2005)

You need msdb.dbo.sysjobhistory and a few JOINs of course to read the data

To change how SQL Server Agent uses this table, use this stored procedure with suitable values

EXEC msdb.dbo.sp_set_sqlagent_properties
    @jobhistory_max_rows = 1000,
    @jobhistory_max_rows_per_job = 100

Best Answer

Related Solutions

Sql-server – Collect performance information on high CPU condition in SQL Server

Sql-server – History from SQL Agent Jobs (SQL Server 2005)

Related Question