March 21, 2023 at 12:35 pm
Has anyone every created / managed a centralized SSIS server?
We have a virtualized environment in which SSISDB is forbidden for "reasons." We have hundreds of application teams of which probably a dozen or so use SSIS, with the packages being stored on NAS shares. When I brought up the issue of business continuity / DR planning, one of our managers asked if maybe we can create a centralized server for everyone to use.
I've been doing a little research. As far as I can tell, once permissions are given in SSISDB, those permissions apply to everyone, similar to msdb permissions. There's no way to segregate teams unless we do different instances for everyone. And there's a max of 50 instances on a server. Maybe that will be enough, maybe not.
Does anyone have any thoughts or links for creating a high availability scenario that centralizes SSIS in one place that can be managed for multiple teams?
Are my assumptions wrong about the permissions?
March 21, 2023 at 6:19 pm
if you use SSIS Catalog then you can specify permission at a project level and at a folder level.
other permissions can be set at ssisdb level but those would be global
so it would be possible to have multiple teams working on the same server without being able to access/modify items belonging to other teams.
you can also (and should!!) setup multiple sql agent proxies/credentials so each team can (and is restricted to) use a account that has access only to their databases/file system folders and shares.
a bit of planning and testing would be required - and likely a big server (and therefore expensive license wise) would be required.
and whoever prohibited SSIS catalog should go through some education to elucidate them on the benefits of it versus filesystem packages.
March 22, 2023 at 6:00 am
Hello this is Gulshan Negi
Well, creating a centralized SSIS server for multiple teams is possible but requires careful planning and consideration of the limitations and potential challenges. Some options to address these challenges include using multiple instances of SQL Server, third-party tools for management and scheduling, and implementing high availability solutions such as failover clustering or Always On Availability Groups.
Hope it can give you an idea.
Thanks
March 22, 2023 at 7:15 pm
You also need to consider who and how agent jobs get created and managed - it is possible but also needs planning up front to ensure that each team is not able to affect other teams jobs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 5, 2023 at 5:11 am
An instance of SQL Server designated as a Central Management Server maintains server groups that contain the connection information for one or more instances. You can execute Transact-SQL statements and Policy-Based Management policies at the same time against server groups.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply