The topic I received most in my inbox this week was redo blocked on a secondary while attempting to acquire SCH-M (schema modify) lock. First of all, this is expected behavior and you can monitor for this with your standard blocking activities (sys.dm_exec_requests, blocked process TRC event, blocked...

The distinction between these two wait types is subtle but very helpful in tuning your Always On environment. The committing of a transaction means the log block must be written locally as well as remotely for synchronous replicas.   When in synchronized state this involves specific waits for...

I keep running into the question: “When will my secondary allow automatic failover?”   Based on the question I did some extended research and I will try to summarize in is blog post.  I don’t want to turn this post into a novel so I am going to take some liberties and assume you have read...

This error surprised me when it showed up in the error log so I decided to dig into it a bit more. “Log backup for database "MyDB" on secondary replica created backup files successfully but could not ensure that a backup point has been committed on the primary.  This is an informational...

The lease is used between the SQL Server resource DLL and the SQL Server instance to prevent split-brain from occurring for the availability group (AG). The lease is a standard signaling mechanism between the SQL Server resource DLL and the SQL Server availability group.  The figure below depicts...

Here is the an issue I saw come across an alias that is a gotcha! I’m running into a problem connecting to an AlwaysOn read-intent secondary and I was wondering if someone could help me out.  I have the .Net Framework 4.5 installed and the newest SQL Client install for SQL Server 2012.  Running...

Suresh and I have blogged about these issues before but this post will put another spin on the information, as it applies to BIG DATA . Previous Blog References http://blogs.msdn.com/b/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx http://blogs.msdn...

I ran into an issue where the AG was no longer present on a specific node of my cluster but I had NOT dropped the AG from another node in the cluster.  ( http://blogs.msdn.com/b/psssql/archive/2012/06/13/how-it-works-drop-availability-group-behaviors.aspx )   Note: Use the XEvent files captured...

I just learned something new about the DROP AVAILABILITY GROUP command behavior that I didn't realize.  The comment on TechNet, alludes to how DROP works but we definitely need to update our documentation. Remove an Availability Group (SQL Server) - http://technet.microsoft.com/en-us/library/ff878113...

I am on several e-mail aliases related to Always On databases (reference Availability Group, AG, HADRON) and the question of worker thread usage is a hot topic this week.  I developed some training around this during the beta so I decided to pull out the relevant details and share them with you...

As I wrote my recent blog posts and did more research I found that is would be helpful to highlight the HealthCheckTimeout behavior in more detail. Always On FCI (Failover Cluster Instance) vs Non-FCI Installations Documentation The first thing that I need to point out is the subtle wording difference...

I had a great question from my post on sp_server_diagnostics ( http://blogs.msdn.com/b/psssql/archive/2012/03/08/sql-server-2012-true-black-box-recorder.aspx ).  As ' luck ' would have it, Bob Ward, was working on some training and had a similar question.  As I investigated I uncovered a few...

Quite an encompassing title I will agree but the features all work together to build a really nice product.  I had this tucked away during beta and thought it would be helpful to post. SQL Server AlwaysON and "Denali" has undergone a significant upgrade to the way the cluster resource...

I have been asked how to automate SQLIOSim on several occasions.  SQLIOSim is a utility to test SQL Server I/O integrity ( not performance ) patterns against a system without needing to install SQL Server on the system.  It ships with SQL Server 2008 and SQL Server 2008 R2 and is located in...

I can't take the credit for all this content as much of the investigation was done by Curt Mathews (SQL Server Escalation Engineer). We are finding that folks want to test the failover abilities of AlwaysON but after a single failover it no longer seems to work.   This is because of the default...

The behavior of a synchronous commit in AlwaysON is to commit the transaction on the local replica as well as the secondary replica before the transaction is considered complete.    If you   have used database mirroring you may be familiar with the term ‘High Availability’ to describe...

WARNING:  The series is based on pre-release software so things could change but I will attempt to provide you with the best information I can!  The enterprise edition of SQL Server has provided page level repair for quite some time now.   For those not familiar with page level repair...

HADRON allows DBCC to be executed directly against a secondary replica.  The DBCC can be run online, as is, or with TABLOCK if the HADR activity is suspended so the DBCC can acquire the database lock necessary to support the TABLOCK option. A secondary that allows connections enables the administrator...

As I have been reviewing and learning about HADRON I have found a great wealth of information exposed in the DMVs.  I have pulled together a starting, entity relationship diagram and the HADRON PMs have been helping me refine it.  We continue to update it for changes but I thought you would...

I have been reviewing and working on supportability aspects of HADRON (High Availability Disaster Recovery - AlwaysON) for months and I am kicking off a blog post series related my 'HADRON Learning Series' which I am putting together for the SQL Server support team. You can download CTP1 and try it yourself...