I have a SQL 2017 mirroring setup and found a query has 2 plans in query store and I wanted to force the good plan which runs much faster. But when i force the query plan I get below error. Same error if I try to do it using Query store GUI.
Note : I am trying to do this on the PRINCIPAL in my DB mirroring setup with high safety automatic failover mode.
Msg 12446, Level 16, State 1, Procedure sp_query_store_force_plan, Line 1 [Batch Start Line 0] Query Store stored procedure is not available on readable secondary replica.
Even though Always On is enabled at the instance level, we are not using it. However, we are using DB mirroring with high safety automatic failover mode.
Why am I not allowed to force a plan on Principal DB and what is the workaround for this.