Obecnie mamy problemy z wydajnością, ponieważ nasza baza danych staje się zbyt duża. Są dane przechowywane z ostatnich 10 lat i nie widzę powodu, dla którego dane starsze niż 2 lata muszą być przechowywane w tych samych tabelach, co nowe dane.

Ponieważ nie mam dużego doświadczenia w administrowaniu bazami danych, szukam najlepszych sposobów archiwizacji starych danych.

Informacje

• Baza danych zawiera łącznie około 310 000 000 rekordów.

• Baza danych potrzebuje 250 GB na dysku twardym.

• Wersja serwera to SQL Server 2008 z poziomem kompatybilności SQL Server 2005 (90), ale planujemy wkrótce uaktualnić do SQL Server 2012

Myślałem o dwóch możliwościach:

Nowa baza danych

Utwórz bazę danych podobną do tej na serwerze produkcyjnym i wstaw wszystkie stare dane do nowej bazy danych.

• Wada: Ponieważ połączone serwery nie są dozwolone w naszym środowisku, w razie potrzeby trudno byłoby dołączyć do starych danych

Schemat historyczny

Utwórz nowy schemat fe [hist] z tymi samymi tabelami, co w produkcyjnej bazie danych. Wstaw wszystkie stare dane do tych nowych tabel w nowym schemacie.

• Zaleta: Łatwe łączenie, jeśli w przyszłości potrzebne będą stare dane

• Czy wolisz jedno z rozwiązań niż drugie?
  • Czemu?
• Czy są jakieś lepsze możliwości?
• Czy istnieją istniejące narzędzia, dzięki którym to zadanie jest łatwo możliwe?
• Jakieś inne myśli?

Z góry dziękuję

Edytować

Dodatkowe pytanie:

Czy nowo utworzona tabela archiwum będzie również potrzebować kluczy podstawowych / obcych?

A może powinny mieć tylko kolumny, ale bez kluczy / ograniczeń?

Myślę, że odpowiedź na wiele twoich pytań brzmi: to zależy. Jakie masz problemy z wydajnością? Wydaje się niezwykłe, że baza danych miałaby problemy z wydajnością od powiększenia do 250 GB.

Być może twoje zapytania wykonują skanowanie tabeli na całej tabeli faktów, nawet jeśli potrzebna jest tylko niewielka część (np. Ostatni rok) zakresu dat? Jeśli jest jakieś zapytanie, które jest najważniejsze do optymalizacji, rozważ zamieszczenie schematu, zapytania i rzeczywistego planu wykonania w innym pytaniu, aby sprawdzić, czy można je zoptymalizować.

Wolisz jedno z rozwiązań niż drugie?

Ogólnie wolę bazę danych historii i myślę, że Guy opisuje dobre powody tego w swojej odpowiedzi .

Główną wadą, jaką widzę w przypadku bazy danych historii (w przeciwieństwie do schematu) jest to, że nie można już używać kluczy obcych do tabeli archiwum. To może być dla ciebie w porządku, ale należy o tym pamiętać.

Wada wymieniona w tym podejściu nie jest dokładna; będziesz w stanie łatwo przesyłać zapytania do baz danych na tym samym serwerze, a optymalizator zapytań ogólnie bardzo dobrze obsługuje zapytania między bazami danych.

Czy są jakieś lepsze możliwości?

Jeśli chcesz regularnie sprawdzać dane archiwalne, możesz rozważyć podzielenie tabeli według daty . Jest to jednak duża zmiana, która może mieć wiele implikacji dotyczących wydajności, zarówno pozytywnych (np. Eliminacja partycji, bardziej wydajne ładowanie danych), jak i negatywnych (np. Wolniejsze szukanie singletonu, większy potencjał przekrzywienia wątku w równoległych zapytaniach). Więc nie podjąłbym tej decyzji lekko, jeśli jest to mocno używana baza danych.

Czy nowo utworzona tabela archiwum będzie również potrzebować kluczy podstawowych / obcych? A może powinny mieć tylko kolumny, ale bez kluczy / ograniczeń?

Poleciłbym mieć przynajmniej klucz główny i unikalne indeksy, abyś mógł uzyskać korzyści z integralności danych, które zapewniają. Zapobiegnie to na przykład przypadkowemu wstawieniu roku danych do tabeli historii dwa razy. Dodatkową korzyścią może być poprawa wydajności, jeśli zajdzie potrzeba przeszukiwania tabeli historii.

Jakieś inne myśli?

Ponieważ używasz wersji Enterprise i planujesz aktualizację do wersji SQL 2008+, możesz rozważyć kompresję danych dla tej tabeli. Kompresja z pewnością zmniejszy przestrzeń dyskową, ale w zależności od zasobów dysku i procesora serwera może również poprawić wydajność zapytań o odczyty, zmniejszając liczbę operacji we / wy dysku i poprawiając wykorzystanie pamięci (więcej danych mieści się w pamięci podręcznej na raz).

Wolałbym mieć schemat historii lub drugą historyczną bazę danych nad połączonym serwerem każdego dnia. Oszczędza koszty licencji, jest łatwiejszy w zarządzaniu i wyszukiwaniu. Następnie można również użyć prostszego schematu i usunąć niektóre indeksy, zmniejszając bazę danych

Ale ponieważ masz wersję Enterprise, masz trzecią opcję, która polega na podzieleniu tabel na partycje, która po jej wprowadzeniu ułatwia archiwizowanie danych, a zapytania do starych danych są przejrzyste dla użytkowników i nie trzeba wprowadzać zmian w aplikacji .

Z mojego doświadczenia wynika, że ​​druga baza danych byłaby preferowanym wyborem z dwóch powodów.

1. Możesz przywrócić dane z historycznej kopii zapasowej, a następnie upuścić niepotrzebne tabele i indeksy.
2. Możesz przenieść to na inny serwer do celów raportowania, ma to tę zaletę, że nie używa zasobów głównego serwera

Nadal będziesz musiał usunąć wszystkie dane historyczne z podstawowej bazy danych, ale można to zaplanować na.

Na razie ignoruję licencję, bo nie spędzam tam czasu.

IMHO, baza danych archiwum jest najprostsza do wdrożenia i utrzymania. To odrębne, luźno powiązane jednostki. Kontrola danych oraz kontrola obciążenia / zasobów mają wyraźne granice. Można łatwo przenieść się do innej instancji lub serwera w celu lepszego zarządzania wydajnością, a koszt nie stanowi większego problemu. Zauważ, że najprostszy! = Najtańszy lub najmniejszy wysiłek. W rzeczywistości ma nieco więcej zadań, ale wszystkie są prostymi zadaniami z dwoma ważnymi wyjątkami:

1. wymuszanie ograniczeń - nie ma czegoś takiego jak ograniczenia między bazami danych w SQL Server, więc musisz zdecydować, czy to złamie umowę.
2. zapytania między bazami danych używają zapytań rozproszonych, które są nadal zależne od OLEDB, które jest przestarzałe. Oznacza to, że możesz napotkać problemy z nowymi typami danych, a jeśli wystąpią problemy z wydajnością, jest mało prawdopodobne, że kiedykolwiek zostaną naprawione

Schemat archiwizacji lub tabela archiwów jest nieco bardziej złożona do wdrożenia, ale znacznie łatwiejsza w użyciu. Wszystkie obiekty w tej samej bazie danych oznaczają, że nie musisz replikować i utrzymywać kontroli dostępu. Brak zapytań między bazami danych, co ułatwia dostrajanie wydajności, monitorowanie, rozwiązywanie problemów itp.

Partycjonowanie tabel jest doskonałym rozwiązaniem i zapewnia wiele korzyści z tabeli / schematu archiwizacji, ale zapewnia przezroczystość użytkownikom / zapytaniom. To powiedziawszy, jest najbardziej skomplikowane do wdrożenia i wymaga stałej opieki, która nie jest łatwa dla początkującego.

Kilka ważnych uwag:

• Czy zapytania regularnie zwracają dane historyczne / zimne, czy dostęp do zimnych danych jest rzadki?
• Czy dane historyczne są niezmienne lub czy są regularnie aktualizowane / usuwane?
• 310m rzędów jest „umiarkowanych” (zakładając wszystko w 1 tabeli) w zależności od wielkości wiersza. Czy masz dane o rozmiarze wiersza? Ile GB ma ten 310-metrowy rząd?
• Jaka jest stopa wzrostu tej tabeli?
• Czy możesz modyfikować kod aplikacji i jej zapytania SQL?

Są to ważne uwagi, ponieważ mogą mieć znaczący wpływ na wybrane przez Ciebie rozwiązanie lub mogą nawet nie zezwalać na niektóre rozwiązania. Na przykład, jeśli twoje dane historyczne są regularnie modyfikowane / aktualizowane (więcej niż raz w tygodniu), użycie oddzielnej bazy danych oznacza, że ​​musisz użyć DTC dla tych zapytań lub ręcznie zarządzać bezpieczeństwem transakcji (nie jest trywialne, aby zapewnić zawsze poprawność). Koszt jest znacznie wyższy niż niezmienne dane historyczne.

Ponadto, jeśli myślisz o aktualizacji, rozważ 2016 i nową funkcję Stretch Database: https://msdn.microsoft.com/en-us/library/dn935011.aspx

Wolałbym podzielić bazę danych na osobną logiczną bazę danych z następujących powodów:

1. Wymagania dotyczące zasobów

Dzieląc to na osobną bazę danych, można go przechowywać na innym dysku i monitorować z inną częstotliwością niż główne dane produkcyjne.

2. Wydajność

Rozdzielając dane do osobnej bazy danych, główna produkcyjna baza danych jest zmniejszana, co pomaga w ogólnej wydajności.

3. Prostsze kopie zapasowe

Tworzenie kopii zapasowych zarchiwizowanych danych nie może być uważane za tak istotne, jak rekordy „bieżące / bieżące” w głównej bazie danych SQL. Może to oznaczać, że zarchiwizowane dane mogą być tworzone rzadziej. Również ze względu na sekwencyjny sposób rejestrowania zarchiwizowanych danych może być możliwe wykonanie kopii zapasowej sekcji zarchiwizowanej bazy danych raz, a potem nigdy więcej. Np. Po zapisaniu danych archiwalnych w bazie danych Zmień archiwum na 2014 r. Już nigdy nie będzie żadnych zmian tych danych.

Uwaga: myślę, że odpowiedź na wiele pytań zależy od okoliczności, charakteru danych i problemów z wydajnością.