{"id":968,"date":"2013-04-22T09:22:51","date_gmt":"2013-04-22T16:22:51","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=968"},"modified":"2013-04-22T09:26:03","modified_gmt":"2013-04-22T16:26:03","slug":"documenting-availability-group-pbm-conditions","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/","title":{"rendered":"Documenting Availability Group PBM Conditions"},"content":{"rendered":"<p>There doesn\u2019t seem to be much documentation out on the web regarding the AlwaysOn Availability Group policies and associated conditions that come with SQL Server 2012, so I thought I would aggregate \/ organize some of the condition logic here for reference-sake\u2026&nbsp; Also \u2013 looking at conditions in SSMS, you\u2019ll see that for some conditions like AlwaysOnArDataSynchronizationHealthCondition, you cannot expand the full field information in the GUI or even select the various fields in order to see the descriptions. Given this, in some cases I had to confirm the expression logic via msdb.dbo.syspolicy_conditions (looking through the Expression XML). Of course, you could just use the raw XML for documentation, but it isn\u2019t very friendly to look at.&nbsp; <\/p>\n<p>So here is the reference information for AG-related conditions as of SQL Server version 11.0.3349\u2026<\/p>\n<p><strong>AlwaysOnAgAutomaticFailoverHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Group State facet<\/li>\n<li>Referenced by AlwaysOnAgAutomaticFailoverHealthPolicy <\/li>\n<li>Expression:<\/li>\n<ul>\n<li>(@IsAutoFailover = True AND @NumberOfSynchronizedSecondaryReplicas &gt; 0) OR @IsAutoFailover = False<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnAgOnlineStateHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Group State facet<\/li>\n<li>Referenced by AlwaysOnAgOnlineStateHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@IsOnline = True<\/li>\n<p><!--EndFragment--><\/ul>\n<\/ul>\n<p><strong>AlwaysOnAgReplicasConnectionHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Group State facet<\/li>\n<li>Referenced by AlwaysOnAgReplicasConnectionHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@NumberOfDisconnectedReplicas = 0<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnAgReplicasDataSynchronizationHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Group State facet<\/li>\n<li>Referenced by AlwaysOnAgReplicasDataSynchronizationHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@NumberOfNotSynchronizingReplicas = 0<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnAgReplicasRoleHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Group State facet<\/li>\n<li>Referenced by AlwaysOnAgReplicasRoleHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@NumberOfReplicasWithUnhealthyRole = 0<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnAgSynchronousReplicasDataSynchronizationHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Group State facet<\/li>\n<li>Referenced by AlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@NumberOfNotSynchronizedReplicas = 0<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnAgWSFClusterHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Server facet<\/li>\n<li>Referenced by AlwaysOnAgWSFClusterHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@ClusterQuorumState = NormalQuorum<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnArConnectionHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Replica facet<\/li>\n<li>Referenced by AlwaysOnArConnectionHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@ConnectionState = Connected<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnArDataSynchronizationHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Replica facet<\/li>\n<li>Referenced by AlwaysOnArDataSynchronizationHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>(@AvailabilityMode = AsynchronousCommit AND (@RollupSynchronizationState = Synchronizing OR @RollupSynchronizationState = Synchronized) OR @RollupSynchronizationState = Synchronized)<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnArJoinStateHealthCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Replica facet<\/li>\n<li>Referenced by AlwaysOnArJoinStateHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@JoinState != NotJoined<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnArRoleHealthCondition<\/strong>&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Availability Replica facet<\/li>\n<li>Referenced by AlwaysOnArRoleHealthPolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@Role = Primary OR @Role = Secondary<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnDbrDataSynchronizationCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Database Replica State facet<\/li>\n<li>Referenced by AlwaysOnDbrDataSynchronizationState (notice the change in naming convention)<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>(@ReplicaAvailabilityMode = AsynchronousCommit AND @SynchronizationState != NotSynchronizing) OR @SynchronizationState = Synchronized<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnDbrJoinStateCondition<\/strong>&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Database Replica State facet<\/li>\n<li>Referenced by AlwaysOnDbrJoinStatePolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@IsJoined = True<\/li>\n<\/ul>\n<\/ul>\n<p><strong>AlwaysOnDbrSuspendStateCondition<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Database Replica State facet<\/li>\n<li>Referenced by AlwaysOnDbrSuspendStatePolicy<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@IsSuspended = False<\/li>\n<\/ul>\n<\/ul>\n<p><strong>IsHadrEnabled<\/strong>&nbsp;&nbsp;&nbsp; <\/p>\n<ul>\n<li>References the Server facet<\/li>\n<li>Referenced by all of the AlwaysOnAg \/ AlwaysOnAr and AlwaysOnDbr policies<\/li>\n<li>Expression:<\/li>\n<ul>\n<li>@IsHadrEnabled = True<\/li>\n<\/ul>\n<\/ul>\n<p>I bothered with documenting this in order to better understand some of the availability group health model logic at the availability group, availability replica and database replica scope.&nbsp; Microsoft did talk a little about the \u201cAlwaysOn Health Model\u201d in the following post as well, so I recommend reviewing this if you plan on deploying availability groups in your environment:<\/p>\n<p><a href=\"http:\/\/blogs.msdn.com\/b\/sqlalwayson\/archive\/2012\/02\/09\/overview-of-the-alwayson-manageability-health-model.aspx\" target=\"_blank\" class=\"broken_link\">The AlwaysOn Health Model Part 1 &#8212; Health Model Architecture<\/a><\/p>\n<p>If you want to extend the model further (or override policies), Microsoft\u2019s Will Snavely also blogged about it here:<\/p>\n<p><a href=\"http:\/\/blogs.msdn.com\/b\/sqlalwayson\/archive\/2012\/02\/13\/extending-the-alwayson-health-model.aspx\" target=\"_blank\" class=\"broken_link\">The AlwaysOn Health Model Part 2 &#8212; Extending the Health Model<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>There doesn\u2019t seem to be much documentation out on the web regarding the AlwaysOn Availability Group policies and associated conditions that come with SQL Server 2012, so I thought I would aggregate \/ organize some of the condition logic here for reference-sake\u2026&nbsp; Also \u2013 looking at conditions in SSMS, you\u2019ll see that for some conditions [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[],"class_list":["post-968","post","type-post","status-publish","format-standard","hentry","category-availability-groups"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Documenting Availability Group PBM Conditions - Joe Sack<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Documenting Availability Group PBM Conditions - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"There doesn\u2019t seem to be much documentation out on the web regarding the AlwaysOn Availability Group policies and associated conditions that come with SQL Server 2012, so I thought I would aggregate \/ organize some of the condition logic here for reference-sake\u2026&nbsp; Also \u2013 looking at conditions in SSMS, you\u2019ll see that for some conditions [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-04-22T16:22:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-04-22T16:26:03+00:00\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/\",\"name\":\"Documenting Availability Group PBM Conditions - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-04-22T16:22:51+00:00\",\"dateModified\":\"2013-04-22T16:26:03+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Availability Groups\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/availability-groups\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Documenting Availability Group PBM Conditions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Documenting Availability Group PBM Conditions - Joe Sack","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/","og_locale":"en_US","og_type":"article","og_title":"Documenting Availability Group PBM Conditions - Joe Sack","og_description":"There doesn\u2019t seem to be much documentation out on the web regarding the AlwaysOn Availability Group policies and associated conditions that come with SQL Server 2012, so I thought I would aggregate \/ organize some of the condition logic here for reference-sake\u2026&nbsp; Also \u2013 looking at conditions in SSMS, you\u2019ll see that for some conditions [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/","og_site_name":"Joe Sack","article_published_time":"2013-04-22T16:22:51+00:00","article_modified_time":"2013-04-22T16:26:03+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/","name":"Documenting Availability Group PBM Conditions - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-04-22T16:22:51+00:00","dateModified":"2013-04-22T16:26:03+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/documenting-availability-group-pbm-conditions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Availability Groups","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/availability-groups\/"},{"@type":"ListItem","position":3,"name":"Documenting Availability Group PBM Conditions"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/968","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=968"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/968\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=968"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=968"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=968"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}