1

We do have two DB and two Application servers for high availability purpose .

Regarding SQL Server:

  1. We have full and transaction log Backup .

  2. We created a high availability group and define the two DB servers we have.

    • Under the availability group there is section for the availability replicas. Previously the main server where the full backup maintained was the primary replica, I noticed that two weeks back the replica has changed, the primary becomes a secondary. As I read about this issue, the full backup is not supported on a secondary replica unless it is configured as a copy_only. I do change the configuration of the full backup to a copy_only and I created one maintenance plane with a full backup in the primary replica which was the secondary before.

Now the issue is that:

  • The secondary replica (main server) is not taking the full backup even after I changed it to a copy_only, it shows below error : This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally. Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  • The full backup on the current primary replica is not working also , it shows below error : Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection

Improve this question
2
  • How do you perform those backups? With a SQL-Server Maintenance-Plan, a custom script or a external Backup-Tool? Commented May 15, 2018 at 5:34
  • @MHeld through maintenance plan. I shut down the primary replica server and the secondary becomes primary as it was before. then I turned on the server again. Now the backup is performed successfully. if anyone have an idea and can explain the situation to me and what to do for such cases in the future, I would really appreciate it and it may help others. I did all the possible solutions as mentioned above but non of them works Commented May 16, 2018 at 9:47

1 Answer 1

Reset to default
0

A SQL Server Maintenance plan is not aware of a failover inside an Availability Group. You might want to experiment with the Backup Preferences options (e.g. set it to Any Replica).

I would recommend you to get in touch with Ola Hallengren's Maintenance Solution which also includes Backup-Capabilities. In an AlwaysOn-Scenario, you would implement those scripts on both sides. The script itself is aware of the AG-states and will follow the Backup Preferences configured at the AvailabilityGroup. This HowTo explaines the required steps.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.