SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I HAVE TO WRITE ONE STORE PROCEDURE THAT EVERY MIN IT HAS TO CHECK MY SQL INSTANCE IS UP AND RUNNING...


I HAVE TO WRITE ONE STORE PROCEDURE THAT EVERY MIN IT HAS TO CHECK MY SQL INSTANCE IS UP AND RUNNING IF NOT IT HAS TO SEND MAIL. PLEASE HELP ME HOW TO WRITE

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88284 Visits: 41128
kenn3th.w0ng (8/25/2014)
Stored procedures is not always the answer...


Correct. They're the answer only 99.5% of the time. ;-)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40286 Visits: 38567
kenn3th.w0ng (8/25/2014)
Any thoughts on keeping it simple and use SSIS to make an Execute SQL Task for each database you want to poll to check if it is alive?

The idea is to run a SQL query against any table (e.g. select count(*) from <table>Wink on any schema of the target server and set the Execute SQL Task to if-fail, then put a subsequent action to send an e-mail with a short text of your choice.

Start somewhere simple and get the setup running to see if the architecture works. You can refine and add bells and whistles later.

With this path, you don't need to do much coding and testing and get the job done in a day or two.

Stored procedures is not always the answer and it's rather localized to one database server. Even with Linked Servers, the code is on one server rather than an independent SSIS server (potentially) overseeing all the SQL Servers.


And even then it is on one server:

an independent SSIS server (potentially) overseeing all the SQL Servers


First, I wouldn't do a select count(*) on a table in a database even if I was sure it was a table with one or only a few rows. Never know when that might change. You can still use SELECT @@SERVERNAME in the context of the database you are checking for access. If you can't connect to the database, the query will fail.

Again, if I had to do this today, I would do it in PowerShell. I could also have it run from several remote servers to ensure some redundancy.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
kenn3th.w0ng
kenn3th.w0ng
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 44
The idea is not to test the contents of the table, but the ability to execute anything on a particular schema or database. The fact that you could tells me that it passes the litmus test that the server is online.

There's also a problem with the need to centrally manage the code and the configuration. I know there's something for registered servers in SSMS, but we're doing this in the context that it's a cheap non-NetCool solution to monitoring outage.
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12597 Visits: 18584
Jeff Moden (8/24/2014)
Steve Jones - SSC Editor (8/24/2014)
Please don't use all caps. It is equivalent to yelling. Also please don't cross post (removed the other one).

You don't want to do this in SQL Server. Use Windows at the service level to check if the service is running, and if not, send an email that way.


How would one do such a thing?

As a bit of a side bar and a play on words, "Just because you can do something in SQL Server, doesn't mean you SHOULDN'T"! ;-)


Well for one - there's SQLH2 (SQL Server health and history tool) from MS. Download, install, and configure. Barring that - there are literally dozens of WMI monitoring tools which can do this with a few clicks.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40286 Visits: 38567
kenn3th.w0ng (8/25/2014)
The idea is not to test the contents of the table, but the ability to execute anything on a particular schema or database. The fact that you could tells me that it passes the litmus test that the server is online.

There's also a problem with the need to centrally manage the code and the configuration. I know there's something for registered servers in SSMS, but we're doing this in the context that it's a cheap non-NetCool solution to monitoring outage.


Then why do a SELECT COUNT(*) FROM someschema.sometable? Like I said, a simple SELECT @@SERVERNAME works.

As Mark said as well, there are tools out there as well. Some are free, why not use them? If it has to be something developed in house, I still think PowerShell is a very good tool for this particular application.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
kenn3th.w0ng
kenn3th.w0ng
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 44
Key point - proof of concept. We got to start somewhere.
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40286 Visits: 38567
kenn3th.w0ng (8/25/2014)
Key point - proof of concept. We got to start somewhere.


So what's the difference between SELECT COUNT(*) FROM someschema.sometable and SELECT @@SERVERNAME? Both will provide proof of concept except one doesn't have to worry about accessing a table in a database. And, you don't have to worry about trying to find a table common in multiple databases on multiple servers if that is your environment.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64283 Visits: 19117
Jeff Moden (8/24/2014)
Steve Jones - SSC Editor (8/24/2014)
Please don't use all caps. It is equivalent to yelling. Also please don't cross post (removed the other one).

