How to route different db requests to read only instance

Question

We have our primary database in Europe, and it is replicated using azure to a closer region as a readonly instance. This costs us quite a lot every month and we are only using it to he able to fail over in a disaster situation. We are also limited in sql scaling, as we have to scale both instances, even thoguh the readonly instance sits and does nothing..

However is there not a way we can intelligently use it for read queries? So I want to somehow make a read only db context and it's it's reading, use the close db.

Use two data sources, one for each one. Use the secondary one for SELECT queries such as reporting. This is the same strategy of Aurora database at AWS. You have two endpoints: one for normal read-write, and the secondary one for read-only. Your app needs to set two connections separately and decide which connection to use per query. This is not automatic. — The Impaler
– The Impaler, Commented Feb 20, 2024 at 4:25
Which dbms are you using? (This isn't really related to the <sql> language.) — jarlh
– jarlh, Commented Feb 20, 2024 at 9:24
Your question has nothing to do with SQL or Entity Framework. I think you should remove irrelevant tags and add tags that explicitly indicate the technologies used. — Alexander Petrov
– Alexander Petrov, Commented Feb 20, 2024 at 10:44

David Browne - Microsoft · Accepted Answer · 2024-02-20 18:27:29Z

-1

An easy way to do this is to have a MyDbContext and a MyDbContextReadOnly, each configured with an appropriate connection string.

One can be a subclass of the other, or they can both be subclasses of a common custom DbContext.

edited Feb 20, 2024 at 18:27

answered Feb 20, 2024 at 15:45

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

How to route different db requests to read only instance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related