Powershell script to find SQL Cluster Nodes

Below is the powershell script to find sql cluster nodes. This is similar to the sql script here except this is powershell and there is no need to connect to database server.

Param ([String[]] $ServerInstance)
$Nodelist=Invoke-Sqlcmd -Query "IF ServerProperty('ISClustered')=1 Begin SELECT NodeName,case when serverproperty('instancename') is null then 'MSSQLSERVER' Else serverproperty('instancename') End as
InstanceName,ServerProperty('ServerName') as DatabaseServer FROM sys.dm_os_cluster_nodes End" -ServerInstance $ServerInstance
for($i=0;$i -le $Nodelist.count-1; $i++)
{
get-service -computerName $Nodelist[$i].NodeName |where-Object {$_.DisplayName -like '*'+$NodeList[$i].InstanceName+'*'}|
Select-Object @{Name="ComputerName";Expression={$Nodelist[$i].NodeName}},@{Name="DatabaseServer";Expression={$Nodelist[$i].DatabaseServer}},DisplayName,Status | FT -Auto
}


Advertisement

Powershell Email Notifications

Using the below powershell code, we can ping the servers and set up email notifications, if any server went down. This could scheduled using Task Scheduler and set to run as frequently as possible to get notified as early as possible, in the event of a shut down.


$ServerList = "ServerA","ServerB","ServerC","ServerD"

foreach ($Server in $ServerList)
{
if (-Not(test-Connection -ComputerName $Server -Count 2 -Quiet ))
{
Send-MailMessage -To "xyz@nodomainexists.com","abc@nodomainexists.com" -Subject "The $Server server is not responding." -Body "The $Server server is not responding. This notification is coming from Powershell Script running on MachineName." -SmtpServer yourSMTPServer -From "def@nodomainexists.com"
}
}

SQL Query to Find SQL Cluster Nodes

Below is the SQL Query to find the sql cluster nodes

If(serverproperty('IsClustered'))=1
Begin
SELECT @@servername as DatabaseServer,NodeName,
case when serverproperty('instancename') is null then 'Default Instance - MSSQLSERVER' Else serverproperty('instancename') End as InstanceName,
Case when serverproperty('Computernamephysicalnetbios')=NodeName then 'Active' else 'Passive' End as NodeStatus
FROM sys.dm_os_cluster_nodes
order by Case when serverproperty('Computernamephysicalnetbios')=NodeName then 'Active' else 'Passive' End
End
Else
Begin
Select 'This is database Server is not clustered.'
End

Sample Output :

Finding LastDayofPreviousMonth, FirstDayofCurrentMonth, LastDayofCurrentMonth using a Given date

Using the below query we can get nth month’s Last Day of Previous Month,First Day of Current Month and Last Day of Current Month for any given date.
--Works in all SQL Versions
Select getdate() as CurrentDate,
dateadd(day,-day(getdate()),dateadd(month,n,getdate())) as [LastDayofPreviousMonth],
dateadd(day,-day(getdate())+1,dateadd(month,n,getdate())) as [FirstDayOfCurrentMonth],
dateadd(day,-day(getdate()),dateadd(month,n+1,getdate())) as [LastDayofCurrentMonth]

--Works in SQL 2012 and above
Select EOMonth(getdate(),n-1) as [LastDayofPreviousMonth],
DateAdd(day,1,EOMonth(getdate(),n-1)) as [FirstDayOfCurrentMonth],
EOMonth(getdate(),n) as [LastDayofCurrentMonth]
Example:
Select getdate() as CurrentDate,
dateadd(day,-day(getdate()),dateadd(month,2,getdate())) as [LastDayofPreviousMonth],
dateadd(day,-day(getdate())+1,dateadd(month,2,getdate())) as [FirstDayOfCurrentMonth],
dateadd(day,-day(getdate()),dateadd(month,2+1,getdate())) as [LastDayofCurrentMonth]

Select EOMonth(getdate(),2-1) as [LastDayofPreviousMonth],
DateAdd(day,1,EOMonth(getdate(),2-1)) as [FirstDayOfCurrentMonth],
EOMonth(getdate(),2) as [LastDayofCurrentMonth]