I need to migrate an on-premises SQL Server 2017 database to an Azure SQL database, and I'm facing some challenges since there's quite a bit of limitations to go through.
In particular, since an Azure SQL database works only in UTC time (no time zones) and we need the local time, we have to change the use of `GETDATE()` **everywhere** in the database, which has proven to be more work than I anticipated.
I created a user defined function to get the local time that works correctly for my time zone:
CREATE FUNCTION [dbo].[getlocaldate]()
RETURNS datetime
AS
BEGIN
DECLARE @D datetimeoffset;
SET @D = CONVERT(datetimeoffset, SYSDATETIMEOFFSET()) AT TIME ZONE 'Pacific SA Standard Time';
RETURN(CONVERT(datetime,@D));
END
The issue I'm having trouble with is to actually change `GETDATE()` with this function in every view, stored procedure, computed columns, default values, other constraints, etc.
What would be the best way to implement this change?
We are in the public preview of [Managed Instances][1]. It still has the same issue with `GETDATE()`, so it doesn't help with this problem. Moving to Azure is a requirement. This database is used (and will be used) always in this time zone.
[1]: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance
Rather than export, manually edit, and rerun, you could try do the job directly in the database with something like:
DECLARE C CURSOR FOR
SELECT sm.definition, so.type
FROM sys.objects so
JOIN sys.all_sql_modules sm ON sm.object_id = so.object_id
WHERE so.type IN ('P', 'V')
ORDER BY so.name
DECLARE @SQL NVARCHAR(MAX), @ojtype NVARCHAR(MAX)
OPEN C
FETCH NEXT FROM C INTO @SQL, @ojtype
WHILE @@FETCH_STATUS = 0 BEGIN
IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE')
IF @objtype = 'V' SET @SQL = REPLACE(@SQL, 'CREATE VIEW' , 'ALTER VIEW' )
SET @SQL = REPLACE(@SQL, 'GETDATE()', '[dbo].[getlocaldate]()')
--PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C INTO @SQL, @ojtype
END
CLOSE C
DEALLOCATE C
of course extending it to deal with functions, triggers, and so forth too.
There are a few caveats:
* You may need to be a bit brighter and deal with different/extra white-space between `CREATE` and `PROCEDURE`/`VIEW`/`<other>`. Rather than the `REPLACE` for that you might prefer to instead leave the `CREATE` in place and execute a `DROP` first, but this risks leaving `sys.depends` and friends out of kilter where `ALTER` may not, also if `ALTER` fails you at least have the existing object still in place where with `DROP`+`CREATE` you may not.
* If you code has any "clever" smells like modifying its own schema with ad-hoc TSQL then you'll need to make sure the search and replace for `CREATE`->`ALTER` doesn't interfere with that.
* You will be wanting to regression test the entire application(s) after the operation, whether you use the cursor or export+edit+run methods.
I've used this method to make similar schema-wide updates in the past. It is a bit of a hack, and feels quite ugly, but sometimes it is the easiest/quickest way.
Defaults and other constraints can be modified similarly too, though those can only be dropped and recreated rather than altered. Something like:
DECLARE C CURSOR FOR
SELECT AlterDefaultSQL = 'ALTER TABLE [' +st.name+ '] DROP CONSTRAINT [' + si.name + '];'
+ CHAR(10)
+ 'ALTER TABLE [' +st.name+ '] ADD CONSTRAINT [' + si.name + '] DEFAULT '+REPLACE(si.definition, 'GETDATE()', '[dbo].[getlocaldate]()')+' FOR '+sc.name+';'
FROM sys.tables st
JOIN sys.default_constraints si ON si.parent_object_id = st.object_id
JOIN sys.columns sc ON sc.default_object_id = si.object_id
DECLARE @SQL NVARCHAR(MAX)
OPEN C
FETCH NEXT FROM C INTO @SQL
WHILE @@FETCH_STATUS = 0 BEGIN
--PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C INTO @SQL
END
CLOSE C
DEALLOCATE C
Some more fun that you may need to contend with: if you are partitioning by time then those parts may need altering too. While partitioning on time more granularly then by day is rare you could have issues where `DATETIME`s are interpreted by the partitioning function as being the previous or next day depending on timezine, leaving your partitions unaligned with your usual queries.
> Dynamically alter all proc and udf to change value
DECLARE @Text NVARCHAR(max),
@spname NVARCHAR(max),
@Type CHAR(5),
@Sql NVARCHAR(max)
DECLARE @getobject CURSOR
SET @getobject = CURSOR
FOR SELECT sc.text,
so.NAME,
so.type
FROM sys.syscomments sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE sc.[text] LIKE '%getdate()%'
--and type in('P','FN')
OPEN @getobject
FETCH next FROM @getobject INTO @Text, @spname, @Type
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( @Type = 'P'
OR @Type = 'FN' )
SET @Text = Replace(@Text, 'getdate', 'dbo.getlocaldate')
SET @Text = Replace(@Text, 'create', 'alter')
EXECUTE Sp_executesql
@Text
PRINT @Text
--,@spname,@Type
FETCH next FROM @getobject INTO @Text, @spname, @Type
END
CLOSE @getobject
DEALLOCATE @getobject
CREATE PROCEDURE [dbo].[Testproc1]
AS
SET nocount ON;
BEGIN
DECLARE @CurDate DATETIME = Getdate()
END
Notice commented [sysobjects Type column][1] condition.My script will alter only proc and UDF.
[1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-2017
This script will alter all `Default Constraint` which contain `GetDate()`
DECLARE @TableName VARCHAR(300),
@constraintName VARCHAR(300),
@colName VARCHAR(300),
@Sql NVARCHAR(max)
DECLARE @getobject CURSOR
SET @getobject = CURSOR
FOR SELECT ds.NAME,
sc.NAME AS colName,
so.NAME AS Tablename
--,ds.definition
FROM sys.default_constraints ds
INNER JOIN sys.columns sc
ON ds.object_id = sc.default_object_id
INNER JOIN sys.objects so
ON so.object_id = ds.parent_object_id
WHERE definition LIKE '%getdate()%'
OPEN @getobject
FETCH next FROM @getobject INTO @constraintName, @colName, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = 'ALTER TABLE ' + @TableName
+ ' DROP CONSTRAINT ' + @constraintName + '; '
+ Char(13) + Char(10) + ' ' + Char(13) + Char(10) + ''
SET @Sql = @Sql + ' ALTER TABLE ' + @TableName
+ ' ADD CONSTRAINT ' + @constraintName
+ ' DEFAULT dbo.GetLocaledate() FOR '
+ @colName + ';' + Char(13) + Char(10) + ' ' + Char(13)
+ Char(10) + ''
PRINT @Sql
EXECUTE sys.Sp_executesql
@Sql
--,@spname,@Type
FETCH next FROM @getobject INTO @constraintName, @colName, @TableName
END
CLOSE @getobject
DEALLOCATE @getobject
Answer #3
Henrik Staun Poulsen (imported from SE)
I have upvoted Evan Carrolls answer, as I think this is the *best* solution. I have not been able to convince my colleagues that they should change a lot of C# code, so I had to use the code that David Spillett wrote. I have fixed a couple of problems with UDFs, Dynamic SQL and Schemas (not all code use "dbo.") like this:
DECLARE C CURSOR LOCAL STATIC FOR
SELECT sm.definition, so.type
FROM sys.objects so
JOIN sys.all_sql_modules sm ON sm.object_id = so.object_id
WHERE so.type IN ('P', 'V')
AND CHARINDEX('getdate()', sm.definition) > 0
ORDER BY so.name
DECLARE @SQL NVARCHAR(MAX), @objtype NVARCHAR(MAX)
OPEN C
WHILE 1=1 BEGIN
FETCH NEXT FROM C INTO @SQL, @objtype
IF @@FETCH_STATUS <> 0 BREAK
IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE')
IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE') /* when you write "create or alter proc" */
IF @objtype = 'V' SET @SQL = REPLACE(@SQL, 'CREATE VIEW' , 'ALTER VIEW' )
IF CHARINDEX('getdate())''', @sql) > 0 BEGIN /* when dynamic SQL is used */
IF CHARINDEX('utl.getdate())''', @sql) = 0 SET @SQL = REPLACE(@SQL, 'GETDATE()', 'utl.getdate()')
end
ELSE begin
SET @SQL = REPLACE(@SQL, 'GETDATE()', 'CONVERT(DATETIME, CONVERT(datetimeoffset, SYSDATETIME()) AT TIME ZONE ''Central Europe Standard Time'')')
end
EXEC dbo.LongPrint @String = @sql
EXEC (@SQL)
END
CLOSE C
DEALLOCATE C
and the default constraints like this:
DECLARE C CURSOR LOCAL STATIC FOR
SELECT AlterDefaultSQL = 'ALTER TABLE [' +sch.name+ '].[' +st.name+ '] DROP CONSTRAINT [' + si.name + '];'
+ CHAR(10)
+ 'ALTER TABLE [' +sch.name+ '].[' +st.name+ '] ADD CONSTRAINT [' + si.name + '] DEFAULT '+REPLACE(si.definition, 'GETDATE()', 'CONVERT(DATETIME, CONVERT(datetimeoffset, SYSDATETIME()) AT TIME ZONE ''Central Europe Standard Time'')')+' FOR '+sc.name+';'
FROM sys.tables st
JOIN sys.default_constraints si ON si.parent_object_id = st.object_id
JOIN sys.columns sc ON sc.default_object_id = si.object_id
INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
WHERE CHARINDEX('getdate()', si.definition) > 0
ORDER BY st.name, sc.name
DECLARE @SQL NVARCHAR(MAX)
OPEN C
WHILE 1=1 BEGIN
FETCH NEXT FROM C INTO @SQL
IF @@FETCH_STATUS <> 0 BREAK
EXEC dbo.LongPrint @String = @sql
EXEC (@SQL)
FETCH NEXT FROM C INTO @SQL
END
CLOSE C
DEALLOCATE C
**UDFs**
The suggestion to use a UDF that returns todays date and time looks nice, but I think there are still enough performance problems with UDFs, so I've chosen to use the very long and ugly `AT TIME ZONE` solution.
I really like David's answer and upvoted that for a programmatic way of doing things.
But you can try this today for a test-run in Azure via SSMS:
**Right click your database --> Tasks --> Generate Scripts..**
[Back Story] we had a junior DBA who upgraded all our test environments to SQL 2008 R2 while our production environments were at SQL 2008. It's a change that makes me cringe to this day. To migrate to production, from test, we had to generate scripts within SQL, using generate scripts, and in the advanced options we used the 'Type of Data to script: Schema and Data' option to generate a massive text file. We were successfully able to move our test R2 databases to our legacy SQL 2008 servers -- where a database restore to a lower version would not have worked. We used sqlcmd to input the large file--as the files were often too big for the SSMS text buffer.
**What I'm saying here is that this option would probably work for you as well. You'll just need to do one additional step and search and replace getdate() with [dbo].[getlocaldate]() in the generated text file. (I would put your function into the database before migration though).**
(I never wanted to be proficient at this band-aid of a database restore, but for a while it became a defacto way of doing things. And, it worked every time.)
If you choose this route, be sure and select the Advanced button and **select all the options you need (read each one)** to move from the old database to the new database--like the defaults you mentioned. But give it a few test runs in Azure. I bet you'll find that this is one solution that works -- with a modicum of effort.
[![enter image description here][1]][1]
[1]: https://i.stack.imgur.com/O6OMW.png
> What would be the best way to implement this change?
I would work the other way around. Convert all your timestamps in the database to UTC, and just use UTC and go with the flow. If you need a timestamp in a different tz, you can create a generated column using `AT TIME ZONE` (as you did above) that renders the time stamp in that specified TZ (for the app). But, I would seriously consider just having UTC returned to the app, and writing that logic -- the display logic -- in the app.
1. Use the SQL Server tool to export the database objects definition to a SQL file which should include: tables, views, triggers, SPs, functions, and so on
2. Edit the SQL file (make a backup first) using any text editor that allows you to find the text `"GETDATE()"` and replace it with `"[dbo].[getlocaldate]()"`
3. Run the edited SQL file in Azure SQL to create your database objects...
4. Execute the migration of data.
Here you have a reference from azure documentation: [Generating Scripts for SQL Azure][1]
[1]: https://azure.microsoft.com/en-us/blog/generating-scripts-for-sql-azure/
