AlwaysOn: Comparing Readable Secondary with the similar functionality available in DB2

In my previous blogs, I had described how Readable Secondary functionality works SQL Server 2012. As you look at other database vendors, you will realize that they also provide the functionality to offload read workload to secondary or mirror. However, if you look closer, you will realize each vendor has significant differences on how this functionality is made available to customers. Here is one comparison that I have done between SQL Server and DB2.

Competitive study of Readable Secondary in DB2 9.7

DB2 9.7 HADR configuration supports ‘active’ standby server that can be used for query workload. From competition perspective, it is similar to Readable Secondary functionality. This report is based on DB2 9.7
documentation. For details, please refer to the link

https://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0054257.html

Key Feature

DB2

SQL Server 2012

Isolation Level Mapping: This is very  relevant for SQL Server as the DB2 provides similar isolation levels

By default only support ‘Uncommitted Read’. An application that uses any other isolation level fails. 

You can however use configuration option DB2_STANDBY_ISO registry variable and set it to UR. In that case, all isolation levels are mapped to Uncommitted Read.

 

 

Provides consistent read by mapping all isolation levels to Snapshot Isolation.

DDL and Read Workload: What happens when DDL or maintenance operations are executed on the primary. What is the impact on the read  workload on the secondary

When an HADR active standby database is replaying DDL log records or maintenance operations, the standby enters the replay-only window. When the standby is in the replay-only window, existing connections to the standby are terminated and new connections to the standby are blocked.

DB2 recommends customers to run these operations during specific time window

No such restrictions. In many cases, the DDL may not get blocked due to concurrent read workload.  In case of DDL getting blocked due to concurrent read-workload and the ‘recovery interval’ threshold is crossed, we generate an XEvent which can be used to kill the blocking readers. We expect this to be not that common, based on ‘recovery interval’ option.

Auto Statistics Creation: Since secondary is available for read-only access, how does it address making statistics available needed only for the queries running on the readable secondary? If statistics are not available, it can potentially increase the execution time by orders of magnitude. 

Requires customers to manually create/update statistics.  Since this is a DDL operation, it can only be done in ‘replay-only window’. This means that query plans will be sub-optimal until the statistics are made available.

Note: DB2 document does not explicitly calls this out but references to statistics seem to imply this.

Allows auto-create/update of statistics on the readable secondary so that optimizer will always generate optimized query plans.

Auditing

Manually create the instance level auditing  configuration on the standby server

Manually create the instance level audit specification on the readable secondary..

Secondary falling behind:

Manually kill the read workload. It is not clear from DB2 documentation if they generate any event to notify customers

If REDO is slowing down, we will generate an XEvent when ‘recovery interval’ threshold is crossed. The customer can choose to kill the offending readers. If the slowdown is because of resources, the reader workload can be throttled using Resource Governor.

 

Temporary Objects

Created temporary tables (CGTTs) and declared temporary tables (DGTTs) are not supported on the standby. 

No restrictions. The read workload is free to do any DDL in temporary database. 

Data types: LOB, XML,

Not supported on the standby server

No  restrictions. SQL Server will support LOBS, XML, FILESTREAM, and FTS

Backup on Secondary

Not supported

Supported

 

Thanks

Sunil Agarwal