Link to home
Start Free TrialLog in
Avatar of czaz
czazFlag for United States of America

asked on

Databases didn't automatically failover

I have setup database mirroring with a witness on multiple databases.  For a test I shutdown the production database server and it doesn't appear that the databases automatically failed over to the mirror server like it was supposed to.  Currently all of the mirrored databases are in a Disconnected/In Recovery state.  Any ideas on what went wrong?
Avatar of jogos
jogos
Flag of Belgium image

DB mirroring <> failover cluster.
Avatar of czaz

ASKER

What's that supposed to mean?
Avatar of Paul MacDonald
Mirroring just duplicates the data.  It's not the same as Microsoft Clustering Services which allows mulitple servers to share resources so those resources are highly available.
Avatar of czaz

ASKER

When I setup the mirroring, it asked me for a witness server, which I setup, which states for operating mode:  "High Safty with automatic failover (synchronous) - Requires a witness server instance.  Commit changes at both the principal and mirror if both are available.  The witness controls automatic failover to the mirror if the principal becomes unavailable." That sounds to me like it should have automatically made the mirror server the principal server when I shut it off, correct?
In that context it just means the failover (backup) server becomes the pricipal server - that is, when the failed server comes back online, changes will replicate from the failover server back to the original server.  In other words, the "automatic failover" refers to database synchronization, not to the SQL server service.

In your specific case it seems the databases should have come online.  Is it possible the failover server didn't have good copies of all the data/transaction files?
Avatar of czaz

ASKER

So the best way to have real failover in the event that one goes down is clustering?
Yes, but note that your data will be in one place.  That is all the servers in the cluster share the same datastore so synchronizing the data to another server (or at least doing good backups) is still advised.
Avatar of czaz

ASKER

Hmm...Are there any other ways for an automatic failover to work with mirroring and not having a sql cluster?
You can specify the failover server in your SQL connection strings so if a connection to your default server times out, the connection can try the failover server.  That's the next best thing.
Avatar of czaz

ASKER

How do I go about doing that?
This is (just about) everything you need to know:
http://msdn.microsoft.com/en-us/library/5h52hef8(v=vs.80).aspx

For the connection string addition, see the section titled "Specifying the Failover Partner".
Avatar of czaz

ASKER

Where do I go within SQL to add the connection string?
Ah, the connection string isn't in the SQL server itself, but in the application that accesses the SQL server.  If you're using COTS software, it probably won't support a failover partner.  If you're writing your own (either desktop or web ) you could specify it yourself.
Avatar of czaz

ASKER

Ohhh...This is for sharepoint 2010.  
Avatar of JohnQM
JohnQM

SharePoint 2010 is mirroring aware, however in order to get it to failover you will need to configure the appropriate settings in SharePoint central admin as well as doing the SQL Server elements.

This TechNet article gives a good overview of setting up SharePoint 2010 (foundation in this case) for mirroring with SQL Server http://technet.microsoft.com/en-us/library/ff628961.aspx, however you should be aware that there are a number of pitfalls that need to be watched for with mirroring. Things like making sure that Server/Instance level objects such as Logins and Agent jobs need to be syncronised on the failover server to make sure that in the event of a failover accounts can login to the databases and the appropriate support jobs are in place, there are other things beyond these too.

Hope this helps a little.

JQ
Avatar of czaz

ASKER

I have gone through that process.  My problem is the witness server doesn't seem to be working properly.  If I stop the SQL services on the principal server, it does not automatically make the mirror server the principal server like I believe its supposed to.  
ASKER CERTIFIED SOLUTION
Avatar of Paul MacDonald
Paul MacDonald
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of czaz

ASKER

I was actually looking into that and noticed during that time that the witness server somehow had an older version of 2008 R2, so I'm currently updating the three SQL servers to 2008 R2 service pack 1.  I'll let you know if that resolves the problem or not.
Avatar of czaz

ASKER

Well having the SQL servers all on the same version didn't work.  I believe I tried using a SQL 2005 and a regular SQL 2008 witness server, but I couldn't get it to work before, but may try again.  Is a SQL 2005 or SQL 2008 SQL server able to be a witness server for a SQL 2008 R2 server?
I can't say for sure.  We're using SQL 2k8 for everything (including the witness).
Avatar of czaz

ASKER

I got it to failover properly with the witness server using another SQL 2008 server that wasn't R2.  The reason why it didn't work the first time because the server$ didn't have connect permissions on both ends.  Thanks for your help.
Great!  Happy to help.