SQL2005 Database Mirroring

[zarkon4]

I have successfully set up database mirroring on SQL2005
I have two servers, Server1 is the primary with a named instance (SQLSERVER\LPD2005) and ROLE=ALL, port 7022, Server2 is the mirror server and has two named instances (SQLSERVER3\LPD20053)-ROLE=ALL port 7022 and (SQLSERVER3\LPWITNESS)-ROLE=WITNESS port 7022.

I want to provide automatic failover, according to MS doc

http://msdn2.microsoft.com/en-us/library/ms19043.aspx

I can have the witness run on the same server as either the primary or the mirror, hence the reason for another instance name of LPWITNESS.
I try to point the primary to the witness and it is failing with a message of "The witness server instance name must be distinct from both of the server instances that manage the database. The ALTER DATABASE SET WITNESS command failed."

What am I doing wrong? Anyone accomplish this with two servers?

 

[zarkon4]

Never mind, I found the problem.

I had to configure the witness instance to be on a different tcp port than what the principal was on.

Now that that is solved...off to changing vb code to support the auto failover.

 

[mrdenny]

If you use the SQL Native driver you don't have to change any code. Failover should be automatic with a connection string change.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[zarkon4]

Thanks, I did realize that but I have to still make changes to various programs to add the parameter to the connection string.
I suppose we should have just used a DSN.

 

[mrdenny]

DSN's would require a reconfig and from everything that I've seen (but I could be wrong) they don't have anywhere to specify the failover partner.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[zarkon4]

If you use the SQL Native Client you can set the failover server in the DSN. On the third screen, if you set the default database the Mirror Server box becomes available.

 

[zarkon4]

I do have another question, when I am in the mirroing monitor I notice that there are some mirrors that are not connected to the witness and some are, shouldn't they all be connected to the witness?