SQL Mirroring with automated failover?

Dear Sir,

I have already configured an SQL mirroring for my Database with 3 physical servers:
1- Principal  (Identical Hardware) - Windows Server 2003 Standard SP2 - SQL 2005 SP2
2- Mirror        (Identical Hardware) - Windows Server 2003 Standard SP2 - SQL 2005 SP2
3- Witness    Windows Server 2003 R2 Standard SP2 - SQL 2005 SP2

Here is more details about my application:

- I have SUN thin clients (SUN Ray) taking windows session from 3 Windows 2003 TS 64 Bit Entreprise Edition, configured as NLB and running a Windows application. These servers are joined to a domain.
And my 3 SQL servers are joined also to the same domain.
So The Windows application running on my 3 Terminal Servers connected to my SQL Principal Server.

I configured my principal SQL Server as mirroring using the High Availability mode to implement an automated failover for my database.

But the problem is, when I am shutting down my principal SQL server to test the fail over, my application crashed and gives error message on users clients, however the auto failover was working fine, the mirrored SQL became principal and the principal SQL became mirrored.
My application using a default connection to my principal SQL Server IP.
Please find the attached file for more information.

Please any help?

Thank you in advance,
SQL-Configuration.jpg
SQL-Mirroring-Report.doc
LVL 1
ISC-IT-AdminAsked:
Who is Participating?
 
Ted BouskillConnect With a Mentor Senior Software DeveloperCommented:
It will only work if the client supports flipping from SQLInstanceA to SQLInstanceB.  Clustering doesn't have that requirement.  In a cluster there is always a SQLInstance that switches between servers.

Also mirroring is per database.  If you have an application that uses multiple databases mirroring failover is consistent which can cause problems.  For example, Sharepoint 2007 uses multiple databases and does not support mirroring.

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx#E6KAG

If you own the client application and control the SQL connection string you can do a client side redirect like so:
"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;"

If your clients can't automatically redirect to a new SQL instance you need a cluster.
0
 
Ted BouskillSenior Software DeveloperCommented:
Mirroring failover is a manual process.  If you want automatic failover you have to use clustering.  Sorry
0
 
ISC-IT-AdminAuthor Commented:
Hell Tedbilly,

So why there is a witness server in mirroring, ( the configuration is High Saftey with automatic failover) Synchronous??
How can I fix my Issue?

Thank you,
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ISC-IT-AdminAuthor Commented:
Thank you tedbilly,

Please could you give me a detailed document with screen shot if avaialabe on how to configure a cluster for my SQL database?
I have already 2 indentical physical servers with SQL 2005 SP2. But Windows Server 2003 Standard Edition installed.


Thank you for your help,

0
 
Ted BouskillSenior Software DeveloperCommented:
This link has all the information you need:

http://www.microsoft.com/windowsserver2003/technologies/clustering/resources.mspx

Clustering costs more money and is far more difficult to set up.  Ultimately you have to decide how important it is to have 100% automatic failover.
0
 
ISC-IT-AdminAuthor Commented:
Ouf I understand.
Thank you a lot tedbilly,

Honestly, I am thinking about using mirroring auto failover because we own our client application with SQL connection.
So, as you told me how can we redirect the DB if the principal server goes down?
"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;"

I need some more information regarding the auto redirecting.
Bcz I already configured my SQL DB as mirroring using 3 physcial servers with Witness (High Availability).
But still only this issue when the Main SQL goes down the application crashed, bcz the default connection doesn't exist anymore to the prinicipal server.

Thank you for your help.




0
 
Ted BouskillSenior Software DeveloperCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.