Recently I was watching a presentation on how to scale performance in your SQL Server environment and one of the suggestions was setting up Availability Groups (AGs) and having read-intent connections that would query the secondary and not the primary. It's not a bad idea, and the SQL Native Client (and other drivers) support this and make it easy to implement.

The pattern of using multiple connections in an application, one for reads and one for writes, has been suggested often. However, in practice, I've rarely seen this work. Apparently having a connection variable, named dbConn, for writes and a second one, named dbConnReadOnly, for reads is too complex for most developers or teams.

Or maybe the idea of having to pick the right access point is a human problem? I've seen no shortage of problems in restaurants when we have specific "in" and "out" doors. Lots of people go through the wrong one and we end up with plates and food on the floor. Even broken noses or fingers at times. Perhaps I shouldn't pick on software developers too much.

How many of you use two connections from apps? Meaning, do you think about reads and writes in separate connections. Even if you read and write from the same database, this can be a nice practice that future proofs code. It's a small change, but it gives you room to grow if you get a read replica for analytics or reporting.

Of course, you could take it too far with different connections for different "services", aiming for a microservice-style architecture. We could have dbUser for user stuff and dbOrders for the business side, and other connections for other services. I wouldn't do that, as I think many of us will get confused, and we'll often be doing two different type of service things in the same code. If I need something from a customer to write an order, do I have two connections in my method? I could, but I bet lots of developers would try to re-use a single one.

Plus, if developers get into trouble with two connections, then what will they do with 5 or more? There are lots of ORMs that might even support this, or if they do, not make this easy to code.

I've always liked the idea of separating reads and writes, but maybe the better solution is using one connection whenever we have simple CRUD work and another one for any sort of complex querying or reporting. That would make more sense as I suspect many of us will eventually offload reporting or analytics in some way to another system. A Delta Lakehouse of some sort seems likely if the current trend takes hold in more organizations.

Let me know today if you used (or have tried) different connections for reads and writes.