3

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

Patrick
  • 1,250
  • 1
  • 15
  • 35
  • As a side note, ntp.microsoft.com sync's up to the atomic clock, so technically they are correct, and everything else is three minutes off. – mrdenny Jan 27 '11 at 18:43

2 Answers2

6

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)

AndyN
  • 1,739
  • 12
  • 14
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.

Christopher Cashell
  • 8,999
  • 2
  • 31
  • 43
  • I am not sure, if Windows is able to correct time as Linux, as long as I see windows is only able to do hard shift ... – Ency Jan 27 '11 at 18:10
  • I'm pretty sure it's part of the NTP spec. . . although, I don't know how well Windows follows the RFC's (and history suggests Microsoft will ignore things like that when they feel like it). – Christopher Cashell Jan 28 '11 at 17:08
  • Thanks for the answers and yes, Windows 2K* does have the facility to slew the time back to match a new NTP server. 0.5ms ever 1 sec, which is small enough that it doesn't knock on to the timestamping on the SQL databases. – Patrick Jan 31 '11 at 09:44