Client Side Redirect - Database Mirroring

Hi All,

My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)

I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.

Principal - SQL 2005 Enterprise Edition, SP1

Mirror - SQL 2005 Enterprise Edition, SP1

Witness - SQL 2005 Express, SP1

I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors?

(NB, the user running the code is a sys admin on all 3 servers)

Thanks in advance.

Imports System.Data.SqlClient

Public Class Form1

Dim DCTADS As New SqlClient.SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"

DCTADS.Open()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim cmd As New SqlClient.SqlCommand

cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"

cmd.Connection = DCTADS

Dim dr As SqlClient.SqlDataReader

dr = cmd.ExecuteReader

MsgBox(DCTADS.DataSource.ToString)

If dr.Read Then

MsgBox(dr("fulldesc").ToString)

End If

dr.Close()

End Sub

End Class

Here is more detailed discussion for client redirect (http://msdn2.microsoft.com/en-us/library/ms366348.aspx). From the code I did not see any problem. One thing to check is to make sure the TCP/IP and "allow remote connection" are enabled on both principal and mirror because by default these two settings are disable after SQL server is installed.