Hi, I configured GR and Failover group for my Azure Sql databases - could you please help with following questions ? I can run read queries on secondary databases configured for either GR or Failover groups but I am unable to see them in SSMS. I couldn't understand the reason behind it. I can make listener name by appending Azure SQL instance name, can I get both listeners (read-write and read only) details from Azure portal? Can I drop the primary database even if it is configured for GR or Failover group ? If yes, I think it should be mandated to first drop GR or Failover group than allow dropping of primary database. Appreciate your insightful response. Thank you.

Hi @anil kumar , welcome to Microsoft Q&A forum. Please find the replies below: 1) You can set the 'ApplicationIntent=ReadOnly' in SSMS while connecting to the database and then you can use the below query to check if db is read-only: SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability'); 2) I could not locate anything on Azure portal but yes we can append the Azure SQL DNS name for listener. For secondaries we need to append 'secondary' as well. 3) Yes we can drop the primary database but it will remove any linking between primary and secondary replicas. Better way to do that will be as mentioned in the article . Please let me know if this helps or we can discuss further. If answer helps, you can mark it as 'Accept Answer'

question on geo-replication and Failover groups on Azure SQL database

Accepted answer

Anurag Sharma 17,591 Reputation points

2021-02-05T07:50:27.373+00:00
Hi @anil kumar , welcome to Microsoft Q&A forum.

Please find the replies below:

1) You can set the 'ApplicationIntent=ReadOnly' in SSMS while connecting to the database and then you can use the below query to check if db is read-only:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

2) I could not locate anything on Azure portal but yes we can append the Azure SQL DNS name for listener. For secondaries we need to append 'secondary' as well.

3) Yes we can drop the primary database but it will remove any linking between primary and secondary replicas. Better way to do that will be as mentioned in the article.

Please let me know if this helps or we can discuss further.

If answer helps, you can mark it as 'Accept Answer'
Please sign in to rate this answer.
anil kumar 1,641 Reputation points

2021-02-05T10:01:42.497+00:00

Thank you Arurag for your response !!

Regarding #1 - I was wrong. Secondary databases configured for either GR or Failover groups can be seen in SSMS.

Regarding #2 - I have configured FG on Azure SQL database so tried connecting same using <fog-name>.database.windows.net but got unable to configure firewall error so connected to primary server and set up the firewall rules and then connected to AG listener name successfully.

Regarding #3 - I think directly dropping primary or secondary databases configured for GR or FG should be prohibited. First GR or FG needs to be broken before allowing database be dropped like it happens in case of on premise server. Can we delete a DB configured for AG in on premise server ? If yes, then may be we are good.

Looking forward for your response on #3.....

Anurag Sharma 17,591 Reputation points

2021-02-09T05:03:48.827+00:00

Hi @@anil kumar , thanks for reverting back and sorry for the delay.

On-premise version of SQL Server and Azure SQL differ in some features. On Azure SQL we can anytime delete the primary database without worrying about the relationship it has with secondary databases like geo-replication. As the automatic sync happens, this secondary database could now be used as independent database.

I have not tried the same with Availability Group on On-premise version as of now. Please let me know if this help or else I can try with AG as well.

anil kumar 1,641 Reputation points

2021-02-09T10:26:04.323+00:00

Hello Anurag,

Thank you for taking time to help !! I tried dropping primary database with failover groups set up for it, my database got dropped without warning.

We are on the same page on "On-premise version of SQL Server and Azure SQL differ in some features". If Microsoft could provide consistent experience in both SQL Server on-premise and Azure SQL, it is going to be very beneficial for all.

Let's say we can delete primary database in GR/FG without removing/breaking geo-replication:

What could be the use of that secondary database ? Even if primary database was deleted in error or other wise, we can not just set up GR with existing secondary database after recovering primary database in Azure. For GR setup, secondary db must be created newly and old secondary can not be used for that. May be same for FG also, couldn't confirm that.

anil kumar 1,641 Reputation points

2021-02-09T10:26:53.447+00:00

Another question is can secondary database still be retained by making it mandatory to first break GR before primary database can be deleted? We know the answer for it. For me, it seems more logical.

I think in on premise we always have to drop Availability Group set up before dropping primary database.

Thanks again for your time - will mark your response as answer.

Anurag Sharma 17,591 Reputation points

2021-02-10T06:37:13.733+00:00

Hi @anil kumar ,

What could be the use of that secondary database : Yes rightly said. If primary is deleted, we cannot set it up as GR as GR creates a new readable database every time. This makes sense as well because creating a secondary means the schemas must match as it is. If we try to link any existing database as part of GR, we never know the schemas match and it will be an extra step that needs to be carried out.

Can secondary database still be retained by making it mandatory: I think this is objective. Secondary replicas could be still stand alone databases that can be utilized to run different kind of analysis after delinking primary and secondary. There could be multiple use cases in real time based on different requirements where users want to retain all copies of databases even after deleting the primary.

Really appreciate you bringing these queries on the front and I can take it up to the product group in internal discussions.

anil kumar 1,641 Reputation points

2021-02-10T09:38:57.153+00:00

Thank you Anurag for your time and valuable help - Appreciate it !!

Stay blessed and happy....

Anurag Sharma 17,591 Reputation points

2021-02-11T04:52:25.037+00:00

Hi @anil kumar , thank you and we are really happy to know we were able to help you out.
Sign in to comment

Share via

question on geo-replication and Failover groups on Azure SQL database

0 additional answers