During my investigation about the new AlwaysOn features, I wrote a blog post about the new SSISDB support for AlwaysOn. You can find it here:
Just as a reminder, for those who have dealt with the SSISDB catalog in the previous version, some extra works were mandatory to be “AlwaysOn” compliant. Fortunately, the new version of SSIDB catalog will make easier the DBA job.
In this blog post, I will talk about a new (little) discovery that concerns the SSIDB catalog. In fact, I found out two new roles in this last CTP 2.3 as shown below:
Among these two roles, the ssis_monitor role raised my awareness and I wonder which tasks are performed across this role.
Let’s begin by looking at this role and its members:
select dp.principal_id as role_p_id, dp.name as role_name, dp2.principal_id as member_p_id, dp2.name as member_name from sys.database_role_members as drm join sys.database_principals as dp on dp.principal_id = drm.role_principal_id join sys.database_principals as dp2 on dp2.principal_id = drm.member_principal_id where dp.name = 'ssis_monitor' and dp.type_desc = 'DATABASE_ROLE'
##MS_SSISServerCleanupJobUser## is a special user that already exists on the SQL Server 2014. This user is the owner of the SSIS Server Maintenance job but what’s the purpose to add it to this new role? To get a response we have to move directly on the related permissions:
select p.name as principal_name, s.name as [schema_name], dp.type, dp.[permission_name], dp.state_desc, o.name as [object_name], o.type_desc as object_type from sys.database_permissions as dp join sys.database_principals as p on p.principal_id = dp.grantee_principal_id join sys.objects as o on dp.major_id = o.object_id join sys.schemas as s on s.schema_id = o.schema_id where grantee_principal_id in (6) order by p.name
We can notice that the new ssis_monitor role is granted to execute the catalog.startup stored procedure. As a reminder, only the ssis_admin role had these permissions on the previous SQL Server version.
In fact, this role has been introduced on SQL Server 2016 to allow the new SSIS Failover Monitor agent job (AlwaysOn feature) to update the operation table’s status after failover (thanks to Jimmy Wong – Principal Lead Program Manager – for this tip). We can confirm this point by looking at the SSIS Failover Monitor Job directly. The first step of this job consists in refreshing the current replicas configuration after a failover event. The second step concerns the execution of the catalog.startup procedure in order to fix the status of any packages there were running if and when the SSIS server instance goes down. That makes sense after a failover! Note that the job’s owner is also the special user ##MS_SSISServerCleanupJobUser##.
Hope this helps
By David Barbarin