Setup custom time zone for azure elastic pool database

1
votes

Looking solutions on how to set up a custom time zone for azure elastic pool database, the default time zone for azure elastic pool databases UTC, how do I Modify the default time zone

  1. How to set up an IST time zone for an elastic pool?

  2. All databases in the elastic pool will take the elastic pool time zone or do we required to set the time zone for each database separately?

    If Yes, How do I modify the default time zone for databases elastic pool databases?

  3. How to control the size of the database in Azure elastic pool databases for each database separately?

1
sql-serverazureazure-sql-databaseazure-elasticpool
Unless I recall incorrectly, Azure is designed to use the UTC time. You don't change it. If you need to display a time in your local timezone, convert it from UTC to IST at the display layer. The 3rd question in this, however, feels completely disjointed to the rest. – Larnu

1 Answers

1
votes

You need to use AT TIME ZONE to convert from UTC to India Standard Time. See example below:

SELECT SYSDATETIMEOFFSET() , CAST(SYSDATETIMEOFFSET()  AT TIME ZONE 'India Standard Time' as datetime);

You can create a function for this on each database. Use elastic jobs to create the function on each database.

CREATE FUNCTION dReturnDate( @dFecha as datetime) returns DATETIME
AS
BEGIN

DECLARE @D AS datetimeoffset

SET @D = CONVERT(datetimeoffset, @Dfecha) AT TIME ZONE 'India Standard Time'

RETURN CONVERT(datetime, @D);

END

About the size, you cannot control de size of each database individually and the same happens with the DTUs. You can control de size of the pool.

PS C:\>Set-AzureRmSqlDatabaseElasticPool -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -ElasticPoolName "ElasticPool01" -StorageMB 209715