Availability Group Secondary Node Backups

• 
  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.

  petey2 July 05, 2023 10:48

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

  cmthomas68 July 05, 2023 11:13

  - 0 +
• 
  The default database for the service account we use is also set to master.

  cmthomas68 July 05, 2023 11:15

  - 0 +
• 
  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

  petey2 July 05, 2023 13:18

  - 0 +
• 
  
  
  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...

  cmthomas68 July 06, 2023 01:46

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

  petey2 July 06, 2023 03:18

  - 0 +

Add comment

Please sign in to leave a comment.