August 9, 2024 at 10:57 am
How do I (ping/check if up and running) a sqlinstance from t-sql ?
August 9, 2024 at 8:13 pm
You can do it a couple ways.
begin try
exec sp_testlinkedserver YOURINSTANCEHERE
select 'Instance Up'
end try
begin catch
select 'Instance Down'
end catch
2. Enable xp_cmdshell and do a ping to the server. This just makes sure the server is up, not necessarily if the instance is running. xp_cmdshell can also be a security audit issue and have potential security implications. If you do enable, it is recommend you disable after the task is complete.
-- To allow advanced options to be changed.
EXECUTE sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXECUTE sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
-- Ping your server and do what you want with the output
EXEC xp_cmdshell 'ping YOURSERVERNAME';
GO
August 9, 2024 at 8:42 pm
Just my 2 cents on this - I wouldn't rely on "ping". PING will only tell you that the computer is online assuming ICMP isn't disabled at the firewall. It tells you nothing about the SQL instance itself.
I'd also be a bit careful with linked servers and scheduling jobs for that to check for uptime. That gets messy as your company spins up and shuts down instances, you need to adjust jobs. PLUS you need to be sure that your "watcher" SQL instance can access the other SQL instances. Plus, I'm not a fan of creating "clutter" on my SQL instances and objects (including linked servers) that don't serve a business purpose, I like to clean up where possible.
What I would recommend is buying a SQL Monitor tool. It'll tell you when things are up and down as well as a lot of other useful metrics. Use the right tool for the job. Just like you wouldn't (well, shouldn't) store binary data in Git or store files in SQL Server, I wouldn't build my own SQL monitoring solution when a LOT of good options already exist and are not that expensive for what you get out of them. There are even free options, but they are a lot more limited.
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.
August 10, 2024 at 1:54 pm
Without a tool what are the options.
We have atleast 200+ instances and I would like a report which states whether the windows box is pingable, if the sqlinstance is up or not, if we have rdp access, if we have sqlengine access .
August 10, 2024 at 3:38 pm
Without a tool what are the options.
We have atleast 200+ instances and I would like a report which states whether the windows box is pingable, if the sqlinstance is up or not, if we have rdp access, if we have sqlengine access .
you are therefore trying to use the wrong tool for it - use powershell (or c#) to check for most of those. RDP can also be tested but more tricky as RDP may be enables on the server (highly likely) but your accounts may not have RDP access to the server.
testing the instance and the server may also not be trivial depending on how your security is setup - you may not be able to ping a server at all even if the server is alive and kicking - as for instance being up - if not blocked by firewalls, you can check it by connecting to it - and then issue a SQL statement to see what type of access you have.
all results of this can then be loaded onto a SQL table on your DBA server - but don't try and do it from within a SP as that is the wrong tool for it.
August 19, 2024 at 6:55 am
I would second what frederico says. You should be doing this type of thing outside SQL server.
Be aware that the response from PING can be very variable and hard to parse, particularly if the instance is not available. Likewise, WIN32_Pingstatus is hard to work with. Depending on your network use, you also may want to check both IPv4 and IPv6 access.
I found the most reliable way to check if if an OS instance is available is to programatically identify an appropriate DNS server and then use DNS functions to check if the desired target exists in your domain and check if it is up and accessible. However these require permission to access the DNS service.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply