Post reply

Disconnected From Listener in Availability Group (When Primary Failed)

  • June 27, 2015 at 4:24 am

    #318516

    Hi

    I setup an availability Group. (Only 2 servers - Primary And secondary) -- 21 , 22

    I also define an listener . IP .. 23

    ---------------------------------------------------------------------------------------------------------

    1- In First step I connected To Listener (23) And in a while I inserted A record to a table .

    While 1=1

    insert into Tbl_T1(f1,f2) Values (1,2)

    ---------------------------------------------------------------------------------------------------------

    2- in second, I Stop the primary .

    - I expected this while whitout disconnect, continue.

    ---------------------------------------------------------------------------------------------------------

    3- The while code stopped whit this message :

    Msg 64, Level 20, State 0, Line 0

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

    ---------------------------------------------------------------------------------------------------------

    4- I execute again the script, And it worked in new primary.

    My questions :

    1- is the listener disconnected between switched primary and secondary ? OR have we data loss between switching?

    2- I did some huge update on Primary that fill the Log fiel space. And in last Update I got this error :

    Msg 9002, Level 17, State 2, Line 27

    The transaction log for database 'Your_DB' is full due to 'LOG_BACKUP'.

    Is this (Fill All space) a reason to switch primary? Or not ?

    Thank you

  • June 27, 2015 at 6:13 am

    #1808589

    What have you defined as the secondary role options for the secondary database?

    You have setup the listener but have you setup read only routing?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • June 27, 2015 at 10:19 pm

    #1808616

    I Only set yes for readable seconday :

    And I read about Read only Routing. Are these Comparison (Between Read only And Read only Routing) true?

    1- In read Only secondary (where we have a Listener)

    - Our App Connect to Listener.

    - Listener Lead app to Primary.

    - All Connection , received by the primary And if we want to Reports-App connection To Secondary, We should do this manualy.

    - Both servers (Primary And secondary are available to connection from app or tsql from Sql server console)

    - If we have fail on primary , we have some seconds disconnection and then we connect to secondary as new primary

    2- In read-intent Only (where we have a listener and set a Readonly Routing)

    - Our App Connect to Listener.

    - Listener Lead (Update,insert and delete) Of app to Primary And (Select) of App to Secondary

    - some Connection , received by the primary and some by secondary. Automaticaly reports connect to Secondary

    - secondary are not accessible in any way.

    - and in fail primary, we dont have disconection

    Thank you for your paitience

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply