Solved

how open sql server always on replica read_only

Posted on 2016-11-14
13
39 Views
Last Modified: 2016-11-15
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
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41887413
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41887492
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
 

Author Comment

by:bibi92
ID: 41887703
sqlcmd -S LISTENER -Uapp_read  -K readonly
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41887759
Try with -M switch:
sqlcmd -M LISTENER -U app_read  -K readonly 

Open in new window

0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41887761
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
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41887774
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
 

Author Comment

by:bibi92
ID: 41887866
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
 

Author Comment

by:bibi92
ID: 41887872
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41887894
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
 

Author Closing Comment

by:bibi92
ID: 41887942
Hello,

Thanks the TCP port wasn't correct.

Regards
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41887961
Have you setup the AG for application intent use?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41887966
If you're looking for an article you could perform your own search.
The command I gave didn't work?
0
 

Author Comment

by:bibi92
ID: 41887982
Have you setup the AG for application intent use ?
yes
Read routing url were not correct.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question