Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



Failed to update database because the database is read-only. Error Code 3096.
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-10-03








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