Autor-Archiv Mathias Mink

Merge Replication – Tutorial

Für mein aktuelles Projekt musste ich mich in die Merge-Replication Funktionalität des SQL Servers einarbeiten. Da das Thema für mich auch neu ist, und sicherlich da draußen der ein oder andere genau an dem selben Punkt wie ich gerade ist, will ich meine gesammelten Erkenntnisse hier in diesem Blogbeitrag festhalten. Im weltweiten Netz gibt es nicht allzu viele gute Tutorials, wie man eine Replikation auf die Beine stellen kann. Deutsche Tutorials schon gar nicht. Daher hier der Versuch alles verständlich auf Deutsch zu beschreiben.

Zunächst mal zu den Eckdaten:
Mein Projekt sieht vor, eine Merge-Replication zwischen einem Standort in China und hier in Deutschland für eine Applikation aufzubauen.
Im ersten Schritt werde ich aber zunächst einmal nur auf der „grünen Wiese“ die Replication ausprobieren und versuchen die ersten Schlüsse zu ziehen und Machbarkeitsanalysen zu erstellen.

Bevor wir beginnen werde ich im Folgenden, die neuen Begriffe, denen man beim Einrichten der Replication immer wieder begegnet, erklären.

Begriff Erklärung
Distributor Verwalter und Überwacher der Mergereplication
Publisher Der Publisher stellt die Daten in Form eines Abonnements bereit
Subscriber Der Subscriber konsumiert die bereitgetellten Abonnements von einem Publisher
Abonnement Ist die Sammlung von Inhalten (Article), die zur Verfügung gestellt werden.
Article Ist ein Objekt einer Datenbank was zur Replizierung ausgewählt wurde

Vorbereitungen

Bevor mit der eigentlichen Einrichtung der Merge-Replication begonnen werden kann, müssen diverse Vorbereitungen im Vorfeld erledigt werden.

  1. Berechtigungen und Rollen anlegen
  2. Snapshot-Share anlegen
  3. Definition der Server – Entweder Publisher und Distributor auf dem selben Server einrichten, oder auf unterschiedlichen.
  4. Einrichten und Konfigurieren des Distributors

Berechtigungen und Rollen anlegen:

In unserem Fall gehen wir davon aus, dass Distributor und Subscriber auf dem gleichen Server laufen. Das „Best-Practice“ Vorgehen sieht 4 neue lokale User für den Publisher und 2 lokale für den Subscriber vor, die für das Einrichten einer funktionierden Replication benötigt werden.

Agent Server Lokaler Benutzer
Snapshot Agent Publisher <machine_name>\repl_snapshot
Log Reader Agent Publisher <machine_name>\repl_logreader
Distribution Agent Publisher und Subscriber <machine_name>\repl_distribution
Merge Agent Publisher und Subscriber <machine_name>\repl_merge

Nun kommen wir zu dem Anlegen der lokalen Benutzer.

Einrichten der Benutzer auf dem Publisher.

Server Manager öffnen
AnlegenLokalerBenutzer

Navigieren zu Tools->Computer Management
AnlegenLokalerBenutzer2

System-Tools->Local Users and Groups
AnlegenLokalerBenutzer3

Hier muss nun aufgepasst werden. Durchaus ist es möglich, dass für das Verwenden von Passwörter von der IT Abteilung eine Gruppenrichtlinie greift.

Diesen Vorgang müssen wir dann noch für die anderen 3 User für den Publisher wiederholen.

Für den Subscriber müssen ebenfalls 2 lokale Benutzer nach o.g. Schema angelegt werden.

Snaphot-Freigabe einrichten

Microsoft empfiehlt hier, den Installationsordner des Data Verzeichnisses zu nutzen, um einen neuen Unterordner „repldata“ zu erzeugen. Nach meiner Meinung ist das aber nicht notwendig. Der Ordner kann an einem beliebigen Ort auf dem Server angelegt werden. Die zuvor angelegt Benutzer „repl_snapshot“,“repl_merge“ und „repl_distribution“ müssen nun für diesen neuen Ordner berechtigt werden. Zuvor muss der Ordner freigegeben werden:

Eigenschaften des Ordners öffnen und den Reiter „Freigabe“ aktivieren.
ShareEinrichten1

-> danach 2 mal bestätigen.

Dann Reiter „Sicherheit“ aktivieren und die zuvor angelegten User berechtigen.
ShareEinrichten2

repl_snapshot User muss Vollzugriff auf diesen Ordner bekommen.

  • repl_snapshot -> Vollzugriff
  • repl_distribution -> Lesen
  • repl_distribution -> Lesen

Nun sind die Vorbereitung erledigt und wir können uns an das Einrichten im Management-Studio machen.

Einrichten und Konfigurieren des Distributors

In unserem Fall sind Publisher und Distributor auf dem selben Server. Wir fangen nun damit an, den Distributor zu konfigurieren.

Rechtsklick auf Replication und dann „Distributor Properties“ auswählen.
DistributionEinrichten

Im nächsten Fenster den ersten Punkt ausgewählt lassen.
DistributionEinrichten2

An dieser Stelle muß man die Freigabe eintragen, die wir weiter oben im Punkt „Snapshot-Freigabe“ eingerichtet hatten.
DistributionEinrichten3

Bestätigen mit „Next“ und dann „Finish“ klicken. (Alle weiteren Fenster belassen wir auf den Standardwerten)

Der Distributor ist nun fertig eingrichtet.

Jetzt geht es an die Konfiguration des Publishers.

Einrichten und Konfigurieren des Publishers

Auf dem SQL Server auf dem der Publisher eingerichtet werden soll navigiert man nun zu „Replication“->Rechtsklick „New Publication“

In dem sich nun öffnenden Wizard müssen wir zunächst einmal die Datenbank auswählen, welche Voll oder Teilweise repliziert werden soll. In unserem Fall ist es die AdventureWorks2014 Datenbank.

PublisherEinrichten

Auf der nächsten Seite des Wizard wählen wir „Merge Publication“

PublisherEinrichten2

  • Next, Next

PublisherEinrichten3

In diesem Tutorial nehmen wir 3 Tabellen, die wir replizieren wollen.

  • Next

An dieser Stelle kann man nun auch noch Filterkriterien auf die Daten setzen, die in die Replizierung mit einbezogen werden sollen. Für diese Demo wollen wir keine Filterung vornehmen und gehen mit „Next“ weiter zur nächsten Seite.

Hier wählen wir nur „Create a snapshot immediately“.

Auf der „Agent Security“ Seite müssen folgende Einstellungen vorgenommen werden.

PublisherEinrichten4

–> Als Process Account wählen wir hier den neu Erstellen User „repl_snapshot“.

  • OK, Next, Next

Abschließend müssen wir einen Namen für die Publication wählen. Wir nennen die Publication „AdvWorksSalesOrdersMerge„.

Jetzt ist die Einrichtung des Publishers fast abgeschlossen. Es fehlt nur noch das initiale Erstellen des Snapshots.

  • Rechtsklick auf die gerade erstellte Publication
  • Klick auf „View Snapshot Agent Status“
  • „Start“ klicken

Der Snapshot wird nun erstellt.

 

Einrichten eines Subscribers

Als letztes müssen wir noch einen Subscriber einrichten.

Öffnen des Management-Studios

-Replication –> Local Subscriptions –> Rechts Klick –> New Subscription

-Publisher – Server auswählen
SubscriberEinrichten

Auf der nächsten Seite wird gefragt, ob die Agents auf dem Subscriber selbst, oder auf dem Distributor laufen sollen.
Wir wählen hier die 2.Option „Run each agent at ist Subscriber (pull subscriptions)

Anschließend „Next“ für weiter.

Auf der folgenden Seite müssen wir festlegen, in welches Ziel repliziert werden soll.

1.Möglichkeit: Neue Datenbank

2.Möglichkeit: Bestehende Datenbank

In unserem Beispiel wählen wir eine neue Datenbank aus und nennen sie „AdventureWorksMergeReplicationTest
SubscriberEinrichten2
WICHTIG!: Wenn eine neue Datenbank ausgewählt wird, dann müssen noch die Berechtigungen für den neu angelegten User repl_merge dort eingerichtet werden.

Next

In dem nächsten Dialog wird der Account abgefragt, den der Merge-Agent verwenden soll.

Hier kommt nun unser repl_merge – User zum Einsatz.
SubscriberEinrichten4
SubscriberEinrichten3

Weiter geht es mit der Auswahl, ob die Initialisierung direkt nach dem Wizard vorgenommen werden soll, oder aber erst mit dem ersten Initialisierungslauf.
SubscriberEinrichten5

Gleich ist es geschafft 🙂 . Nur noch 2 oder 3 Dialogfenster …

Im nächsten wird nun angegeben, ob Client oder Server Subscription Type. Wir wählen „Client“.
SubscriberEinrichten6

Next, Next

Juhuu es ist geschafft. Wir haben unsere Subscription fertig eingerichtet.

 

Relationale Partitionierung #3 – Partition Merge+Split

