Timeout while trying to add database to availability group

  • Hello experts,

    I'm trying to learn what network access is being blocked when I get the timeout error below. It happens after I click Connect in the Add Database to Availability Group wizard.

    I have sometimes gotten around this by using T-SQL to add the db, but I want to finally understand how to fix the timeout. I tried adding the node IPs to the local firewalls but no luck.

    A screenshot is attached as well. Thanks for any help.

    -- webrunner

    TITLE: Connect to Server

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

    Cannot connect to [secondary node].

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

    ADDITIONAL INFORMATION:

    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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

    • This topic was modified 1 month, 3 weeks ago by  webrunner.
    Attachments:
    You must be logged in to view attached files.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Every time I've seen that error it has been either:

    A- typo in server/instance name

    B- firewall

    C- network issues

    A is easy to fix - double check the server and instance name (and port if applicable) and see if it helps.

    B is easy to TEST - One way to SHORT TERM test if it is the software firewall or not (ie windows firewall), turn it off on the server you are connecting TO and see if you can consistently connect. If so, fix the firewall settings and you are good to go. If that doesn't fix it, you may have a hardware firewall in the mix that is blocking things.

    C is harder to test. I know I have some systems where SOMETIMES the backups fail due to the backup verify step where it complains it can't find the path specified. This failure happens roughly 1-2 times every quarter and only on 1-2 instances out of the 60+ we have. The backup succeeds but the verify fails. Working with our IT team, they don't know why it fails, but it always succeeds on next run, so we haven't resolved it yet. PLUS it is rare and unpredictable when it will act up.

    But I'd say that B is the most common reason I've seen for failure to connect.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Thanks, Brian.

    I'll double-check the firewall (B) possibility. I also think it has to be that.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Hello experts,

    Following up on this post, I found something interesting.

    In the environment mentioned above, one of the AGs hangs with the connection timeout error, but another one connects successfully.

    Does anyone know what underlying firewall settings would result in that behavior?

    I'm guessing the successful AG has its listener IP address correctly added, but before trying things haphazardly I'm hoping someone help me identify where that difference between the AGs could be.

    Also, this is a multi-subnet setup, not sure if that is relevant.

    Thanks again!

    -- webrunner

    • This reply was modified 1 month, 2 weeks ago by  webrunner.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Use powershell and Test-NetConnection to the right TCP ports for the SQL instance and hadr_endpoint ports.

    So if you have Host A, B, C

    on A do

    TNC ServerB 1433

    TNC ServerB 5022

    TNC ServerC 1433

    TNC ServerC 5022

    On B do

    TNC ServerA 1433

    TNC ServerA 5022

    TNC ServerC 1433

    TNC ServerC 5022

    On C do

    TNC ServerA 1433

    TNC ServerA 5022

    TNC ServerB 1433

    TNC ServerB 5022

    Also looking at your original error message it seems to be defaulting to named pipes provider, you may need to try and force the TCP protocol in the server connection too

    TCP:ServerA,1433

    TCP:ServerB,1433

    TCP:ServerC,1433

    Guess here something isn't allow the TCP connections into the host you're having problems with, so ensure TCP protocol is enabled, firewall has the needed ACL's in to allow traffic and test with PowerShell and TNC

  • Hi Ant-Green,

    Thanks so much for this info! I'll test it out.

    Thanks again,

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 6 posts - 1 through 5 (of 5 total)

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