You don't want to do this in SQL Server. Use Windows at the service level to check if the service is running, and if not, send an email that way.


How would one do such a thing?

As a bit of a side bar and a play on words, "Just because you can do something in SQL Server, doesn't mean you SHOULDN'T"! ;-)


Windows will monitor service status and can take action when something stops, just like an alert in SQL Server. It's easy to script this to notify you. You can also have this alert in the Event logs, which means any monitoring system will pick it up.

http://social.technet.microsoft.com/Forums/windowsserver/en-US/6ee23534-01ba-454b-b823-15fb243a51f7/how-to-send-email-msg-if-a-windows-service-stops?forum=winservergen

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Leonard Rutkowski
Leonard Rutkowski
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 1213
Here is some Powershell code, that I set up in a SQL job, to run every 30 minutes. I have a list of Instances in an AdminServer. I use that, to determine which servers to check. You will have to change the AdminServer and AdminDB, or hardcode the list, in the script. I also use a proxy, for connection on remote servers.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Initialize Arrays
$FailedConnections = @()

#Retrieve Instance list from database.
$ServerList = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "AdminServer"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $ServerList.Databases.Item("AdminDB")
$ds = $db.ExecuteWithResults("SELECT [InstanceName] FROM [AdminDB].[dbo].[SQLServerInfo] WHERE AllowConnection = 1 AND CheckConnection = 1 ORDER BY InstanceName")

# Loop through all instances
FOREACH ($InstanceRow in $ds.Tables[0].Rows)
{
Try
{
$Instance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceRow.Item("InstanceName")
IF(!$Instance.Product)
{
$FailedConnections += "" | SELECT @{Name='InstanceName';Expression={$InstanceRow.Item("InstanceName")}}, @{Name='Message';Expression={"Connection failed to instance"}}
}
ELSE
{
Try
{
IF(!$Instance.JobServer.ServiceAccount)
{
$FailedConnections += "" | SELECT @{Name='InstanceName';Expression={$InstanceRow.Item("InstanceName")}}, @{Name='Message';Expression={"SQL Server Agent is not accessible"}}
}
}
Catch
{
$FailedConnections += "" | SELECT @{Name='InstanceName';Expression={$InstanceRow.Item("InstanceName")}}, @{Name='Message';Expression={"SQL Server Agent is not accessible"}}
}
}
}
Catch
{
$FailedConnections += "" | SELECT @{Name='InstanceName';Expression={$InstanceRow.Item("InstanceName")}}, @{Name='Message';Expression={"Connection failed to instance"}}
}
}

# Set html style
$a = "<style>"
$a = $a + "<!-- "
$a = $a + " TD{font-family: Arial; font-size: 8pt;} "
$a = $a + "--->"
$a = $a + "BODY{background-color:peachpuff;}"
$a = $a + "TABLE{font-family: Arial;font-size: 8pt;width:100%; height:75%; border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-colorTonguealeGoldenrod}"
$a = $a + "</style>"

IF ($FailedConnections)
{
# Send Email listing failed SQL Server jobs.
$smtpServer = "smtpserver"
$smtpFrom = "From@your.com"
$smtpTo = "To@your.com"
$messageSubject = "SQL Server Connection Failure Report"
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true
$message.Body = $FailedConnections | SELECT @{Name='Instance Name';Expression={$_.InstanceName}}, Message | ConvertTo-HTML -head "<H2>SQL Server Connection Failure Report</H2>" -body $a
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)

}
stadmanwi
stadmanwi
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 147
Not a best practice by any means.

Run a sql agent, set the task to powershell. I believe you need to be admin on box, etc... So not meant for large organizations.



try {
Get-Service MSSQLServer -ComputerName (insert your servername here) -EA stop
}

catch {
Send-MailMessage -To "stadman@test.org" -Subject "(insert your servername here) sql service is not running." -Body "Please consult the system event logs." -SmtpServer mail.test.org -From sqlchecker@test.org
}


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search