Nachdem wir uns nun angeschaut haben, wie man eine relationale Partitionierung einrichtet und uns die Switch-Function angeschaut haben, kommen wir jetzt zu weiteren Möglichkeiten, die eine Partitionierung einer Tabelle mit sich bringt. In diesem Post soll es um die beiden Funktionen Merge und Split gehen.

Anwendungsbeispiele:

In unserem Beispiel mit den ProductInventories könnte man sich vorstellen, das wir kurz nach dem Jahreswechsel die Inhalte der Current-Partition Filegroup in die Hist-Partition Filegroup verschieben möchten.

Für das Simulieren benötigen wir noch weitere Testdaten, damit wir für 2014 das Jahr mit Daten vollmachen können. Ich stelle die vorbereitenden Skripte für dieses Beispiel hier zur Verfügung.

  1. -- Daten Insert in FactProductInventory ab 07.2014 bis 12.2014v
  2. INSERT INTO dbo.[FactProductInventory]
  3. SELECT
  4. [ProductKey]
  5. ,[DateKey]+10000 AS  [Datekey]            -- + 1Jahr
  6. ,DATEADD(dd,365,[MovementDate])  AS [MovementDate]
  7. ,CAST([UnitCost] * 1.03 AS DECIMAL(18,2)) AS [UnitCost]
  8. ,[UnitsIn]
  9. ,[UnitsOut]
  10. ,[UnitsBalance] * 1.03        AS [UnitsBalance]
  11. FROM [dbo].[FactProductInventory]
  12. WHERE DateKey between 20130701 and 20131231

Zunächst müssen wir die letzte Partition „aufsplitten“. Aus einer Partition machen wir zwei.

  1. -- 1. Partition-Split auf die leere 2015er Partition, Cur-Filegroup wird verwendet.
  2. ALTER PARTITION SCHEME [PSProductInventoriesRight] NEXT USED [ProductInventoriesCur]
  3. ALTER PARTITION FUNCTION PFYearsRight() SPLIT RANGE (20160000)  -- > + 1 Jahr
  4. -- 2. Aufteilung der Daten auf die jeweiligen Partitionen anschauen
  5. USE [AdventureWorksDW2014]
  6. GO
  7. SELECT DISTINCT
  8. --SysTables.object_id                                    as [OBJECT_ID]
  9. --,object_schema_name(SysPartitions.object_id)        as [TABLE_SCHEMA]
  10. SysTables.name                                        AS [TABLE_NAME]
  11. ,SysPartitions.partition_number                        AS [PARTITION_NUMBER]
  12. ,SysRangeValue.VALUE                                AS [PARTITIONCOLUMNID_FROM]
  13. ,SysRangeValue.boundary_id                            AS [BOUNDARY_ID]
  14. ,SysPartitions.ROWS                                    AS [PARTITION_ROWS]
  15. ,SysPartitionFunctions.name                            AS [PARTITION_FUNCTION]
  16. ,SysSchemes.name                                    AS [PARTITION_SCHEMA]
  17. ,[SysFileGroups].name                                AS [PARTITION_FILEGROUP]
  18. FROM sys.partitions AS [SysPartitions]
  19. INNER join sys.indexes AS [SysIndizes]
  20. ON SysPartitions.OBJECT_ID = SysIndizes.OBJECT_ID
  21. and SysPartitions.index_id = SysIndizes.index_id
  22. INNER join sys.tables AS SysTables
  23. ON SysPartitions.OBJECT_ID = SysTables.OBJECT_ID
  24. INNER join sys.partition_schemes AS SysSchemes
  25. ON SysIndizes.data_space_id = SysSchemes.data_space_id
  26. INNER join sys.partition_functions AS SysPartitionFunctions
  27. ON SysPartitionFunctions.function_id = SysSchemes.function_id
  28. INNER join sys.partition_range_values AS SysRangeValue
  29. ON SysRangeValue.function_id = SysSchemes.function_id
  30. and SysPartitions.partition_number-1 = SysRangeValue.boundary_id
  31. INNER join sys.allocation_units AS [SysAllocationUnits]
  32. ON [SysAllocationUnits].container_id = [SysPartitions].hobt_id
  33. INNER join sys.filegroups AS [SysFileGroups]
  34. ON [SysFileGroups].data_space_id = [SysAllocationUnits].data_space_id
  35. WHERE [SysTables].name = 'FactProductInventory'
  36. ORDER BY SysTables.name

Als nächstes fügen wir Testdaten für Januar/2015 ein.

