Home
%3CLINGO-SUB%20id%3D%22lingo-sub-384044%22%20slang%3D%22en-US%22%3EMonitoring%20AlwaysOn%20Health%20with%20PowerShell%20-%20Part%202%3A%20Advanced%20Cmdlet%20Usage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-384044%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Feb%2013%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-1680717884%22%20id%3D%22toc-hId-1848715133%22%3EPart%202%20%E2%80%93%20Advanced%20Usage%20of%20AlwaysOn%20Health%20Cmdlets%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EIn%20this%20part%2C%20I%E2%80%99ll%20cover%20some%20advanced%20features%20of%20the%20AlwaysOn%20health%20cmdlets.%26nbsp%3B%20Please%20see%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsqlalwayson%2Farchive%2F2012%2F02%2F13%2Fmonitoring-alwayson-health-with-powershell-part-1.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20Part%201%20%3C%2FA%3E%20for%20a%20basic%20overview%20of%20these%20cmdlets.%20We%E2%80%99ll%20look%20at%20how%20we%20can%20drill%20down%20into%20unhealthy%20objects%20to%20discover%20individual%20policy%20failures%2C%20and%20how%20to%20include%20custom%20policies%20in%20health%20evaluation.%26nbsp%3B%20We%E2%80%99ll%20also%20talk%20about%20some%20performance%20considerations.%26nbsp%3B%20Please%20note%20that%20all%20PowerShell%20commands%20demonstrated%20below%20should%20be%20executed%20within%20the%20SQLPS%20shell.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERecall%20the%20AlwaysOn%20health%20cmdlets%20are%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CB%3E%20Test-SqlAvailabilityGroup%20%3C%2FB%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CB%3E%20Test-SqlAvailabilityReplica%20%3C%2FB%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CB%3E%20Test-SqlDatabaseReplicaState%20%3C%2FB%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EAlwaysOn%20Health%20Model%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERead%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsqlalwayson%2Farchive%2F2012%2F02%2F09%2Foverview-of-the-alwayson-manageability-health-model.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20this%20blog%20%3C%2FA%3E%20for%20an%20in%20depth%20look%20at%20the%20AlwaysOn%20health%20model.%26nbsp%3B%20To%20briefly%20summarize%3A%20to%20compute%20the%20health%20of%20an%20availability%20group%2C%20availability%20replica%2C%20or%20availability%20database%2C%20we%20execute%20a%20collection%20of%20Policy-Based%20Management%20(PBM)%20policies%20that%20are%20installed%20along%20with%20SQL%20Server%202012.%26nbsp%3B%20These%20policies%20test%20various%20aspects%20of%20your%20high%20availability%20configuration%2C%20and%20based%20on%20the%20results%20we%20compute%20an%20overall%20health%20state%20of%20the%20object%20in%20question.%26nbsp%3B%20Some%20policies%20are%20categorized%20as%20more%20critical%20than%20others%2C%20so%20that%20one%20policy%20failure%20might%20result%20in%20a%20warning%20and%20another%20in%20a%20critical%20error.%26nbsp%3B%20Users%20can%20add%20their%20own%20policies%20to%20this%20collection%20to%20extend%20the%20health%20model.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EViewing%20Policy%20Evaluation%20Details%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUsing%20our%20AlwaysOn%20health%20cmdlets%2C%20you%20can%20obtain%20policy%20evaluation%20details%20in%20two%20ways%3A%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EEvery%20health%20result%20returned%20by%20a%20Test-Sql*%20cmdlet%20has%20a%20field%2C%20PolicyEvaluationDetails.%26nbsp%3B%20This%20field%20stores%20an%20array%20of%20policy%20evaluation%20details%2C%20one%20entry%20for%20each%20policy%20executed.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EEvery%20Test-Sql*%20cmdlet%20has%20a%20%E2%80%93ShowPolicyDetails%20parameter.%26nbsp%3B%20If%20you%20specify%20this%20parameter%2C%20the%20cmdlet%20will%20enumerate%20the%20policy%20evaluation%20details%20instead%20of%20returning%20a%20single%20result.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CP%3EHere%20are%20some%20examples.%26nbsp%3B%20Last%20time%20we%20were%20investigating%20the%20availability%20group%20%E2%80%98VLEApplication%E2%80%99%20which%20had%20a%20critical%20error.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20cd%26nbsp%3BSQLSERVER%3A%5CSQL%5CWSNAVELY1-SH21Q%5CDEFAULT%5CAvailabilityGroups%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20PS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20Test-SqlAvailabilityGroup%26nbsp%3B.%5CVLEApplication%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20HealthState%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BName%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20-----------%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B----%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Error%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BVLEApplication%3C%2FEM%3E%20%3CBR%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWe%20know%20that%20the%20availability%20group%20is%20in%20a%20critical%20state%2C%20but%20we%20can%E2%80%99t%20immediately%20see%20the%20policy%20failures%20responsible%20for%20this.%26nbsp%3B%20We%E2%80%99ll%20first%20demonstrate%20method%20(1)%20from%20above%20for%20getting%20this%20information%2C%20namely%20accessing%20the%20PolicyEvaluationDetails%20field%20on%20the%20object%20returned%20by%20the%20Test-SqlAvailabilityGroup%20cmdlet.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20%24result%26nbsp%3B%3D%26nbsp%3BTest-SqlAvailabilityGroup%26nbsp%3B.%5CVLEApplication%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20PS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20%24result.PolicyEvaluationDetails%26nbsp%3B%7C%26nbsp%3Bft%26nbsp%3BResult%2CName%20-AutoSize%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Result%26nbsp%3B%26nbsp%3BName%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20------%26nbsp%3B%26nbsp%3B----%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20True%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgOnlineStateHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20True%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgWSFClusterHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20False%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgAutomaticFailoverHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20True%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgReplicasConnectionHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20False%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgReplicasDataSynchronizationHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20True%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgReplicasRoleHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20False%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ENote%20I%20apply%20some%20simple%20formatting%20to%20the%20output%20(ft%2C%20alias%20for%20Format-Table).%20We%20see%20that%20three%20policies%20failed%20(returned%20a%20result%20of%20False)%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EAlwaysOnAgAutomaticFailoverHealthPolicy%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EAlwaysOnAgReplicasDataSynchronizationHealthPolicy%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EAlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EAt%20this%20point%2C%20I%20can%20connect%20to%20the%20server%20instance%20with%20SQL%20Server%20Management%20Studio%20and%20determine%20the%20conditions%20monitored%20by%20these%20policies%2C%20and%20take%20appropriate%20action.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ENow%20I%E2%80%99ll%20demonstrate%20method%20(2)%20from%20above%2C%20using%20the%20%E2%80%93ShowPolicyDetails%20parameter.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20Test-SqlAvailabilityGroup%26nbsp%3B.%5CVLEApplication%26nbsp%3B-ShowPolicyDetails%26nbsp%3B%7C%26nbsp%3Bft%26nbsp%3BResult%2CName%26nbsp%3B-AutoSize%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Result%26nbsp%3B%26nbsp%3BName%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20------%26nbsp%3B%26nbsp%3B----%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20True%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgOnlineStateHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20True%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgWSFClusterHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20False%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgAutomaticFailoverHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20True%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgReplicasConnectionHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20False%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgReplicasDataSynchronizationHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20True%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgReplicasRoleHealthPolicy%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20False%26nbsp%3B%26nbsp%3B%26nbsp%3BAlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAgain%20I%20apply%20some%20simple%20formatting%20to%20the%20result.%26nbsp%3B%20We%20see%20that%20the%20output%20of%20method%20(1)%20and%20(2)%20is%20identical.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIncluding%26nbsp%3BUser%20Policies%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EYou%20are%20able%20to%20extend%20the%20default%20AlwaysOn%20health%20model%20with%20your%20own%20policies.%26nbsp%3B%20I%20won%E2%80%99t%20go%20into%20details%20here%20about%20how%20to%20create%20these%20custom%20policies%2C%20but%20please%20refer%20to%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsqlalwayson%2Farchive%2F2012%2F02%2F13%2Fextending-the-alwayson-health-model.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20this%20post%20%3C%2FA%3E%20for%20more%20information.%26nbsp%3B%20By%20default%2C%20the%20AlwaysOn%20health%20cmdlets%20won%E2%80%99t%20evaluate%20user%20policies%3B%20however%20you%20can%20force%20them%20to%20do%20so%20by%20specifying%20the%20%3CSTRONG%3E%20AllowUserPolicies%20%3C%2FSTRONG%3E%20parameter%2C%20supported%20by%20all%20three%20cmdlets.%26nbsp%3B%20This%20is%20a%20simple%20switch%20parameter%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Test-SqlAvailabilityGroup%20.%5CVLEApplication%20-ShowPolicyDetails%20-AllowUserPolicies%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPerformance%20Considerations%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20AlwaysOn%20health%20cmdlet%20follow%20this%20general%20pattern%3A%20accept%20an%20object%20from%20the%20pipeline%2C%20refresh%20its%20properties%2C%20then%20execute%20a%20collection%20of%20PBM%20policies%20against%20the%20object.%26nbsp%3B%20The%20objects%20in%20this%20case%20are%20part%20of%20the%20SQL%20Server%20Management%20Object%20(SMO)%20framework.%26nbsp%3B%20Test-SqlAvailabilityGroup%20accepts%20objects%20of%20type%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fmicrosoft.sqlserver.management.smo.availabilitygroup(SQL.110).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20AvailabilityGroup%20%3C%2FA%3E%20%2C%20Test-SqlAvailabilityReplica%20objects%20of%20type%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fpt-br%2Flibrary%2Fmicrosoft.sqlserver.management.smo.availabilityreplica(v%3Dsql.110).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20AvailabilityReplica%20%3C%2FA%3E%20%2C%20and%20Test-SqlDatabaseReplicaState%20objects%20of%20type%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fmicrosoft.sqlserver.management.smo.databasereplicastate(v%3Dsql.110).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20DatabaseReplicaState%20%3C%2FA%3E%20.%26nbsp%3B%20If%20you%20are%20monitoring%20many%20objects%20%E2%80%93%20say%2C%20you%20have%20an%20availability%20group%20with%20100%20databases%20%E2%80%93%20this%20process%20will%20be%20very%20chatty%2C%20since%20each%20object%20refresh%20will%20result%20in%20a%20query%20to%20the%20server.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20this%20scenario%2C%20you%20can%20suppress%20the%20refresh%20behavior%20of%20these%20cmdlets%3A%20simply%20specify%20the%20%E2%80%93NoRefresh%20parameter.%26nbsp%3B%20In%20this%20case%20you%20will%20be%20responsible%20for%20ensuring%20that%20the%20SMO%20objects%20passed%20to%20the%20cmdlets%20have%20recent%20data.%26nbsp%3B%20Let%E2%80%99s%20consider%20the%20scenario%20mentioned%3A%20our%20availability%20group%20%E2%80%9CLargeAvailabilityGroup%E2%80%9D%20has%20100%20databases%20across%20two%20replicas%2C%20for%20a%20total%20of%20200%20databases%2C%20and%20you%20want%20to%20evaluate%20the%20health%20of%20all%20these%20databases.%26nbsp%3B%20First%20let%E2%80%99s%20use%20the%20na%C3%AFve%20approach.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20%24serverObj%26nbsp%3B%3D%26nbsp%3BNew-Object%26nbsp%3BMicrosoft.SqlServer.Management.Smo.Server(%22WSNAVELY1-SH21Q%22)%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20PS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20%24ag%26nbsp%3B%3D%26nbsp%3B%24serverObj.AvailabilityGroups%5B%22LargeAvailabilityGroup%22%5D%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20PS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20Measure-Command%26nbsp%3B%7B%26nbsp%3B%24ag.DatabaseReplicaStates%26nbsp%3B%7C%26nbsp%3BTest-SqlDatabaseReplicaState%26nbsp%3B%7D%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Days%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B0%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Hours%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B0%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Minutes%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B0%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Seconds%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B23%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Milliseconds%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B430%3C%2FEM%3E%20%3CBR%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20took%20about%2020%20seconds%20to%20execute%2C%20rather%20a%20long%20time.%26nbsp%3B%20Now%20we%E2%80%99ll%20demonstrate%20an%20optimized%20approach.%26nbsp%3B%20First%2C%20we%E2%80%99ll%20want%20to%20turn%20off%20the%20auto-refresh%20behavior%20of%20the%20cmdlet%20using%20the%20%E2%80%93NoRefresh%20parameter%20as%20mentioned.%26nbsp%3B%20Next%2C%20we%20need%20to%20figure%20out%20an%20efficient%20way%20to%20load%20the%20data%20we%20need%20from%20the%20server.%26nbsp%3B%20Fortunately%2C%20SMO%20provides%20a%20mechanism%20for%20efficiently%20loading%20data%20for%20collections%20of%20objects%2C%20the%20%E2%80%9CSetDefaultInitFields%E2%80%9D%20method%20on%20the%20SMO%20Server%20object.%26nbsp%3B%20For%20a%20detailed%20explanation%20of%20how%20this%20mechanism%20works%2C%20please%20refer%20to%20these%20blog%20posts%20by%20Michiel%20Wories%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fmwories%2Farchive%2F2005%2F04%2F22%2Fsmoperf1.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fblogs.msdn.com%2Fb%2Fmwories%2Farchive%2F2005%2F04%2F22%2Fsmoperf1.aspx%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fmwories%2Farchive%2F2005%2F04%2F22%2Fsmoperf2.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fblogs.msdn.com%2Fb%2Fmwories%2Farchive%2F2005%2F04%2F22%2Fsmoperf2.aspx%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EHere%E2%80%99s%20a%20complete%20example%20with%20our%20cmdlet%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20%24ServerObj%26nbsp%3B%3D%26nbsp%3BNew-Object%26nbsp%3BMicrosoft.SqlServer.Management.Smo.Server(%22WSNAVELY1-SH21Q%22)%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20PS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20%24ServerObj.SetDefaultInitFields(%5BMicrosoft.SqlServer.Management.Smo.AvailabilityGroup%5D%2C%26nbsp%3B%24true)%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20PS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20%24ServerObj.SetDefaultInitFields(%5BMicrosoft.SqlServer.Management.Smo.AvailabilityReplica%5D%2C%26nbsp%3B%24true)%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20PS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20%24ServerObj.SetDefaultInitFields(%5BMicrosoft.SqlServer.Management.Smo.DatabaseReplicaState%5D%2C%26nbsp%3B%24true)%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20PS%26nbsp%3B%26gt%3B%20%3CSTRONG%3E%20Measure-Command%26nbsp%3B%7B%26nbsp%3B%24ag.DatabaseReplicaStates%26nbsp%3B%7C%26nbsp%3BTest-SqlDatabaseReplicaState%26nbsp%3B-NoRefresh%26nbsp%3B%7D%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Days%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B0%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Hours%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B0%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Minutes%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B0%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Seconds%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B7%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Milliseconds%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3A%26nbsp%3B317%3C%2FEM%3E%20%3CBR%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20time%20the%20evaluation%20took%20about%207%20seconds%2C%20much%20faster.%26nbsp%3B%20The%20primary%20difference%20here%20is%20the%20number%20of%20queries%20issued.%26nbsp%3B%20The%20first%20example%20issues%20a%20query%20for%20each%20database%2C%20while%20the%20second%20example%20issues%20a%20single%20query%20that%20fetches%20all%20the%20required%20data.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWe've%20now%20covered%20most%20of%20the%20advanced%20usage%20scenarios%20for%20these%20cmdlets.%26nbsp%3B%20In%20Part%203%20we%20will%20combine%20what%20we've%20learned%20into%20a%20simple%20script.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-384044%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Feb%2013%2C%202012%20Part%202%20%E2%80%93%20Advanced%20Usage%20of%20AlwaysOn%20Health%20CmdletsIn%20this%20part%2C%20I%E2%80%99ll%20cover%20some%20advanced%20features%20of%20the%20AlwaysOn%20health%20cmdlets.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-384044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerAlwaysOn%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft
First published on MSDN on Feb 13, 2012

Part 2 – Advanced Usage of AlwaysOn Health Cmdlets


In this part, I’ll cover some advanced features of the AlwaysOn health cmdlets.  Please see Part 1 for a basic overview of these cmdlets. We’ll look at how we can drill down into unhealthy objects to discover individual policy failures, and how to include custom policies in health evaluation.  We’ll also talk about some performance considerations.  Please note that all PowerShell commands demonstrated below should be executed within the SQLPS shell.


Recall the AlwaysOn health cmdlets are:



  • Test-SqlAvailabilityGroup

  • Test-SqlAvailabilityReplica

  • Test-SqlDatabaseReplicaState


AlwaysOn Health Model


Read this blog for an in depth look at the AlwaysOn health model.  To briefly summarize: to compute the health of an availability group, availability replica, or availability database, we execute a collection of Policy-Based Management (PBM) policies that are installed along with SQL Server 2012.  These policies test various aspects of your high availability configuration, and based on the results we compute an overall health state of the object in question.  Some policies are categorized as more critical than others, so that one policy failure might result in a warning and another in a critical error.  Users can add their own policies to this collection to extend the health model.


Viewing Policy Evaluation Details


Using our AlwaysOn health cmdlets, you can obtain policy evaluation details in two ways:



  1. Every health result returned by a Test-Sql* cmdlet has a field, PolicyEvaluationDetails.  This field stores an array of policy evaluation details, one entry for each policy executed.

  2. Every Test-Sql* cmdlet has a –ShowPolicyDetails parameter.  If you specify this parameter, the cmdlet will enumerate the policy evaluation details instead of returning a single result.


