Time synchronization on Windows Servers stinks. And has stunk for years.
In fact, for servers operating in a workgroup (instead of as part of a domain) I’d even argue that time synchronization is busted. Because even, for example, if you set up these servers to ‘synchronize’ with ‘internet time,’ you’ll still regularly run into issues with synchronization failures—where you see something like the following regularly cropping up in the logs:
For a long time I figured that this was just a problem with a couple of machines that I watched for a client—until I started noticing that this problem seems to crop up with just about every server that’s working as part of a workgroup.
Not Every SQL Server Operates in a Domain
In my experience, servers operating in a domain don’t have the same problems as servers working in a workgroup—because servers in a domain at least stay synchronized with their local domain controllers. (Of course, whether that server is accurately ‘synchronized’ with ‘reality’ is a different story—but at least your domain-level servers all operate consistently at the same time.)
But the obvious problem here, however, is that not all SQL Servers operate in a domain. In fact, I find that many startups commonly tend to skip the use of a Domain Controller entirely—leaving their SQL Servers to operate in a workgroup. And it’s safe to say that SQL Servers running a few minutes slow or fast probably isn’t the ‘end of the world’ in all cases, it can present some weird and ugly problems. For example, if you’re trying to track down application errors or problems with a SQL Server that’s a few minutes fast or slow, that can be a bit of a pain. That, and I’m guessing that most organizations want or need their ‘CreateDate’ and ‘ModifiedDate’ columns in their tables/apps to more or less accurately measure when something was modified, created, or whatever. But where this really doesn’t sit well with me is when I encounter mirroring operating in a workgroup environment where the primary, the secondary, AND the witness are all running a few minutes ‘off’ from reality and from each other—because, in some ways, that gives me the same feeling I get when I see a big, hairy spider: logically I know it’s not something I truly have to worry about—but it still freaks me out.
Fixing Windows Time Synchronization for Servers in a Workgroup
For about a year or so now I’ve been addressing this problem for a couple of clients—and on a few of my own Cloud Servers (that I’m using for one of my own projects) thanks to an excellent approach, or technique, that I found online.
The specific tutorial I used can be found here:
Pretentious Name: Make Windows synchronize time more often
And what I love about this approach is that it not only outlines some of the ways you can go WRONG with trying to get servers to synchronize more frequently, but that it also provides a set of step-by-step instructions for how to set up a new Scheduled Task in Windows that will synchronize time more regularly.
And, while this tutorial mentions that it’s for Windows 7, I’ve found that it works just fine on Windows Server 2008 and 2008 R2 without any issues. Otherwise, if you’re operating servers in a workgroup (for whatever reason), then I highly recommend this tutorial/approach as a great way to help keep your servers better synchronized with each other and more connected to ‘real time’ than they’re going to be other otherwise.