March 1, 2013 at 5:03 pm
I'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 Engine
Server 2: SSIS & SSAS
My 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. As this forum post states:
Which quotes this article from MS:
Delegation Is Not Supported
SQL 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.
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.
March 1, 2013 at 6:51 pm
That 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 typo
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 1, 2013 at 7:13 pm
In my experience scaling out SSIS in that manner isn't possible.
In fact I have a Connect ticket 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 PSExec, which will be triggered from a Job on Server 1... You could probably achieve a remote command call using PowerShell too.
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
March 3, 2013 at 1:32 am
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).
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?'
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 4, 2013 at 7:05 am
Evil Kraig F (3/3/2013)
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).
...
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.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 4, 2013 at 9:21 am
Fist, 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 🙂
August 22, 2013 at 5:36 am
Hi Jason,
You mentioned below usage
"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."
I have 5 packages in which 3 packages are inside 1 package and other package is a stand alone package. Currently we are running it in single SQL server ( for both database and SSIS). We are in server upgrade and have 2 servers now ( development and testing phase now)-one for SSIS and another for database. we deployed the packages into msdb on new SSIS server. when i tried to run( open msdb in SSIS server and right click on package name and run) the package it failed in the step where it calls a child package. Then i opened the package in BIDS and opened the execute package task for the child package. I opened the task and checked the package location under SQL Server option and I couldnt find any packages under MSDB.( In current environment where we have only one server so we were able to select the packages under MSDB without giving server details). Then I created a new connection for SSIS server and tried to get the package but getting error. Then I imported the package to MSDB under Database server and I was able to select package under MSDB of Database server for child package location. That means for selecting a package location for a child package under SQL Server option (other option is file system which we are not using) we need to import the package in to MSDB under Database server? Then what is the use of MSDB under SSIS server other than running the packages which are also needed to be deployed under MSDB of database server. Please help. Because of this error we are thinking of having only one server for both SSIS and database again.
Thanks
Ajeesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply