Post reply

Always on Db not able to add in secondary

  • July 14, 2019 at 5:36 am

    #3662767

    Hi,

    we configured Always on configuration. Due to some reasons we removed 1.3 tb db from always on.

    Now we wanted to add that db to again in Ag Group. But when i am adding the db in Ag, everything successful. But in secondary replica in Availability database i am able to see the db with yellow mark down. but in main database list i am not able to see .

    While adding the db configuration i checked the backup in shared folder whether it saved or not. every thing is successful. But i am not able to see db in main db list with Syn message. i am not able to understand why db not syncing in secondary. Similarly this happens in DR secondary node.

    we configured as Primary -Synchronous, secondary & DR Secondary as Asynchronous,Manual  mode.

    Just for sample i created Test db in primary and tried adding in Ag group. Without any issue it added in both secondaries. Also the db is sync.

    Why i am not able to add one perticular db which we deleted before?

    Please help me.i am struggling to add the db. i googled. i found this below link which is similar to my issue, but when i tried executing the command i am getting syntax error at Grant.I am not able to understand y i am not able to add the db to secondary replicas.

    https://dba.stackexchange.com/questions/232787/issue-with-adding-a-database-to-an-availability-group

    Thanks,

    Jo

  • July 15, 2019 at 6:10 am

    #3662874

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • July 25, 2019 at 8:40 pm

    #3666192

    Did you follow up with your network team, whether there are any ports are blocking from Node 1 to Node2 SQL?

  • July 26, 2019 at 3:11 pm

    #3666382

    Jo,

    Could you tell me the results ... open the SSMS from your desktop / Jump server and

    1. Connect Node 1 SQL Server (Primary). Are you able to connect?
    2. Connect Node 2 SQL Server (Secondary). Are you able to connect?
    3. Connect with Listener. Are you able to connect?   Repeat the same process on node 2.
    4.  If all are working, there is no issue with AG setup.
    5. Ask your network engineer whether 5025 port is blocking on firewall side.
    6. Let us know if any issues....

  • July 26, 2019 at 6:41 pm

    #3666434

    Please outline the steps you took to add the database back into the AG?  Prior to doing that - did you actually remove the database from the AG or was it in a suspect state?

    For a database of this size - the simplest way I have found to add the database back is:

    1. Insure the secondary database is no longer part of the AG - and the primary database is no longer part of the AG
    2. Copy the backup file to the secondary and restore with norecovery
    3. Copy all tlog backup files taken since the backup from step 1 - restore with norecovery
    4. Add the database to the AG and JOIN

    Because of the amount of time it can take to copy the backup files and restore - I adjust the process to:

    1. Copy backup file and restore
    2. Once restored - take differential backup, copy and restore
    3. Copy tlog backups and restore
    4. Add the database to the AG and JOIN

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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