Availability Group Secondary Node Backups

cmthomas68

Hi, I'm trying to run a backup from a secondary node in new cluster, however when I run the backup command, it is failing with a connection error to the primary node, even though I am running it on the secondary.



I'm not sure why it is even trying to talk to the primary when running from the secondary?

We do run a custom port number for our SQL connections, so if it is trying to connect on the default port, it will fail. I have also tried setting this parameter:


Which doesn't seem to have made a difference. Is there something additional I need to do? We can run sql native backups from the secondaries without a problem, just not redgate backups like we would like to use.

petey2

By design, SQL Backup checks the rights of the user backing up the database, and also retrieves the database size and other details from the database that is being backed up.

If your secondary replica does not allow connections, SQL Backup attempts to get those details from the primary replica.  As you pointed out, this is failing because the instance the primary replica is running on a non-default port.

Can you confirm that your secondary replica does not allow connections?  Thanks.

cmthomas68

Both secondary instances are both set to allow connections, however none of the databases in the AG are set to read only.

cmthomas68

The default database for the service account we use is also set to master.

petey2

Could you please run this query on the instance hosting the secondary replica and post the results?  Thanks.

SELECT c.secondary_role_allow_connections, d.primary_replica 
FROM master.sys.databases a 
INNER JOIN master.sys.dm_hadr_availability_replica_states b ON a.replica_id = b.replica_id 
INNER JOIN master.sys.availability_replicas c ON a.replica_id = c.replica_id 
INNER JOIN master.sys.dm_hadr_availability_group_states d ON b.group_id = d.group_id 
WHERE a.name = 'ActuarialUpdates'
AND b.role = 2 
AND c.replica_server_name = @@SERVERNAME

cmthomas68

So we don't have read enabled on the secondaries, intentional design for licensing/etc. Just wondering can we change the way it connects to the primary so that it uses the non standard port number? I've checked the documentations and can't find anything...

petey2

Could you please raise a support ticket so that we can follow up on this internally?  Thank you.