October 5, 2017 at 9:26 am
When I fail over from my primary to my secondary I receive below error message. I check and the server I fail over to shows it being the Primary now so why am I not able to connect to it. Am I missing something?
Could not retrieve configuration profile.
Msg 976, Level 14, State 1, Line 65536, Procedure
The target database, 'XXXXXX', 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.
October 5, 2017 at 9:55 am
Are you definitely trying to connect to the new Primary? Is there a DNS alias (or something) pointing you at the original Primary (now Secondary)?
October 5, 2017 at 11:52 am
Connect to your listener name and run SELECT @@SERVERNAME to confirm you are on your secondary node.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
October 6, 2017 at 4:28 am
mpoulter35 - Thursday, October 5, 2017 9:26 AMWhen I fail over from my primary to my secondary I receive below error message. I check and the server I fail over to shows it being the Primary now so why am I not able to connect to it. Am I missing something?Could not retrieve configuration profile.
Msg 976, Level 14, State 1, Line 65536, Procedure
The target database, 'XXXXXX', 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.
as already suggested it sounds like you're trying to connect to what was previously the old primary.
Do you have a listener configured?
have you configured reqadonly routing?
What are the settings when in the secondary role for each of the replicas?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 6, 2017 at 6:12 am
This was a new build of SQL standard edition. From what I have been reading with SQL standard edition the secondary can only be read only. When I fail over I connect to the listener and run SELECT @@SERVERNAME it shows I am connect to the new server and it does shows primary when I go to properties on the AG group but I still get error when I try to connect when launching the application. Since 2016 standard doesn't allow the secondary to be read only do I need to configure read only routing.
October 6, 2017 at 6:19 am
SQL STD AG is a basic availability group, with no read only routing. Still sounds like your application is connecting to a node name and not a listener name.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
October 9, 2017 at 9:53 am
mpoulter35 - Friday, October 6, 2017 6:12 AMThis was a new build of SQL standard edition. From what I have been reading with SQL standard edition the secondary can only be read only. When I fail over I connect to the listener and run SELECT @@SERVERNAME it shows I am connect to the new server and it does shows primary when I go to properties on the AG group but I still get error when I try to connect when launching the application. Since 2016 standard doesn't allow the secondary to be read only do I need to configure read only routing.
The secondary in a Basic Availability Group is not readable at all.
Check your application connection string; it appears to be hard-coded to the original Primary (readable) replica.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply