July 14, 2014 at 12:19 pm
New to SSIS. Looking for direction, white papers, articles that discuss pros and cons, best practices to installing SSIS stand-alone vs. with user databases. Wanting to use new project deployment model and not sure what install location best supports it.
Jacki
July 15, 2014 at 3:37 pm
Are you considering installing SSIS on one server and SQL Server engine on another? First point to consider is that this will require a separate SQL Server license in addition to the h/w.
While we would not want to run SSIS on our busiest OLTP server, we also don't do enough (or intensive enough) SSIS to justify purchasing another license. I do know of some big shops that do this, though...do you anticipate a really serious SSIS workload?
We have SSIS running on an FCI (for HA), along with staging and some other smaller DBs, works ok. We're using the project deployment model, and store pkgs in the SSISDB database. All seems to work pretty well, nice improvement from SQL 2005. Seems relatively easy to migrate pkgs from dev-qa-uat-prod environments. We like it. We made this decision about 2 years ago, haven't looked back. Our SSIS pkgs run fast enough and do not adversely impact on the SQL engine.
Googled for "separate ssis server" and got a few hits, including some who claim separation is best practice but do not address the licensing; that makes me skeptical. is for SQL 2005 but I think logic still holds--the idea makes sense if you really need it, but you're going to have to pay for it.
July 15, 2014 at 3:49 pm
My plan was to install a default instance of SQL Server to manage the SSISDB database and run SSIS packages from there. We are also planning on using the new Project Deployment model. We are currently developing a new application that will be loading data into an operational data store throughout the work day and I wanted to keep the ETL workload away from other databases. I am also looking to build some consistency across our environments to improve upon the dev-qa-uat-prod migration path. We are not concerned about licensing this additional instance.
I too struggled to find anyone discussing running SSIS on its own server. I appreciate your response.
Jacki
July 15, 2014 at 3:52 pm
I'm a proponent of the centralized SSIS server, but mostly because of organization and not having to worry about if you found all the pull subscribers out there. If you can make sure your organization only does PUSH packages, you have a fighting chance to not blow up production with every schema change.
Anyway, the cost is there for the extra server. It's useful as a secondary staging location and whatnot to keep weight off the primaries. You don't need a full Enterprise version on the SSIS server however, Standard works quite well and will keep costs reasonable.
It really depends on load. Our primary servers are kept at near 100% utilization and can't take the load of our ETL processing as well. Since we're using a separate server for that anyway, it's our policy to use it for ALL SSIS packages that aren't Maintenance (IE: Reindexes, backups, etc... those live on their local servers so there's no dependency on a separate box for maintenance tasks). We keep our configuration database, a staging structure, our RAW directories, etc in one place too. It's very useful for organization purposes.
The result comes down to a balance of ROI for the extra license(s). In our case, it's more than worth it. We bought it for silver and it pays off in gold. I would examine any environment in depth before I'd recommend it there though.
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
July 15, 2014 at 7:43 pm
I've implemented a centralised SSIS hub. Its a VM and works well licence wise if you VM host is licenced correctly. Also, allows for better version control of all your packages. Allows you install multiple third party components to connect to multiple sources e.g. Oracle client software, sharepoint list adapters, nsoftware components.
Having a central hub allows you to standardise and control your environment and prevents the need for outages on production apps database boxes in relation to making changes to the SSIS hub.
If you dont go down the centralised route and you have multiple SQL instances with multiple SSIS pkg's deployed it is far harder to manage and support.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply