czaz
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?
DB mirroring <> failover cluster.
ASKER
What's that supposed to mean?
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.
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?
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?
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.
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.
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".
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".
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.
ASKER
Ohhh...This is for sharepoint 2010.
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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).
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.