Zmiana użycia GETDATE () w całej bazie danych

Muszę przeprowadzić migrację lokalnej bazy danych SQL Server 2017 do bazy danych Azure SQL i mam do czynienia z pewnymi wyzwaniami, ponieważ istnieje wiele ograniczeń.

W szczególności, ponieważ baza danych Azure SQL działa tylko w czasie UTC (bez stref czasowych) i potrzebujemy czasu lokalnego, musimy zmienić użycie GETDATE() wszędzie w bazie danych, co okazało się więcej pracy, niż się spodziewałem.

Utworzyłem funkcję zdefiniowaną przez użytkownika, aby uzyskać czas lokalny, który działa poprawnie dla mojej strefy czasowej:

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

Problem, z którym mam problem, polega na tym, aby zmienić GETDATE()tę funkcję w każdym widoku, procedurze składowanej, kolumnach obliczeniowych, wartościach domyślnych, innych ograniczeniach itp.

Jaki byłby najlepszy sposób na wdrożenie tej zmiany?

Jesteśmy w publicznej wersji zapoznawczej instancji zarządzanych . Nadal ma ten sam problem GETDATE(), więc nie pomaga w rozwiązaniu tego problemu. Przeprowadzka na platformę Azure jest wymagana. Ta baza danych jest używana (i będzie używana) zawsze w tej strefie czasowej.

1. Użyj narzędzia SQL Server, aby wyeksportować definicję obiektów bazy danych do pliku SQL, który powinien zawierać: tabele, widoki, wyzwalacze, SP, funkcje i tak dalej

2. Edytuj plik SQL (najpierw wykonaj kopię zapasową) za pomocą dowolnego edytora tekstu, który pozwala znaleźć tekst "GETDATE()"i zastąpić go"[dbo].[getlocaldate]()"

3. Uruchom edytowany plik SQL w Azure SQL, aby utworzyć obiekty bazy danych ...

4. Wykonaj migrację danych.

Oto odniesienie z dokumentacji lazurowej: Generowanie skryptów dla SQL Azure

Jaki byłby najlepszy sposób na wdrożenie tej zmiany?

Pracowałbym na odwrót. Konwertuj wszystkie swoje znaczniki czasu w bazie danych na UTC, po prostu użyj UTC i idź z prądem. Jeśli potrzebujesz znacznika czasu w innym tz, możesz utworzyć wygenerowaną kolumnę za pomocą AT TIME ZONE(tak jak wcześniej), który renderuje znacznik czasu w określonym TZ (dla aplikacji). Ale poważnie zastanowiłbym się nad powrotem UTC do aplikacji i napisaniem tej logiki - logiki wyświetlania - w aplikacji.

Zamiast eksportować, ręcznie edytować i uruchamiać ponownie, możesz spróbować wykonać zadanie bezpośrednio w bazie danych za pomocą czegoś takiego:

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

oczywiście rozszerzając go o funkcje, wyzwalacze i tak dalej.

Istnieje kilka zastrzeżeń:

• Być może będziesz musiał być nieco jaśniejszy i radzić sobie z inną / dodatkową białą przestrzenią pomiędzy CREATEi PROCEDURE/ VIEW/ <other>. Zamiast REPLACEtego możesz raczej zostawić CREATEmiejsce na miejscu i wykonać DROPpierwsze, ale ryzykuje to pozostawieniem sys.dependsznajomych i zbłąkaniem tam, gdzie ALTERmoże nie być, a także, jeśli się ALTERnie powiedzie, masz przynajmniej istniejący obiekt w miejscu, gdzie za pomocą DROP+ CREATEmożesz nie.

• Jeśli kod ma jakieś „sprytne” zapachy, takie jak modyfikacja własnego schematu za pomocą ad-hoc TSQL, musisz upewnić się, że wyszukiwanie i zamiana na CREATE-> ALTERnie przeszkadza w tym.

• Będziesz chciał przetestować regresję całą aplikację po operacji, niezależnie od tego, czy używasz kursora, czy eksportujesz + edytujesz + uruchamiasz metody.

W przeszłości korzystałem z tej metody, aby dokonywać podobnych aktualizacji dla całego schematu. Jest to trochę hack i wydaje się dość brzydki, ale czasami jest to najłatwiejszy / najszybszy sposób.

Wartości domyślne i inne ograniczenia można również modyfikować podobnie, chociaż można je tylko usunąć i odtworzyć, a nie zmienić. Coś jak:

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

Więcej zabawy, z którą możesz się zmagać: jeśli partycjonujesz według czasu, te części również mogą wymagać modyfikacji. Podczas gdy partycjonowanie na czas bardziej szczegółowo niż w dzień jest rzadkie, możesz mieć problemy, w których DATETIMEs są interpretowane przez funkcję partycjonowania jako poprzedni lub następny dzień w zależności od strefy czasowej, pozostawiając partycje bez wyrównania ze zwykłymi zapytaniami.

Naprawdę podoba mi się odpowiedź Davida i głosowałem za jej programowym sposobem robienia rzeczy.

Ale możesz wypróbować to dzisiaj, aby uruchomić test na platformie Azure za pośrednictwem SSMS:

Kliknij bazę danych prawym przyciskiem myszy -> Zadania -> Generuj skrypty.

[Back Story] mieliśmy młodszego DBA, który uaktualnił wszystkie nasze środowiska testowe do SQL 2008 R2, podczas gdy nasze środowiska produkcyjne były w SQL 2008. Jest to zmiana, która powoduje, że aż mi się kręci po dziś dzień. Aby przeprowadzić migrację do produkcji, z testów musieliśmy wygenerować skrypty w SQL, używając generowania skryptów, aw zaawansowanych opcjach skorzystaliśmy z opcji „Typ danych do skryptu: schemat i dane”, aby wygenerować ogromny plik tekstowy. Z powodzeniem przenieśliśmy nasze testowe bazy danych R2 na nasze starsze serwery SQL 2008 - gdzie przywracanie bazy danych do niższej wersji nie zadziałałoby. Użyliśmy sqlcmd do wprowadzenia dużego pliku - ponieważ pliki były często zbyt duże dla bufora tekstowego SSMS.

Mówię tutaj, że ta opcja prawdopodobnie również zadziałałaby dla Ciebie. Musisz tylko zrobić jeden dodatkowy krok i wyszukać i zastąpić getdate () przez [dbo] .getlocaldate w wygenerowanym pliku tekstowym. (Chciałbym jednak umieścić twoją funkcję w bazie danych przed migracją).

(Nigdy nie chciałem być biegłym w tym wspomaganiu przywracania bazy danych, ale przez pewien czas stał się defacto sposobem robienia rzeczy. I działało za każdym razem.)

Jeśli wybierzesz tę trasę, upewnij się i wybierz przycisk Zaawansowane i wybierz wszystkie potrzebne opcje (przeczytaj każdą), aby przejść ze starej bazy danych do nowej bazy danych - podobnie jak wspomniane wartości domyślne. Ale daj mu kilka uruchomień testowych na platformie Azure. Założę się, że przekonasz się, że jest to jedno rozwiązanie, które działa - przy odrobinie wysiłku.

Dynamicznie zmieniaj wszystkie proc i udf, aby zmienić wartość

    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

Zauważ, że skomentowano sysobjects Typ warunku kolumny. Mój skrypt zmieni tylko proc i UDF.

Ten skrypt zmieni wszystkie, Default Constraintktóre zawierają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   

Poparłem odpowiedź Evana Carrolla, ponieważ uważam, że jest to najlepsze rozwiązanie. Nie byłem w stanie przekonać moich kolegów, że powinni zmienić dużo kodu C #, więc musiałem użyć kodu napisanego przez Davida Spilletta. Rozwiązałem kilka problemów z UDF, dynamicznym SQL i schematami (nie wszystkie kody używają „dbo”) w następujący sposób:

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

i domyślne ograniczenia takie jak to:

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

UDF
Sugestia użycia UDF, która zwraca dzisiejszą datę i godzinę, wygląda dobrze, ale myślę, że wciąż jest wystarczająco dużo problemów z wydajnością UDF, więc zdecydowałem się na użycie bardzo długiego i brzydkiego rozwiązania AT TIME ZONE.