search

microsoft / go-sqlcmd

The new sqlcmd, CLI for SQL Server and Azure SQL (winget install sqlcmd / sqlcmd create mssql / sqlcmd open ads)
https://learn.microsoft.com/sql/tools/sqlcmd/go-sqlcmd-utility
MIT License
323 stars 56 forks source link

Sporadically can't find instance in availability group #462

Closed sliekens closed 8 months ago

sliekens commented 9 months ago

Hello,

I intermittently get an error with sqlcmd v.1.2.1 when connecting to an instance in a high availability cluster.

no instance matching 'inst01' returned from host 'redacted'

This sounds to me like an issue with MultiSubnetFailover. I removed -M from my sqlcmd command because it has been deprecated, but I didn't have this problem with the ODBC version of sqlcmd (with -M).

My full command is something like:

sqlcmd -S 'redacted\inst01' -d 'redacted' -U 'redacted' -P '****' -b -i 'script.sql'

OS: Debian 11 running inside Docker

Thanks for your attention.

shueybubbles commented 9 months ago

Can you reproduce the error after adding --driver-logging-level=65 to the command line? It may provide more insight on what happened at the driver level.

sliekens commented 9 months ago

@shueybubbles no problem (I removed some portion of IPs because I don't know who else is reading this)

DRIVER:unable to get instances from Sql Server Browser on host redacted: read udp 172.23.x.x:46208->10.208.x.x:1434: i/o timeout
DRIVER:Skipping protocol tcp. Error:no instance matching 'inst01' returned from host 'redacted'

I checked with our network engineer, there is no visible traffic on port 1434. However we do see a connection on port 60000.

Do you know what it means?

shueybubbles commented 9 months ago

1434 is the SQL Browser service port. The driver tries to query the browser service on that port to get the port number for inst01. If you know the port number for inst01 you could change your connection string to use it directly instead of using the instance name, it will speed up the connection a bit. Alternately try increasing the dial timeout.

sliekens commented 9 months ago

Thanks. I was told the port for inst01 is 50001. Do you have any insight what the connection to port 60000 could be? How would I increase the dial timeout? Is it the -l option?

shueybubbles commented 9 months ago

I don't know what 60000 would be. -l is the dial/connect timeout

sliekens commented 8 months ago

Just gonna close this as I'm waiting for krb5 support to switch from old sqlcmd to sqlcmd-go. It was probably an environmental issue anyway.