March 24, 2016 at 10:38 am
We have a data warehouse SQL server and it has a lot of ETL SSIS running nightly on the server .
Currently it has 8 cpu with 16 hyperthread, 96 GB RAM.
Even this some nights the ETL runs use a lot CPU and crash the server.
My question is we are going to upgrade the servers from SQL 2008 to 2014.
Since the SSIS is heavily used on the server, should we install a dedicated server just for SSIS, or still keep the SSIS service on the same server as the data warehouse databases?
One concern to separate db server from SSIS is that I notice if destination db is on local, the import will be much faster. Otherwise if source and destination neither is not in local server, since they cross the network, it is much slower. This could be problem to use a dedicated server just for SSIS.
Any advice, thanks
March 24, 2016 at 11:12 am
sqlfriends (3/24/2016)
We have a data warehouse SQL server and it has a lot of ETL SSIS running nightly on the server .Currently it has 8 cpu with 16 hyperthread, 96 GB RAM.
Even this some nights the ETL runs use a lot CPU and crash the server.
My question is we are going to upgrade the servers from SQL 2008 to 2014.
Since the SSIS is heavily used on the server, should we install a dedicated server just for SSIS, or still keep the SSIS service on the same server as the data warehouse databases?
One concern to separate db server from SSIS is that I notice if destination db is on local, the import will be much faster. Otherwise if source and destination neither is not in local server, since they cross the network, it is much slower. This could be problem to use a dedicated server just for SSIS.
Any advice, thanks
Your concerns about bandwidth are valid & you should do your best to make sure that connectivity is the best it can be. Perhaps you could have both servers virtualized on the same physical box to avoid unnecessary cross-network traffic?
Last time I checked, an installation of SSIS on a separate server required a full SQL Server licence, so if cost is an issue ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 24, 2016 at 1:47 pm
This may be of interest to you:
SSIS 2012 standalone on server
If you put SSIS on the same box as your SQL Service, make sure you know the memory requirements and configure memory so that SQL and SSIS aren't stepping on each other.
March 24, 2016 at 2:27 pm
Still not clear which way I should go?
We can buy another license. just not sure what is the best option to go:
And for now we consider all use physical servers not virtual:
1. Install both data warehouse database and ssisCatalog database , and SSIS service on the same machine
Set max memory appropriate for SQL servers.
2. Install data warehouse database on one server.
and install SSIS and SSIScatalog database on the other server. This allows SSIS uses more dedicated sources, but again it still runs SQL server and also ETL either datasource or data destination no longer on the same server, it will cross network, may actually slow down ETL.
Summary, it looks still good to select Option one, plus saving one license.
March 24, 2016 at 2:31 pm
We moved most of our SSIS packages over to our BI server mainly because we are running on AG's now and it makes managing the jobs a bit easier.
March 24, 2016 at 2:33 pm
When you say BI server, does that mean it hosts all the data warehouse databases?
March 25, 2016 at 7:25 am
sqlfriends (3/24/2016)
When you say BI server, does that mean it hosts all the data warehouse databases?
Yes, all the cubes, along with the SSRS stuff and now SSIS stuff.
March 25, 2016 at 8:11 am
sqlfriends (3/24/2016)
Even this some nights the ETL runs use a lot CPU and crash the server.
It's likely NOT going to help to move to separate server if that's what's going on. You need to fix the ETL code. There's no reason why it should ever crash anything.
And, don't forget what Phil said above... a separate server with SSIS is going to require separate licenses for SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2016 at 8:38 am
At least from my experience, it's unusual for an ETL process, or even several concurrent ETL processes, to max out CPU. It's typically I/O, network, or memory that get's bottlenecked, if anything. I would suggest digging into the root cause of what specific task or query is eating CPU and address. Maybe even consider scheduling some of these more complex processes so they don't overlap concurrently. You may find that the total load time is the same or even less when they run serially.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 25, 2016 at 9:47 am
Thanks for all the tips, they are helpful
March 28, 2016 at 7:48 pm
Manic Star (3/24/2016)
We moved most of our SSIS packages over to our BI server mainly because we are running on AG's now and it makes managing the jobs a bit easier.
I did the same thing.
Do not put SSIS on the same server as the SQL Server Engine unless you have a slight load. Way to much memory. It causes serious performance issues.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 28, 2016 at 7:54 pm
poste removed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 29, 2016 at 6:40 am
I vote for having SSIS on its own server. This post[/url] outlines some reasons (be sure to read the comments, too), and although it's primarily targeted at SQL Agent, the argument is really for application logic-type jobs to be separated off from the database server.
We run SSIS, SSRS, and the databases each on their own servers. This setup has been helpful with monitoring and resource allocation, as well as general management. Granted, we done have hundreds or even tens of databases or servers, but I would think that if you are responsible for that number, separation of duties would be even more useful.
March 29, 2016 at 10:16 am
Just as mentioned, you would need to look at the root cause for the CPU peaking.
Monitor the workloads. If all your BI cubes et al are built after the one time SSIS job, then you can have the SSIS on the SSAS server. But if this is SSIS job this is called at regular intervals many time a day, its is going to contest with resources on both your OLTP and SSAS Server. In that scenario, its better to have a separate SSIS server.
March 29, 2016 at 3:11 pm
I will put CPU thing on another research.
But for implementing and upgrading to new environment, just want to know to put ssis on a separate server or on the ETL destination server.
We only have nightly jobs that runs.
And the destination is all in the data warehouse databases tables.
If SSIS has its own server:
I know SQL 2014 has SSIScatalog database, and I read from previous post, it has to be on the same server of SSIS service, that means we do need to install database engine on the same server of SSIS, even it only hosts SSIScatlog database.
This again will also compete resources with SSIS.
The second is if on same server at least either source or destination will on the same server, if put on its own server, both destination and source will not be local, that I found slow for it is cross network.
Currently we put on the same server, and the RAM is already 94GB.
Thanks,
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply