SI: Add a ApplicationIntent="ReadOnly" option for select queries

[mtrutledge]

Is your feature request related to a problem?

This feature request is related to a performance issue. Hotcakes does a lot of database calls. The majority of these calls are read-related. In modern environments, especially in the cloud, it is not uncommon to have a read-only instance. It would be nice to take advantage of this by adding a ReadOnly connection string and have all Select statements request a read-only instance.

Describe the solution you'd like

Add a connection string in the web.config file that is a Read Only connection string like this:

<add name="SiteSqlServerReadOnly" connectionString="Data Source=(local);Initial Catalog=dnnplatform;Integrated Security=True;ApplicationIntent=ReadOnly;" providerName="System.Data.SqlClient" />

OR

Expose a ReadOnlyConnectionString Property that looks like this:

public string ReadOnlyConnectionString
{
    get 
    {
        return "ApplicationIntent=ReadOnly;" + System.Configuration.ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
    }
}

Adding the ApplicationIntent would have no effect if there was only a single SQL server. Everything would operate normally.

Describe alternatives you've considered

Alternatives may be to heavily use the cache for anything read-related but might be impractical in most environments. The proposed solution gives the ability to scale out if the environment supports it.

[mtrutledge]

Updating the plan. Since Hotcakes uses Entity Framework it would be best to update the Repositories to call something like CreateReadStrategywhich will then call CreateReadOnlyHccDbContext using a ReadOnlyConnectionString as outlined above.