Verify Server online status using sql server query
-
Brian,
Here's what my morning report looks like. It only reports missing servers, disks that have 10% or less free space, or disks that have the "IsDirty" bit (indicates that a ScanDisk needs to be execute to fix any bad sectors). If a system has no such problems, it's not included in the morning report but ALL disks for all servers in the server list are captured in a special table for use by DBAs.
The Blue grid reports on any removable media left in the servers so that the removable media can easily be located.
Of course, I've obfuscated all the server names here.
If this looks like it would be what you would need, post back and I'll provide the fully documented code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello Jeff,
Not sure if it is useful for the OP, but it does look like an interesting report to me.
I would like to have a copy of that if you can share the code.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Hello Jeff,
Any update on that code? I am curious to look at it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - Mr. Brian Gale wrote:
Hello Jeff,
Any update on that code? I am curious to look at it.
See attached.
There are 3 files in the ZIP file...
The "Create Table DiskStatus" will create the table where the server names need to be stored. The other two files are stored procedures that need to be created in the same databases as the table.
I run the SaveDiskStatus stored procedure on a nightly basis to collect the information I want and store it in the DiskStatus table. Note that the SaveDiskStatus stored procedure does us xp_CmdShell to make WMI calls which, if setup correctly, isn't a security risk when used by SQL Agent or a DBA that has sysadmin privs.
The MorningReportDisks stored procedure reports on any rows inserted into the DiskStatus table the previous day. You can fine tune or change that to suit you. I also use CDOSYS for server emails rather than sp_send_dbmail to send the HTML formatted email. You'll probably need to change last part of the proc to use sp_send_dbmail. There is a "ToDo" in this file, as well. Look for the term "ToDo" to find it. You need to change the domain name to send the email.
The code is fairly well documented but, if you have any questions that the embedded documentation doesn't answer, please don'tn hesitate to ask.
{EDIT} I absolutely hate this "new" forum. It won't allow me to change out an already saved file and the code blocks absolutely suck for multiple reasons. Anyhow, use the ZIP-1 file. It has a couple of corrections. I see if I can get management to delete the first zip file.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Thom A wrote:
One idea, using Powershell, would be something like this.
Firstly set up some test objects:
CREATE TABLE dbo.ServerList (ServerID int IDENTITY PRIMARY KEY,
ServerName sysname);
CREATE TABLE dbo.ConnectionTest (TestID int IDENTITY PRIMARY KEY,
ServerID int NOT NULL,
TestTime datetime2(0) NOT NULL,
Success bit NOT NULL);
ALTER TABLE dbo.ConnectionTest ADD CONSTRAINT FK_Server FOREIGN KEY (ServerID) REFERENCES dbo.ServerList (ServerID);
GO
INSERT INTO dbo.ServerList (ServerName)
VALUES('srvsql2012dev'),
('srvsql2012uat'),
('srvwww01'),
('srvdc1'),
('srvdc2');Then a Powershell script like below:
$Servers = Invoke-Sqlcmd -Query "SELECT ServerID, ServerName FROM dbo.ServerList" -ServerInstance "srvsql2012dev\Sandbox" -Database "Sandbox"
foreach ($Server in $Servers)
{
$ServerID = $Server.ServerID
$ServerName = $Server.ServerName
If (Test-Connection -ComputerName $ServerName -Count 1 -ErrorAction silentlycontinue)
{
Invoke-Sqlcmd -Query "INSERT INTO dbo.ConnectionTest (ServerID, TestTime, Success) VALUES ($ServerID,SYSDATETIME(),1);" -ServerInstance "srvsql2012dev\Sandbox" -Database "Sandbox"
}
else
{
Invoke-Sqlcmd -Query "INSERT INTO dbo.ConnectionTest (ServerID, TestTime, Success) VALUES ($ServerID,SYSDATETIME(),0);" -ServerInstance "srvsql2012dev\Sandbox" -Database "Sandbox"
}
}Which might then insert some data like the below:
TestID ServerID TestTime Success
----------- ----------- --------------------------- -------
1 1 2020-02-19 10:20:14 1
2 2 2020-02-19 10:20:14 1
3 3 2020-02-19 10:20:14 1
4 4 2020-02-19 10:20:17 0
5 5 2020-02-19 10:20:17 1Note, this was written for Powershell, not Powershell Core.
Test-Connectionis quite different in Powershell Core, and this will not work correctly in it, asTest-Connectiondoes not fail in the event that the ping times out. In Powershell, if you attempt to useTest-Connectionagainst a host that does not respond (but is in the DNS), you'll get the below:PS C:\> Test-Connection -ComputerName UnresponsiveHost -Count 1
Test-Connection : Testing connection to computer 'UnresponsiveHost' failed: A non-recoverable error occurred during a database
lookup
At line:1 char:1
+ Test-Connection -ComputerName UnresponsiveHost -Count 1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ResourceUnavailable: (UnresponsiveHost:String) [Test-Connection], PingException
+ FullyQualifiedErrorId : TestConnectionException,Microsoft.PowerShell.Commands.TestConnectionCommandOn the other hand, if you try this in Powershell Core, you'll get the below:
PS C:\> Test-Connection -ComputerName UnresponsiveHost -Count 1
Pinging UnresponsiveHost [192.168.1.1] with 32 bytes of data:
Request timed out.
Ping complete.
Source Destination Replies
------ ----------- -------
Local UnresponsiveHost {System.Net.NetworkInformation.PingReply}Considering that Powershell is still the default CLI and Powershell Core isn't installed "out of the box", even on Windows 10 1909, I made the assumption that Powershell was the correct choice.
regarding the use of test-connection - it will throw that error in some weird cases even with the computer online - I found out that it was better to use the following construct (which never fails) than to rely on a "broken" powershell cmdlet
$online=$false
$reply = $null
$a=ping -n 10 servername.domain.com
$a|ForEach-Object {
if ($_ -match 'reply \s*(.*)$')
{
$reply += $matches[1]
}
}
if ($reply)
{
$online = $true
} -
thanks for the code Jeff.
Downloading it and looking through that today!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Let me know how it works out for you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
I sent you a PM on the scripts because I feel we are getting a bit off topic from the original post. If you prefer, I can post it here.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply