Using DBCO with Microsoft SQL Server AlwaysOn Readable Secondary Replicas

[luisdarui]

In a recent support case, a customer asked whether it would be possible to create an External Database Connection from ABAP (DBCON) to a SQL Server AlwaysOn Readable Secondary Replica.

Short explanation about AlwaysOn: AlwaysOn Availability Groups is an HA/DR feature introduced on SQL Server 2012 that, among all other important things (which aren't the object of this blog post, but feel free to read more about here and here), allows you to access the secondary replicas of the database for read-only and/or some backup operations.You can, for example, offload some jobs from the primary replica, like data extraction and backups.

In the given scenario, the intent was to extract data from an external database. In theory, it would require to create a DBCO entry for the database server (where the secondary replica runs) and database. As long as the "Readable secondary" property is set to "Read-Intent only" and the program do only extract data from external source, this shouldn't be a problem, right?

Given the following image (a scenario with only two replicas):



Let's analyze the following scenario:

• The AlwaysOn Availability Group for the database name DBS;


• The AAG Listener name is: AAGListener1;


• The Primary Replica is currently running on DBSERVER1;


• The Secondary Replica is currently running on DBSERVER2;


• Both replicas are configured for Readable secondary: "Read-Intent only".

Assume that:

• Any connection made through the listener AAGListner1 to access the DBS database will be redirected to the primary replica, currently running on DBSERVER1.


• Any access to the DBSERVER2 will only be allowed if you specify that your intent is read-only.

Creating a DBCON connection then to the DBSERVER2 would would suffice the requirement (a full description on how to create a DBCON is explained on SAP Note 178949), but what happens in the case of a Failover?

• The Primary Replica is now DBSERVER2.


• The Secondary Replica is now DBSERVER1.


• The DBCON would be pointing to the Primary Replica (DBSERVER2) instead the Secondary Replica (DBSERVER1), therefore, nullifying the offload of the data extraction.

There is our problem. In order to deal with this, there is a feature for AlwaysOn that is called Read-Only Routing for an Availability Group, which you might guess what it does by its name. Basically, you can connect to a Readable Secondary Replica through the Listener name, by simply adding in your connection string the applicationintent=readonly option. To make this work, you have to create this routing as described in Books Online.

What are the benefits of having the Read-Only routing set?

• You can use the Listener instead the Instance name to connect to the readable secondary replica;


• You don't have to worry in case a failover occurs: the listener will always redirect you to the readable secondary replica;


• As of SQL Server 2016, you can also configure a load-balancing across a set of secondary-replicas.

A more detailed steps on how to configure the DBCON to a readable secondary replica is described in the SAP Knowledge Base Article 2422999 - Database Connection to AlwaysOn Readable Secondary Replica.

As always, a good reading on Books Online and some practice are recommended.