HINWEIS: Bei der Erstellen des Beispiels hatte ich nicht an den DimDate-ForeignKey in der Tabelle gedacht. Dieser würde das Einfügen der Testdaten verhindern, da in der DimTime Tabelle diese Einträge fehlen. Deshalb bitte vorher den ForeignKey-Constraint für das durchführen des Beispiels entfernen.

  1. -- Januar 2015 Werte einfügen -- Vorher muss der ForeignKey Constraints von DateKey genommen werden. DimDate enthält nur Daten bis 2014.
  2. INSERT INTO [dbo].[FactProductInventory]
  3. SELECT
  4. [ProductKey]
  5. ,[DateKey]+30000 AS  [Datekey]            -- + 3 Jahr
  6. ,DATEADD(dd,1095,[MovementDate])  AS [MovementDate]
  7. ,CAST([UnitCost] * 1.07 AS DECIMAL(18,2)) AS [UnitCost]
  8. ,[UnitsIn]
  9. ,[UnitsOut]
  10. ,[UnitsBalance] * 1.07        AS [UnitsBalance]
  11. FROM [dbo].[FactProductInventory]
  12. WHERE DateKey between 20120101 and 20120131

Jetzt können wir die 2014er Daten in die Hist-Filegroup „mergen“ in dem wir die 2013er und 2014 Partitionen zusammenziehen. Erst danach machen wir einen Split auf die 2014er Partition. Wichtig hierbei ist die Filegroup mit „NEXT USED“  für die 2014er Partition zu ändern.

  1. -- Die 2014er Daten sollen nun in die Hist Filegroup verschoben werden
  2. -- Die Partition 20140000 in die 2013er mergen
  3. ALTER PARTITION FUNCTION PFYearsRight() MERGE RANGE (20140000)
  4. -- Dann per Split wieder erzeugen. Diesmal aber in die Hist-Filegroup
  5. ALTER PARTITION SCHEME [PSProductInventoriesRight] NEXT USED [ProductInventoriesHist]
  6. ALTER PARTITION FUNCTION PFYearsRight() SPLIT RANGE (20140000)

 

Database Projects – Fehler bei Deployment

In dem heutigen Post geht es um einen Fehler beim Deployment aus einem Database Project heraus.

Folgende Schritte wurden durchgeführt.

  1. Build des Database Projects
  2. Publish
    Screen3-PublishDatabase
    –> Click Publish
  3. Fehlermeldung

Fehlermeldung1

Um diesen Fehler zu lösen müssen folgende Installationen deinstalliert werden

Screen2-AddRemovePrograms_DeinstallMarkedPrograms

Der Fehler ist seit 2014 Microsoft bekannt. Leider ist anscheinend bis heute kein Bugfix erschienen. Der offizielle Post aus dem Forum von Microsoft ist hier zu finden:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4564ef7a-9175-4349-8129-5d63ba9a65ee/new-version-still-missing-things?forum=ssdt

Auf meiner Entwicklungsumgebung hat der Workaround nur zum Teil funktioniert. Das Reparieren der Installation der Visual Studio Data Tools ergab zunächst keinen Erfolg. Erst nachdem ich die aktuelle Version der Visual Studio Data Tools installiert hatte, konnte ich mit den Database Projects wieder Publishen (Deployen).

Die aktuelle Version der Visual Studio 2013 Data Tools findet man hier:

https://msdn.microsoft.com/en-us/dn864412

Relationale Partitionierungen von SQL Tabellen #2

Partition-Switching

Im zweiten Teil der Reihe über die relationale Partitionierung soll es um die Partion-Switch Funktion gehen.

Partition-Switch ist mit Abstand die performanteste Lösung, wenn aus einer Tabelle mit mehreren 10 Mio. Datensätzen viele Daten gelöscht werden müssen. Faktentabellen eigenen sich für den Einsatz besonders gut, weil es meistens bei dem Ladeprozess darauf ankommt, möglichst schnell die Daten in der Zieltabelle zu aktualisieren. Dieser Artikel zeigt anhand eines Beispiels wie man Daten mit Hilfe der Switch-Methode aus einer Tabelle löschen und wieder einfügen kann.

Als Beispieltabelle dient wieder die FactProductInventory-Tabelle in der AdventureWorksDW2014 Datenbank. Die Partitionierung muss für diese Tabelle eingerichtet worden sein.

Anhand dieser Tabelle erstellen wir eine neue Tabelle, die absolut identisch mit der Grundtabelle aufgebaut sein muss. Wichtig ist auch, dass das Partitionscheme das Gleiche sein muss, wie in der Grundtabelle.

