AlwaysOn: I just enabled Readable Secondary but my query is blocked?

When you connect to Secondary Replica, but it has not been enabled for read workload, you will get the following error under two situations (1)    You connect directly to one of the databases under availability group. It is denied because the database is not enabld for read workload. (2)    You connect to a non-AG database… Read more

AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot

 In the previous blog we described that stale or missing statistics will potentially lead to a sub-optimal query plan and how it can impact the query performance on on read-only database, database snapshot and readable secondary. This blog describes how we have solved this in SQL 12 for all three flavors of databases transparently… Read more

AlwaysOn: Challenges with statistics on ReadOnly database, Database Snapshot and Secondary Replica

I am sure you all have dealt with situations when the statistics on one or more columns is either missing or not up-to date. When user submits a query for execution, the SQL Server goes through logical (simplifying or restructuring a query) and physical optimization that considers various query plans based on statistical information and… Read more

AlwaysOn: Impact of mapping reporting workload on Readable Secondary to Snapshot Isolation

In my previous blog, I described how Readable Secondary eliminates potential REDO thread blocking for DML work running on the primary replica by mapping all isolation levels used in the reporting workloads to Snapshot Isolation. While it was essential that we eliminate REDO thread blocking with concurrent DML on secondary replica, it comes at… Read more

AlwaysOn: Minimizing blocking of REDO thread when running reporting workload on Secondary Replica

In earlier blog, I discussed the impact on RTO in case the REDO thread gets blocked. While designing this feature, it was one of the key usability constraints we had (i.e. to eliminate REDO blocking for common usage scenario). For the un-initiated, here is the problem   Primary Replica Secondary Replica   Set Isolation… Read more

AlwaysOn: Impact on the primary workload when you run reporting workload on the secondary replica

The primary goal of AlwaysOn technology is to provide High Availability for Tier-1 workloads.  The ability to leverage secondary replica(s) to offload reporting workloads and database/transaction log backups is useful, but only if it does not compromise High Availability. This is one of the very common concern/question that I have heard from many of you…. Read more

AlwaysOn: Readable Secondary and data latency

One question that I often get asked is if there will any data latency if you are using ‘Sync’ replica for read workload. The short answer is yes. Here is the blog that explains it in detail. While the reporting workload running on the secondary replica gets to access the data as it is changing… Read more

AlwaysOn: Why there are two options to enable a secondary replica for read workload?

In the previous blog I mentioned that there are two options to configure secondary replica for running read workload. The first option ‘Read-intent-only’ is used to provide a directive to AlwaysOn secondary replica to accept connections that have the property ApplicationIntent=ReadOnly set. The word ‘intent’ is important here as SQL Server makes no application check  to guarantee… Read more

AlwaysOn: Setting up Readable Seconary Replica

The easiest way to create an availability group and adding replicas is to use the availability group wizard. You can invoke the wizard by right-clicking on ‘Availability Groups’. The picture of the explorer window below shows that I have created 1 availability group ‘StockPro’ with two replicas ‘SUNILA03-6PLHSI’ (primary) and ‘SUNILA03-YLZO1U’ (secondary). The availability group StockPro contains one… Read more

AlwaysOn: Value Proposition of Readable Secondary

This series of blogs is targeting AlwaysON Readable Secondary. Please refer to other blogs in the related area Readable Secondary is part of AlwaysOn suite of functionality available in SQL12 release. It allows customers to leverage their investment in High Availability hardware for offloading read workload such as reporting to one or more secondary… Read more