Lost NTP sync on SQL servers

ntpw32timewindows-server-2003windows-server-2008

I've just noticed that one of our clients SQL servers are syncing against time.microsoft.com, the rest of the domain is looking at ntp.[clients.domain].co.uk, so our SQL servers are about 3 minutes fast.

My argument is that we need to stop all SQL services, on both servers at the same time, update the time service to the domain one and then restart all the SQL services >no less< than 3 minutes later to avoid duplication of timestamps in the databases.

the other argument I've had is 'go on, I'm sure it will be fine'.

Am I being over cautions here? I'm not the SQL admin and in fact the clients SQL servers host a variety of third party DB's so getting reliable answers from them could take some time. Unfortunately this is knocking off the reporting for the call centre out as the DB entries no longer ties up with the phone system, so I'm getting heat from the Call Centre manager and her boss.

I realise that this might be a case of 'it entirely depends on the nature of the databases that you are using' but if thats the case, just some friendly advice?

Thanks, Patrick

Best Answer

NTP has a mechanism built into it to bring a client into discipline slowly, over a span of several minutes. You can watch it happen with w32tm /stripchart /computer:ntptarget after you've issued the resync command. There is a threshold above which it will not sync, and so you may need to force it somehow if your difference is too great. The threshold is called "MaxPosPhaseCorrection" and "MaxNegPhaseCorrection".

http://support.microsoft.com/kb/884776

http://support.microsoft.com/kb/314054

(n.b. the output of "stripchart" looks best when the cmd window width is upped from 80 to 120 columns. Properties-> Layout -> Window Size)

Related Topic