Here are some examples.  Last time we were investigating the availability group ‘VLEApplication’ which had a critical error.


PS > cd SQLSERVER:\SQL\WSNAVELY1-SH21Q\DEFAULT\AvailabilityGroups
PS > Test-SqlAvailabilityGroup .\VLEApplication

HealthState            Name
-----------            ----
Error                  VLEApplication


We know that the availability group is in a critical state, but we can’t immediately see the policy failures responsible for this.  We’ll first demonstrate method (1) from above for getting this information, namely accessing the PolicyEvaluationDetails field on the object returned by the Test-SqlAvailabilityGroup cmdlet.


PS > $result = Test-SqlAvailabilityGroup .\VLEApplication
PS > $result.PolicyEvaluationDetails | ft Result,Name -AutoSize

Result  Name
------  ----
True    AlwaysOnAgOnlineStateHealthPolicy
True    AlwaysOnAgWSFClusterHealthPolicy
False   AlwaysOnAgAutomaticFailoverHealthPolicy
True    AlwaysOnAgReplicasConnectionHealthPolicy
False   AlwaysOnAgReplicasDataSynchronizationHealthPolicy
True    AlwaysOnAgReplicasRoleHealthPolicy
False   AlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy


Note I apply some simple formatting to the output (ft, alias for Format-Table). We see that three policies failed (returned a result of False):



  • AlwaysOnAgAutomaticFailoverHealthPolicy

  • AlwaysOnAgReplicasDataSynchronizationHealthPolicy

  • AlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy


