Hello,
We are in process to decide weather SSIS on separate dedicated server v/s on the same database server.
I would prefer to have on dedicated server but also curious to know that it will be a network traffic will be involved which might slows the process back n forth.
If i go with dedicated server for SSIS then what kind of server configuration you are suggesting like CPU, MEMORY, DISK SPACE.
We have few SSIS packages and our databases are not that big which also resides on various instances.
We are right now confuse with dedicated v/s on the sane database server and what will be the new recommended server specification we should order?
We created a Jobs server to run the SSIS packages on it. We have integration services and SQLCMD installed on it, but not the engine. We didn't want to take away resources from Prod server with the SSIS packages. We have Prod and Non-Prod jobs servers. The Non-prod has 8Gb memory, 350Gb of space. and 4 CPUs. We do write the packages to clean up after themselves after certain number of days.
Thanks Mike for your quick reply.
We have also separate Job schedule server to run the SSIS package as normally we don't run through Sql Agent directly.
We have to install sql engine, right? not only Integrations SSISDB only?
Integration Services uses its own pool of memory retrieved from available memory at runtime so SSIS packages required more memory so 8 GB is enough?
Do you think that 8 CPU core is better to run so processes can run in parallel?
We opted to go with a single what we termed our BI server where we have reporting server, ssis and ssas, datawarehouse, powerbi and all.
Also well designed Ssis packages make a big difference. Just because you can do xyz in ssis does not mean you should implement it. We discourage the use of inline sql query but rather use stored procedures if at all possible
Thanks Yosiaz.
Our current SSIS in older version using Dtx and we also mostly using Stored procedure.
Currently we don't have SSRS but we have Tableau.
So you are suggesting one server for all with Data Warehouse?
If we go with that route then what you suggest for Server configuration?
If not then what you suggest for server configuration for SSIS and may be SSRS only?
That is what we have and its working fine for us. You wil have to customize and architect per your needs. High volume or low volume of use.
What @mike01 suggested is what we use. Start with that basic setup and revisit every quarter and see what needs to change. Do you have some usage metric dashboard? Leverage that.
But tableau is another beast, I would put that elsewhere again depends on your use
Just a follow-up, we don't use SSISDB. We use package deployments with Configs. so we don't need SSISDB. We also have automated deployments, so we don't want to give access to db to deploy "environments". AND, we have automated scheduler, so we stay away from SQL Agent. All jobs are centrally monitored
Thanks Yosiasz.
Ok, got it!
So basically 8 GB RAM and 4 CPU is working for all in one including DW so that's very good.
I will follow the same configuration but we are not sure as we are thinking to keep only SSIS or DW too.
Tableau is already on other it's own.
That's great Mike!
We are debating to which we should move forward Package v/s Project deployment.
I like Project Deployment but both has pros and cons.
WE have also 3rd party Scheduler for all our jobs so mostly our DTx packages are running on this job scheduler and avoiding Sql Agent job.
We are using ESP. It's about 100 years old and runs from a mainframe. We are in the process of replacing it with Control-M. I don't know how this works, but seems to be similar to ESP. Install agent on the box, then run batch files to run your jobs