Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

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
0
bibi92
Asked:
bibi92
  • 5
  • 5
  • 2
  • +1
1 Solution
 
Pawan KumarDatabase ExpertCommented:
I think yes..
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
 
bibi92Author Commented:
sqlcmd -S LISTENER -Uapp_read  -K readonly
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try with -M switch:
sqlcmd -M LISTENER -U app_read  -K readonly 

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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 

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
End to End – Using a Listener to Connect to a Secondary Replica (Read-Only Routing) <<I think below should help>>

https://blogs.msdn.microsoft.com/alwaysonpro/2013/07/01/end-to-end-using-a-listener-to-connect-to-a-secondary-replica-read-only-routing/

NOTE from above MSDN URL : You must specify one availability database from the availability group using the database option (-d). If this option is not specified your connection will not be successfully routed to the secondary replica.

Hope it helps !
0
 
bibi92Author Commented:
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..
0
 
bibi92Author Commented:
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-Works.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.Adventure-Works.com:1433 corp/svclogin2  

Thanks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
 
bibi92Author Commented:
Hello,

Thanks the TCP port wasn't correct.

Regards
0
 
ZberteocCommented:
Have you setup the AG for application intent use?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you're looking for an article you could perform your own search.
The command I gave didn't work?
0
 
bibi92Author Commented:
Have you setup the AG for application intent use ?
yes
Read routing url were not correct.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now