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

  • How do you perform those backups? With a SQL-Server Maintenance-Plan, a custom script or a external Backup-Tool? – MHeld May 15 '18 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 – Arwa May 16 '18 at 9:47
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.