SQL is today perhaps the most commonly used database programming language. Just like working with other programming languages, you’re likely to run into errors with SQL. One of the most common one is SQL Server Error 976. This takes places because the user wasn’t able to access the AlwaysON Availability’s secondary replica. In this article, we’ll guide on how to fix the SQL Server 976 error.
What exactly is the SQL Server Error 976?
To understand the 976 Error, you should have knowledge on Microsoft’s AlwatsON Availability Group, As defined by Microsoft themselves, these independent groups are meant to support a replicated environment for specific group of user databases. These user databases are what is known as availability databases.
These type of databases are hosted by availability replicas which are of two types. Those are:
The 976 error usually occurs when an user is trying to connect to the secondary replica. When it happens, you get the following message:
“Cannot Connect to ‘Secondary Replica’”
When further inspecting the error, you realize that your target database, SGDC, is part of an availability group and is not accessible at the moment for any queries.
Reasons for the SQL server error 976
If an user encounter this error, then more often than not because of the following reasons:
Just like multiple problems can cause the error, there are multiple ways to fix it. Try one of the following solutions:
1. Change Connect to database value in SSMS (Server Management Studio)
When establishing the connection, you need to visit the SQL Server Management Studio and follow the steps:
If you want to fix this issue for once and all, then you need to change the default login database to a master database. But a prerequisite is the default database isn’t mandatory to be the availability database. Any other database should be able to support this role.
For changing the default database, you have to connect to the server where it’s showing the error. Otherwise, you can connect to secondary replica as well. Follow the steps:
This is the final way you can solve the server error. For this, you’d be using T-SQL command. Use the following codes:
To make the changes in SSMS, here are the steps:
What exactly is the SQL Server Error 976?
To understand the 976 Error, you should have knowledge on Microsoft’s AlwatsON Availability Group, As defined by Microsoft themselves, these independent groups are meant to support a replicated environment for specific group of user databases. These user databases are what is known as availability databases.
These type of databases are hosted by availability replicas which are of two types. Those are:
- A primary replica that is responsible for hosting the primary database
- And secondary replicas which can be between 1 to 8 in number. Each of these host a secondary availability database.
The 976 error usually occurs when an user is trying to connect to the secondary replica. When it happens, you get the following message:
“Cannot Connect to ‘Secondary Replica’”
When further inspecting the error, you realize that your target database, SGDC, is part of an availability group and is not accessible at the moment for any queries.
Reasons for the SQL server error 976
If an user encounter this error, then more often than not because of the following reasons:
- The availability replica got disconnected mid-way
- Data movement was suspended (perhaps due to network error)
- The database was inaccessible in the first place
- Temporary delay because of network latency or imbalance in load between the primary and secondary replica
Just like multiple problems can cause the error, there are multiple ways to fix it. Try one of the following solutions:
1. Change Connect to database value in SSMS (Server Management Studio)
When establishing the connection, you need to visit the SQL Server Management Studio and follow the steps:
- Click on the Options tab
- Find the “Connect to database” value which should be set to default. Cross check if the default database for login and database facing issues are the same.
- If yes, you need to change the “Connect to database” to either master or other database of your choice
- Click on the connect button to establish a connection.
If you want to fix this issue for once and all, then you need to change the default login database to a master database. But a prerequisite is the default database isn’t mandatory to be the availability database. Any other database should be able to support this role.
For changing the default database, you have to connect to the server where it’s showing the error. Otherwise, you can connect to secondary replica as well. Follow the steps:
- Right-click+Shift on the SSMS icon
- Select “Run as different user” and provide the newly-generated credentials
- After SSMS launches, click on Connect
- Expand the folder named Security and select the login for which you’re supposed to change the settings
- Right-click on it which will launch the properties window
- Change the default value to master or any other available database of your choice
- Click on OK and save the changes
This is the final way you can solve the server error. For this, you’d be using T-SQL command. Use the following codes:
Code:
ALTER DATABASE MyDatabase SET HADR RESUME;- Open Object Explorer and connect to the availability replica.
- Expand the server tree and then the AlwaysON High Availabilty and Availability Groups and then Availability Databases
- Right-click on the database and select “Resume Data Movement”
- Click OK and exit
