Analysis to check if SQL Server is Pimary/Secondary

Content Authoring
1

Is the are an available inspector that can be used to check if SQL Server is primary or not?

2 Likes
2

Iā€™m not sure about that specifically. How would you find if ā€œif SQL Server is primary or notā€ in the registry or WMI or on the file system or through a command?

Document how you would find the answer WITHOUT using BigFix, ideally on the command line or through something BigFix can easily inspect, then I can help figure out the best way to get the answer WITH using BigFix.

I know how to help inspect things with BigFix, but I donā€™t know how to determine if a SQL Server is primary or not.

Also, see these related analyses for hints:

Example Relevance for getting raw MSSQL data in the Fixlet Debugger:

selects "* from SqlServiceAdvancedProperty" of wmis ("ROOT\Microsoft\SqlServer\ComputerManagement" ; "ROOT\Microsoft\SqlServer\ComputerManagement10" ; "ROOT\Microsoft\SqlServer\ComputerManagement11" ; "ROOT\Microsoft\SqlServer\ComputerManagement12" ; "ROOT\Microsoft\SqlServer\ComputerManagement13" ; "ROOT\Microsoft\SqlServer\ComputerManagement14" ; "ROOT\Microsoft\SqlServer\ComputerManagement15")

This is not helpful unless you are writing relevance in weird ways, but I actually used this relevance to write that relevance above:

Q: ("selects %22* from SqlServiceAdvancedProperty%22 of wmis (%22" & it & "%22)") of concatenations "%22 ; %22" of unique values of ("ROOT\Microsoft\SqlServer\ComputerManagement" & it) of ("";(it as string)) of integers in (10, 15)
A: selects "* from SqlServiceAdvancedProperty" of wmis ("ROOT\Microsoft\SqlServer\ComputerManagement" ; "ROOT\Microsoft\SqlServer\ComputerManagement10" ; "ROOT\Microsoft\SqlServer\ComputerManagement11" ; "ROOT\Microsoft\SqlServer\ComputerManagement12" ; "ROOT\Microsoft\SqlServer\ComputerManagement13" ; "ROOT\Microsoft\SqlServer\ComputerManagement14" ; "ROOT\Microsoft\SqlServer\ComputerManagement15")
T: 8.859 ms
I: plural string

Example relevance to determine if a MSSQL server is clustered (this should probably be tweaked to only examine when ServiceName=MSSQLServer

Q: unique values of string values of properties "PropertyNumValue" of select objects "* from SqlServiceAdvancedProperty" whose(exists string values whose(it = "CLUSTERED") of properties "PropertyName" of it) of wmis ("ROOT\Microsoft\SqlServer\ComputerManagement" ; "ROOT\Microsoft\SqlServer\ComputerManagement10" ; "ROOT\Microsoft\SqlServer\ComputerManagement11" ; "ROOT\Microsoft\SqlServer\ComputerManagement12" ; "ROOT\Microsoft\SqlServer\ComputerManagement13" ; "ROOT\Microsoft\SqlServer\ComputerManagement14" ; "ROOT\Microsoft\SqlServer\ComputerManagement15")
A: 0
T: 403.009 ms
4 Likes
3

@jgstew

Will you be able to assist with creating something similar to the analysis provided on this thread? Checking for active/passive node of cluster

Then use ā€˜MSCluster_ClusterToNodeā€™ to get the ā€˜RoleOfNodeā€™ as shown on this link:

4

If you give me a working WMI query I can help translate that into relevance, but I donā€™t have a way to test this or write this myself since I donā€™t have a clustered system. I can mostly give you pointers.

Can you show me a working query that gives you what you want in WMI Explorer or PowerShell or wmic or similar?

This is purely a guess, I donā€™t really know if this is right, but it should be close based upon the documentation:

tuple string items ( (it as integer) of string values of selects "RoleOfNode from MSCluster_ClusterToNode" of wmis "Root\MSCluster" ) of "Unknown, Other, Peers, Primary, Secondary, Standby"

I donā€™t have a system to verify this on myself. If this does not work, then please provide a working WMI example in PowerShell or similar so that I can translate it into Relevance.