Quick Tip: Taking advantage of Read-Only SQL Server replicas in your C# application
I've been working in and around SQL for over 3 decades.
I am currently working on making our business intelligence systems self-driven by using an event-based architecture. This involves providing curated data in some of the largest SQL Azure Analysis Services cubes ever built (over 6 billion records in 1 table alone).
I've developed several APIs leveraging a serverless Azure architecture to provide real-time data to various internal processes and projects.
Currently working on Project Dormouse; Durable select messaging queue that enables polling processes to extract specific messages.
If you happen to be using C# there is a very easy way to set your application up to take advantage of Read-Only replicas.
Using this connection string: (Some portions were left out for brevity)
"Server=tcp:<servername,port>;Initial Catalog=<database name>;ApplicationIntent=ReadOnly;"
The option ApplicationIntent=ReadOnly tells the system to first look for a replica when asking for a connection, but if there is no replica, then connect to the existing primary(read/write) server.
I have 2 connection strings in my APIs, one that includes that option and one that doesn't. This helps balance the calls between all the available backend resources without doing any special work within the calls themselves.
