July 26, 2012 at 7:39 am
Hi all
We have two datacentres and I am looking to add some HA to one of our applications through mirroring. We want automatic failover but my question is, in which data centre would you place the witness server?
My current thinking is that I would have the witness in the same datacentre as the mirror. That way should the primary datacentre is lost for some reason then automatic failover would occur. If we lost the secondary datacentre then failover would not occur as we would still be on the primary.
What are other peoples thoughts?
Thanks
July 26, 2012 at 7:55 am
That would be how I would do it to, given the choices - with the witness on a separate server.
However, if you lose both the witness and mirror you also lose access to the principal until you break the mirror session with 'ALTER DATABASE <name> SET PARTNER OFF', if I recall correctly.
July 26, 2012 at 8:10 am
Just remember, if the witness server is located in the remote data center with the mirror server, and you lose communication between the data centers that the mirror server will become the principal server even if both are still up and visible by user applications.
Also, if you do lose the remote data center, the principal database will become unavailable when it can no longer communicate with the witness. Not sure if you can just remove the witness from the mirror at that point to bring the principal back online or if you would have to break the mirror to it.
Just a few thoughts to keep in mind.
July 26, 2012 at 8:58 am
Hi thanks for the replies.
So there would be some manual intervention involved in certain scenarios, but I think they are something we could live with.
I am actually hoping we could use SQL 2012 availability groups for this but perhaps the licensing is going to be too much for Enterprise.
July 29, 2012 at 4:07 am
Kwisatz78 (7/26/2012)
Hi thanks for the replies.So there would be some manual intervention involved in certain scenarios, but I think they are something we could live with.
I am actually hoping we could use SQL 2012 availability groups for this but perhaps the licensing is going to be too much for Enterprise.
But you could easily use clustering if you have a sql server standard edition as it supports 2 node cluster.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 29, 2012 at 5:11 am
Its multi site with no shared storage so I don't think that would be possible.
July 29, 2012 at 11:10 am
Lynn Pettis (7/26/2012)
Just remember, if the witness server is located in the remote data center with the mirror server, and you lose communication between the data centers that the mirror server will become the principal server even if both are still up and visible by user applications...
But I think that depends on the mode under which the mirroring is running.If safety is set to FULL and the remote data center goes down and if witness is also being hosted on the remote data center then it will cause the principal also to go down.
But if safety is OFF then any problem on the remote data center would not affect the principal as such.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 29, 2012 at 11:17 am
Sachin Nandanwar (7/29/2012)
But if safety is OFF then any problem on the remote data center would not affect the principal as such.
The OP states that he wants automatic failover. That means safety on and a witness server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2012 at 11:28 am
GilaMonster (7/29/2012)
Sachin Nandanwar (7/29/2012)
But if safety is OFF then any problem on the remote data center would not affect the principal as such.The OP states that he wants automatic failover. That means safety on and a witness server.
But he did state that its OK if it does not have to be automatic fail-over.That's what I understood with this post.
Kwisatz78 (7/26/2012)
Hi thanks for the replies.So there would be some manual intervention involved in certain scenarios, but I think they are something we could live with....
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 29, 2012 at 11:38 am
No, he said they can probably live with some manual intervention in certain scenarios, not that it's OK if manual intervention is required each and every time and that data loss is acceptable (which is what safety off means)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2012 at 11:49 am
Not sure what would that manual intervention be and when in this scenario.
The only time I have to do any manual intervention in a DB mirroring is when I have to restart my witness server (stupid old junk box) each week.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 29, 2012 at 11:54 am
If the principal and mirror are in the same data centre and the entire data centre goes offline then the mirror has to be brought up manually because it does not have quorum.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2012 at 3:03 pm
GilaMonster (7/29/2012)
If the principal and mirror are in the same data centre and the entire data centre goes offline then the mirror has to be brought up manually because it does not have quorum.
Indeed Gail which was something I was not immediately aware of until I started this post. The other thing is that the apps used share multiple databases. I have not tested any of my scenarios yet but I have found an article on automatically failing over all databases should one fail if anyone is interested.
Thanks
July 29, 2012 at 4:02 pm
Kwisatz78 (7/29/2012)
GilaMonster (7/29/2012)
The other thing is that the apps used share multiple databases.
Yeah, that's a pain with mirroring. It's a database-level HA solution. So if app requires multiple DBs and expects them on the same server (cross database queries), you have to put something in place so that if one DB fails over, the rest are failed over as well.
That's another nice thing about 2012's availability groups. 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2012 at 7:40 am
Hi again
Just to add to this it looks like we won't be going with Enterprise for AG as its too expensive, in which case we will have to use mirroring.
I just want to clarify that Mirroring can still be used in SQL 2012 Std, as it looks like it will be deprecated soon in favour of log shipping however this does not provide the automatic failover we require.
Thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply