Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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?
0
czaz
Asked:
czaz
1 Solution
 
jogosCommented:
DB mirroring <> failover cluster.
0
 
czazDirector of Systems AdministrationAuthor Commented:
What's that supposed to mean?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
czazDirector of Systems AdministrationAuthor Commented:
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?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
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?
0
 
czazDirector of Systems AdministrationAuthor Commented:
So the best way to have real failover in the event that one goes down is clustering?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
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.
0
 
czazDirector of Systems AdministrationAuthor Commented:
Hmm...Are there any other ways for an automatic failover to work with mirroring and not having a sql cluster?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
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.
0
 
czazDirector of Systems AdministrationAuthor Commented:
How do I go about doing that?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
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".
0
 
czazDirector of Systems AdministrationAuthor Commented:
Where do I go within SQL to add the connection string?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
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.
0
 
czazDirector of Systems AdministrationAuthor Commented:
Ohhh...This is for sharepoint 2010.  
0
 
JohnQMCommented:
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
0
 
czazDirector of Systems AdministrationAuthor Commented:
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.  
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Can you configure/try another witness?
0
 
czazDirector of Systems AdministrationAuthor Commented:
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.
0
 
czazDirector of Systems AdministrationAuthor Commented:
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?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
I can't say for sure.  We're using SQL 2k8 for everything (including the witness).
0
 
czazDirector of Systems AdministrationAuthor Commented:
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.
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Great!  Happy to help.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now