At this point, I can connect to the server instance with SQL Server Management Studio and determine the conditions monitored by these policies, and take appropriate action.


Now I’ll demonstrate method (2) from above, using the –ShowPolicyDetails parameter.


PS > Test-SqlAvailabilityGroup .\VLEApplication -ShowPolicyDetails | ft Result,Name -AutoSize

Result  Name
------  ----
True    AlwaysOnAgOnlineStateHealthPolicy
True    AlwaysOnAgWSFClusterHealthPolicy
False   AlwaysOnAgAutomaticFailoverHealthPolicy
True    AlwaysOnAgReplicasConnectionHealthPolicy
False   AlwaysOnAgReplicasDataSynchronizationHealthPolicy
True    AlwaysOnAgReplicasRoleHealthPolicy
False   AlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy


Again I apply some simple formatting to the result.  We see that the output of method (1) and (2) is identical.


Including User Policies


You are able to extend the default AlwaysOn health model with your own policies.  I won’t go into details here about how to create these custom policies, but please refer to this post for more information.  By default, the AlwaysOn health cmdlets won’t evaluate user policies; however you can force them to do so by specifying the AllowUserPolicies parameter, supported by all three cmdlets.  This is a simple switch parameter:


Test-SqlAvailabilityGroup .\VLEApplication -ShowPolicyDetails -AllowUserPolicies


