0

I am trying to use the Geo-replicate for databases in Azure.

But if I have a SQL Server, lets say S1 with database DB1 sitting in subscription S1, then in order to geo-replicate the database I need to create a brand new SQL Server ? So does that mean if i have app services that rely on the database , then does that mean i need to change the connection settings in Configuration for that App Service ?

Peter Bons
  • 26,826
  • 4
  • 50
  • 74
Jennifer
  • 31
  • 6

1 Answers1

0

Yes, you need a server for the secundary database, it could be a new one or an existing one.You can select any region for your secondary server, but it is recommended to use the paired region.

Connections to your existing database will still work and there is no need to change the connection settings in Configuration for that App Service unless a failover is happening, either manual or automatically. In that case the secundary server becomes the primary server and you need to switch connection strings to point at the new primary.

Geo replicaton is covered here and here

Addressing the comments:

Also i have the primary server in a Region North Central US , now is it still safe or does it defeat the purpose to create a new sql server in the same region ? Reason being is that access to the clients would be quicker than lets say creating in Canada

Yes it defeats the purpose. The whole point is that if there is a failure in that region (North Central US) a secondary in that same region will also fail. Sure, clients will need to connect to another region potentially increasing latency but that is still better than no connectivity at all and only temporary until the primary region is restored and fully functional again.

Now North America has several Azure regions, including West US, Central US, South Central US, East Us, and Canada East as per Microsoft documents. So if the primary server is in Central US , I assume it would make sense to put the secondary in one of the regions of North America , maybe South Central US , would that make sense ?

Correct. The paired region North Central US of is South Central US and it is recommended by Microsoft to use the paired region.

[...] If i do then the configuration in my app service has a settings of SRV1 and database as DB1 . If i set the database DB2 as failover does the setting in the config change ? Thats what is confusing me a little. Thanks

Only when a failover takes place you need to update the settings in the config to use the secondary database connection string.

Can you elaborate a bit more on your use case? Have you considered auto-failover groups?

Peter Bons
  • 26,826
  • 4
  • 50
  • 74
  • I just dont get if i can have a Server in one location do a replication and it asks for a server , do i pick the same server ? Where will it replicate the database ? – Jennifer Mar 01 '23 at 03:22
  • @Jennifer No you don't pick the same server! The whole thing of geo replication is that, in case an Azure region fails, business continuity is provided by the database hosted on a server in a different Azure region. Putting the replica database on the same server as the source kind of defeats that purpose. – Peter Bons Mar 01 '23 at 06:32
  • Ok so if i can pls run through something . I have a subscription and in that subscription is a RG named Test in that RG is a server named SRV1 and in that SRV1 is a database named DB1. Now I have a different RG named RG2 in the same location as the RG . If i want to replicate the DB1 i can replicate as DB2 , but it needs to sit on a Server so do i need to create a new Server ? . If i do then the configuration in my app service has a settings of SRV1 and database as DB1 . If i set the database DB2 as failover does the setting in the config change ? Thats what is confusing me a little. Thanks – Jennifer Mar 01 '23 at 21:40
  • Is the replication kept up to date with the primary database . What i mean if a replication is taken today and the in the Primary database 1000 records are added , is that replicated to the secondary , is there incremental replication ? – Jennifer Mar 02 '23 at 06:22
  • Also i have the primary server in a Region North Central US , now is it still safe or does it defeat the purpose to create a new sql server in the same region ? Reason being is that access to the clients would be quicker than lets say creating in Canada – Jennifer Mar 02 '23 at 06:24
  • Now North America has several Azure regions, including West US, Central US, South Central US, East Us, and Canada East as per Microsoft documents. So if the primary server is in Central US , I assume it would make sense to put the secondary in one of the regions of North America , maybe South Central US , would that make sense ? – Jennifer Mar 02 '23 at 06:42