July 12, 2018 at 9:39 am
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
Any suggestions?
July 12, 2018 at 10:30 am
Marcia Q - Thursday, July 12, 2018 9:39 AMI'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,PortAny suggestions?
Make sure you are using: -K readonly
option with sqlcmd.
Connecting with sqlcmd
Sue
July 12, 2018 at 10:47 am
Thanks for the reply. Yes.I use -K when I connect through a command prompt. However, I am using Management Studio (SSMS). -K doesn't work there.
In Management studio SQLCMD Mode, this works to get me to the primary: :CONNECT listenername.domain,port
However, if I use :CONNECT listenername.domain,port -K OR if I use :CONNECT listenername.domain,port; -K
then I get the following error: A fatal scripting error occurred. Incorrect syntax was encountered while parsing :CONNECT.
July 12, 2018 at 11:59 am
Marcia Q - Thursday, July 12, 2018 10:47 AMThanks for the reply. Yes.I use -K when I connect through a command prompt. However, I am using Management Studio (SSMS). -K doesn't work there.In Management studio SQLCMD Mode, this works to get me to the primary: :CONNECT listenername.domain,port
However, if I use :CONNECT listenername.domain,port -K OR if I use :CONNECT listenername.domain,port; -K
then I get the following error: A fatal scripting error occurred. Incorrect syntax was encountered while parsing :CONNECT.
Sorry didn't understand what you were asking originally. You want to change connections using :CONNECT in SQLCMD mode and connect to read-only intent secondaries. I don't think there is a parameter for that using :CONNECT. The documentation is lacking but if it's correct, you can only specify server, user and password. When read-only intent isn't supported you need to set readable secondary to yet.
Sue
July 12, 2018 at 12:14 pm
OK. Thank you for the followup. I haven't found any documentation for it either, but I was hopeful that someone had found a way to make it happen!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply