1

I have a SQL Database in Azure, I have geo-replicated. I am simulating a failover by using the "force failover" in Azure. One question , since the app service that connected to the primary has a connection that relies on the sql server that lives in North Central US ,like the following:

"Server=mynthcentralusserver.database.windows.net ....

When i create my secondary sql database backup it requires a server . I dont want to put the server in the same region as the primary (North Central US) as that region I am simulating is down, so if i create a sql server in South Central US , the connection string will obviously change to something like:

"Server=mysthcentralusserver.database.windows.net .... just the server part.

So does that mean i need to manually go and change the app service configuration settings ? Also if the primary location North Central US is back up and I want to move back there , do in I need to go back and change the connection string again to point to the server in North Central US ?

Abbey
  • 153
  • 2
  • 11

2 Answers2

1

If you are using the Failover Groups setting, in combination with Geo-Replication, you would use the "Read/write listener endpoint" (groupname.database.windows.net). This automatically selects the "active/primary" server in the group.

Follow through with your setup (secondary server in another Region), add the replication, then create the Failover Group, and you should see it there.

Bruno L.
  • 391
  • 8
  • 16
  • Microsoft Documentation: https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-configure-portal – Bruno L. Mar 02 '21 at 23:31
  • So what your saying is that if I create a failover group for the primary sql server, the primary db will automatically be replicated. But when I simulate a fail over , I can see the Primary and secondary switch , but will the app service config be automatically changed to point to the new Primary sql server ? – Abbey Mar 03 '21 at 00:07
  • Why when i create a new sql server it doesnt allow to put in a different resource group , as the primary database is on a server in the resource group which is in the location that has failed? – Abbey Mar 03 '21 at 00:50
1

Update:
My Spring app connects to the Failover Group. I did a Failover manually, In the console output, we can see that the Client IP needs to be added to the firewall to access. After that, our app can access the new server.
enter image description here

Cannot open server 'josephserver3' requested by the login. Client with IP address 'xxx.xxx.xxx.xxx' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.

After you created a failover group for the primary sql server, the primary db will automatically be replicated. Then we should connect to the failover group name. we can see the Primary and secondary switch , and the app service will be automatically changed to point to the new Primary sql server. When we create a new sql server it doesnt allow to put in the same location.

If your connection string is as follows:

  • Primary Server : mynthcentralusserver.database.windows.net
  • Secondary Server :mysthcentralusserver.database.windows.net
  • Failover Group Read/write listener endpoint : failover-group.database.windows.net

As @Bruno L. said. Your app service should connect to failover-group.database.windows.net. So you don't need to manually go and change the app service configuration settings.

You can find it in the location shown in the follows picture. enter image description here

Joseph Xu
  • 5,607
  • 2
  • 5
  • 15
  • 1
    This helped , but it obviously doesnt take across any firewall settings ? In order to really test , why cant the secondary be put offline to make sure it really connects to the primary ? – Abbey Mar 03 '21 at 02:13
  • You're right, we need to add firewall rules to the Secondary Server. After added your app service to the rules, when primary down, it will automatically change to point to the new Primary sql server. – Joseph Xu Mar 03 '21 at 02:36
  • 1
    yes thankyou , saw that , manually i can do and it works fine. I was looking at trying to automate everything . I may look at writing a powershell script for copying firewall settings and putting it as a timer based Azure Function. – Abbey Mar 03 '21 at 03:12
  • One other thing .. how often does the replication from Primary to Secondary occur ? Is it configurable ? – Abbey Mar 03 '21 at 05:36
  • According to this [document](https://learn.microsoft.com/azure/azure-sql/database/active-geo-replication-overview#active-geo-replication-terminology-and-capabilities), after you initiate failover group,this synchronization is real-time. – Joseph Xu Mar 03 '21 at 06:05