Hello,
I am researching whether to install SSIS on its own server or install it on our production SQL server,
right now we have 16 cores and 128 gigs on our current SQL server, which we will call it SQLA, we will be running a lot of packages... say at least 15-20 packages daily to do ETL stuff from flat files to transforming local data from other database sources to the SQLA. right now I have the SQLA set to use 12 cores, 100 gigs etc., leaving some for the OS, but if I install SSIS, of course I assume I will need more hardware, or if I install SSIS on another server, will I still need to increase the hardware on the SQL Server?
thanks in advance
February 3, 2020 at 8:54 pm
It really depends on how you intend to use it. However, any SQL Server service outside of the engine will use resources (memory being one of the main ones along with CPU) that cannot be controlled by increasing the amount of cores or memory "SQL Server (i.e. meaning the engine)" is using. So, if you're leaving 28 GB of RAM for the OS, SSIS will have to share that with the OS and cannot touch the 100 GB you have reserved for SQL Server.
Also worth noting that if you install SSIS (or SSRS or SSAS) on a separate server, it will have to have a full license for whatever edition being used.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
This is a big "it depends" answer. When you say you are running 15-20 packages daily as "a lot of packages", it makes me wonder how bad my setup is where we run 10 packages per hour plus another 29 in a daily load on top of another 15 that are run on-demand (usually just once per month). But most of our packages are fairly simple data transforms, with some being simple "truncate table A->populate table A". So it really depends on what you mean by a lot of packages. Are they complex packages? How much data are you pulling? Will it fit in the memory you have not allocated to SQL/OS?
If money is no object, my preference is to do separation of servers. If SQLA is used for hosting your SQL instance, then SQLA should be used for that alone. It makes testing, upgrading and maintenance much easier. Plus SSIS operates in separate memory space from SQL Server, so you can get memory pressure. I know we would never hit 28 GB of data in our SSIS loads, but that is by design. Your data loads may be 1 GB each, or you could have 100GB sitting in memory. So how much do you need? It depends on the size of the data you are moving across. Plus having it on multiple machines makes patching a bit easier.
That being said, where I work we care more about the bottom line - money. If the solution is going to cost more money and we can leverage our existing systems, even with a performance hit, it is preferred over spending money. If you install SSIS (at least with 2012/2016, not 100% certain if they changed that licensing model with 2017+) you need to have a SQL Server license.
I would recommend doing some analysis (perfmon or task manager will likely be good enough metrics) of how heavy those SSIS packages are on your test system and add a buffer for data growth and then compare if that will fit in on your SQLA server AND if you are comfortable having it all on a single box like that. I know a single box for all my SQL instances plus SSIS would make me nervous.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 4, 2020 at 8:34 am
If you move SSIS away from the main SQL server you have to have a license for it.
In fact moving any component, SSRS/SSAS/SSIS/DQS/MDM/Distributed Replay etc etc to some other server it has to be licensed.
So you can imagine the pain this will bring if you have a full split out service DW SQL/DW SSRS/DW SSAS/DW SSIS all 4 servers need to be licensed, 16 cores in each that's 64 cores you need to license, hope your pockets are deep. Now that being said if your in a virtual shop and have unlimited virtualisation, hey whats stopping you right?
But should you is a different question, it all goes down to the "it depends", what are your SSIS packages doing, are they the traditional ETL or are you doing more ELT? If your transforming the data in the DTSX then it could be beneficial to separate the process, but if your only doing it once a day, spinning up a costly box may be overkill, could you not live with the pain while the packages run?
If its more ELT where its get data from Source, load to DW, then stored procs do the transforms, then I wouldn't really bother separating as the DTSX's are trivial.
Again it all comes down to "It Depends" on how your using and how frequently the SSIS stuff runs.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply