March 29, 2017 at 5:03 am
Hi there,
We are about to deploy a third party SSIS DWH project which contains over 100 packages on SQL Server 2016.
The third party preference would be to use the Legacy Deployment Model - file system to store the packages, since they have noticed performance issues using the Project Deployment model using the SSISDB.
I would prefer not to use legacy, however with the sheer number of packages I can understand why this may have an effect on performance.
I've read a number of articles comparing the two and there are many reasons to go with the new feature.
However I was just wondering if anyone out there has a large number of packages in the SSISDB and what there experience has been?
Many thanks in advance.
P
March 29, 2017 at 10:42 am
rarelyamson - Wednesday, March 29, 2017 5:03 AMHi there,We are about to deploy a third party SSIS DWH project which contains over 100 packages on SQL Server 2016.
The third party preference would be to use the Legacy Deployment Model - file system to store the packages, since they have noticed performance issues using the Project Deployment model using the SSISDB.
I would prefer not to use legacy, however with the sheer number of packages I can understand why this may have an effect on performance.
I've read a number of articles comparing the two and there are many reasons to go with the new feature.
However I was just wondering if anyone out there has a large number of packages in the SSISDB and what there experience has been?Many thanks in advance.
P
While I don't have any experience with the newer deployment model, I'm not all that in favor of it. I have looked at it, but it just seems a tad too complex without sufficient compensation. Nothing wrong with having the file system be your package repository, and while it may not be quite as secure as the alternative, most folks I've run into don't have that as a concern. I do still strongly recommend that you have an SSIS configuration database so that packages can configure themselves to the environment they are in. It's a very beneficial trade off - the slight increase in complexity but a huge gain in flexibility across environments.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 29, 2017 at 10:58 am
sgmunson - Wednesday, March 29, 2017 10:42 AMrarelyamson - Wednesday, March 29, 2017 5:03 AMHi there,We are about to deploy a third party SSIS DWH project which contains over 100 packages on SQL Server 2016.
The third party preference would be to use the Legacy Deployment Model - file system to store the packages, since they have noticed performance issues using the Project Deployment model using the SSISDB.
I would prefer not to use legacy, however with the sheer number of packages I can understand why this may have an effect on performance.
I've read a number of articles comparing the two and there are many reasons to go with the new feature.
However I was just wondering if anyone out there has a large number of packages in the SSISDB and what there experience has been?Many thanks in advance.
P
While I don't have any experience with the newer deployment model, I'm not all that in favor of it. I have looked at it, but it just seems a tad too complex without sufficient compensation. Nothing wrong with having the file system be your package repository, and while it may not be quite as secure as the alternative, most folks I've run into don't have that as a concern. I do still strongly recommend that you have an SSIS configuration database so that packages can configure themselves to the environment they are in. It's a very beneficial trade off - the slight increase in complexity but a huge gain in flexibility across environments.
I am not aware of any performance differences – can you provide any references for me to read? I wonder whether it is the performance of the 'SSIS Server Maintenance Job' SQL Agent job which you have read about. This can be a pain if you leave your SSISDB catalog with the default settings for 'Retention Period' and 'Max Number of Versions Per Project'.
I much prefer not having packages dotted around the file system. I never liked the indirect XML config method. Out of the box logging is extremely detailed and helpful. Project-scoped parameters and connection managers avoid redundancy and mean it's less likely that you'll miss something in your config. Bundling packages into a single ispac file and deploying that, rather than the packages themselves, seems so much easier and works better with source control (IMO).
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 29, 2017 at 11:16 am
Phil Parkin - Wednesday, March 29, 2017 10:58 AMsgmunson - Wednesday, March 29, 2017 10:42 AMrarelyamson - Wednesday, March 29, 2017 5:03 AMHi there,We are about to deploy a third party SSIS DWH project which contains over 100 packages on SQL Server 2016.
The third party preference would be to use the Legacy Deployment Model - file system to store the packages, since they have noticed performance issues using the Project Deployment model using the SSISDB.
I would prefer not to use legacy, however with the sheer number of packages I can understand why this may have an effect on performance.
I've read a number of articles comparing the two and there are many reasons to go with the new feature.
However I was just wondering if anyone out there has a large number of packages in the SSISDB and what there experience has been?Many thanks in advance.
P
While I don't have any experience with the newer deployment model, I'm not all that in favor of it. I have looked at it, but it just seems a tad too complex without sufficient compensation. Nothing wrong with having the file system be your package repository, and while it may not be quite as secure as the alternative, most folks I've run into don't have that as a concern. I do still strongly recommend that you have an SSIS configuration database so that packages can configure themselves to the environment they are in. It's a very beneficial trade off - the slight increase in complexity but a huge gain in flexibility across environments.
I am not aware of any performance differences – can you provide any references for me to read? I wonder whether it is the performance of the 'SSIS Server Maintenance Job' SQL Agent job which you have read about. This can be a pain if you leave your SSISDB catalog with the default settings for 'Retention Period' and 'Max Number of Versions Per Project'.
I much prefer not having packages dotted around the file system. I never liked the indirect XML config method. Out of the box logging is extremely detailed and helpful. Project-scoped parameters and connection managers avoid redundancy and mean it's less likely that you'll miss something in your config. Bundling packages into a single ispac file and deploying that, rather than the packages themselves, seems so much easier and works better with source control (IMO).
Phil,
You quoted my post, but I'm not aware of any performance issues nor did I even mention performance. Perhaps rarelyamson can respond ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 29, 2017 at 12:48 pm
sgmunson - Wednesday, March 29, 2017 11:16 AMPhil,You quoted my post, but I'm not aware of any performance issues nor did I even mention performance. Perhaps rarelyamson can respond ?
Yep, that was a mark of genius, wasn't it? :hehe: I should put the rum away.
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 29, 2017 at 1:01 pm
Phil Parkin - Wednesday, March 29, 2017 12:48 PMsgmunson - Wednesday, March 29, 2017 11:16 AMPhil,You quoted my post, but I'm not aware of any performance issues nor did I even mention performance. Perhaps rarelyamson can respond ?
Yep, that was a mark of genius, wasn't it? :hehe: I should put the rum away.
Yep, been there... done that,... just not with any kind of alcohol, so in theory, I didn't even have a good excuse other than "oops!".
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 29, 2017 at 1:54 pm
Hi,
Thanks all the response.
It was the third party company that have experienced performance issues with the Project Deployment Model.
But even so I would still rather use this newer method of the SSISDB, set all the retention config levels and versioning correctly and have a far more detailed error reporting ability.
I'm also with Phil in that I like the tidier more centralised approach.
So in answer to my question, no one can see any issues with us running 100+ packages from the SSISDB.
Many thanks again to all who responded.
p
March 29, 2017 at 3:30 pm
rarelyamson - Wednesday, March 29, 2017 1:54 PMHi,Thanks all the response.
It was the third party company that have experienced performance issues with the Project Deployment Model.
But even so I would still rather use this newer method of the SSISDB, set all the retention config levels and versioning correctly and have a far more detailed error reporting ability.
I'm also with Phil in that I like the tidier more centralised approach.So in answer to my question, no one can see any issues with us running 100+ packages from the SSISDB.
Many thanks again to all who responded.
p
One thing I would recommend is that you consider whether or not to group the packages into projects. Are there logical divisions between what the packages do?
If you had, say, five projects each containing 20 packages, the time required to build and deploy to SSISDB is less. You may also find that source control is easier if different developers can work on different projects.
Having said that, one of our projects (among several others) does contain around 100 packages and there is no issue with it in terms of run times or deployment times.
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 30, 2017 at 5:08 am
In my last DBA job we designed an SSIS 2012 solution that contained 530 packages that used project deployment. All of these packages ran every night to reload tables from a legacy system, about 80Gb in total. You do need to set up the retention period on SSIDB, as the default can lead to bloat. Other that that performance was not an issue.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply