Sql Server Database Status

[William Oliveri]

Hi all,

I'm writing some code for disaster recovery. I need to have an application
which accesses Sql Server to determine if the local database is up or not
and then if not go to the secondary server.

So like this:

1. Determine if Sql Server A is up, if so is the database active.
2. If no on either count, go to Sql Server B/database.

Is there some way to query Sql Server through the Dot Net framework to
determine the server/database status?

Thank in advance,

Bill

[Bernie Yaeger]

Hi BIll,

Here's what I do. The key is an sproc I've created called testdatabase.
Here is its code:
CREATE PROCEDURE testdatabase AS
select databasepropert yex('imc','stat us')
where 'imc' is the name of the server to check. The code returns a string,
which I then report in the statusbar of my app, as follows:
Dim oconn As New SqlConnection(g lobalconnection string)

oconn.Open()

Dim ocmd As New SqlCommand

ocmd = New SqlCommand("exe c testdatabase", oconn)

Dim statusstring As String

Try

statusstring = ocmd.ExecuteSca lar()

Catch ex As Exception

MessageBox.Show (ex.Message)

End Try

oconn.Close()

If statusstring = "ONLINE" Then

StatusBar1.Pane ls(6).Text = "Database Online"

Me.ErrorProvide r1.SetError(Sta tusBar1, "")

ElseIf statusstring = "OFFLINE" Then

StatusBar1.Pane ls(6).Text = "Database Offline"

Me.ErrorProvide r1.SetIconAlign ment(StatusBar1 , ErrorIconAlignm ent.TopLeft)

Me.ErrorProvide r1.SetError(Sta tusBar1, "Database Offline")

ElseIf statusstring = "RESTORING" Then

StatusBar1.Pane ls(6).Text = "Database Restoring"

Me.ErrorProvide r1.SetIconAlign ment(StatusBar1 , ErrorIconAlignm ent.TopLeft)

Me.ErrorProvide r1.SetError(Sta tusBar1, "Database Restoring")

ElseIf statusstring = "RECOVERING " Then

StatusBar1.Pane ls(6).Text = "Database Recovering"

Me.ErrorProvide r1.SetIconAlign ment(StatusBar1 , ErrorIconAlignm ent.TopLeft)

Me.ErrorProvide r1.SetError(Sta tusBar1, "Database Recovering")

ElseIf statusstring = "SUSPECT" Then

StatusBar1.Pane ls(6).Text = "Database Suspect"

Me.ErrorProvide r1.SetIconAlign ment(StatusBar1 , ErrorIconAlignm ent.TopLeft)

Me.ErrorProvide r1.SetError(Sta tusBar1, "Database Malfunctioning - Call
Cherwell Immediately")

End If

HTH,

Bernie Yaeger

"William Oliveri" <wu**@bigvalley .net> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..

Hi all,

I'm writing some code for disaster recovery. I need to have an application which accesses Sql Server to determine if the local database is up or not and then if not go to the secondary server.

So like this:

1. Determine if Sql Server A is up, if so is the database active.
2. If no on either count, go to Sql Server B/database.

Is there some way to query Sql Server through the Dot Net framework to
determine the server/database status?

Thank in advance,

Bill