Jaka jest najlepsza metoda odświeżenia tylko kilku tabel w testowej bazie danych z produkcji?

Mam bardzo dużą produkcyjną bazę danych i bardzo dużą bazę danych środowiska testowego w SQL Server 2008R2. Obie bazy danych mają podobną strukturę tabel, ale różnych użytkowników / loginy / uprawnienia / role.

Muszę od czasu do czasu odświeżać tylko kilka tabel w testowej bazie danych, mniej więcej raz w miesiącu.

Obecnie planuję to zrobić

1. Użyj narzędzia BCP, aby wyeksportować tabele, których potrzebuję z produkcji.
2. Skopiuj plik eksportu bcp na serwer testowy
3. Wyłącz indeksy i ograniczenia dla wszystkich tabel, które odświeżam w teście
4. Obetnij tabele testowej bazy danych
5. Załaduj dane z powrotem do testowania tabel bazy danych za pomocą BCP.
6. odbuduj indeksy i ponownie włącz ograniczenia w teście

To wszystko wydaje się trochę zbyt skomplikowane jak na tak małe zadanie. Wydaje się również, że spowodowałoby to wiele przeróbek (w dzienniku T) Czy istnieje lepszy sposób na zrobienie tego?

Innym sposobem, w jaki o tym pomyślałem, jest przywrócenie kopii zapasowej z Produkcji do środowiska testowego - ale mam problem z tym, że pełna kopia zapasowa byłaby dość duża i nie potrzebuję odświeżania wszystkich tabel, tylko kilka - - a także użytkownicy i bezpieczeństwo w produkcyjnej bazie danych różnią się od testu. Zostaną zastąpione przez ustawienia bezpieczeństwa w produkcyjnej bazie danych, jeśli przywrócę całą bazę danych.

Istnieją 2 metody, które odpowiadają Twoim potrzebom:

(Uwaga: jeśli do tabel odwołuje się klucz obcy, nie będziesz mógł ich używać TRUNCATE. Musisz usunąć fragmenty . Alternatywnie możesz usunąć wszystkie indeksy + Klucze obce i załadować dane, a następnie ponownie je utworzyć).

• Docelowa baza danych BCP OUT i BULK INSERT INTO .

  • Upewnij się, że ustawiłeś testową bazę danych w trybie odzyskiwania prostego / zbiorczego.

  • Włącz flagę śledzenia 610 - minimalnie zalogowane wstawki do indeksowanych tabel.

    /************************************************************************************************************************************************
    Author      :   KIN SHAH    *********************************************************************************************************************
    Purpose     :   Move data from one server to another*********************************************************************************************
    DATE        :   05-28-2013  *********************************************************************************************************************
    Version     :   1.0.0   *************************************************************************************************************************
    RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
    *************************************************************************************************************************************************/
    
    -- save below output in a bat file by executing below in SSMS in TEXT mode
    -- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat 
    
    select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
            +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
            +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
            +  QUOTENAME(name)  
            +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored
            +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
            +  REPLACE(name,' ','') 
            + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format
    from sys.tables
    where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
    and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
    order by schema_name(schema_id)                         
    
    
    
    --- Execute this on the destination server.database from SSMS.
    --- Make sure the change the @Destdbname and the bcp out path as per your environment.
    
    declare @Destdbname sysname
    set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in
    select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
            +  QUOTENAME(@Destdbname)+ '.'
            +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.' 
            +  QUOTENAME(name) 
            + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path
            +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
            +  REPLACE(name,' ','') 
            +'.dat'' 
            with (
            KEEPIDENTITY,
            DATAFILETYPE = ''native'',  
            TABLOCK
            )'  + char(10) 
            + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go' 
    from sys.tables
    where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
    and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema 
    order by schema_name(schema_id)

-

• Metoda 2: SSIS - w tym przypadku moja preferowana metoda.

  • Nie jest wymagane przenoszenie na dysk. Całe przetwarzanie odbywa się w pamięci.
  • Możesz zaplanować pakiet SSIS przy użyciu zadania agenta sql co miesiąc, aby zautomatyzować odświeżanie tabel od PROD do serwera TEST.
  • Wybierz opcję „ FAST LOAD ”
  • Upewnij się, że wybierasz dobre wiersze dla numeru partii (jeśli wybierzesz zbyt wysoką, nastąpi eskalacja blokady - utrzymuj ją poniżej 5K)

