0

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.

  • 1
    Here I found a sample, is this what you mean? poshcode.org/5810 – Magier Apr 25 '16 at 18:31
  • I saw this post . But it is talking about invoke-sqlcmd4 that I am not familiar with. And ether my server. – itzik Paz Apr 25 '16 at 18:59
2

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.

| improve this answer | |
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.