February 16, 2012 at 10:25 am
Hi,
I made a test on a Virtual Machine with three SQL Server instances at the same server.
I tested the failover time with this option enabled:
ALTER DATABASE NOME_BASE set partner timeout 60
But the database mirroring failover in only 2 seconds.
This option "set partner timeout 60" doesn't work in the same instance? Only for different servers?
I found this information on microsoft site:
Failure is detected: The failure is detected by the mirror and the witness. Hard failures (such as server power failure) are detected quickly (typically in around 1 second). Soft failures (such as storage failure) take a bit longer to detect. The default timeout for communication (a ping message) between the principal, the mirror, and the witness is 10 seconds. The timeout can be modified by using the ALTER DATABASE SET PARTNER TIMEOUT command. If the principal doesn’t respond (to the ping message) within the timeout period, it is considered to be down. If you change the timeout setting, it is a best practices recommendation to set it to 10 seconds or higher. Setting it to lower than 10 seconds may result in false failures under heavily loaded or sporadic network conditions.
http://technet.microsoft.com/en-us/library/cc917681.aspx
Thanks for your time
Fabricio França Lima
MCITP - Database Administrator
http://fabriciolima.net
Twitter: @fabriciodba
February 16, 2012 at 1:32 pm
To make sure I understand, you are mirroring a database between two instances on the same server?
How are you simulating the failure of the principal database?
February 16, 2012 at 3:54 pm
That's right.
I stopped the SQL Server Service of the Principal database instance.
Fabricio França Lima
MCITP - Database Administrator
http://fabriciolima.net
Twitter: @fabriciodba
February 17, 2012 at 6:59 am
I think I found the answer at this link:
I was right. This only work with a windows failure in diferent servers.
Thanks for your time guys.
Fabricio França Lima
MCITP - Database Administrator
http://fabriciolima.net
Twitter: @fabriciodba
February 17, 2012 at 10:07 am
fabricio_lima_es (2/17/2012)
I think I found the answer at this link:I was right. This only work with a windows failure in diferent servers.
Thanks for your time guys.
with instances on the same server fail over should be pretty quick, the timeout is exactly that. It's saying if i don't get a response within the specified time declare a failover, your local instance are responding immediately as you have no network latency 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 17, 2012 at 10:31 am
I was thinking that with the "timeout" option the failover would obligatory initiate after X seconds. In any situation.
Thanks
Fabricio França Lima
MCITP - Database Administrator
http://fabriciolima.net
Twitter: @fabriciodba
February 17, 2012 at 11:01 am
fabricio_lima_es (2/17/2012)
I was thinking that with the "timeout" option the failover would obligatory initiate after X seconds. In any situation.Thanks
No it doesn't
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 17, 2012 at 11:12 am
Thank you Perry Whittle
Fabricio França Lima
MCITP - Database Administrator
http://fabriciolima.net
Twitter: @fabriciodba
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply