Share via

SQL Server 2016 database not accessible

Terry Chan 56 Reputation points
Jan 3, 2022, 4:57 AM

I have a SQL always-on availability group with two nodes db1 as primary and db2 as secondary. After failover to db2 as primary and db1 as secondary, I have stopped the data movement for databases on db1. After resume data movement for database on db1, I tried to expand the databases to check the tables but I go the message that the database is not accessible as shown below. Is there any problem? How to fix it? I need to failover db2 to db1 as primary. Will there be any problem?

161826-sql.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,165 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,476 Reputation points
    Jan 3, 2022, 8:08 AM

    Hi @Terry Chan ,

    Welcome to Microsoft Q&A!
    Go to the node of the primary database, in SSMS, click Always On High Availablity->Availablity Groups->Right click YourAG->Properties and then check this:
    161884-1.png
    If your secondary database is Read-intent only, you should go to "Additional Connection Parameters" tab and enter ApplicationIntent=ReadOnly in the text box. You should be able to access it.
    161834-2.png
    If your secondary database is No, of course you cannot connect to this database. If you want to access it, you need to modify the readability in my first screenshot.
    Please check this MS document: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server?view=sql-server-ver15#SSMSProcedure

    No: No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.
    Read-intent only: Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
    Yes: All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.

    You can choose which kind of readability according to your actual needs.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Seeya Xi-MSFT 16,476 Reputation points
    Jan 3, 2022, 8:48 AM

    Hi @Terry Chan ,

    You can use the DROP DATABASE statement to drop this database on the node of the secondary database. Then add the database again on the node of the primary database.
    161770-image.png

    Best regards,
    Seeya

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.