Allow for FHIR server to run on read only db replica

[nlorenzen1]

We are running Hyperscale SQL on top of an STU3 server with a heavy write work load. We have noticed that during peak business hours where writes are the heaviest, read performance is inconsistent and can degrade as much as 60%.

Our Hyperscale SQL server has a read replica that we think would help read performance during peak business hours, but the app service fails to start when a read-only connection string is used:

Server=tcp:xxxx.database.windows.net,1433;Initial Catalog=FHIR;Persist Security Info=False;ApplicationIntent=ReadOnly;User ID=xxxxx;Password=xxxxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Could something be changed to allow the web app to start with read only db access?

[johnstairs]

Excellent suggestion.

[CaitlinV39]

@CaitlinV39 create a user story

[johnstairs]

@rbans96, are we planning anything for a FHIR server to "lock" a particular schema version, to signal it is in use?

[rbans96]

@rbans96, are we planning anything for a FHIR server to "lock" a particular schema version, to signal it is in use?

Not yet, As of now the max version in the SchemaVersion table is selected as the current(in use) schema version. Thanks for the good suggestion. Probably, we can plan to acquire HOLDLOCK in the SchemaVersion table for the specific version in use. And whenever the schema upgrades are made the lock is released from the previous version and acquired on the upgraded version.

[CaitlinV39]

AB#76668

[SergeyGaluzo]

Ability to read from replica was added to FHIR server. Today there is a ReplicaHandler property in the SqlRetryService class that can be used to direct some read traffic to read-only replicas. Percentage of traffic is controlled from the database by value of Parameters.ReplicaTrafficRatio. By default, 0% of traffic is directed to replicas. If ReplicaTrafficRatio = 1, all traffic is routed to replicas. Values between 0..1 can be used to control exact percentage of traffic. For example, if database has 1 replica and value is 0.5, then 50% of reads are routed to read-only replica and 50% to read-write one. If database does not have replicas, any value (0..1)) in the database, does not affect FHIR server behavior.

[EXPEkesheth]

@nlorenzen1 - please see the comment from Sergey above. Let us know if you have any additional questions/ comments.

[EXPEkesheth]

@nlorenzen1 closing the issue as question is answered