CREATE TABLE [dbo].[FactProductInventorySwitch](
    [ProductKey] [INT] NOT NULL,
    [DateKey] [INT] NOT NULL,
    [MovementDate] [DATE] NOT NULL,
    [UnitCost] [MONEY] NOT NULL,
    [UnitsIn] [INT] NOT NULL,
    [UnitsOut] [INT] NOT NULL,
    [UnitsBalance] [INT] NOT NULL
) ON [PSProductInventoriesRight]([DateKey])
WITH
(
DATA_COMPRESSION = PAGE
)
GO
 
CREATE NONCLUSTERED INDEX [IXFactProductInventorySwitchDateKey] ON [dbo].[FactProductInventorySwitch]
(
    [DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [PSProductInventories]([DateKey])
GO
 
ALTER TABLE [dbo].[FactProductInventorySwitch]  WITH CHECK ADD  CONSTRAINT [FK_FactProductInventorySwitch_DimDate] FOREIGN KEY([DateKey])
REFERENCES [dbo].[DimDate] ([DateKey])
GO
 
ALTER TABLE [dbo].[FactProductInventorySwitch] CHECK CONSTRAINT [FK_FactProductInventorySwitch_DimDate]
GO
 
ALTER TABLE [dbo].[FactProductInventorySwitch]  WITH CHECK ADD  CONSTRAINT [FK_FactProductInventorySwitch_DimProduct] FOREIGN KEY([ProductKey])
REFERENCES [dbo].[DimProduct] ([ProductKey])
GO
 
ALTER TABLE [dbo].[FactProductInventorySwitch] CHECK CONSTRAINT [FK_FactProductInventorySwitch_DimProduct]
GO

Screen6-ResultPartitionRangesBeforeSwitch

Als nächstes werden wir aus einer Partition-Range die Daten aus der Grundtabelle in die neu angelegt Switch-Tabelle verschieben.

ALTER TABLE [dbo].[FactProductInventory]
SWITCH PARTITION 1 TO [dbo].[FactProductInventorySwitch] PARTITION 1;

Screen7-ResultPartitionRangesAfterSwitch

In einem Ladeprozess würde man nun nur noch ein Truncate Table auf die Switch-Tabelle machen und schon wären die Daten aus der eigentlich Tabelle für diese Partition-Range gelöscht worden. Diese Lösung ist sowie extrem einfach also auch extrem effizient. Einfach ausprobieren! Es lohnt sich. Der Ladeprozess kann so um einen hohen Faktor schneller gemacht werden.

 

 

Relationale Partitionierungen von SQL Tabellen #1

Grundlagen

Wenn es um Massendaten im SQL-Server geht, dann kommt man relational kaum bis gar nicht um eine Partitionierung herum. Das Feature gibt es leider nur in der Enterprise Edition des SQL-Servers.

Ich möchte mich in dieser Blogreihe detailiert um die Planung und Einrichtung einer partitionierten Tabelle beziehen.

Das A und O einer korrekten Partitionierung ist die Planung vorweg, bevor man an den Aufbau der eigentlichen Partitionsfunktion und Partitionsschemas geht.

Als Beispiel soll für diesen Artikel die Tabelle „dbo.FactProductInventory“ dienen. Diese Tabelle bietet sich zum einen wegen der Datumsspalte und zum anderen durch die große Anzahl an Datensätzen an. Das Jahr 2014 wird als aktuelles Jahr angenommen.

Vorbereitungen

Um eine Partitionierung einer Tabelle einrichten zu können wird in der Tabelle eine Spalte benötigt, die für die „Einsortierung“ der Datensätze in die jeweiligen „Partition-Ranges“ verwendet werden kann.

Grundsätzlich ist zu empfehlen, dass keine Datentypen verwendet werden, die von Hause aus nicht zu den schnellsten zu verarbeiteten Datentypen gehören. Dazu zählen u.a. die Date und DateTime Datentypen.

Wird eine Einteilung auf Tag, Monats oder Jahres-Basis in Betracht gezogen, ist die beste Vorgehensweise, eine separate Spalte für die Partitionierung anzulegen, die Datums-Datentypen castet.

In dem Fallbeispiel erübrigt sich das, da die DateKey-Spalte schon eine Integer-Spalte ist.

Screen1-Spalte fuer Partitionierung

Hat man sich für eine Einteilung der Partition-Ranges entschieden geht es an die Definition des Partionschemes und Partitionfunction.

Partitionscheme und Partitionfunction

Der Kern jeder Partitionierung bildet die Partionscheme und Partitionfunction.

Das Paritionschema teilt die einzelnen Partition-Ranges in zugordnete Filegroups auf, wohin gegen die Partitionfunction die Grenzen der einzelnen Partition-Ranges definiert.

Zufinden sind die beiden Bereiche unter:

Screen2-Storage

Einem Scheme muss eine Function zugewiesen werden. D.h. ohne eine Function, kann auch kein Scheme erstellt werden.

Wenn einmal ein Partition-Scheme einer Tabelle zugeordnet ist, kann ein Scheme und eine Function nicht mehr gelöscht werden. Auch ein Ändern ist dann sehr umständlich.

Bevor eine Function erstellt werden kann, müssen die Daten in der Tabelle analysiert werden. In dem Beispiel gibt es Datensätze für die Jahre von 2010 bis 2014. Wie bereits oben geschrieben, wird in diesem Beispiel davon ausgegangen, dass das aktuelle Jahr – 2014 ist. In der Tabelle befinden sich Daten zwischen 2010 und 2014.

Was rät Microsoft? Was ist Best Practice?

Grundsätzlich empfiehlt Microsoft, dass die erste und letzte Partition in einer Function keine Daten enthalten soll. Dadurch wird gewährleistet, dass Daten problemlos angefügt werden können, da ein Split auf eine leere Partition  sehr schnell geht und keine physischen Daten verschoben werden müssen. Und zum anderen würde ein nachträgliches Ergänzen von noch älteren Daten somit auch kein Problem darstellen.

Zur Demonstration soll es genügen, wenn die Partition-Ranges so gewählt werden, dass alle Daten eines Jahres in Jahres Ranges eingeteilt werden.

Partition-Function

Partition-Functions bilden innerhalb der relationalen Partitionierung die jeweiligen Bereiche ab, in die die Daten einsortiert werden sollen. Hierbei kann man beim Anlegen der Function bestimmen, ob der Bereich an dem angegebenen Wert endet oder beginnt.

RANGE LEFT:

– 20091215 Daten würden in untenstehender Function in die zweite Range einsortiert werden.

–> Dadurch entsteht aber eine Unstimmigkeit, wenn man die einzelnen Ranges abfragt. 2009er Daten stehen in einer Range, die mit 20100000 benannt ist.

Umgehen kann man das, in dem direkt mit RANGE RIGHT gearbeitet wird. Die Daten vom 15.12.2009 würden dann in die letzte Partitionrange fallen, die quasi ein „Auffangbecken“ abbildet. Das kann man als Nachteil der RANGE RIGHT Methode ansehen, da die Daten vor dem Jahr 2010 in den anderen Ranges erstmal gefunden werden müssen. Durch ein Anlegen einer PartitionRange die weit in der Vergangenheit liegt, kann man dieses Problem lösen.

Code für Erzeugen einer Partion-Function mit „RANGE LEFT“

CREATE PARTITION FUNCTION [PFYearsLeft](INT) AS RANGE LEFT
FOR VALUES (20050000,20100000,20110000,20120000,20130000,20140000,20150000)
GO

Und hier die Version mit „RANGE  RIGHT“

CREATE PARTITION FUNCTION [PFYearsRight](INT) AS RANGE RIGHT
FOR VALUES (20050000,20100000,20110000,20120000,20130000,20140000)
GO

Das Ergebnis mit „RANGE LEFT“:

Screen5-CreatePartionFunctionLeft

Partition-Scheme

Bevor mit dem Anlegen eines Schemes begonnen werden kann, muss definiert werden, wie die Daten zukünftigt gespeichert werden sollen. Außerdem ist zu empfehlen, eigene Filegroups für bestimmte Inhaltsbereiche festzulegen. Jede Filegroup benötigt mindest ein zugeordnetes File.

Filegroup

Um flexibel mit den Daten auch in der Zukunft umgehen zu können werden 2 Filegroups mit jeweils einem File angelegt.

1.Filegroup: ProductInventoriesHist (Historische Daten bis einschließlich 31.12.2013)

2.Filegroup: ProductInventoriesCur (Aktuelles Jahr)

Ich rate davon ab, dass man z.B. Sales Daten aus dem Ecommerce-Geschäft mit Product-Inventories in einer Filegroup ablegt. Für unterschiedliche Inhalte, sollten auch eigene Filegroups angelegt werden.

Screen3-CreateFilegroup

Wichtig bei der Vergabe der Dateinnamen ist daran zu denken, dass die Files keinerlei Hinweise darauf haben, zu welcher Datenbank sie gehören. Daher sollte der Datenbankname auch in der Benennung der Files eine Rolle spielen.

Screen4-CreateFiles

Die Optimierung der Filegrößen ist ein Thema für sich und wird hier in diesem Post nicht weiter behandelt.

Und hier der T-SQL Code:

USE [master]
GO
ALTER DATABASE [AdventureWorksDW2014] ADD FILEGROUP [ProductInventoriesCur]
go
ALTER DATABASE [AdventureWorksDW2014] ADD FILEGROUP [ProductInventoriesHist]
go
 
ALTER DATABASE [AdventureWorksDW2014] 
	ADD FILE ( NAME = N'AdventureWorksDW2014_ProductInventoriesCur', FILENAME = N'd:\SQLData\AdventureWorksDW2014_ProductInventoriesCur.ndf' , SIZE = 307200KB , FILEGROWTH = 16384KB ) TO FILEGROUP [ProductInventoriesCur]
GO
ALTER DATABASE [AdventureWorksDW2014] 
	ADD FILE ( NAME = N'AdventureWorksDW2014_ProductInventoriesHist', FILENAME = N'd:\SQLData\AdventureWorksDW2014_ProductInventoriesHist.ndf' , SIZE = 307200KB , FILEGROWTH = 16384KB ) TO FILEGROUP [ProductInventoriesHist]
GO

Nach erfolgreichem Erstellen, kann mit der Erstellung der Partion-Scheme und Function begonnen werden.

Scheme-Erstellung

Ein Scheme kann nur über T-SQL erstellt werden. Eine GUI für diesen Vorgang gibt es in der 2014er Version des Management Studios nicht.

Hier ist der Code dafür:

CREATE PARTITION SCHEME [PSProductInventoriesRight] AS PARTITION [PFYearsRight]
TO ([ProductInventoriesHist],[ProductInventoriesHist],[ProductInventoriesHist], [ProductInventoriesHist], [ProductInventoriesCur], [ProductInventoriesCur], [ProductInventoriesCur])
GO

 

Zuweisen eines Partitionschemes zu einer Tabelle

Als nächstes soll das neu angelegte Scheme der Tabelle factProductInventory zugeordnet werden.

Grundsätzlich gibt es hierfür 2 Wege.

  1. Neue Tabelle mit Partitionierung anlegen und die Daten aus der alten Tabelle in die neu einfügen. Danach Renaming der Tabellen.
  2. Bestehende Tabelle so modifizieren, dass das Partitionschema verwendet wird.

Beide Vorschläge haben ihre Vor- und Nachteile. Im Folgenden schauen wir uns diese genauer an.

Vorschlag 1: Neue Tabelle mit Partitionierung anlegen

Vom Vorgehen her, ist dieser Ansatz sicherlich der einfachste. Jedoch muss bedacht werden, dass auf der alten Tabelle auch evtl. Foreignkey Einschränkungen liegen. Diese müssten dann auf allen Quellen geändert werden. Außerdem werden die Indizes durch die Umbenennung der neuen Tabelle nicht mit geändert, was zur Folge hat, dass die Indizes wahrscheinlich noch im Namen einen Teil enthalten, der auf die alte Tabelle hinweißt.

Vorschlag 2: Bestehende Tabelle so modifizieren, dass das Partitionschema verwendet wird

In diesem Abschnitt schauen wir uns nun die 2.Möglichkeit mal genauer an.

Um ein Partitionscheme einer Tabelle zuweisen zu können, müssen als erstes alle Constraints und Indizes von der Tabelle runtergenommen werden.

-- *****************************************************************************************
-- Alle Constraints in der Tabelle entfernen
-- *****************************************************************************************
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [FK_FactProductInventory_DimProduct]
GO
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [FK_FactProductInventory_DimDate]
GO
--ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [PK_FactProductInventory]
--GO
-- *****************************************************************************************
-- Index löschen fals vorhanden (PartitionColumnId)
-- *****************************************************************************************
DROP INDEX [IXFactProductInventoryDateKey] ON [dbo].[FactProductInventory]
GO

Tipp:

Man kann sich nach diesen einzelnen Steps die Tabelle skripten lassen, um den Fortschritt festzustellen.

Als nächstes müssen wir der Tabelle einen Clustered Index  verpassen, der dem neuen Partitionscheme zugewiesen wird, um ihn später durch einen Non-Clustered Index zu ersetzen. Desweiteren wird gleichzeitig ein weiteres sehr nützliches Feature der Enterprise Edition des SQL Servers genutzt. Die PAGE bzw. ROW COMPRESSION. In unserem Fall gehen wir davon aus, dass ausschließlich die Notwendigkeit besteht, aus der Tabelle schnell Lesen zu können. Die PAGE Compresssion ist hierfür gut geeignet.

-- *****************************************************************************************
-- Clustered Index auf die DateKey Spalte legen (PartitionColumnId)
-- *****************************************************************************************
CREATE CLUSTERED INDEX [IXFactProductInventoryDateKey] ON [dbo].[FactProductInventory] (
[DateKey])
WITH  (DATA_COMPRESSION = PAGE)    ON [PSProductInventoriesRight]([DateKey])
;
-- *****************************************************************************************
-- Diesen Clustered Index wieder entfernen. Dadurch bleibt aber das PartitionScheme
-- auf der Tabelle.
-- *****************************************************************************************
DROP INDEX [IXFactProductInventoryDateKey] ON  [dbo].[FactProductInventory];
;

Abschließend wird ein Non-Clustered Index erzeugt, der ebenfalls mit dem Scheme verbunden werden muss.

-- *****************************************************************************************
-- Non-Clusterd Index auf die DateKey Spalte legen (PartitionColumnId)
-- *****************************************************************************************
CREATE NONCLUSTERED INDEX [IXFactProductInventoryDateKey] ON [dbo].[FactProductInventory]
(
[DateKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF
, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, DATA_COMPRESSION = PAGE) ON [PSProductInventoriesRight]([DateKey])
;

Wenn wir uns nun das Skript anschauen, welches bei Erzeugen eines Create-Skripts über das Management-Studio generiert wird, kann man erkennen, dass das Partitionscheme nun einerseits auf der Tabelle liegt und andereseits auf dem Index.

CREATE TABLE [dbo].[FactProductInventory](
[ProductKey] [INT] NOT NULL,
[DateKey] [INT] NOT NULL,
[MovementDate] [DATE] NOT NULL,
[UnitCost] [MONEY] NOT NULL,
[UnitsIn] [INT] NOT NULL,
[UnitsOut] [INT] NOT NULL,
[UnitsBalance] [INT] NOT NULL
) ON [PSProductInventoriesRight]([DateKey])
WITH
(
DATA_COMPRESSION = PAGE
)
GO
/****** Object:  Index [IXFactProductInventoryDateKey]    Script Date: 28.05.2016 07:45:31 ******/
CREATE NONCLUSTERED INDEX [IXFactProductInventoryDateKey] ON [dbo].[FactProductInventory]
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [PSProductInventoriesRight]([DateKey])
GO

Der nächste Codeteil liefert dann die Aufteilung der Daten auf die Partitionen zurück.

SELECT
    SysTables.OBJECT_ID                                    AS [OBJECT_ID]
    ,object_schema_name(SysPartitions.OBJECT_ID)        AS [TABLE_SCHEMA]
    ,SysTables.name                                        AS [TABLE_NAME]
    ,SysPartitions.partition_number                        AS [PARTITION_NUMBER]
    ,SysRangeValue.VALUE                                AS [PARTITIONCOLUMNID_FROM]
    ,SysRangeValue.boundary_id                            AS [BOUNDARY_ID]
    ,SysPartitions.ROWS                                    AS [PARTITION_ROWS]
    ,SysPartitionFunctions.name                            AS [PARTITION_FUNCTION]
    ,SysSchemes.name                                    AS [PARTITION_SCHEMA]
FROM sys.partitions AS [SysPartitions]
    INNER join sys.indexes AS [SysIndizes]
        ON SysPartitions.OBJECT_ID = SysIndizes.OBJECT_ID
            and SysPartitions.index_id = SysIndizes.index_id
    INNER join sys.tables AS SysTables
        ON SysPartitions.OBJECT_ID = SysTables.OBJECT_ID
    INNER join sys.partition_schemes AS SysSchemes
        ON SysIndizes.data_space_id = SysSchemes.data_space_id
    INNER join sys.partition_functions AS SysPartitionFunctions
        ON SysPartitionFunctions.function_id = SysSchemes.function_id
    INNER join sys.partition_range_values AS SysRangeValue
        ON SysRangeValue.function_id = SysSchemes.function_id
            and SysPartitions.partition_number = SysRangeValue.boundary_id

Bei dieser Methode muss beachtet werden, dass bei großen Datenmengen dieser Vorgang mehrer Stunden dauern kann und somit das System für längere Zeit beinträchtigt ist. Durch die Möglichkeit, den Index Online neu aufzubauen (Enterprise Feature) kann aber vermieden werden, dass das System überhaupt nicht zur Verfügung steht.

Fazit:

Beide Herangehensweisen haben ihren Charm, wobei ich sagen muss, dass die 2. Variante mir am Besten gefällt. Der einfachste Weg ist aber immer noch das Anlegen und Zuweisen eines Partitionschemes bevor Daten in die Tabelle geladen wurden.