January 19, 2016 at 1:27 pm
Looks like there is bit of confusion here, may be i wasn't clear.
I have two servers DB4 and DB5 set in AAG. DB4 is primary and DB5 is secondary. As you can see from the attached image in previous post, i have routing enabled with priority such that all my read-only queries should go to DB5 (secondary) . Here are my questions:
i) I open SSMS , put in listener name (DBvnn) and under options i entered 'ApplicationIntent = ReadOnly'. I was successfully able to connect. However when i execute 'select @@servername' , i see primary server name DB4, i was expecting to see DB5. What am i missing here.
January 19, 2016 at 1:27 pm
Perry Whittle (1/19/2016)
you also need to specify the database name in the connection string otherwise it doesn't know where to route the query to
:Whistling:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 19, 2016 at 1:33 pm
I'm going to assume the answer to the question you keep ignoring is 'No', in which case Perry explained above why you're not seeing the read-only routing
Are you specifying the database name in the connection string?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 1:39 pm
An availability group may have multiple databases, you need to specify the listener name, database name and readonly intent option, then (if routing is correctly configured) you'll be redirected to a secondary replica
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 19, 2016 at 1:48 pm
GilaMonster (1/19/2016)
I'm going to assume the answer to the question you keep ignoring is 'No', in which case Perry explained above why you're not seeing the read-only routingAre you specifying the database name in the connection string?
Alright, i never knew you could even specify a database name in connection string in SSMS. I gave it a shot and below error.
[highlight=#ffff11]
"Format of the initialization string does not conform to specification starting at index 186. (System.Data)"[/highlight]
I tried DBvnn.MyDB
January 19, 2016 at 1:48 pm
Perry Whittle (1/19/2016)
An availability group may have multiple databases, you need to specify the listener name, database name and readonly intent option, then (if routing is correctly configured) you'll be redirected to a secondary replica
I suspect (but haven't tested) that when connecting with SSMS, all that's needed there is to select the appropriate DB from the drop down box on the connection properties tab. That should add the db name to the connection string that SSMS uses.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 1:54 pm
No luck 🙁
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)
January 19, 2016 at 2:17 pm
GilaMonster (1/19/2016)
Perry Whittle (1/19/2016)
An availability group may have multiple databases, you need to specify the listener name, database name and readonly intent option, then (if routing is correctly configured) you'll be redirected to a secondary replicaI suspect (but haven't tested) that when connecting with SSMS, all that's needed there is to select the appropriate DB from the drop down box on the connection properties tab. That should add the db name to the connection string that SSMS uses.
That is correct.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 19, 2016 at 3:06 pm
I think i am missing something here.I am trying to connect using listener name, is that even right?
January 19, 2016 at 3:14 pm
curious_sqldba (1/19/2016)
I think i am missing something here.I am trying to connect using listener name, is that even right?
Yes you specify the listener name and the database name you wish to connect to.
Are you previously an oracle dba by any chance?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 19, 2016 at 3:25 pm
Perry Whittle (1/19/2016)
curious_sqldba (1/19/2016)
I think i am missing something here.I am trying to connect using listener name, is that even right?Yes you specify the listener name and the database name you wish to connect to.
Are you previously an oracle dba by any chance?
Has this worked for you? I specify the listener name, selected the database from the drop and in options entered ;ApplicationIntent=ReadOnly and i get below error message.
'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (.Net SqlClient Data Provider)'
P.S: Lol, no i am not Oracle DBA. Why would you ask that?
January 19, 2016 at 6:47 pm
I was mistaken
If you want to have all read-only connections use the "other" server then you need to delete the entries for the "primary"
https://msdn.microsoft.com/en-us/library/hh710054.aspx
Typically, each read-only routing list would contain every read-only routing URL, with the URL of the local replica at the end of the list.
I had previously connected to the primary using the listener in SSMS but this is the first time I have attempted to connect to a readonly replica using SSMS. Thanks Gail and Perry for a couple of details that made the difference for me.
January 19, 2016 at 10:04 pm
curious_sqldba (1/19/2016)
Perry Whittle (1/19/2016)
curious_sqldba (1/19/2016)
I think i am missing something here.I am trying to connect using listener name, is that even right?Yes you specify the listener name and the database name you wish to connect to.
Are you previously an oracle dba by any chance?
Has this worked for you? I specify the listener name, selected the database from the drop and in options entered ;ApplicationIntent=ReadOnly and i get below error message.
'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (.Net SqlClient Data Provider)'
Can you provide screenshots of what youre doing?
curious_sqldba (1/19/2016)
P.S: Lol, no i am not Oracle DBA. Why would you ask that?
Because it seemed to me you were confused about the way the listener works
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 4, 2019 at 7:56 pm
Hey, I know this is an old post, but people searching for ways to check the read state of an availability database will probably find this page. That said, my question: is there a reliable way to test if a database is part of a readable secondary for maintenance/data gathering purposes? E.g. before pulling a ton of polling info about the database, make sure it is part of a readable secondary replica first so that I do not have to error handle when it cannot be read.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply