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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ted BouskillSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.