Question :
How is it possible to execute a query as a applicationintent=readonly using powershell?
I have an cluster alwayson environment.
I want to call a procedure with invoke sqlcmd to the readonly database (secondary). I have the listener name and the database name.
Answer :
Currently Invoke-Sqlcmd does not support the ability to connect to a read-only secondary replica.
The SQL Server, and some of the PowerShell community, however started working with the Program Manager at Microsoft to get SQLPS and SSMS improvements organized. A Trello board was opened in the past month that we are showing by votes what priority we want Microsoft to take on the improvements. This has actually already gotten some things added to the SSMS March preview.
I created a card for the Application Intent functionality so go vote it up and the connect item linked.
Right now your only option is to call sqlcmd within PowerShell using the -K ReadOnly.
Someone else has actually created a script to retrieve the name of the readonly node (only works if there is only one secondary).
So, you can use Invoke-Sqlcmd but in order to get the server name you need to run your server name through the function found here:
https://www.sqlservercentral.com/Forums/1687300/Application-Intent-with-Invoke-SQLcmd