Environment Setup:
On-Premise:
- Production Microsoft SQL Server 2014 SP1 hosting a main OLTP database
- Transactional Replication Publisher configured locally
- Transactional Replication Distributor configured locally
Azure:
- Azure SQL Database Geo-Replication
- Azure SQL Database Primary located in EAST US 2
- Azure SQL Database Secondary located in CENTRAL US
Issue:
If issue this Powershell Command - Set-AzureRmSqlDatabaseSecondary - to fail-over to a Azure Geo-Replication Secondary, the On-Premise
Transactional Replication will start logging with this error message in the Distribution database, MSrepl_errors table:
"Failed to update database because the database is read-only."
The problem is the On-Premise Transactional Replication is configured with the Azure Subscriber based on a ServerName. So, after the Azure SQL
Database fail-over is completed, the new Primary in Central is now the Read-Write Node, while the new Secondary in EAST US 2 is now the
Read-Only Node. And Replication is trying to update commands on a Read-Only Node, which is not allowed.
To handle this Azure SQL Database Failover we updated our Azure Web App config with the -failover parameter, plus setup alerts on the On-Premise
SQL Server, using SQL Jobs, to detect the Error 3096. Once that Error is alerted on, we need to either fail back Azure or re-configure
Transactional Replication to connect to the new Subscriber / new Read-Write Node.
SQL Job Alert:
Use Distribution
go
Declare @RepErrorsNew int --check last 5 mins; sql job executes every 5 mins
Select @RepErrorsNew = (select top 1 ID from Distribution.dbo.MSrepl_errors with (nolock)
where error_text like '%the database is read-only%'
and [time] > dateadd(mi,-15,getdate()) order by [time] desc)
If @RepErrorsNew IS NOT NULL and @RepErrorsNew > 0
Begin
RAISERROR('Transactional Replication Error Alert - The database is read-only!', 16, 1)
End
|
|
|
|
|
|