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 databasepropertyex('imc','status')
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(globalconnectionstring)

oconn.Open()

Dim ocmd As New SqlCommand

ocmd = New SqlCommand("exec testdatabase", oconn)

Dim statusstring As String

Try

statusstring = ocmd.ExecuteScalar()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

oconn.Close()

If statusstring = "ONLINE" Then

StatusBar1.Panels(6).Text = "Database Online"

Me.ErrorProvider1.SetError(StatusBar1, "")

ElseIf statusstring = "OFFLINE" Then

StatusBar1.Panels(6).Text = "Database Offline"

Me.ErrorProvider1.SetIconAlignment(StatusBar1, ErrorIconAlignment.TopLeft)

Me.ErrorProvider1.SetError(StatusBar1, "Database Offline")

ElseIf statusstring = "RESTORING" Then

StatusBar1.Panels(6).Text = "Database Restoring"

Me.ErrorProvider1.SetIconAlignment(StatusBar1, ErrorIconAlignment.TopLeft)

Me.ErrorProvider1.SetError(StatusBar1, "Database Restoring")

ElseIf statusstring = "RECOVERING" Then

StatusBar1.Panels(6).Text = "Database Recovering"

Me.ErrorProvider1.SetIconAlignment(StatusBar1, ErrorIconAlignment.TopLeft)

Me.ErrorProvider1.SetError(StatusBar1, "Database Recovering")

ElseIf statusstring = "SUSPECT" Then

StatusBar1.Panels(6).Text = "Database Suspect"

Me.ErrorProvider1.SetIconAlignment(StatusBar1, ErrorIconAlignment.TopLeft)

Me.ErrorProvider1.SetError(StatusBar1, "Database Malfunctioning - Call
Cherwell Immediately")

End If

HTH,

Bernie Yaeger