Develop SQL SERVER Monitoring website?

Question

Post reply

Develop SQL SERVER Monitoring website?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 19, 2012 at 1:37 am

#262700

Hi,
Basic monitor SQL server database for backup,SQL Jobs etc., through website by using any script (C#, aspx).
Could anyone share the script for how to develop the monitor application?
Thanks for any help

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Just buy a 3rd party monitoring tool. Redgate's SQLMonitor is web-based.

Otherwise you'll be spending months writing your own.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Pl. provide, any open source webpage script is available?

terryj30 SSCommitted Points: 1926 More actions · Answer 3

Here is some code that may help, this will allow you to add 6 procs look at line 82 its where you can added your connection string also look at procs 1 to 6 you can add your store procs there. All the best

<%@ Page Language="VB" debug="true" %>

<%@ Import Namespace = "System.Data.SQLClient" %>

<%@ Import Namespace = "System.Data" %>

<script runat="server" language="vb">

Function dr2tablerows(poDr)

Dim iLastColumn as integer

dim i as integer

Dim sb as new stringbuilder("")

iLastColumn = poDr.FieldCount- 1

sb.Append("<TR class='header_row' >")

for i = 0 to iLastColumn

select case poDr.GetName(i).toLower

case "ignorethisfield"

case else

sb.Append("<TD>")

sb.Append(poDr.GetName(i))

sb.Append("</TD>")

end select

next i

sb.Append("</TR>")

sb.Append(vbCrLf)

Dim sRowPrefix As String = ""

While poDR.Read

sRowPrefix = "<TR class='table_row' >"

If poDr.GetName(0).toLower = "sql server cpu utilization (%)" Then

If CDbl(poDr.Item("SQL Server CPU Utilization (%)")) > 75 Or CDbl(poDr.Item("System Idle Process (%)")) < 35 Then

sRowPrefix = "<TR class='table_row_red' >"

Else

sRowPrefix = "<TR class='table_row_green' >"

End If

sb.Append(sRowPrefix)

for i = 0 to iLastColumn

sb.Append("<TD>")

select case poDr.GetName(i).toLower

case "ignorethisfield"

case Else

sb.Append(poDr.Item(i).ToString)

end select

sb.Append("</TD>")

next i

sb.Append("</TR>")

sb.Append(vbCrLf)

end While

dr2tablerows = sb.ToString

End Function

</script>

<%

Dim sConnectionString as string = "Data Source=;Initial Catalog=teststats;User Id=;Password=;"

Dim sProc1 as string = ""

Dim sProc2 as string = ""

Dim sProc3 as string = ""

Dim sProc4 as string = ""

Dim sProc5 as string = ""

Dim sProc6 as string = ""

Dim sTable1 as string = ""

Dim sTable2 as string = ""

Dim sTable3 as string = ""

Dim sTable4 as string = ""

Dim sTable5 as string = ""

Dim sTable6 as string = ""

Dim sServerName as string = ""

Dim iRefreshTime as integer

Dim sFormat as string = "{0:n0}"

Dim oDR as SQLDataReader

Dim oCmd As SqlCommand

Dim oConn as SQLConnection

try

oConn = New SQLConnection (sConnectionString)

oConn.Open()

oCmd = New SqlCommand()

with oCmd

.Connection = oConn

.CommandType = CommandType.StoredProcedure

.CommandTimeOut = 1000

end with

oCmd.CommandType = CommandType.Text

oCmd.CommandText = "select @@servername + '..' + db_name() servername"

oDR = oCmd.ExecuteReader()

While oDr.Read

sServerName = oDr.Item("servername")

end while

oCmd.CommandType = CommandType.StoredProcedure

if sProc1 <> "" then

if not IsNothing(oDR) then oDR.Close

oCmd.CommandText = sProc1

oDR = oCmd.ExecuteReader()

sTable1 = dr2tablerows(oDR)

end if

if sProc2 <> "" then

if not IsNothing(oDR) then oDR.Close

oCmd.CommandText = sProc2

oDR = oCmd.ExecuteReader()

sTable2 = dr2tablerows(oDR)

end if

if sProc3 <> "" then

if not IsNothing(oDR) then oDR.Close

oCmd.CommandText = sProc3

oDR = oCmd.ExecuteReader()

sTable3 = dr2tablerows(oDR)

end if

if sProc4 <> "" then

if not IsNothing(oDR) then oDR.Close

oCmd.CommandText = sProc4

oDR = oCmd.ExecuteReader()

sTable4 = dr2tablerows(oDR)

end if

if sProc5 <> "" then

if not IsNothing(oDR) then oDR.Close

oCmd.CommandText = sProc5

oDR = oCmd.ExecuteReader()

sTable5 = dr2tablerows(oDR)

end if

if sProc6 <> "" then

if not IsNothing(oDR) then oDR.Close

oCmd.CommandText = sProc6

oDR = oCmd.ExecuteReader()

sTable6 = dr2tablerows(oDR)

end if

oConn.close

oConn = nothing

oCmd.Dispose

oCmd = nothing

oDR.Close

oDR = nothing

catch ex as exception

'Response.Write("Error:" & ex.message)

sTable1 = "<FONT COLOR=RED><H3>" & "Error: " & ex.message & "</H3></FONT>"

end try

%>

<HTML>

<HEAD>

<TITLE>Database Status - <% response.write(sServerName) %></TITLE>

<META HTTP-EQUIV='refresh' content='60' >

<META HTTP-EQUIV="Pragma" CONTENT="no-cache">

<META HTTP-EQUIV="Cache-Control" CONTENT="no-cache">

<META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">

<META HTTP-EQUIV="Cache-Directive" CONTENT="no-cache">

<META HTTP-EQUIV="Expires" CONTENT="0">

<style type="text/css">

body {font-family:arial,verdana;font-size:12px;padding-left:20px;}

body.mainbody {padding-top:20px;padding-left:60px;}

table {font-family:arial,verdana;font-size:12px;border-collapse: collapse; background-color: white; white-space:nowrap;}

table td {border-width: 1px; text-align:left; vertical-align:top; padding-left: 5px;padding-right:5px;padding-top:5px;padding-bottom:5px; border-style: solid; border-color: gray;white-space:nowrap;}

tr.header_row { background-color: #B8C8D8; font-weight:bold;font-size:12px;padding-left:10px;padding-right:10px;text-decoration:none;}

tr.table_row_red {background-color: red;height:10px;}

tr.table_row_green {background-color: #33FF00;height:10px;}

tr.table_row {font-size:12px;text-align:left;}

.col1 {}

.col2 {}

.col3 {text-align:right; }

.col4 {text-align:right;}

.col5 {}

.col6 {}

.col7 {text-align:right;}

.col8 {text-align:right;}

.col9 {text-align:right;}

.col10 {}

.col11 {}

.col12 {}

.col13 {}

.col14 {}

.col15 {}

.col16 {}

.col17 {}

.col18 {}

.col19 {}

.col20 {}

</style>

</HEAD>

<BODY class=mainbody >

<%

response.write("<H3>Database Status - " & sServerName & "</H3>" )

if sTable1 <> "" then