Home / server / Questions / 227726
Accepted
Patrick
Asked: 2011-01-28 09:52:48 +0800 CST

Lost NTP sync on SQL servers

  • 772

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

windows-server-2008 windows-server-2003 ntp w32time

2 Answers

  1. 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)

    • 6

  2. Fix your NTP configuration and then let things go (without doing a forced sync). NTP, when it detects a small time difference, will slowly skew the clock towards the correct time a (i.e. adding or subtracting .5ms every second) until it has returned the clock to sync.

    Note: I know this is accurate for ntpd on Unix/Linux. I'm not 100% sure that it matches for Windows.

    • 2