Performance Considerations


The AlwaysOn health cmdlet follow this general pattern: accept an object from the pipeline, refresh its properties, then execute a collection of PBM policies against the object.  The objects in this case are part of the SQL Server Management Object (SMO) framework.  Test-SqlAvailabilityGroup accepts objects of type AvailabilityGroup , Test-SqlAvailabilityReplica objects of type AvailabilityReplica , and Test-SqlDatabaseReplicaState objects of type DatabaseReplicaState .  If you are monitoring many objects – say, you have an availability group with 100 databases – this process will be very chatty, since each object refresh will result in a query to the server.


In this scenario, you can suppress the refresh behavior of these cmdlets: simply specify the –NoRefresh parameter.  In this case you will be responsible for ensuring that the SMO objects passed to the cmdlets have recent data.  Let’s consider the scenario mentioned: our availability group “LargeAvailabilityGroup” has 100 databases across two replicas, for a total of 200 databases, and you want to evaluate the health of all these databases.  First let’s use the naïve approach.


PS > $serverObj = New-Object Microsoft.SqlServer.Management.Smo.Server("WSNAVELY1-SH21Q")
PS > $ag = $serverObj.AvailabilityGroups["LargeAvailabilityGroup"]
PS > Measure-Command { $ag.DatabaseReplicaStates | Test-SqlDatabaseReplicaState }
Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 23
Milliseconds      : 430


This took about 20 seconds to execute, rather a long time.  Now we’ll demonstrate an optimized approach.  First, we’ll want to turn off the auto-refresh behavior of the cmdlet using the –NoRefresh parameter as mentioned.  Next, we need to figure out an efficient way to load the data we need from the server.  Fortunately, SMO provides a mechanism for efficiently loading data for collections of objects, the “SetDefaultInitFields” method on the SMO Server object.  For a detailed explanation of how this mechanism works, please refer to these blog posts by Michiel Wories:



Here’s a complete example with our cmdlet:


PS > $ServerObj = New-Object Microsoft.SqlServer.Management.Smo.Server("WSNAVELY1-SH21Q")
PS > $ServerObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityGroup], $true)
PS > $ServerObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityReplica], $true)
PS > $ServerObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.DatabaseReplicaState], $true)
PS > Measure-Command { $ag.DatabaseReplicaStates | Test-SqlDatabaseReplicaState -NoRefresh }
Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 7
Milliseconds      : 317


This time the evaluation took about 7 seconds, much faster.  The primary difference here is the number of queries issued.  The first example issues a query for each database, while the second example issues a single query that fetches all the required data.


We've now covered most of the advanced usage scenarios for these cmdlets.  In Part 3 we will combine what we've learned into a simple script.