W jaki sposób przechowywać dane archiwalne w bazie danych SQL Server?

2
2
1.1K

W naszej firmie zapisujemy bardzo dużo danych transakcyjnych. Obawiamy się o wydajność naszej bazy danych w przyszłości.
Zatem czy dane archiwalne należy przenosić do osobnych tabel?

Zapytał
@ Uczestnik szkolenia | 10.07.2013

6

Przenoszenie danych archiwalnych do osobnych tabel to częsta praktyka ale mało efektywna.
Takie podejście wymaga systematyczności i dodatkowych nakładów pracy. Można to wprawdzie zautomatyzować ale problem pojawi się jeślii ktoś zarząda dostępu do danych archiwalnych lub zbiorczych z wielu tabel.

Na szczęście SQL Server posiada specjalny mechanizm, który przeznaczony jest do efektywnego zarządzania dużą ilością danych. Służy do tego partycjonowanie danych.

Partycjonowanie danych polega na podzieleniu zbioru danych na mniejsze części i zapisywania ich w osobnych plikach bazy danych. Podział zbioru odbywa się na podstawie ustalonego kryterium, zwykle na podstawie "wieku", na przykład daty utworzenia rekordu.

W rezultacie aktualne i archiwalne dane znajdują się w fizycznie osobnych plikach, lecz z punktu widzenia użytkownika jest to nadal jedna tabela.
Dzięki temu zyskujemy na wydajności a użytkownik może nadal pracować na pojedynczej tabeli.

Należy pamiętać, że partycjonowanie danych dostępne jest w wersji Enterprise i Developer.

Partycjonowanie składa się z kilku kroków:

1. Tworzymy wiele grup plików

CREATE DATABASE [TestPartitionj ON PRIMARY
( NAME = N'TestPartition',FILENAME = N'C:\DATA\TestPartition.mdf' ,
SIZE = 2046KB , FILEGROWTH = 1024KB ),
FILEGROUP [fg1]
(NAME = N'f1', FILENAME = N'C:\DATA\f1.ndf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [fg2]
( NAME = N'f2',
FILENAME = N'C:\DATA\f2.ndf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [fg3]
(NAME = N'f3',
FILENAME = N'C:\DATA\f3.ndf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestPartition log',
FILENAME = N'C:\DATA\TestPartition_log.ldf'
SIZE = 1024KB, FILEGROWTH = 10%)
GO

Dla zwiększego efektywności można wskazać osobne dyski.

2. Tworzymy funkcję partycjonującą
Funkcja będzie ustalać do jakiego przedziału trafi rekord.

CREATE PARTITION FUNCTION MyPF
AS
RANGE RIGHT FOR VALUES('2008-01-01','2008-02-0l')
GO

3. Tworzymy schemat partycjonowania
Wskazujemy funkcję partycjonują i partycje w których będą zapisywane rekordy.

CREATE PARTITION SCHEME MyPS
AS
PARTITION MyPF
TO
(FGl, FG2, FG3)

4. Tworzymy tabelę
Podajemy schemat partycjonowania i wskazujemy pole na podstawie którego odbywać się będzie partycjonowanie danych.

CREATE TABLE Orders
(OrderID INT IDENTITY,
OrderDate DATE)
ON MyPS (OrderDate)

Gotowe.

Teraz wystarczy zapisywać dane do tabeli i obserwować do jakiej partycji zostały przydzielone:

SELECT 

	OrderId,

	OrderDate,

	$PARTITION.MyPF ([OrderDate]) AS [Numer partycji]

FROM Orders

Schematy partycjonowania możemy obejrzeć za pomocą zapytania:

SELECT * FROM sys.partition_schemes

Powodzenia!

Skomentuj

Odpowiedział
@ | 13.07.2013
TRENER ALTKOM AKADEMII

Pytanie