Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Questions from My AlwaysOn Availabilty Group Presentation

  • 19 March 2013
  • Author: Kathi Kellenberger
  • Number of views: 6808

I gave a Training on the Ts Webinar today on AlwaysOn Availability Groups (AG) today. The recording should show up in a couple of days.  My session was focused on showing how AG compares to SQL Server Failover Clustering and Database Mirroring as well as a quick tour of the features.

One of the questions at the end of the presentation asked about licensing all the nodes. At the time, I could not recall if this is something that I had looked at since leaving Microsoft last year, a few months before SQL Server 2012 was released.  With SQL Server Clustering and Database Mirroring, if you have a truly passive node, you do not have to license that node for SQL Server. You do have to license the node for Windows Server, however.

A quick search this afternoon shows that AG is licensed the same way. If you have a node that is really going to be passive, that is, not used for ANYTHING besides hosting the secondaries, you do not have to license the node for SQL Server. And, when I say ANYTHING, I mean you cannot offload backups nor reporting to this node.  Please refer to your Microsoft rep to validate that you meet this scenario.

Another question came up about performance of read-only secondaries. My reply was almost correct. I had heard at one time while 2012 was still in CTP (Beta) that you had to enable Snapshot Isolation on the readable secondaries to get good performance. Researching today, I found out that AG uses Snapshot Isolation under the covers. Regardless of which isolation level you use, AG will use Snapshot isolation on readable secondaries to avoid blocking the replay of transactions from the primary.  NOTE: This means tempdb will tend to be larger.

Finally, a question came up about performance of the Primary after AG was configured. I have been trying to find something today about this topic with no luck so far. Here are some items to think about when troubleshooting this issue:

• Make sure that the bandwidth between nodes when using synchronous mode is high enough to support synchronous mode. For example, one of the nodes connected by the WAN is using synchronous mode and should really be set to asynchronous mode.

• Make sure you have applied all applicable patches and followed recommended configuration steps

• You may want to avoid offloading reporting on a synchronous node and report against an asynchronous node instead.

• Is the synchronous secondary server possibly undersized and just can't keep up? That will affect the primary.

AlwaysOn Availability Groups is an exciting SQL Server 2012 feature that is easy to implement and will simplify how you do HA and DR!

Categories: SQL Server
Rate this article:
No rating

Please login or register to post comments.