Pay attention when running Biztalk Server and SQL Server on servers in different timezones

This post discusses what most likely is a seldom encountered setup, but nevertheless a possible one. Consider a global company that is using BizTalk. For various reasons, the SQL cluster will have Windows configured with another timezone than the rest of the servers.

This is our mock setup. BizTalk is located in the U.S. and at UTC-5 while the SQL server is in Sweden at UTC+1.

During normal operations, this will work just fine. Each server will locally run in it's own timezone and all is good. The issues appear when working with service windows.

Let's create a receive port with a corresponding receive location pulling in files from a folder.

Now we want to use a service window on this receive location to only allow files to be processed during two hours every afternoon. This is in a real world scenario most likely going to be set up with a time reference to the BizTalk Server, i.e. the service window time will match the timezone on the BizTalk Server. In our example, it's 16:00 to 18:00 that is allowed, UTC+1.

If we look in the corresponding table in the SQL Server for this receive location, we can see that the time is set accordingly.

However, when the BizTalk Server rolls up to this time, the receive location will still not pull the file in! Why is that?

Well, it seems that when BizTalk is checking if the receive location should be active or not, it compares the stored service window time with the local time on the SQL Server. BizTalk is after all heavily based on SQL Server. This have the effect that the time that we set on the BizTalk machine in UTC+1 and which is stored as such but without the timezone information will be used as is and hence be offset by six hours in this example.

If we change the service window time in the BizTalk Administration Console to be offset with the corresponding six hours (i.e. 16:00 will be 10:00 instead), the receive location will trigger at the intended time.

So far so good. If you know about this issue, it is easy to work around it. However, if you add in the normal complexity regarding daylight saving time that will change the offset yet a little twice a year, you are most likely heading towards a small headache!