SAP Commerce Cloud and Read-Only Replica
Tags: sap, commerce, performance, database
Published: 2023-06-04 11:01:38

# Introduction

SAP Commerce Cloud introduced Read-Only Replicas as a standard feature for all production environments.
Aim for Read-only data source is to offload batch queries or expensive ones from master data. 
That data-source gives copy of same sized like master. When production runs on Premium P11 master, RO replica will be same, Premium P11 instance running side-by-side with master.
Read-Only replica is available for all Premium DBs in CCv2. That is a standard functionality provided by Azure.

# Product Availability

Feature is available from SAP Commerce Cloud version `1905`+. 
With patches onward: `1905.37`, `2005.21`, `2011.16`, and `2105.6`. (You need to run update (aka 'Migrate Data') operation during deployment). `CronJob` and `Job` models have been extended and usage of replica has been simplified to a flag.

> This patch introduces the support for a read-only replica. The feature includes additional configuration options for the Backoffice search, task engine, Job/CronJob configuration, catalog synchronization, and Solr indexing, access to the database metrics in the Administration Console, and an option to select a data source to run FlexibleSearch queries on.

From: https://help.sap.com/docs/SAP_COMMERCE/eed845124da0491e875df8139c4e6e8c/f18f6a711d07462b80137df6ed533eee.html?version=2005#patch-2005.21

# Added Features

## HAC Hybris Administration Console

Administration Console has been enriched by metrics review and possibility to query particular datasource explicitly.

Tab `Monitoring` > `Database` right now lists all data sources information:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x1c31i608si8nj4ejih8.png)


Tab `Azure SQL` > `Usage` is located DB monitoring and DTU usage. Now it is possible to find two tabs: `Main data source` and `Read only data source`, where additionally it is possible to see other metrics:

- DTU Usage
- Number of DB connections
- Read Only Data Source Redo Queue Size
- Read Only Data Source Secondary Replica Lag


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ursh9rc326pszxpwy94j.png)


## Backoffice

There have been added features for all CronJobs and Jobs to easily set which datasource cronjob should use.
Using navigation go `System` > `Background Processes` > `CronJobs` or `Jobs`. Field called `useReadOnlyDatasource` is a Boolean flag which enables alternative datasource.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/czba0ngeiaietnr0ef4d.png)

### Backoffice Search

When Read Replica is enabled it is also possible to use it in backoffice for all DB querying. Property flag which drive that feature by default is set to `true`.

```bash
backoffice.search.read-replica.enabled=true
```

It is possible to disable Read Replica for particular item types by property too.

```bash
backoffice.search.read-replica.type.codes.exclude=Address
```

Setting that property to `Address` causes that all queries for given type will be fetched from main database.


## SAP Commerce Cloud SDK

It is also possible to use RO replica in code. There is important to keep in mind that update to RO replica is done asynchronously. Updates are visible usually up to XX milliseconds. There is no SLA available and Microsoft assures that most changes are done in one-digit ms time, up to two-digit ms delay.

There are two ways of running query on RO replica.

###  Local Session Context

It is possible to create local session context in code and after execution, remove it. Below code shows the way how it should be executed. That way generates a bit of boiler-plate, especially if you have to add it in multiple places.

```java
final SessionContext ctx;
try
{
    ctx = JaloSession.getCurrentSession().createLocalSessionContext();
    ctx.setAttribute("ctx.enable.fs.on.read-replica", "true");

    final SearchResult<PK> search = flexibleSearchService.search("SELECT {PK} FROM {Product}");
    
    // TODO process the query result
}
finally
{
    JaloSession.getCurrentSession().removeLocalSessionContext();
}
```

### Query Hints Categorisation

Another way is to use hints to flexible search creation. That way requires configuration beforehand. It is required to prepare application configuration e.g. in local.properties:

```bash
flexiblesearch.categorizedQuery.MyCategory1.useReadOnlyDataSource=true
flexiblesearch.categorizedQuery.MyCategory2.useReadOnlyDataSource=false
```
where `MyCategory1` is your own name. Then it is required to set suffix `useReadOnlyDataSource` and value for that data-source usage.

Then in code it is straightforward and requires just one additional line:

```java
query1 = new FlexibleSearchQuery("select {PK} from {Product}");
query1.addHints(FlexibleSearchHints.categorizedQuery("MyCategory1"));

query2 = new FlexibleSearchQuery("select {PK} from {Product}");
query2.addHints(FlexibleSearchHints.categorizedQuery("MyCategory2"));
```

That is it. Rest is handled by core.


##  Task Engine Support

Task engine can also generate traffic on master data-source. That is why it also has been selected to be configured to use RO replica.

There is new property added, which in platform by default is set to `false`. By setting it in e.g. `local.properties` you can enable it.

```bash
task.polling.queryTaskProvider.read-replica.enabled=true
```

# What if

It is possible that in the runtime RO replica will not be available, or for given patch that functionality will be disabled (it happened in the past, that RO replica has been disabled in few patches due to found issues). There is a fail-over, which causes that all queries, will be silently rerouted to master data-source. Application will run normally.

## Can I disable that?

If for some reason you would like to **disable** that function on hac and stop using RO replica, you can set in properties that value to `''` (empty) value.

```bash
flexiblesearch.readOnly.datasource=
```


# Documentation Links

SAP Help Read-Only Replica:  https://help.sap.com/docs/SAP_COMMERCE_CLOUD_PUBLIC_CLOUD/0fa6bcf4736c46f78c248512391eb467/1c7d5dde153a45fc9af868244741c32c.html?locale=en-US
SAP Help CronJobs Replica: https://help.sap.com/docs/SAP_COMMERCE_CLOUD_PUBLIC_CLOUD/aa417173fe4a4ba5a473c93eb730a417/d5aabf6700f249a2b433b956fa0620e2.html?version=v2105&locale=en-US
Microsoft Documentation: https://docs.microsoft.com/en-us/azure/azure-sql/database/read-scale-out?view=azuresql
Read-Only Replica SDK: https://help.sap.com/docs/SAP_COMMERCE_CLOUD_PUBLIC_CLOUD/aa417173fe4a4ba5a473c93eb730a417/51204da1b55d468484ba73222eaea5ce.html