Odniesienie: Przewodnik po wydajności ładowania danych i moja odpowiedź na - Wstaw do tabeli wybierz * z tabeli vs wstawianie zbiorcze

Nie ma potrzeby robienia kopii zapasowych i przywracania, ani wywoływania / koordynowania procesów zewnętrznych (np. BCP), ani nawet bałagania w SSIS (bardzo potężny, bardzo fajny, ale jeśli mogę tego uniknąć, na pewno będę :). Możesz sobie z tym wszystkim poradzić, korzystając z T-SQL, w procedurze przechowywanej, którą możesz zaplanować za pomocą agenta SQL lub skryptu uruchamianego raz w miesiącu (chociaż posiadanie go w proc i planowanie jest mniej pracochłonne biegać). W jaki sposób? Korzystając z SQLCLR, aby uzyskać dostęp do SqlBulkCopyklasy w .NET, ponieważ jest to zasadniczo BCP, bez kłopotów z wywoływaniem BCP. Możesz to sam kodować: nie ma super skomplikowanej konfiguracji ani niczego takiego jakSqlBulkCopyklasa zajmuje się prawie wszystkim za Ciebie (możesz ustawić wielkość partii, niezależnie od tego, czy chcesz uruchamiać wyzwalacze itp.). Lub, jeśli nie chcesz zadzierać ze skompilowaniem i wdrożeniem zestawu , możesz użyć wstępnie zbudowanej procedury składowanej SQLCLR, takiej jak DB_BulkCopy, która jest częścią biblioteki SQL # SQLCLR (której jestem autorem, ale jest ona przechowywana procedura jest w wersji darmowej). Opisuję to bardziej szczegółowo, w tym przykład użycia DB_BulkCopy , w następującej odpowiedzi:

Importuj dane z jednej bazy danych do innego skryptu

Jeśli nie jest jasne, gdzie umieścić to w bieżącym planie, wykonaj następujące czynności:

• Usuń kroki 1 i 2 (woo hoo!)
• Wymienić krok 5 ze związkiem EXECo DB_BulkCopy czy cokolwiek to nazwać, jeśli to sam kod, który po prostu przenosi dane z punktu A do punktu B.

Należy również zauważyć, że SqlBulkCopyi DB_BulkCopy :

• może zaakceptować dowolny zestaw wyników: nie ma znaczenia, czy jest to WYBÓR, czy EXEC procedury składowanej
• są bardzo łatwe do aktualizacji, gdy zmiany schematu zostaną wprowadzone w dowolnej z tych tabel; po prostu ZMIEŃ zapytanie w procedurze przechowywanej, która wywołuje tę procedurę przechowywaną SQLCLR
• pozwalają na ponowne mapowanie pól, jeśli jest to kiedykolwiek potrzebne

AKTUALIZACJA dotycząca minimalnie zarejestrowanych operacji za pośrednictwem SqlBulkCopy

Możliwe jest uzyskanie minimalnie zarejestrowanych operacji, ale powinieneś wiedzieć:

• Musisz użyć opcji TableLock Bulk Copy
• W ten sposób, w przypadku tabel z indeksami klastrowymi, najpierw załaduj dane do, [tempdb]a następnie wykonaj uporządkowane wstawianie do miejsca docelowego. W związku z tym wiąże się to z dodatkowym obciążeniem, zarówno fizycznym we / wy do tempdb (pliki danych i dziennika), jak i operacją sortowania (ze względu na to, ORDER BYktóra jest wymagana do uzyskania minimalnie rejestrowanych operacji)
• Niektóre wyniki testów tutaj: Oficjalny dokument: wydajność SqlBulkCopy
• Niektóre wyniki testów tutaj: Rozwiązywanie problemów z SqlBulkCopy nie robi minimalnego rejestrowania