January 18, 2011 at 1:06 pm
In general should SSIS sit on its own server seperate from the database (which is SQL Server) in a large DW production installation? I am not the DBA but I am the developer but I was asked this question on a project I am working on. In my experience I haven't ever done this the SSIS has sat on same server as DB but not sure if there would be some gains from separating these things out on seperate servers.
January 18, 2011 at 1:19 pm
I have seen a couple installations where SSIS was separated but in general they are run together. About the only compelling reason I could think of is if you wanted to centralize the SSIS function onto a single server.
From my perspective I would run them together..
CEWII
January 18, 2011 at 1:19 pm
Thats kind of a question that only someone who knew the environment would be able to answer fully.
That being said, a good way to see for yourself is to use perfmon. Perfmon has counters for both SSIS and the SQL Server. Collect as much data as you can from perfmon, including buffer/memory, cpu, and most imporantly IO throughput with perfmon. If you identify a performance bottleneck that could be resolved by moving SSIS to its own box (typically CPU or Memory), then you might gain from moving it to its own box.
But really it all depends. If you are moving a large amount of data around you might introduce network IO as a new bottleneck by moving SSIS to its own box. Take metrics using perfmon, identify the bottleneck, and post back with more data about the environment and we might be able to steer you in the right direction.
Edit: SSIS can use ALOT of memory...
January 18, 2011 at 1:56 pm
Currently working with a centralized SSIS server. It's rather nice actually.
A) Don't need full sa/rights to create/deal with jobs on the SSIS server on each independent server (we have heavy developer lockdown here).
B) Organizationally makes things easier, no guessing as to if you're dealing with a push or pull of data.
C) Local tasks on servers are maintenance specific. If a server other then our SSIS servers pukes up an error, it's a lot easier to see than when jobs without sev1 priority and maintenance are mixed.
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
January 19, 2011 at 4:43 pm
They usually go hand in hand. Centralized SSIS doesn't help you in a significant manner.
Thank You,
Best Regards,
SQLBuddy
January 21, 2011 at 12:22 pm
Thanks for all your input much appreciated.
January 21, 2011 at 12:52 pm
We have been using centralized servers for DTS and SSIS for several years for packages that mainly produce scheduled customer reporting deliverables like spreadsheets.
A big advantage is that it allows us to install specific software that is needed in one spot without having to install it on database servers.
January 21, 2011 at 1:28 pm
Michael Valentine Jones (1/21/2011)
We have been using centralized servers for DTS and SSIS for several years for packages that mainly produce scheduled customer reporting deliverables like spreadsheets.A big advantage is that it allows us to install specific software that is needed in one spot without having to install it on database servers.
Curious - how to you manage scheduling and running the SSIS packages? I am assuming that you do not install the database engine on that machine and are using the file system to store the packages.
I would also caution anyone who does this - make sure you are appropriately licensed. SSIS would still have to be fully licensed just the same as an instance of SQL Server (per processor, Server CAL, User CAL, etc...).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 21, 2011 at 1:37 pm
I have worked with SSIS both ways. I like having the centralized SSIS server - dedicated resources. Plus with some security setups, having a centralized SSIS server can be helpful when separated from the database (e.g. should your SSIS package need to access the internet or ftp for instance). In such a scenario I would prefer to have an extra layer between the db and the outside which a dedicated SSIS server can be.
Many shops, it makes little sense to split the two.
One concern is the extra licensing if you do split it 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
January 21, 2011 at 1:39 pm
Jeffrey Williams-493691 (1/21/2011)
Michael Valentine Jones (1/21/2011)
We have been using centralized servers for DTS and SSIS for several years for packages that mainly produce scheduled customer reporting deliverables like spreadsheets.A big advantage is that it allows us to install specific software that is needed in one spot without having to install it on database servers.
Curious - how to you manage scheduling and running the SSIS packages? I am assuming that you do not install the database engine on that machine and are using the file system to store the packages.
I would also caution anyone who does this - make sure you are appropriately licensed. SSIS would still have to be fully licensed just the same as an instance of SQL Server (per processor, Server CAL, User CAL, etc...).
We actually do use a full install of SQL Server on that box, including a configurations database for storage and if we need temp staging we can do that local to that box or in the target structure. It helps to keep some of the load off our production servers, especially at the CPU level when we're doing a lot of stream manipulation.
So, it's fully licensed for us. You'd need to be a large enough shop that an extra 3 SQL servers (dev/qa/prod) aren't going to painfully abuse your bottom line.
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
January 21, 2011 at 5:01 pm
Craig Farrell (1/21/2011)
We actually do use a full install of SQL Server on that box, including a configurations database for storage and if we need temp staging we can do that local to that box or in the target structure. It helps to keep some of the load off our production servers, especially at the CPU level when we're doing a lot of stream manipulation.So, it's fully licensed for us. You'd need to be a large enough shop that an extra 3 SQL servers (dev/qa/prod) aren't going to painfully abuse your bottom line.
That makes sense - and would be the way I would set up and integration system. I don't have a lot of SSIS/Integration processes - but I do have a couple. Those sit on a separate reporting system and also have the reporting databases.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 24, 2011 at 4:26 am
Another advantage is in companies which have dedicated teams for managing SSIS packages and database engine. In these cases, if we have a separate instance for SSIS packages then the relevant team can manage their packages without infringing on the database side. They could use proxy accounts etc.
M&M
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply