This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
First published on MSDN on Nov 20, 2018If you try to create more than 4 geo-replicas in the Portal it will fail with the error message " Replication limit reached. The database 'XXXXX' cannot have more than 4 replication relationships. "
There are 3 ways to resolve this problem:
Solution 1: C haining (secondary of a secondary)
*Be sure to check supportability below
According to the DOC: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-overview#active-geo-replication-capabilities
Multiple readable secondaries
Two or more secondary databases increase redundancy and level of protection for the primary database and application. If multiple secondary databases exist, the application remains protected even if one of the secondary databases fails. If there is only one secondary database, and it fails, the application is exposed to higher risk until a new secondary database is created.
Note
If you are using active geo-replication to build a globally distributed application and need to provide read-only access to data in more than four regions, you can create secondary of a secondary (a process known as chaining) . This way you can achieve virtually unlimited scale of database replication. In addition, chaining reduces the overhead of replication from the primary database. The trade-off is the increased replication lag on the leaf-most secondary databases.
Sample in Powershell
Connect-AzureRmAccount
$databasename = "TEST"
$primaryresourcegroupname = "RESGROUP"
$primaryservername = "SECONDARYSERVER"
$secondaryresourcegroupname = "RESGROUP"
$secondaryservername = "SECONDARYOFSECONDARYSERVER"
$database = Get-AzureRmSqlDatabase `
-DatabaseName $databasename `
-ResourceGroupName $primaryresourcegroupname `
-ServerName $primaryservername
$database | New-AzureRmSqlDatabaseSecondary ` -PartnerResourceGroupName $secondaryresourcegroupname `
-PartnerServerName $secondaryservername `
-AllowConnections "All"
Get-AzureRmSqlDatabaseReplicationLink `
-DatabaseName $databasename `
-PartnerResourceGroupName $secondaryresourcegroupname `
-PartnerServerName $secondaryservername ` -ServerName $primaryservername `
-ResourceGroupName $primaryresourcegroupname
LinkId : 1647023e-1235-4dc5-be04-448f1fb9cb72
ResourceGroupName : RESGROUP
ServerName : SECONDARYSERVER
DatabaseName : TEST
Role : Primary
Location : West US
PartnerResourceGroupName : RESGROUP
PartnerServerName : SECONDARYOFSECONDARYSERVER
PartnerRole : Secondary
PartnerLocation : East US
AllowConnections : All
ReplicationState : CATCH_UP
PercentComplete : 100
StartTime : 2018-11-13 10:21:55 AM
Pay attention this can lead to an unsupported scenario if you failover to this (Secondary / Primary) you will have more than 4 replicas for 1 primary .
If you fail back to the primary it will still stay in unsupported scenario, and if you want to go back to a supported state you will need to break some replicas and create again
Note
Make sure also to have same SLO on secondaries. So primary could be P11 but you set one geo-secondary to P6 and then another one to P1 (because they were directing lower load to this replica). Unfortunately, this sort of configuration led to the replicas lagging behind, sometimes to the extent of becoming unhealthy when they could not keep up with the log generation load happening on primary .
Solution 2: Read Scale Out
Here we see a supported and more cost effective solution using Read Scale Out
For each PREMIUM server you have 2 endpoints . Considering 1 primary + 4 replicas = 1 READ/WRITE endpoint + 9 READ ONLY endpoints
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-read-scale-out
You need to enable ReadScale on all databases using power shell
$databasename = "teste"
$resourcegroupname = "ResGroup"
$servername = "server"
$database = Get-AzureRmSqlDatabase `
-DatabaseName $databasename `
-ResourceGroupName $resourcegroupname `
-ServerName $servername
$database | Set-AzureRmSqlDatabase -ReadScale Enabled
And use the connection strings
-
Server=tcp:<server>.database.windows.net;Database=<mydatabase>; ApplicationIntent=ReadOnly ;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;
-
Server=tcp:<server>.database.windows.net;Database=<mydatabase>; ApplicationIntent=ReadWrite ;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;
You can test on SSMS changing the options
And you can check if you are on the read/write endpoint and other things like DTU using the TSQL below
* Note: On the geo-replica secondary both endpoints with show as READ_ONLY, however if you open two connection you should not see the other when you query the sys.dm_exec_connections
SELECT
@@SPID,
DB_NAME(),
@@SERVERNAME,
DATABASEPROPERTYEX(DB_NAME(), 'Updateability'),
AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent',
MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent',
AVG(avg_data_io_percent) AS 'Average Data IO In Percent',
MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent',
AVG(avg_log_write_percent) AS 'Average Log Write I/O Throughput Utilization In Percent',
MAX(avg_log_write_percent) AS 'Maximum Log Write I/O Throughput Utilization In Percent',
AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent',
MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent'
FROM sys.dm_db_resource_stats;
SELECT * FROM sys.dm_exec_connections WHERE net_transport = 'TCP' order by session_id DESC
Solution 3: Azure Data Sync
Using this method you can sync between multiple databases, and will work like a merge replication, where all members can be read/write
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data
Be sure to check the limitation on this approach
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data#sync-req-lim