Menu
I have a database in SQL Azure which is being actively replicated to 2 different regions. They are both read-only accessible (tested from SQL management studio by connecting to their respective servers) but what connection string do I need to use for my application to use automatic failover/fallback and/or to use the readonly instance for read and the master instance for write?
509
This is connection string that worked for me. Server=tcp:[serverName]. database.windows.net;Database=myDataBase; User ID=[LoginForDb]@[serverName];Password=myPassword;Trusted_Connection=False; Encrypt=True; This also works.
In the Azure portal, browse to the primary database in the geo-replication partnership. Scroll to Data management, and then select Replicas. In the Geo replicas list, select the database you want to become the new primary, select the ellipsis, and then select Forced failover. Select Yes to begin the failover.
Navigate to the database blade in the Azure portal and, under Settings, select Connection strings.
You can now create a failover group and set up auto-failover (currently in preview) and then you will have a read/write listener connection string
Go to the Azure SQL Server (not a specific database) -> Failover Groups -> Create and fill in the required info - servers, DBs and policy
After the group is created you will have a listener connection string you can use across both DBs in the failover group configuration details
For more info, please check https://docs.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-overview#auto-failover-group-capabilities
106
Your application has to connect in the same way to the readable secondaries as you did from SSMS: You have to use the connection string of the server that holds to replica.
Failovers can be triggered use T-SQL commands, PowerShell and through the management portal. Check these two documentation articles:
Active Geo-Replication for Azure SQL Database Terminate a Continuous Copy Relationship
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
