More Questions on geo-replication and Failover groups on Azure SQL database

anil kumar 1,641 Reputation points
2021-02-07T11:58:38.207+00:00

Hello,

I am trying to understand the capabilities/limitations of geo-replication and Failover groups in Azure with respect to Alwayson AG feature of On premise SQL Sever ENT edition.

  1. In on-premise AG setup, secondary database is not charged if read queries are not offloaded to it and SA (software assurance) agreement is in place. I think we would always be charged for secondary databases of geo-replication or Failover groups - Can you please confirm this?
  2. I know about Azure Hybrid benefits, still we would be charged for secondary databases of geo-replication or Failover groups?
  3. Secondary databases of geo-replication or Failover groups would always be in readable mode, weather we need/use it or not?
  4. I know Failover groups initiate automatic failover after 60 minutes grace period. Is there a way not to wait so long for failover - like when we are notified of some issues in primary, trigger some logic app or something to initiate failover without human intervention?
  5. Does Azure portal or some inbuild mechanism (not DMVs actually) show how much my secondary database is lagging compared to primary ?

Appreciate your insightful response. Thank you.

Azure SQL Database
Accepted answer
  1. Navtej Singh Saini 4,216 Reputation points Microsoft Employee
    2021-02-23T00:10:58.167+00:00

    @anil kumar

    In on-premise AG setup, secondary database is not charged if read queries are not offloaded to it and SA (software assurance) agreement is in place. I think we would always be charged for secondary databases of geo-replication or Failover groups - Can you please confirm this?
    Yes. We confirm.

    I know about Azure Hybrid benefits, still we would be charged for secondary databases of geo-replication or Failover groups?
    Yes you will be charged.

    Secondary databases of geo-replication or Failover groups would always be in readable mode, weather we need/use it or not?
    Yes, secondaries can be used for read-only access queries

    Please let us know if you need further help.

    Regards
    Navtej S

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. tibor_karaszi@hotmail.com 4,301 Reputation points
    2021-02-23T08:27:17.587+00:00

    Regarding failover, you can configure those 60 minutes if you want to change this "grace period". Geo replication doesn't have auto-failover, that is only for Failover groups.

    In addition, you can use sp_wait_for_database_copy_sync to ensure that all transactions are committed to the primary database get sent to its active secondary database on the target server. Can be useful when doing a manual failover.