| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

Failed to update database because the database is read-only. Error Code 3096.
by BF (Principal Consultant; Architecture; Engineering)

Environment Setup:

- Production Microsoft SQL Server 2014 SP1 hosting a main OLTP database
- Transactional Replication Publisher configured locally
- Transactional Replication Distributor configured locally

- Azure SQL Database Geo-Replication
- Azure SQL Database Primary located in EAST US 2
- Azure SQL Database Secondary located in CENTRAL US


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

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
RAISERROR('Transactional Replication Error Alert - The database is read-only!', 16, 1)