SQL server error 976 occurs while connecting to the secondary replica of AlwaysOn Availability Group configuration.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Let us today discuss the possible causes and fixes for this error.

What is SQL server error 976?

An availability group supports a replicated environment for a discrete set of user databases, known as availability databases.

Each set of availability database is hosted by an availability replica. Two types of availability replicas exist:

• A single primary replica that hosts the primary databases.
• One to eight secondary replicas, each of which hosts a set of secondary databases and serves as a potential failover target for the availability group.

Further, SQL server error 976 occurs while connecting to the secondary replica of AlwaysOn Availability Group configuration.

For instance, a typical error message looks like:

~~
Cannot connect to ‘Secondary Replica Server’.

ADDITIONAL INFORMATION:
The target database, ‘SDGC’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)
~~

What causes SQL server error 976?

The possible reasons for the 976  error include:

• Disconnected availability replica.
• Suspended data movement.
• Inaccessible database.
• Temporary delay due to network latency or the load on the primary or secondary replica.

Let us now look at the steps to fix this error.

How to fix SQL server error 976?

As there are a number of reasons that trigger the error 976,  there are multiple methods available to fix it. Some of them to list out include:

1. Change Connect to database value in SQL Server Management Studio while establishing the connection.
2. Change default database value of your Login in Login Property window.
3. Force the resume synchronization

Change Connect to database value in SSMS

• Initially, from the SQL Server Management Studio, click on Options tab of the connection details window in SSMS.
• The ‘Connect to database’ value will now show as ‘default’. Now, verify if the default database for the user’s login is the same as that of the database facing issues.
• If yes, change the “connect to database” value from default to master or some other database.
• Once you make changes, hit the connect button and establish a database connection. This time you will be able to establish a database connection to the secondary replica.

Change default database value of your Login

We can fix this issue permanently by changing the default database for login to a master one. However, this can only be done if it is not mandatory for the default database of the login to be an availability database.

To change the default database, we can connect to the secondary replica or to the server where we are facing this issue. This can be done using a different admin or security admin account. A right-click on SSMS icon while pressing SHIFT button will help to login using a different user to the SQL Server instance.

• Click on “Run as different user” option. Enter the new credentials in the login screen displayed.
• After authentication, it launches SSMS. Click on Connect button to establish the connection.
• Now expand Security folder and Identify the login for which you have to change the default database setting.
• Right-click and choose properties on this login to launch properties window. It shows the default database set for this login.
• Now we need to change it to master or any accessible database. Once you make changes, click on Ok button to proceed.

Force the resume synchronization

Using a T-SQL command to force the resume synchronization can also help to fix this error. The command is as follows:

ALTER DATABASE YourDatabase SET HADR RESUME;

Further, we can perform this task using SQL Server Management Studio as well:

• In Object Explorer, connect to the server instance that hosts the availability replica. Then, expand the server tree.
• Now, expand the AlwaysOn High Availability node and the Availability Groups node.
• Then, expand the Availability Databases node, right-click the database, and click Resume Data Movement.
• Finally, in the Resume Data Movement dialog box, click OK.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, SQL server error 976 occurs while connecting to the secondary replica of AlwaysOn Availability Group configuration. Today, we saw how our Support Engineers fix this error.