Infrastructure at your Service

David Barbarin

SQL Server 2016 : availability groups and the new ssis_monitor role for SSIDBB catalog

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:

> SQL Server 2016 : availability groups and the new SSISDB support

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:

blog 62- 1 - new ssisdb roles

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'

 

blog 62- 2 - ssis monitor members

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

 

blog 62- 3 - ssis monitor permssions

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

 

blog 62- 4 - ssis failover job

Hope this helps

 

Leave a Reply


− four = 4

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader