SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged

When we released Always On Availability Groups in SQL Server 2012 as a new and powerful way to achieve high availability, hardware environments included NUMA machines with low-end multi-core processors and SATA and SAN drives for storage (some SSDs). Performance issues surrounding Availability Groups typically were related to disk I/O or network speeds. As we… Read more

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… Read more

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 http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-challenges-with-statistics-on-readonly-database-database-snapshot-and-secondary-replica.aspx 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 http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-minimizing-blocking-of-redo-thread-when-running-reporting-workload-on-secondary-replica.aspx, 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 http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-impact-on-the-primary-workload-when-you-run-reporting-workload-on-the-secondary-replica.aspx, 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 http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/AlwaysOn-setting-up-readable-seconary-replica.aspx 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