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
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
Comments
Anonymous
September 09, 2012
Hopefully, one day you can also have a comparison between SQL Server AlwasyON redable secondary vs feature offer by ORACLE "Active" data guard (physical standby and logical standby)Anonymous
April 21, 2014
It is so nice article. I was really satisfied by seeing this article and we are also giving Tibco Online Training. Tibco <a href="http://www.tibco-online-training.com">Tibco online training</a>is best online training institute in USA.Anonymous
August 26, 2016
I really recommend to everyone perform a double-check over IBM documentation. I've found a UNTRUE information about LOB data type. More details: http://ibm.co/2bCq3Lj