I'm new to SQL Server 2017 availability groups so any help would be appreciated.
For my scripts that use select queries, I would like to use SQLCMD mode to reach the secondary/read only database via a listener, instead of clicking my way through.
I have unsuccessfully tried variations of :CONNECT Listener_FQDN,Port;database=MyDatabase;ApplicationIntent=ReadOnly
I tried using variables but couldn't get that working either.
Is it possible to use SQLCMD Mode :connect to get to the secondary with a listener? I do not want to have to look first to see which server is secondary and then change my script accordingly.
I can successfully connect as follows:
a) I can connect to the secondary using the "Connect to Server" --> Options --> Additional Connections Parameter --> ApplicationIntent=Readonly (and I specify a user database name so that it doesn't attempt to connect to master)
b) I can connect to the primary via SQLCMD mode using :CONNECT Listener_FQDN,Port