Failover in Azure SQL Database

Question

Hi Team,

I have geo replicated an Azure SQL Database(which is in elastic pool) to a secondary Database. My primary db is worked on by Boomi tool. By giving name of the server and dbname, Boomi tool connects to primary azure sql database. Now when I will be doing failover of primary database so that secondary will become primary and vice-versa. Based on this can you please answer the following -

1. As per understanding, after geo replication secondary db is read only.

Once failover happens secondary db will become primary, it will not be readable any more and DML operation would get performed on it. Can you please confirm.

1. Once failover happens primary db will become secondary and it will become readable. Now the replication will travel from new primary to new seconadt db, both dbs would still be in sync. Can you please confirm.
2. After failover there would be no impact on initial primary db, it will become primary again once we again do the failover.
3. Is there a setting in Boomi tool(such as using an if else statement) that I could use so that once failover happens and secondary db becomes primary db, using the expression (or if else statement), Boomi tool also starts pointing to new primary db (after failover)?

Thanks,

Salil

Answer 1

Hello Salil , hope having a good day! , please find below detail answer to your questions

It seems like you have a good understanding of the basic concepts around Azure SQL Database geo-replication and failover scenarios. Let me confirm and clarify your points:

Read-Only Secondary Database: Yes, after setting up geo-replication, the secondary database is typically set to read-only mode. This means that you can't perform any DML (Data Manipulation Language) operations like INSERT, UPDATE, or DELETE on the secondary database. It's intended to be used for reporting and querying purposes only.

Failover Impact on Secondary: When a failover occurs, the secondary database becomes the new primary database. This new primary database will indeed allow DML operations. So, your understanding is correct that after failover, the previously read-only secondary will become the new primary, and DML operations can be performed on it.

Sync After Failover: After a failover, the new primary database will be fully operational and in sync with the data from the original primary database. Any changes made on the original primary database before the failover occurred will be replicated to the new secondary database (which was the original primary before failover). So, both databases will still be in sync after failover.

Impact on Initial Primary Database: The initial primary database, which now becomes the secondary after failover, will not be impacted negatively. It will continue to operate as the secondary database and will remain read-only until another failover occurs.

1. Boomi Tool Configuration for Failover: Whether Boomi Tool can automatically handle failover scenarios depends on the specific capabilities of the tool. , I don't have specific information about Boomi Tool's integration with Azure SQL Database failover.

If Boomi Tool doesn't natively support automatic redirection after a failover, you might need to implement a failover detection mechanism in your integration process. This could involve monitoring the health of the primary database and, upon detecting a failover, updating the Boomi Tool's connection configuration to point to the new primary database. This could potentially involve using if-else statements or other conditional logic within your integration workflows.

However, I will recommend referring to the latest documentation or contacting Boomi support to get the most accurate and up-to-date information on how to handle failover scenarios in your specific use case with Boomi Tool.

Remember that technologies and tools can change, so always consult the latest documentation or support resources for accurate guidance.

1. Azure SQL Database Documentation: Go to the Azure SQL Database documentation to find comprehensive information about all aspects of Azure SQL Database.
2. Geo-Replication: To learn about Azure SQL Database geo-replication, you can specifically look for documentation on Azure SQL Database Geo-Replication.
3. Failover: Information on failover scenarios and operations can be found in the documentation section about Azure SQL Database Failover.
4. Boomi Integration: For integrating Boomi Tool with Azure SQL Database failover, you might need to refer to Boomi's official documentation or resources related to database integration. Start by searching on the Boomi website or checking their support resources for information on handling database failover scenarios.

Please let me know if anything else needed , Thanks!