Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 The Yak Corral
 Server Clock set back One Day

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2012-12-03 : 08:08:49
The complete and utter prats at our ISP set the server clock back a day (wilst trying to adjust a 5 minute discrepancy). Yes, I know, should be using a Time Server. EVERY server they set up for us they forget to do that, and I guess we forgot to check this one

Anything I should be worrying about?

Other than already on my list:

No backups taken until time "overtook" original time. Consequence of this was that a TLog filled up, extended, and was then too large to backup for available disk space. This went unnoticed for a further 24 hours. LDF was hand-optimised for minimum VLF's - is SHRINK (back to original hand-extended size) going to be enough?

We make Fraud Detection exports daily. This data is kept for 5 years and is used in case we need to investigate a fraud. By design an existing export will not be overwritten, so we are now missing 24 hours data ...

Data Integration screwed up. Systems ask us for "All data changed since XXX", where "XXX" is the timestamp of the last time it asked. We have now failed to give 24 hours data to all such external services.

All sorts of sequence inconsistencies. Order Number (IDENTITY) no longer chronological (we will no doubt find that some reports are by Date, rather than ID ... in fact that is probably the correct way, but now that 24 hours period will be goofy)

As a consequence I have decided to change the code that creates a "new session" so that it will fail, with error, if the System Time is earlier than the latest time on record, so at least if it happens again no out-of-sequence data will be created. (need to think about the hour when the clocks go back in Autumn)

I suppose the bright side is that if they had accidentally put the date forward, and some of our scheduled tasks had run (and then set "Next Run Date" to some future point) and then the ISP realising their mistake and putting the date BACK and NOT telling us would have been far more serious. Nothing would have run for days and we would have been seriously at risk if there was the a major disaster.

Anyways, its something to add to my list of "Things to instruct third parties". I cannot believe that an ISP would not have the most extreme & rigorous procedures about adjusting server-time, but I am certainly going to include it in my discussion in future, including that I require any change to be done double-handed to prevent mistakes.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-03 : 12:21:06
Did the ISP adjust the time directly on your server, or on some master time server that your server was using for an NTP server?

We had a similar problem with time on ISP hosted servers. I discovered that our servers were as much as 5 minutes away from the correct time. The ISP had not configured a time source for any of our servers, and they told me that was up to us to setup.

I configured stand-alone ISP hosted servers to get their time directly from the ntp.org time server pool.

ISP hosted servers on a domain have the domain controller configured to get time directly from the ntp.org time server pool and domain members get their time from the domain controller.

Since the ntp.org pool is made up of hunderds of servers, it is less likely to experience problems than a single ntp server configured by your ISP.

You can read about the ntp pool project here:
http://www.pool.ntp.org/en/


FYI: I just double checked and all of our ISP hosted servers had a time difference of less than one second from each other.








CODO ERGO SUM
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2012-12-04 : 06:14:36
Turns out I was wrong (which is pretty shocking in itself )

Wasn't human error on the part of the ISP.

Its something goofy in the virtualisation of the machine. The virtual machine has its own idea of what "time" is, which is different to the underlying machine's idea of time (and that itself is maybe different to the machine's physical clock??). I have no idea why it is deemed necessary to be able to run a virtual machine on a different time, but there we go :(

Some bug caused the virtual machine to wrongly update itself from the master machine, and instead of making a small adjustment, or even just making the two times become the same, it subtracted a whole day. WTF? Apparently we had this a few days ago on another virtual machine and Googling around there is some very slim evidence that other people have had the same problem.

Virtual machines have wasted huge amounts of my time trying to track down issues ... particularly WHY something is slow. The complexity of all the inter-connected Spaghetti means that solving an issue is never easy (in fact I'd go so far as to say its boarding on being "beyond the wit of man")

In the past: only 50% of CPUs being visible to SQL on a particular box (dedicated to SQL ...). Dreadful network-storage drive performance ... Memory problems ...

Service Provider never managed to fix any of those; we removed virtualisation and moved the disks "inboard" to fix the problem in those cases ... but only after weeks of mucking about trying stuff (usually at antisocial times, 8pm-10pm, when our client's eCommerce machines come under the heaviest load) all the while our clients getting well and truly Tee'd off ...

But my IT colleagues keep telling me that virtualisation is the best thing since sliced-bread. I think it should be toast ...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2012-12-04 : 07:10:28
Just had a thought:

Would winding the clock back cause any risk of duplicate GUIDs being re-generated? (I presume Sequential GUIDs would be fine)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-04 : 09:46:28
VMWare virtual machines can be configured to get their time from the host they are running on, but they can also be configured to get their time from an NTP server like a regular standalone server.

All the servers that I mentioned in my previous post are on VMs, and are configured to get their time directly or indirectly from internet NTP servers. I questioned our hosting provider about why they didn't configure VMs to get time from the host and they said that was not their default way of doing things. Maybe they are aware of the problem you mentioned.

I have had generally good experience with VMs (SQL Server on 100+ VMs), but you need to have two things in place to have success:
1. Good hardware for the VM hosts that is up to the job.
2. Good administrators that really know what they are doing.

It sounds like your hosting provider may be a little short in both areas.




CODO ERGO SUM
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2012-12-05 : 11:08:44
We thought the hardware was good (paid [what seemed to me] like a lot of money for Network Storage box from HP [i.e. before we even put any drives in it]. Either performance was rubbish, or ADMIN was rubbish/ Seems unlikely that HP kit was rubbish (although I've been suckered making decisions like that in the past), or perhaps the particular storage box we got was just not suited to SQL in some way ...

Anyways, I've put all the hassle of that behind me.

I'll pass on your thoughts about getting Time direct from Timer Server, rather than Host Machine; thanks.
Go to Top of Page
   

- Advertisement -