In the process of connecting to a secondary replica of AlwaysOn Availability Group Configuration, issues within the database can lead to SQL server error 976.

Here at Ibmi Media, as part of our Server Support Services, We have previously helped customers solve SQL related issues.

In this context, we shall see why this error occur and how to get rid of it.

More about SQL server error 976?

Availability databases are a discrete set of user databases in a replicated environment supported by an Availability group.

These availability databases are hosted by an availability replica. We have 2 types of availability replica mentioned below;

i. A single primary replica in charge of the primary databases.
ii. 1-8 secondary replicas hosted by a set of secondary databases and is responsible for handling any failover target in respect of the availability group.

When this error occurs, you will see an error message as displayed below;

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)

In summary, the main factors responsible for SQL error 976 are outlined below;

i. Database not accessible.
ii. Data movement suspended.
iii. Availability replica disconnected.
iv. Temporary Delay as a result of load on the primary or secondary replica or network latency.

How to solve SQL server error 976?

SQL error 976 is usually triggered by different factors and we outlined below are tips on how to fix it;

i. Modifying the default database value of the Login via the Login Property window.
ii. In the SQL Server Management Studio, in the process of establishing a connection change Connect to database value.
iii. The resume synchronization should be forced.

Modifying the default database value of the Login

Changing the default database for Login to a Master one can solve this issue. In order to complete this modification of the default database, first make a connection to the secondary replica affected. This can be done via the admin account. Log into SSMS with a different use other than the SQL Server Instance and follow the steps below;

i. You will see the "Run as different user" option which you are to click. Then in the Login window, enter the new Login credentials.
ii. After a successful authentication, SSMS will open. Here you can connect to the Server by clicking the Connect button.
iii. Click the Security folder to expand where you will see the Login which you want to change the default database settings.
iv. Open properties for this Login which will show the default database of this Login.
v. Modify by changing it to Master followed by the Ok button to complete this process.

In SSMS, Change Connect to database value.

Follow the steps below to change to database value.

i. To begin, in the SQL Server Management Studio, click on the Options tab which will display the default instance.
ii. Check that the default database for the Login is the affected one.
iii. If so, simply modify the "connect to database" value from default to the master one.
iv. Now you can connect to the secondary replica.

The resume synchronization should be forced.

With the T-SQL command below, you can force the resume synchronization to fix this error;

ALTER​​ DATABASE​​ Your_Database​​ SET​​ HADR​​ RESUME;

Alternatively, in the SQL Server Management Studio (SSMS) follow the steps below;

1. Make a connection to the server instance hosting the availability replica from the Object Explorer. From here you can expand the Server tree.
2. Next, expand the AlwaysOn High Availability node and the Availability Groups node. From here right-click the database followed by clicking Resume Data Movement.
3. Accept the changes by clicking OK in the Resume Data Movement dialog box.

Need support in fixing SQL errors? We are here to help you.