SSIS Catalog Agent - by Jamie Thomson

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


20
0
Sign in
to vote
ID 726102 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 2/22/2012 7:19:28 AM
Access Restriction Public

Description

This is an embryonic idea that I'd like to capture here for future discussions when the post-Denali release of SSIS is in the planning stages. It was prompted by a comment to my blog post:
Is the SSIS Catalog going to be enough? 
(http://sqlblog.com/blogs/jamie_thomson/archive/2012/02/22/is-the-ssis-catalog-going-to-be-enough.aspx)

That comment was:
"The problem with the new built in logging is the fact that it is not centralized. If you have 10 servers, you will have to visit each server to look at your ssis logs."


Putting aside the question of why on earth anyone would have 10 servers it does raise an interesting point.
Currently if we wanted all SSIS logging to occur centrally (i.e. on a single server) then all projects would need to be deployed to and executed on that SSIS server. In an organisation where they have many packages running all day long it is quite possible that that SSIS server could become a CPU/Memory/Network bottleneck - its not unfeasible that that organisation would want to scale out by having multiple SSIS servers however as soon as you do that you lose the benefit of centralised logging.

I propose a solution to this below.
Sign in to post a comment.
Posted by Mike Flakus on 6/25/2013 at 8:51 AM
We have the same issue with having a server specific SSISDB / Catalog. We spread SSIS 'work' across multiple servers that use a central database for Package Configurations, Logging, and Job Control. Regarding logging, if it's too hard to redirect logging directly to a central server, then SQL Replication or SSIS packages can be provided to move the data to the "SQL Server Management Data Warehouse (MDW)" similar to how SQL Server data is collected (http://msdn.microsoft.com/en-us/library/dd939169(v=SQL.100).aspx). For deployment, a 'Server Group' could be created with a central (and backup) server acting as the primary repository and the other servers SSISDB kept in sync during deployment activities. Ideally, this would be coupled with a load balancing job scheduling utility that could dynamically spread the SSIS 'work' across the servers using runtime information from prior runs to determine the resource profile of each package.
Posted by Darwin Fisk on 6/7/2013 at 2:39 PM
This is an important issue to our organization. I had been driving to a centralized model for managing configurations and logging on previous versions of SQL. This was easy to do using SSIS Frameworks around our packages. It appears that in SSIS 2012 we will have a difficult (I'm told impossible) using the framework apporach, but The SSISDB/Catalog does not allow central management. Please keep me updated as you develop ideas on how to centrally manage SSIS package storage, configuration, and logging in SQL 2012.
Posted by Koen Verbeeck on 9/4/2012 at 11:38 PM
Is there already more news on the whitepaper?
Features like these would make SSIS scale-outs a lot more efficient.
Posted by Microsoft on 3/13/2012 at 12:44 AM
We will be looking at centralizing logging from the angle of SSIS scale-out implementation. We are working on a white paper on this topic and will keep you all in the loop. In the meantime, we will close this thread for now.
Posted by Pedro Perfeito on 2/28/2012 at 1:03 PM
I'm completly agree that something should be done for SSIS logging under Project Deployment Model when we have more than one server and we need to centralize SSIS Catalog information. I hope this issue could not be a barrier to this new approach.
Pedro
Posted by Phil Brammer on 2/27/2012 at 11:04 AM
I think the larger discussion around this should be with the scale-out of the SSIS server architecture model. That is, how do I scale out my SSIS server components, while maintaining central logging and scheduling.
Posted by Jamie Thomson on 2/22/2012 at 8:30 AM
SSB=SQL Service Broker, right?
Posted by Allan Mitchell on 2/22/2012 at 7:55 AM
So long as the 2/3/4/n servers are not coupled. I would like to see a coordinated logging mechanism like this but have more of an SSB feel. The availability of the log server should not stop the execution of the packages. The logs can be delivered later (loosely coupled). Good idea Jamie. This should provoke discussion.