Automatic failover... help

Hi there,

We've recently set up a Principle, Mirror and Witness configuration with the Mirror and Witness in a separate building to the Principle. All three are part of the same domain (DMZ) and are different servers, the buildings are connected via a fiber optic cable. All servers and SQL Server instances are logged in with the same domain admin account DMZ\esAdmin.

Mirroring is all set-up and the databases are synchronized. Every once in a while some (not all, normally 6 out of 15) databases will switch roles and become active on the mirror. The SQL Server mirroring monitor job then reports:

Date 25/01/2007 12:37:01
Log Job History (Database Mirroring Monitor Job)

Step ID 1
Server DMZSQL01
Job Name Database Mirroring Monitor Job
Step Name
Duration 00:00:02
Sql Severity 16
Sql Message ID 32038
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: DMZ\esadmin. An internal error has occurred in the database mirroring monitor. [SQLSTATE 42000] (Error 32038). The step failed.

I have no idea, what causes the failover, it could be a slow network or a bad set-up, can anyone give me some ideas of what to do to track down the problem or any experience of what could be causing this, it happens randomly every day or three. No warning and if I go to the mirror and failover back to the principle again then it's all just fine. However I don't want half my databases working on 1 server and half on the other.

Any ideas?

Thanks
Ed

UPDATE:

I've just been looking at the logs on my Mirror and at the same time it reports in this order

Error: 1479, Severity: 16, State: 1.

The mirroring connection to "TCP://DMZSQL01.dmz.local:5022" has timed out for database "WARCMedia" after 10 seconds without a response. Check the service and network connections.

Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.

Recovery is writing a checkpoint in database 'WARCMedia' (41). This is an informational message only. No user action is required.

The mirrored database "WARCMedia" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover.

Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.

...

This looks like a time out, is there any way to set the TimeOut threashold for Database mirroring or set retry intervals?

Have you implemented network monitoring? I suggest you compare the failover times to the network monitoring history.

-Matt

|||

No, but any pointes on what to monitor would be usefull.

Thanks

Ed

|||

Couple of things to consider:

1. run with the safety OFF (then you wouldn't need the witness server). This won't allow automatic failover from with a real failure or a false failure. The false failures are what you seem to be seeing now, but it will allow your system to run in a more predictable manner, while you understand what is going on.

2. Consider moving the Witness (if you MUST run with the witness) to the building with the Principal. All things being equal, (i.e. if the probability of both building having a disaster is equal); then having the Witness and Principal share a "more reliable" connection should provide better reliability. Assuming: 1. servers closer together physically is more reliable. 2. You can't put the Witness in a third, equally reliable data center (who has that kind of $$$) 3. Your business needs dictate that you can put the Witness with the Principal

3. Increase the timeout "ping" for mirroring. ALTER DATABASE db SET PARTNER TIMEOUT = xx. (see BOL) By default, it is 10 seconds.

|||

Thank you all for your help.

I modified the timout pint to 60 seconds and I've not had any problems since. I have a feeling either there is something on the network causing a delay or the server was just under too much load at one off times. This has solved 1 problem so I'll continue to look into the cause over the coming weeks.

Thanks
Ed

Automatic Date Field

Hi, I am in the process of building a table and would
like one of the rows to contain a date time field that
is automatically populated with date/time once the
submit button is hit. I can do this in access with
selecting Data Type = Date/Time and default value
= Date()
SQL ver 2K sp3.
TIA for any pointers
JohnUse GETDATE() OR CURRENT_TIMESTAMP
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Johnny" <use@.stamp.co.uk> schrieb im Newsbeitrag
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
>|||"Johnny" <use@.stamp.co.uk> wrote in message
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
Thanks Jens, Unfortunatley that makes the whole column into the same date.
John

Autogenerate reply or ServerProc generated message............

Hello,

I got my sample application to work that I am building my proof of concept out of. I need to be able to auto generate a reply message that would normally be in the run routine. The only way I see to do this is pull it from a table but I do not want to do that. I have tried tests where I change the code to see if I can send a message back but I have to reinstall the assembly into the database which is not what I am looking for. I am looking for a way to change the message by either accessing the GUI and getting the string, calling another function to do this, or something like that. I want it so I can change the code in the run routine in VS 2005 but this does not work. I am sure their is a trick to do this but am not sure what that trick is. What is a good way to do this other than accessing a table in the database?

Thanks,

Scott Allison...

What do you mean by auto-generate a reply? A reply could either be static (i.e. independent of the request and state), a stateless (i.e. dependent only on the request) or stateful (i.e. dependent on both the request as well as some state... the state would normally be stored in the database, but may also be stored separately). I cannot see why you need to recompile and redeploy your app to meet any of the above patterns.

Rushi

|||

Hello Rushi,

It calls the ServiceProc in my code and that is loaded into the database. I am writing this for applications that will need to report the status of a current event. If this is not stored in the database then they would not want to write anything to the database. This means they will get the request then need to reply to that request with a text message that they generate. In the worst case situation it can come from the database but would rather just be retrieved from a function call. It seems as though from your message this can be done so I am sure I have a setting incorrect. What I have is currently static and I want the response dependent on the request. What I meant from Autogenerated is retrieved from another assembly or another part of that service.

Thanks,

Scott Allison...

|||If the function that generates a response from request is a method in a different assembly, you could deploy that assembly into the database and simply invoke the method from ServiceProc.|||

Hello Rushi,

This is a remote system and will not be able to deploy all of the assemblies to the database. It consists of several services and needs dynamic access to what the message. Are you telling me that the only way to get a dynamic message is to write it to a database and have the ServiceProc read it? This is a draw back beacuse it will slow things down but I want to make sure before I tell people that they have to do that.

Thanks,

Scott Allison...