Query for when a SQL server node failover from Primary to secondary

%3CLINGO-SUB%20id%3D%22lingo-sub-683937%22%20slang%3D%22en-US%22%3EQuery%20for%20when%20a%20SQL%20server%20node%20failover%20from%20Primary%20to%20secondary%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-683937%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20fairly%20new%20to%20Log%20Analytic's%20query%20language.%20I%20am%20trying%20to%20figure%20out%20a%20query%20to%20show%20when%20a%20SQL%20server%20node%20failover%20from%20Primary%20to%20Secondary.%20The%20server%20is%20connected%20within%20Azure.%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-683937%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-684974%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20when%20a%20SQL%20server%20node%20failover%20from%20Primary%20to%20secondary%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-684974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358394%22%20target%3D%22_blank%22%3E%40OpsRaven%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESQL%20Event%20IDs%20are%20well%20documented%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Ferrors-events%2Fdatabase-engine-events-and-errors%3Fview%3Dsql-server-2017%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Ferrors-events%2Fdatabase-engine-events-and-errors%3Fview%3Dsql-server-2017%3C%2FA%3E%26nbsp%3Bhowever%20there%20are%20a%20lot%20of%20them!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20guess%20ones%20like%2013221-14223%20might%20help%20-%20but%20someone%20with%20SQL%20experience%20might%20have%20to%20conform%20if%20you%20cant%20observe%20these%20yourself%20in%20the%20logs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%2F%2F%20find%203%20named%20Event%20Ids%0A%0AEvent%0A%7C%20where%20EventID%20in%20(13221%2C13222%2C13223)%0A%7C%20summarize%20count()%20by%20EventID%2C%20RenderedDescription%20%3C%2FPRE%3E%0A%3CP%3Eor%20maybe%20look%20in%20your%20work%20space%20with%20a%20query%20below%2C%20or%20run%20it%20in%20the%20demo%20system%2C%20online%20to%20see%20what%20it%20does%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA3MtS80r4eWqUShOTSxKzlBQCg70UQLzS3NzE4syq1IVkioVXEGqPF10FIJS81JSi1JTXFKLk4syC0oy8%252FO4ACgPnRBDAAAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EEvent%0A%7C%20search%20%22SQL%22%0A%7C%20summarize%20by%20EventID%2C%20RenderedDescription%0A%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA3MtS80r4eWqUShOTSxKzlBQCg70UQLzS3NzE4syq1IVkioVXEGqPF10FIJS81JSi1JTXFKLk4syC0oy8%252FNAinMyczNLFAy5APJsu1pOAAAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3CBR%20%2F%3EExample%20output%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EEventID%3C%2FTH%3E%0A%3CTH%3ERenderedDescription%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E59006%3C%2FTD%3E%0A%3CTD%3E%5BWarning%5D%20CredentailManagementSupportedCheckFailed%3A%20System.Exception%3A%20Exception%20while%20getting%20version%20of%20sql%20server.%20---%26gt%3B%20Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Contract.IaaSAgentSqlQueryException%3A%20SQL%20number%3A%2018456%2C%20SQL%20error%3A%20Login%20failed%20for%20user%20'CONTOSORETAIL%5CCONTOSOSQLSRV2%24'.%20---%26gt%3B%20System.ServiceModel.FaultException%601%5BMicrosoft.SqlServer.Management.IaaSAgentSqlQuery.Contract.SqlQueryExceptionFault%5D%3A%20The%20creator%20of%20this%20fault%20did%20not%20specify%20a%20Reason.%20Server%20stack%20trace%3A%20at%20System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime%20operation%2C%20ProxyRpc%26amp%3B%20rpc)%20at%20System.ServiceModel.Channels.ServiceChannel.Call(String%20action%2C%20Boolean%20oneway%2C%20ProxyOperationRuntime%20operation%2C%20Object%5B%5D%20ins%2C%20Object%5B%5D%20outs%2C%20TimeSpan%20timeout)%20at%20System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage%20methodCall%2C%20ProxyOperationRuntime%20operation)%20at%20System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage%20message)%20Exception%20rethrown%20at%20%5B0%5D%3A%20at%20System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage%20reqMsg%2C%20IMessage%20retMsg)%20at%20System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData%26amp%3B%20msgData%2C%20Int32%20type)%20at%20Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Contract.ISqlQueryService.GetSqlServerProperties()%20at%20Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Client.IaaSAgentSqlQueryClient.HandleSqlQueryCalls%5BT%5D(Func%602%20func%2C%20Int32%20retryCount%2C%20Int32%20retryIntervalInSeconds)%20---%20End%20of%20inner%20exception%20stack%20trace%20---%20at%20Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Client.IaaSAgentSqlQueryClient.HandleSqlQueryCalls%5BT%5D(Func%602%20func%2C%20Int32%20retryCount%2C%20Int32%20retryIntervalInSeconds)%20at%20Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementQueries.GetSqlServerProperties()%20at%20Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementHelpers.IsSqlSupportedVersion()%20at%20Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementHelpers.CheckIfCredentialManagementSupported()%20---%20End%20of%20inner%20exception%20stack%20trace%20---%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello, I am fairly new to Log Analytic's query language. I am trying to figure out a query to show when a SQL server node failover from Primary to Secondary. The server is connected within Azure.

Any suggestions?

1 Reply
Highlighted

@OpsRaven 

 

SQL Event IDs are well documented: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-e... however there are a lot of them!

 

I'd guess ones like 13221-14223 might help - but someone with SQL experience might have to conform if you cant observe these yourself in the logs.

 

 

// find 3 named Event Ids

Event
| where EventID in (13221,13222,13223)
| summarize count() by EventID, RenderedDescription 

or maybe look in your work space with a query below, or run it in the demo system, online to see what it does?

 

Go to Log Analytics and Run Query

 

Event
| search "SQL"
| summarize by EventID, RenderedDescription

Go to Log Analytics and Run Query
Example output:

 

EventID RenderedDescription
59006 [Warning] CredentailManagementSupportedCheckFailed: System.Exception: Exception while getting version of sql server. ---> Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Contract.IaaSAgentSqlQueryException: SQL number: 18456, SQL error: Login failed for user 'CONTOSORETAIL\CONTOSOSQLSRV2$'. ---> System.ServiceModel.FaultException`1[Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Contract.SqlQueryExceptionFault]: The creator of this fault did not specify a Reason. Server stack trace: at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Contract.ISqlQueryService.GetSqlServerProperties() at Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Client.IaaSAgentSqlQueryClient.HandleSqlQueryCalls[T](Func`2 func, Int32 retryCount, Int32 retryIntervalInSeconds) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Client.IaaSAgentSqlQueryClient.HandleSqlQueryCalls[T](Func`2 func, Int32 retryCount, Int32 retryIntervalInSeconds) at Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementQueries.GetSqlServerProperties() at Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementHelpers.IsSqlSupportedVersion() at Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementHelpers.CheckIfCredentialManagementSupported() --- End of inner exception stack trace ---