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.

##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##.