How to insert record or redirect query when connection string using ApplicationIntent=ReadOnly in SQL Server

Question

Hi Experts,

We have set up of always on for our SQL instances 2017 and we are trying to move our read only queries to secondary server we are using ApplicationIntent=ReadOnly to avoid burden on server , there are few issues with our existing code could you please advise on this.

Problem:

We are using secondary server for getting call for SSRS RDL for reporting and using ApplicationIntent=ReadOnly in connections string but there are few stored proc where we are using insert statement logging errors or steps of code.

since we are in read only connection string we are getting error of read only database. do we have any work around to overcome such scenario.

Answer 1

since we are in read only connection string we are getting error of read only database. do we have any work around to overcome such scenario.

No you do not have any workaround, the database is in read-only mode which mean only read queries can run. You would have to remove the insert part and put it in other procedure which runs on primary replica