Jak mogę wykonać kopię zapasową dużej bazy danych SQL Server bez wpływu na wydajność?

Mieliśmy raporty o tym, że zapytania działają powoli lub upłynęły limit czasu wcześnie rano, a jedyne zadanie, które według mnie może wpłynąć na to, to zadanie tworzenia kopii zapasowej bazy danych.

Sama baza danych ma około 300 GB, a zadanie tworzenia kopii zapasowej rozpoczyna się o 4:30 i kończy się dopiero po 7:00. Obecna składnia naszego zadania tworzenia kopii zapasowej to:

BACKUP DATABASE [DatabaseName]
TO DISK = N'E:\Database Backups\DatabaseName.Bak'
WITH INIT, NOUNLOAD, NAME = N'DatabaseName.Bak',
NOSKIP, STATS = 10, NOFORMAT

E:\ to partycja na serwerze, która przechowuje zarówno bazy danych, jak i kopie zapasowe bazy danych.

Należy również prawdopodobnie zauważyć, że jest to serwer wirtualny, a nie dedykowany serwer autonomiczny. Zaczęliśmy otrzymywać skargi na spowolnienia podczas tworzenia kopii zapasowej zaraz po przejściu na serwer wirtualny, więc myślę, że może to być powiązane.

Czy istnieje sposób uruchomienia tego zadania tworzenia kopii zapasowej, aby nie wpłynęło to na wydajność zapytania podczas jego działania?

Używamy SQL Server 2005

1. Izoluj zapasowe We / Wy od reszty We / Wy w systemie. Chociaż może to potrwać dłużej i być bardziej podatne na czkawkę, tworzenie kopii zapasowych przez sieć zamiast na dysku lokalnym może pomóc złagodzić bezpośredni wpływ na instancję. Nawet na maszynie wirtualnej powinieneś być w stanie udostępnić inną pamięć, aby program SQL Server mógł zapisywać w innym podsystemie we / wy.
2. Jest mało prawdopodobne, aby miało to jakiekolwiek znaczenie, ale jeśli jest to uruchamiane z zadania, dlaczego musisz skorzystać z tej STATSopcji? Czy na pewno chcesz inne opcje ( NOUNLOAD, NOSKIP, NOFORMAT)? Nie przeprowadziłem żadnych obszernych testów wydajności na całej matrycy opcji, ale IMHO powinieneś używać tylko tych opcji, o których wiesz, że potrzebujesz.
3. Twórz pełne kopie zapasowe w innym czasie, który nie zakłóca regularnej aktywności użytkownika. Nie zawsze jest to możliwe, ale nawet w trybie 24/7 występują czasy szczytu i przerwy.
4. Jeśli przejdziesz do wersji 2008+, możesz skorzystać z kompresji kopii zapasowych (Enterprise w wersji 2008, Standard lub Enterprise w wersji 2008 R2 +). Jeśli nie możesz przejść do 2008 r. Lub nie masz odpowiedniej wersji, istnieją narzędzia do tworzenia kopii zapasowych innych firm, które wykonają kompresję i wszystkie są w tym całkiem dobre. Od 2008 roku jestem w stanie korzystać z kompresji natywnej i jestem bardzo zadowolony zarówno z prędkości, jak i% kompresji, bez potrzeby dalszego inwestowania w podmiot zewnętrzny. Ale w 2005 roku pamiętam dobry sukces z Red-Gate SQL Backup ; Quest LiteSpeed jest również całkiem niezły, ale nie mam pojęcia, jaki wysiłek włożono w to od czasu przejęcia Dell.
5. Jeśli Twoje bazy danych są w pełni przywracane, możesz przechodzić dłużej między pełnymi kopiami zapasowymi, zamieniając to na wiedzę, że jeśli musisz odzyskać do określonego momentu, potencjalnie będziesz mieć więcej dzienników do przywrócenia. Możesz połączyć tę opcję z poniższą sugestią Ali o przyjmowaniu różnic przez cały tydzień - jeśli musisz odzyskać w czwartek, musisz przywrócić 1 pełny, 1 różnicę i dzienniki od czasu różnicy.
6. Jeśli masz dużo danych, które są nieaktualne i niezmienne, możesz rozważyć hostowanie ich w innej bazie danych z innym planem odzyskiwania lub przynajmniej w różnych aplikacjach i w ten sposób podzielić operacje tworzenia kopii zapasowych. Jeśli masz dane referencyjne lub archiwalne, które można przekształcić tylko do odczytu, i jeśli jesteś w prostym odzyskiwaniu, możesz przenieść je do własnej grupy plików tylko do odczytu, wykonać kopię zapasową raz i nie musisz uwzględniać ich w nocnych kopiach zapasowych nigdy więcej. Zobacz następujące tematy MSDN:

Wykonywanie przywracania fragmentarycznego

Przykład: częściowe przywracanie tylko niektórych aplikacjami (prosty model odzyskiwania)

Jest to częsty problem, istnieje wiele rozwiązań i tak naprawdę zależy od środowiska. Przejrzyjmy je:

1- Kompresja zapasowa w locie

W 2008 r. R1 Backup Compression stał się dostępny w wersji Enterprise, w 2008 R2 stał się dostępny w wersji Standard. To jest ogromne. Zaoszczędzi ci to DUŻO czasu. Jeśli możesz zaktualizować, wybierz go. Jeśli nie możesz, sprawdź narzędzie HyperBak RedGate lub Quest LiteSpeed . Oba mają bezpłatny okres próbny.

2- Pełne i różne kopie zapasowe

Odziedziczyłem bazę danych 2 TB, ponieważ spowodowałem wiele przekroczeń limitu czasu dla dużej firmy internetowej 24/7, którą wypracowałem. Włączyliśmy pełne i różnicowe kopie zapasowe, co pozwoliło nam zaoszczędzić dużo czasu. Zrobiłbym pełną kopię zapasową w niedzielę o 12:00, kiedy aktywność była niska, i robiłam różnice w ciągu tygodnia. Pozwoliło to zaoszczędzić dużo miejsca. Praca Diff różni się od dzienników transakcji, ponieważ działają one na stronach, które zostały zmienione. Wszystkie zmienione strony są archiwizowane. W ten sposób wykonujesz pełne przywracanie, a następnie przywracanie różnic, aby dodać zmodyfikowane strony.

3- Jakie jest Twoje wąskie gardło?

Analiza wąskiego gardła jest ważna do zdiagnozowania. Czy tworzysz kopię zapasową w tej samej macierzy dyskowej, co pliki danych? Czy twoje pliki danych są ustalane? Jakie są Twoje DISK SEC / READ i DISK SEC / WRITE dla dysków z danymi podczas tworzenia kopii zapasowych? Zmodyfikowałem kopie zapasowe, aby utworzyć 4 pliki. Każdy plik ma własnego edytora wątków i w naszej sieci SAN, który sprawdził się świetnie. Przetestuj to. Ogoliłem 45 minut, tworząc 4 kopie zapasowe. Upewnij się tylko, że wskaźniki dysku wymienione powyżej są niskie. Uzyskaj linię bazową.

4- Replikuj na innym serwerze i wykonaj kopię zapasową

Ten jest nieco zaawansowany. Musisz upewnić się, że replikowana baza danych jest aktualna i potrzebujesz odpowiedniego monitorowania. Jeśli tak, możesz po prostu wykonać kopię zapasową replikowanej bazy danych.

Możesz użyć tych parametrów:

BLOCKSIZE - Wybierz rozmiar 512, 1024, 2048, 4096, 8192, 16384, 32768, 65536. (w KB)

BUFFERCOUNT - Określa całkowitą liczbę buforów we / wy, które zostaną użyte do wykonania kopii zapasowej. Możesz podać dowolną dodatnią liczbę całkowitą; jednak duża liczba buforów może powodować błędy braku pamięci z powodu nieodpowiedniej wirtualnej przestrzeni adresowej w procesie Sqlservr.exe. - z MSDN

MAXTRNASFERSIZE - od 65536 bajtów (64 KB) do 4194304 bajtów (4 MB)

Spróbuj. rozwiązano problem z przekroczonym limitem czasu, gdy db o dużym rozmiarze.

Private Sub Command1_Click()
On Error Resume Next
Dim con As New Connection
Dim tm As String
con.CommandTimeout = 500'''Command timeout should be 500


 With con
    .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=dbiBMS;Data Source=192.168.103.4"
    .Open
 End With
 tm = CStr(Time)

con.Execute " backup database dbiBMS to disk='E:\Database_Backup\Test1.bak' with format "

con.Close
MsgBox tm
Exit Sub
x:
MsgBox Err.Description

End Sub