bibi92
asked on
how open sql server always on replica read_only
Hello,
I have forgotten how I can access on secondary replica read only.
If replica status is synchronized it isn't possible to access it.
Thanks
Regards
I have forgotten how I can access on secondary replica read only.
If replica status is synchronized it isn't possible to access it.
Thanks
Regards
I have forgotten how I can access on secondary replica read only.What are you using to access the replica?
If replica status is synchronized it isn't possible to access it.Is returning any error?
ASKER
sqlcmd -S LISTENER -Uapp_read -K readonly
Try with -M switch:
sqlcmd -M LISTENER -U app_read -K readonly
Oh, connecting to an AG you need to provide the database, so test first like this:
sqlcmd -S LISTENER -U app_read -d databasename -K readonly
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks if I specify -d option :
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : TCP Provider: No connec
tion could be made because the target machine actively refused it.
.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or in
stance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see
SQL Server Books Online..
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : TCP Provider: No connec
tion could be made because the target machine actively refused it.
.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or in
stance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see
SQL Server Books Online..
ASKER
Is it maybe https://msdn.microsoft.com/en-us/library/hh213417.aspx :
Availability Group Listeners and Server Principal Names (SPNs)
Server Principal Name (SPN) must be configured in Active Directory by a domain administrator for each availability group listener name in order to enable Kerberos for the client connection to the availability group listener. When registering the SPN, you must use the service account of the server instance that hosts the availability replica . For the SPN to work across all replicas, the same service account must be used for all instances in the WSFC cluster that hosts the availability group.
Use the setspn Windows command line tool to configure the SPN. For example to configure an SPN for an availability group named AG1listener.Adventure-Work s.com hosted on a set of instances of SQL Server all configured to run under the domain account corp/svclogin2:
Copy
setspn -A MSSQLSvc/AG1listener.Adven ture-Works .com:1433 corp/svclogin2
Thanks
Availability Group Listeners and Server Principal Names (SPNs)
Server Principal Name (SPN) must be configured in Active Directory by a domain administrator for each availability group listener name in order to enable Kerberos for the client connection to the availability group listener. When registering the SPN, you must use the service account of the server instance that hosts the availability replica . For the SPN to work across all replicas, the same service account must be used for all instances in the WSFC cluster that hosts the availability group.
Use the setspn Windows command line tool to configure the SPN. For example to configure an SPN for an availability group named AG1listener.Adventure-Work
Copy
setspn -A MSSQLSvc/AG1listener.Adven
Thanks
I'm not sure if it's a SPN issue.
Can you check if your AG is healthy (all databases need to be synchronized)?
If you change the LISTENER to the respective SQL Server instance it would work?
Can you check if your AG is healthy (all databases need to be synchronized)?
If you change the LISTENER to the respective SQL Server instance it would work?
ASKER
Hello,
Thanks the TCP port wasn't correct.
Regards
Thanks the TCP port wasn't correct.
Regards
Have you setup the AG for application intent use?
If you're looking for an article you could perform your own search.
The command I gave didn't work?
The command I gave didn't work?
ASKER
Have you setup the AG for application intent use ?
yes
Read routing url were not correct.
yes
Read routing url were not correct.
https://technet.microsoft.com/en-us/library/ff878253(v=sql.110).aspx
What do you want to achieve? T-SQL ? <<To view the properties of an availability replica>>
https://technet.microsoft.com/en-us/library/hh212946(v=sql.110).aspx#TsqlProcedure