March 15, 2009 at 10:49 pm
We are developing a script to verify all our SQL servers are alive and responding to connections. The utility doing this "heartbeat" verify isn't a TSQL script; the platform is TBD, but it should be connecting with a standard connection string. If it is relevant, the environment is mixed SQL 2000 and 2005, with a couple of new 2008 and leftover 7.0s.
Is there a standard way to do this? We'd considered doing a "SELECT @@VERSION" or something else simple, because that wouldn't actually require any table access.
What are the *minimum* permissions that would need to be assigned to a SQL user to make this possible?
March 15, 2009 at 11:09 pm
Hello Brad,
It's always good to have proactive work.
Also I suggest that instead of running select @@version you can check the services of the sql server instance. You can refer the following thread for the further reference.
http://www.sqlservercentral.com/Forums/Topic664157-146-1.aspx
HTH!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 17, 2009 at 12:04 pm
Here's a code snippet of mine to get you started - xp_servicecontrol did not exist in v7.0 so you have a voild to fille - likewise I have no 2008 instances - well here you go:
---
if(substring(convert(varchar(32),serverproperty('ProductVersion')),1,1)) = '8'--> SQL 2000
begin
print 'checking MSSQLSERVER service'
exec master..xp_servicecontrol 'QueryState', 'MSSQLSERVER'
print 'checking SQLSERVERAGENT service'
exec master..xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'
print 'checking MSSearch service'
exec master..xp_servicecontrol 'QueryState', 'MSSearch'
end
---
if(substring(convert(varchar(32),serverproperty('ProductVersion')),1,1)) = '9'--> SQL 2005
begin
print 'checking MSSQL service'
exec master..xp_servicecontrol 'QueryState', 'MSSQL'
print 'checking SQLAgent service'
exec master..xp_servicecontrol 'QueryState', 'SQLAgent'
print 'checking MSFTESQL service'
exec master..xp_servicecontrol 'QueryState', 'MSFTESQL'
end
---
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 31, 2010 at 7:28 am
Nicely done. I have a question about checking SQL Server Analysis services. When I check on a named instance, I get the proper status but on a default instance I only get the status of "Running" even though the SSAS server is turn off.
Thank you in advance for your time and efforts.
Rudy
Rudy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply