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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy