SQL Server and Timezones
-
Hi,
Following a recent upgrade to our server we have discovered that the timezone is wrong.
We have some specific concerns with just changing the time zone. Just to help clarify, the problem is
that the SQL server has a time zone of "GMT +9:00 Seoul". It should in fact be "GMT +8:00 Perth". This
has caused the SQL to adjust its time forward over the weekend after being added to the domain. The
net result is that it is 1 hour ahead of all the other servers on the local sub-net.
The specific concerns and questions I have are:
1. If the timezone is adjusted, will the operating system "jump" to adjust its time or will it slowly adjust
like novell servers do ?
2. If this "slow" adjust is the case, how long will it take.
3. What are the implications in SQL if a database is open with the time zone change is adjusted.
Without any further information our approach to fix the problem would be..
1. Perform the normal "Prior" backup.
2. Take the database, SQL Server and SQL Server Agent offline.
3. Adjust the time-zone, not the time itself.
4. Reboot the server to DOS
5. Adjust the time.
6. Shutdown the server for a least an hour.
7. Restart and proceed as normal.
Some of this may seem a bit overboard, but we want to be safe not sorry.
I would appreciate any advice you may have.
Regards
John Matear
-
I may be missing something here entirely, but AFAIK SQL Server has no clue about the time - it's the o/s that runs the clock. (meaning that it's Windows that has the zones mixed up, not SQL Server)
If changing the time will have any adverse effects on your system sort of falls into the 'it depends' category.
But, if it's sensitive, and you can afford it - by all means stop SQL Server, change the system time and wait until it have 'catched up' until you turn on SQL Server again.
That way you can be sure to not risk any contradicting log issues.
/Kenneth
-
Hi John,
I agree with Kenneth. I am not aware of any problems with changing the time in SQL Server since the operating system is controlling the clock.
To answer your first concern, Windows will change the time immediately, it does not do it over a period of time.
I would probably stop SQL Server, change the time, give the machine a reboot and start everything up again.
Let us know if you run into any problems.
Cheers,
Angela
-
Thank you for your replies. Our main concern was related to the particular application that was running on SQL Server. We were not 100% confident that changing the timezone could not have an adverse impact on the application in question. We contacted Microsoft Premium Support and ran our solution past them. For reference I have included their response. Fortunately we did have a window in order to perform this switch and everything worked OK.
CASE SUMMARY
============
Issue:
--------
You had a SQL Server 2000 running on a Windows 2000 member server with
the incorrect timezone setup. In this case you wanted to verify the
steps you were going to take to change the timezone.
Solution:
---------
After discussing the case with our platforms support guys and conducting
some quick tests with SQL Server and its behavior with date time data
fields, the below steps you provided were appropriate with minor
modification:
1. Perform the normal "Prior" backup.
2. Take the database, SQL Server and SQL Server Agent offline - turn
These services to manual, so that when you restart the service you can
manually bring them online after the OS has started and you can test
that everything is fine from the OS perspective.
3. Adjust the time-zone, not the time itself.
4. Reboot the server to DOS - I would recommend going into the BIOS and
changing the time on the actual machine as well, and then you can go
into the command prompt mode of Windows 2000 and check the time it
displays
and ensure it is correct.
5. Adjust the time - this is done in the above step in the BIOS
6. Shutdown the server for a least an hour - over an hour e.g. 1 hour
and 10 minutes
7. Restart the server - after the OS has started bring up SQL Server
manually (also change the service to start automatically if that is
normal setting) and test it to ensure that all your data is correct by
running your typical queries/ checking your main tables used by the
application.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply