InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 03:11:16 GMT20http://www.sqlservercentral.com/Forums/Topic1425790-2799-1.aspxFist, thank you all for your responses and insight, this is very helpful.In my setup, the licensing is not an issue, but its something i should keep in mind for the future.It seems like if i were to do a separate SSIS, i should just include the DBE dedicated for it. I'm also testing a similar setup with SSRS and its own DBE.At this point i'm exploring what's possible and for now the goals are to see what i can separate out to be able to see how each component consumes resources. The other side of this is to allow the DB users/developers to access the system only via the proper tools such as SSMS and VS/BIDS. The split up may be overkill, but I'm certainly learning things :)Mon, 04 Mar 2013 09:21:44 GMTpolishpaulhttp://www.sqlservercentral.com/Forums/Topic1425790-2799-1.aspx[quote][b]Evil Kraig F (3/3/2013)[/b][hr]Unlike Jason, I work in a shop where we have a dedicated SSIS box. There are benefits, but they're not readily apparently. However, it's also not a scalable solution like you're looking for.Basically there's three reasons we run our SSIS on a separate box (well, vm instance, but same thing to us). First is memory control. SSIS runs outside the server cache and they'll compete. We keep a small engine on the same box simply for configuration controls and SQL Agent but that's it. Second is security. SQL Agent requires a large amount of rediculous control to create test jobs (to test network security for filepaths, doublehops, etc) and share them amongst a team (group ownership isn't allowed for a job yet)....[/quote]We actually run a separate instance for SSIS - it's just not like what the OP requested.I prefer the SSIS server for the same reasons you just laid out.Mon, 04 Mar 2013 07:05:50 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1425790-2799-1.aspxUnlike Jason, I work in a shop where we have a dedicated SSIS box. There are benefits, but they're not readily apparently. However, it's also not a scalable solution like you're looking for.Basically there's three reasons we run our SSIS on a separate box (well, vm instance, but same thing to us). First is memory control. SSIS runs outside the server cache and they'll compete. We keep a small engine on the same box simply for configuration controls and SQL Agent but that's it. Second is security. SQL Agent requires a large amount of rediculous control to create test jobs (to test network security for filepaths, doublehops, etc) and share them amongst a team (group ownership isn't allowed for a job yet).By splitting the SSIS box off to a separate server we've guarateed any memory pressure on the box is self-generated and won't detract from production data systems and that our job builds even on the dev boxes are well-maintained except for the SSIS sandbox.You'll notice I neglected the third reason. That's because it depends on where you work. If your workplace is organized and all SSIS packages are 'pushes' or 'pulls', you're good to go. But when they start to mix and match, or they're communicating both directions, it can get hard to keep track of them. A centralized box for ALL packages relieves the entire discussion of 'was there a package on server 8 that needed this database on server 5 that I'm about to adust the schema on?' 'Nope.' '9?' 'Erm, maybe?'Sun, 03 Mar 2013 01:32:28 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1425790-2799-1.aspxIn my experience scaling out SSIS in that manner isn't possible.In fact I have a [url=https://connect.microsoft.com/SQLServer/feedback/details/735429/decoupling-is-server-and-the-new-ssis-catalog-db-scale-out]Connect ticket[/url] suggesting this as a feature, and extending this concept further by connecting multiple SSIS servers to the same centralized SSISDB (SQL instance), achieving some form of centrally-controlled distributed data processing cluster.If your end goal is to simply kick-off an SSIS package (on Server 2) from a SQL Server Job Agent Job (on Server 1), you could potentially achieve this by having the SSIS package in the file system (along with any configuration files) on Server 2, and then executing the SSIS package through command-line using [url=http://technet.microsoft.com/en-gb/sysinternals/bb897553.aspx]PSExec[/url], which will be triggered from a Job on Server 1... You could probably achieve a remote command call using PowerShell too.Fri, 01 Mar 2013 19:13:15 GMTib.najihttp://www.sqlservercentral.com/Forums/Topic1425790-2799-1.aspxThat kind of setup would not provide any real benefit as far as SSIS is concerned. You would be paying for the licenses for an additional server, just to run the SSIS service.A more typical setup would be to store the packages in msdb on this SSIS server (you're paying for the license anyway) or to just store the packages on some network share. My preference is to store them in msdb on the SSIS server.edit: fixed spelling typoFri, 01 Mar 2013 18:51:42 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1425790-2799-1.aspxI've been working in a lab to see what is possible as far as separation of services in SQL Server 2012.I have this setup:Server 1: DB EngineServer 2: SSIS & SSASMy goal is to have the SSIS packages in a database on the main DB server (Server 1) and have the users access Server 2's SSIS via SSMS from their workstation. I'm finding that this is perhaps not possible. [url=http://www.sqlservercentral.com/Forums/Topic1373364-1550-1.aspx#bm1373371]As this forum post states:[/url]Which quotes [url=http://msdn.microsoft.com/en-us/library/aa337083(v=sql.110).aspx]this article from MS[/url]:[quote]Delegation Is Not SupportedSQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.[/quote]So it looks like i finally hit a brick wall. I'm wondering if there is any way to configure such a scenario (mostly for knowledge) or if that is even practical. I'd be curious to hear from experienced DBA's who can perhaps point me in the right direction. Thank you in advance.Fri, 01 Mar 2013 17:03:25 GMTpolishpaul