Post reply

Read-only error in Primary AG replica, but the db is not read-only

  • October 27, 2020 at 4:11 pm

    Go to Answer

    #3801768

    Hello experts,

    I am trying to add db permissions (read/write) on the primary replica of an AOAG setup. For some reason I am getting this error:

    Failed to update database "MyDB" because the database is read-only. (Microsoft SQL Server, Error: 3906)

    I checked the MyDB options and Database Read-Only is set to False.

    Does anyone know why setting this permission would fail with a read-only error?

    Thanks for any help.

    -- 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

  • October 27, 2020 at 7:34 pm

    Answer

    #3801830

    How are you connecting to the 'primary' instance?

    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

  • October 27, 2020 at 7:50 pm

    #3801836

    Hi Jeffrey,

    I tried both the listener and the primary replica node. I use this query to determine the current primary replica.

    IF SERVERPROPERTY ('IsHadrEnabled') = 1
    BEGIN
    SELECT
       AGC.name -- Availability Group
     , RCS.replica_server_name -- SQL cluster node name
     , ARS.role_desc  -- Replica Role
     , AGL.dns_name  -- Listener Name
    FROM
     sys.availability_groups_cluster AS AGC
      INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
       ON
        RCS.group_id = AGC.group_id
      INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
       ON
        ARS.replica_id = RCS.replica_id
      INNER JOIN sys.availability_group_listeners AS AGL
       ON
        AGL.group_id = ARS.group_id
    WHERE
     ARS.role_desc = 'PRIMARY'
    END

     

    Ref.:

    https://www.mssqltips.com/sqlservertip/3206/finding-primary-replicas-for-sql-server-2012-alwayson-availability-groups-with-powershell/

    -------------------
    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

  • October 27, 2020 at 7:59 pm

    #3801839

    Hi Jeffrey,

    Actually I think I realize what's happening. There are multiple availability groups filled with databases that have somewhat similar names. I just checked and the database giving me the read-only error is actually in a different AG. So I will have to add the permissions AG by AG accordingly.

    Sorry for posting before I realized this.

    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 4 posts - 1 through 3 (of 3 total)

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