1

Thank you in advance...

I created a failover group and want to test a failover. I force a failover through the failover group tab and it says it is successful and the Secondary DB is now the Primary. However, when I try to write to it I get this error:

Error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Failed to update database "PTextingDvlp" because the database is read-only.

My understanding is that once the secondary DB becomes primary it is read/write.

Does anyone know the reason for this?

Improve this question

2 Answers 2

Reset to default
2

When you choose to use failover with Azure SQL you may not notice that the connectionstring changes.

So you will need to use the correct connectionstring, the one that points to the read/write listener endpoint.

You can find this by in you azure portal at:

Dashboard > All resources > yourgeoname - Failover groups > yourgeoFailover

Scroll to the bottom and you'll see your Read/Write listener endpoint and your Read-only listener endpoint.

Change your connectionstrings to use one of these, if you want to write then choose the Read/Write one. This will always point at the active db no matter which failover is used.

Additionally, if you need to simply Read data then you can use the Read only connectionString as load balancing. Thereby reducing load on the Read/Write db.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

To clarify, you need to change the endpoint in your connection string from myserver.database.windows.net (where myserver is the ORIGINAL sql server) to the FAILOVER GROUP! So, myserverfo.database.windows.net. That was not clear to me but Alex Stephens got me to where I could figure it out. Thanks ALEX!
1

When you failover, as you mentioned, the primary DB becomes secondary and also read only. The connection string to the new primary changes as a result of the failover.

It is more that likely that your connection string is still pointing to the original DB which is now read only.

Solution: Please check that you have updated the connection string to point to the new primary to be able to make the updates.

Reference: Active geo-replication and auto-failover groups

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.