Amazon RDS - are there workarounds to change a database time zone in SQL Server?

Question

Amazon recently announced support for time zone change in Oracle RDS.

Since this is still not supported for Microsoft SQL Server 2012, are there any workarounds, to obtain functionality similar to changing the whole database time zone?

Answer 1

Since you're asking for workarounds...

We basically totally disregard server time/database time zone and work entirely off of UTC. GetUtcDate() for instance for all 'DateCreated' columns. Since we've committed to that approach we just don't bump up against any issues.

If you need to store the time zone alongside your date data, you can use DateTimeOffset.

The one caveat is that maintenance plans will be run on server time. This has not been an issue because we normalize everything to local time (which is not UTC and not server time) in any of our calendaring programs.

Answer 2

I did this with MySQL on RDS by changing my instance DB Parameter Group to a custom one that I can edit the parameters for.

I then created the following procedure:

DELIMITER |
CREATE PROCEDURE mysql.init_connect_procedure () 
IF  NOT(POSITION(‘rdsadmin@’ IN user()) = 1) 
THEN SET SESSION time_zone = 'America/New_York';
END IF |
DELIMITER ;

Note: every other instruction on the internet uses the function current_user() instead of user() which did not work for me!

The catch to this configuration is that then you have to give privileges to all your database users to be able to execute this function, or they won't even be able to connect to the database, so for every user and every future user you have to run this command. (and no there is no wildcard access to procedures)

GRANT EXECUTE ON PROCEDURE mysql.init_connect_procedure TO 'user'@'%' ; 

I edited the parameter init_connect for to be set as CALL mysql.init_connect_procedure . I am sure SQL SERVER has an equivalent parameter if not the same.

Restart the server and you should be good!

Warning: The user rdsadmin is the root user that only Amazon has the password to and uses to maintain and backup the database. You don't want to change the timezone for this user or you might damage your entire database. Hence the code to make sure it is not this user. I really recommend making sure the user is the same for SQL SERVER, this solution is only for MySQL and is a terrible solution, unfortunatly I had no other choice. If you can avoid doing this handle the timezone on your application end.