AWS RDS Configuration - rds_create_login_trigger error
Context
I was running a set of SQL scripts on the AWS RDS Microsoft SQL Server (MSSQL) server. I encountered following error while attempting to grant a specific list of permissions with grant option for a custom/user-defined server role.
There is little information regarding the rds_create_login_trigger online. From the error message, I inferred that it is an internal stored procedure we do not have access as users.
Msg 50000, Level 15, State 1, Procedure rds_create_login_trigger, Line 95
Cannot grant to server role
Understanding how AWS RDS MSSQL Multi-AZ works
Let's say you have a RDS instance in single Availability Zone (AZ) mode. When converting to Multi-AZ, AWS will take an automated system snapshot of your current RDS. Then this snapshot will be used to replicate to the secondary zone.
AWS RDS MSSQL has 2 different failover technologies - Database Mirroring (DBM) and Always On Availability Groups (AGs).
Difference in behaviours for multi-AZ RDS
I created a test RDS by restoring from the automated system snapshots in Single-AZ. To my surprise, the scripts could be executed successfully with no issues. I narrowed down the issue to be from the multi-AZ configurations.
However, I observed 2 different behaviours between the failover technologies
Database Mirroring (DBM): Face the
rds_create_login_triggerAlways On Availability Groups (AGs): Can run the SQL scripts to grant permissions
After reading through AWS RDS documentations, I realize it is actually a limitation. User-defined server roles, a feature of SQL Server 2012 feature) are only replicated in Multi-AZ instances for Availability Groups (AGs) instance.
Conclusion
In the end, the resolution is to
Run the database scripts on the AWS RDS MSSQL on Single-AZ first before converting the RDS to be Multi-AZ.
AWS will take a system snapshot to replicate the RDS to the secondary zone for the conversion from Single-AZ to Multi-AZ deployment.
As DBM will be removed in a future version of MSSQL, it is recommended to use Always On availability groups instead. Hence, consider switching to a newer version where Multi-AZ for Availability groups is supported if possible.
You can find the supported versions here. This is because the failover technologies is determined by the MSSQL versions and edition.
That's all, folks. Hope you gain insights on AWS RDS Mulit-AZ settings for MSSQL!