Background

Error code 45174 occurs when an attempt is made to perform a write operation on an Azure SQL Database that is in read-only mode. This can happen in scenarios like geo-replication, where secondary replicas are read-only, or when a database has been explicitly set to a read-only state. In such cases, any operations that modify data (e.g., INSERT, UPDATE, DELETE, CREATE, etc.) are not permitted, while read-only queries (SELECT) are allowed.

Summary Table

Error Explanation

The error message for error 45174 typically reads:

Error 45174: The operation cannot be performed because the database is in read-only mode.

This error occurs because the database is in a state where write operations are restricted, either due to replication, maintenance, or specific configuration that sets the database to read-only mode.

Common Causes

1. Geo-replication: The database is a secondary replica in a geo-replication setup. Secondary replicas are always in read-only mode to ensure data consistency with the primary database.
2. Explicit Read-only Configuration: The database has been explicitly set to read-only mode by the database administrator.
3. Maintenance or Failover: The database might temporarily be in read-only mode due to maintenance or during a failover operation.
4. Database Recovery: If the database is in recovery mode after an issue, it may be set to read-only until the recovery process completes.

Steps to Troubleshoot and Resolve Error Code 45174

1. Check if the Database is in Geo-replication

If the database is part of a geo-replication setup, the error occurs because the database is a secondary replica, which can only handle read operations. Secondary replicas in Azure SQL Database are designed to provide high availability and disaster recovery, but they are not writable.

Steps to Check Geo-replication Status:

1. Go to Azure Portal.
2. Navigate to your SQL Database.
3. Under Settings, select Geo-Replication.
4. Check the role of your database. If the database is listed as a Secondary, it will be in read-only mode.

In this case, the solution is to perform any write operations on the primary database, as the secondary cannot be modified.

Example of Geo-replication Status:

• Primary Database: writable
• Secondary Database: read-only

If you try to run an INSERT or UPDATE statement on the secondary, you will receive error 45174.

Solution

Ensure that all write operations are performed on the primary database. You can failover to the secondary if necessary and make it the primary.

2. Check if the Database is Explicitly Set to Read-only Mode

The database may have been manually configured to read-only mode by an administrator. You can check the read-write status of the database using the following query:

SELECT name, is_read_only 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

If the is_read_only column returns 1, the database is in read-only mode.

Example Output:

name          is_read_only
YourDatabase  1

Solution:
If the database was set to read-only by an administrator, you can change it back to read-write mode with the following command (if you have sufficient permissions):

ALTER DATABASE YourDatabaseName
SET READ_WRITE;

This command will change the database from read-only to read-write mode, allowing you to perform modifications.

3. Check for Maintenance or Failover

Sometimes, Azure SQL Database might enter a read-only mode temporarily during system maintenance, backups, or failover operations. In this case, the database will be in read-only mode until the operation completes, after which it should return to its normal state.

Steps to Check Resource Health:

1. Go to Azure Portal.
2. Navigate to your SQL Database.
3. Under Support + Troubleshooting, click on Resource Health.
4. Look for any ongoing maintenance or failover events.

If maintenance is the cause, you will need to wait until the operation completes. Afterward, the database should return to its normal state, and write operations will be allowed again.

4. Check for Database Recovery

If the database encountered an issue (e.g., corruption, unexpected shutdown), it might be in recovery mode and set to read-only while Azure SQL Database attempts to recover the data.

You can check the state of the database using the following query:

SELECT name, state_desc 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

If the state is RECOVERY, the database is in the process of recovering. Write operations will be blocked until the recovery process completes.

Solution:
Wait for the recovery process to finish. Once the database is back online, it will return to the normal read-write mode.

5. Failover to Secondary (in Geo-replication)

If you are working with a geo-replicated database and need to perform write operations on the current secondary database, you can failover to the secondary and make it the new primary. This will allow write operations to be performed on the previously read-only secondary.

Steps to Perform Failover:

1. Go to Azure Portal.
2. Navigate to your SQL Database.
3. Under Geo-Replication, select the secondary database.
4. Click on Failover to promote the secondary to primary.

Once the failover is complete, the former secondary database will now be writable.

Workarounds

• Read-only Queries: If the database is in read-only mode due to geo-replication or maintenance, ensure that you are running only read queries like SELECT until the database returns to read-write mode.
• Perform Write Operations on the Primary: If the database is a secondary replica in geo-replication, all write operations must be performed on the primary database.

Solutions

1. Perform Writes on Primary Database: In a geo-replication setup, ensure that all write operations are directed to the primary database, as secondary replicas are read-only.
2. Change Database to Read-write Mode: If the database was explicitly set to read-only, run the ALTER DATABASE SET READ_WRITE command to allow modifications.
3. Wait for Maintenance or Recovery: If the database is in read-only mode due to maintenance or recovery, wait for the process to complete, after which the database will return to its normal state.
4. Failover to Secondary: In geo-replication scenarios, you can failover to the secondary database to make it the primary and allow write operations.

Example Scenario

Suppose you’re trying to insert sales data into your database using the following query:

INSERT INTO Sales (SaleID, ProductID, Amount) 
VALUES (1, 1001, 50);

You receive the following error:

Error 45174: The operation cannot be performed because the database is in read-only mode.

Step 1: You check whether the database is part of a geo-replication setup by querying the role of the database in the Azure Portal. You find that the database is a secondary replica in geo-replication.

Step 2: Since the secondary database is in read-only mode, you cannot perform write operations on it.

Step 3: You perform the insert operation on the primary database instead:

INSERT INTO Sales (SaleID, ProductID, Amount) 
VALUES (1, 1001, 50);

This query succeeds because the primary database is writable.

Step 4: If necessary, you can failover to the secondary database to promote it to primary status, allowing it to accept write operations.