Verwalten der Beibehaltung von Verlaufsdaten in temporalen Tabellen mit SystemversionsverwaltungManage retention of historical data in system-versioned temporal tables

Anwendungsbereich:Applies to: JaSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) und höher JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceAnwendungsbereich:Applies to: JaSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed Instance

Durch temporale Tabellen mit Systemversionsverwaltung kann die Verlaufstabelle die Datenbank stärker vergrößern als reguläre Tabellen, insbesondere in den folgenden Situationen:With system-versioned temporal tables, the history table may increase database size more than regular tables, particularly under the following conditions:

  • Sie behalten Verlaufsdaten für eine langen Zeitraum bei.You retain historical data for a long period of time
  • Ihr Muster für Datenänderungen erfordert umfangreiche Aktualisierungen oder Löschungen.You have an update or delete heavy data modification pattern

Eine große und ständig wachsende Verlaufstabelle kann zu einem Problem werden, sowohl aufgrund der reinen Speicherkosten als auch durch Leistungsbeeinträchtigungen aufgrund von temporalen Abfragen.A large and ever-growing history table can become an issue both due to pure storage costs as well as imposing a performance tax on temporal querying. Daher ist die Entwicklung einer Aufbewahrungsrichtlinie für die Verwaltung von Daten in der Verlaufstabelle ein wichtiger Aspekt der Planung und Verwaltung des Lebenszyklus jeder temporalen Tabelle.Hence, developing a data retention policy for managing data in the history table is an important aspect of planning and managing the lifecycle of every temporal table.

Verwaltung der Datenbeibehaltung für die VerlaufstabelleData retention management for history table

Das Verwalten der Datenbeibehaltung für temporale Tabellen beginnt damit, die Beibehaltungsdauer für jede temporale Tabelle zu bestimmen.Managing temporal table data retention begins with determining the required retention period for each temporal table. Ihrer Beibehaltungsrichtlinie sollte in den meisten Fällen als Teil der Geschäftslogik der Anwendung betrachtet werden, die die temporalen Tabellen verwendet.Your retention policy, in most cases, should be considered to be part of the business logic of the application using the temporal tables. Für Anwendungen in Datenüberwachungs- und Zeitreiseszenarios gelten beispielsweise feste Anforderungen dafür, wie lange Verlaufsdaten für Onlineabfragen verfügbar sein müssen.For example, applications in data audit and time travel scenarios have firm requirements in terms of for how long historical data must be available for online querying.

Nachdem Sie die Beibehaltungsdauer bestimmt haben, ist der nächste Schritt, einen Plan für die Verwaltung von Verlaufsdaten zu entwickeln. Dazu gehört, wie und wo Sie Verlaufsdaten speichern und wie Sie Verlaufsdaten löschen, die älter sind, als die Beibehaltungsanforderungen vorsehen.Once you determine your data retention period, your next step is to develop a plan for managing historical data how and where you store your historical data and how to delete historical data that is older than your retention requirements. Die folgenden vier Ansätze für das Verwalten von Verlaufsdaten in der temporalen Verlaufstabelle stehen Ihnen zur Verfügung:The following four approaches for managing historical data in the temporal history table are available:

Bei jedem dieser Ansätze basiert die Logik für die Migration oder Bereinigung von Verlaufsdaten auf der Spalte, die dem Ende der Dauer in der aktuellen Tabelle entspricht.With each of these approaches, the logic for migrating or cleaning history data is based on the column that corresponds to end of period in the current table. Der Wert für das Ende der Dauer für jede Zeile bestimmt den Moment, an dem die Zeilenversion „geschlossen“ wird, an dem sie also in die Verlaufstabelle aufgenommen wird.The end of period value for each row determines the moment when the row version becomes "closed", i.e. when it lands in the history table. Beispielsweise gibt die Bedingung SysEndTime < DATEADD (DAYS, -30, SYSUTCDATETIME ()) an, dass Verlaufsdaten, die älter als einen Monat sind, aus der Verlaufstabelle entfernt oder verschoben werden müssen.For example, the condition SysEndTime < DATEADD (DAYS, -30, SYSUTCDATETIME ()) specifies that historical data older than one month needs to be removed or moved out from the history table.

Hinweis

In den Beispielen in diesem Thema wird dieses Beispiel für eine temporale Tabelle verwendet.The examples in this topic use this Temporal Table example.

Verwenden des Stretch Database-AnsatzesUsing stretch database approach

Hinweis

Der Ansatz mit Stretch Database kann nur für SQL ServerSQL Server verwendet werden, aber nicht für SQL-DatenbankSQL Database.Using the Stretch Database approach only applies to SQL ServerSQL Server and does not apply to SQL-DatenbankSQL Database.

Stretch Database in SQL ServerSQL Server migriert die Verlaufsdaten transparent zu Azure.Stretch Database in SQL ServerSQL Server migrates your historical data transparently to Azure. Zur Erhöhung der Sicherheit können Sie Daten während der Übertragung mit der SQL Server-Funktion Always Encrypted verschlüsseln.For additional security, you can encrypt data in motion using SQL Server's Always Encrypted feature. Darüber hinaus können Sie zum Schutz Ihrer Daten Sicherheit auf Zeilenebene und andere erweiterte SQL Server-Sicherheitsfeatures für eine temporale Datenbank und Stretch Database verwenden.Additionally, you can use Row-Level Security and other advanced SQL Server security features with Temporal and Stretch Database to protect your data.

Mit Stretch Database können Sie für einige oder alle Ihrer temporalen Verlaufstabellen ein Stretching auf Azure durchführen, und SQL Server verschiebt Verlaufsdaten im Hintergrund nach Azure.Using the Stretch Database approach, you can stretch some or all of your temporal history tables to Azure and SQL Server will silently move historical data to Azure. Durch die Aktivierung von Stretch für eine Verlaufstabelle ändert sich die Interaktion mit der temporalen Tabelle im Hinblick auf Datenänderungen und temporale Abfragen nicht.Stretch-enabling a history table does not change how you interact with the temporal table in terms of data modification and temporal querying.

  • Stretching der gesamten Verlaufstabelle: Konfigurieren Sie Stretch Database für die gesamte Verlaufstabelle, wenn das wichtigste Szenario die Datenüberwachung in einer Umgebung mit häufigen Datenänderungen und relativ seltenen Abfragen von Verlaufsdaten ist.Stretch the entire history table: Configure Stretch Database for your entire history table if your main scenario is data audit in the environment with frequent data changes and relatively rare querying on historical data. Verwenden Sie diesen Ansatz also, wenn die Leistung temporaler Abfragen nicht entscheidend ist.In other words, use this approach if performance of temporal querying is not critical. In diesem Fall kann die von Azure bereitgestellte Kosteneffizienz interessant sein.In this case, the cost-effectiveness provided by Azure may be compelling. Beim Stretching der gesamten Verlaufstabelle können Sie den Stretch-Assistenten oder Transact-SQL verwenden.When stretching the entire history table, you can either use the Stretch Wizard or Transact-SQL. Beispiele für beides sind weiter unten aufgeführt.Examples of both appear below.

  • Stretching für einen Teil der Verlaufstabelle: Konfigurieren Sie Stretch Database nur für einen Teil der Verlaufstabelle, um die Leistung zu verbessern, wenn Ihr wichtigstes Szenario in erster Linie das Abfragen aktueller Verlaufsdaten beinhaltet, Sie aber die Option zum Abfragen älterer Verlaufsdaten bei Bedarf beibehalten möchten, solange diese Daten remote zu geringeren Kosten gespeichert werden.Stretch a portion of the history table: Configure Stretch Database for only a portion of your history table to improve performance if your main scenario involves primarily querying recent historical data, but you wish to preserve the option to query older historical data when needed while storing this data remotely at a lower cost. Mit Transact-SQL erreichen Sie dies, indem Sie eine Prädikatfunktion angeben, um die Zeilen auszuwählen, die aus der Verlaufstabelle migriert werden, anstatt alle Zeilen zu migrieren.With Transact-SQL, you can accomplish this by specifying a predicate function to select the rows that will be migrated from the history table rather than migrating all of the rows. Wenn Sie mit temporalen Tabellen arbeiten, ist es in der Regel sinnvoll, Daten basierend auf einer Zeitbedingung zu verschieben (d. h. basierend auf dem Alter der Zeilenversion in der Verlaufstabelle).When you work with temporal tables, it typically makes sense to move data based on time condition (i.e. based on age of the row version in the history table).

    Wenn Sie eine deterministische Prädikatfunktion verwenden, können Sie einen Teil des Verlaufs in derselben Datenbank zusammen mit den aktuellen Daten behalten, während der Rest zu Azure migriert wird.Using a deterministic predicate function, you can keep portion of history in the same database with the current data, while the rest is migrated to Azure. Beispiele und Informationen zu Einschränkungen finden Sie unter Auswählen zu migrierender Zeilen mithilfe einer Filterfunktion (Stretch-Datenbank)For examples and limitations, see Select rows to migrate by using a filter function (Stretch Database). Da nicht deterministische Funktionen nicht gültig sind, wenn Sie Verlaufsdaten in der Form eines gleitendes Fensters übertragen möchten, müssten Sie die Definition der Inlineprädikatfunktion regelmäßig ändern, damit das Fenster von Zeilen, das Sie lokal speichern, im Hinblick auf das Alter konstant ist.Because non-deterministic functions are not valid, if you want to transfer history data in sliding window manner, you would need to regularly alter definition of the inline predicate function so that window of rows you keep locally is constant in terms of age. Mit einem gleitenden Fenster können Sie Verlaufsdaten, die älter als ein Monat sind, kontinuierlich nach Azure verschieben.Sliding window allows you to constantly move historical data older than one month to Azure. Ein Beispiel dieses Ansatzes ist weiter unten dargestellt.An example of this approach appears below.

Hinweis

Stretch Database migriert Daten zu Azure.Stretch Database migrates data to Azure. Daher benötigen Sie ein Azure-Konto und ein Abonnement für die Abrechnung.Therefore, you have to have an Azure account and a subscription for billing. Melden Sie sich für eine einmonatige kostenlose Testversion an, um ein kostenloses Azure-Testkonto zu erhalten.To get a free trial Azure account, click Free One-Month Trial.

Sie können eine temporale Verlaufstabelle für Stretch mit dem Stretch-Assistenten oder Transact-SQL konfigurieren, und Sie können eine temporale Verlaufstabelle für Stretch aktivieren, wenn die Systemversionsverwaltung auf ON festgelegt ist.You can configure a temporal history table for Stretch using either the Stretch Wizard or Transact-SQL, and you can stretch-enable a temporal history table while system-versioning is set to ON. Ein Stretching der aktuellen Tabelle ist nicht zulässig, da es nicht sinnvoll ist, für die aktuelle Tabelle ein Stretching durchzuführen.Stretching the current table is not allowed because it does not make sense to stretch the current table.

Verwenden des Stretch-Assistenten für ein Stretching der gesamten VerlaufstabelleUsing the Stretch Wizard to stretch the entire history table

Die einfachste Methode für Anfänger ist, den Stretch-Assistenten zu verwenden, um Stretch für die gesamte Datenbank zu aktivieren. Wählen Sie dann die temporale Verlaufstabelle im Stretch-Assistenten aus (in diesem Beispiel wird davon ausgegangen, dass Sie die Department-Tabelle als eine temporale Tabelle mit Systemversionsverwaltung in einer ansonsten leeren Datenbank konfiguriert haben).The easiest method for beginners is to use the Stretch Wizard to enable stretch for the entire database and then select the temporal history table within the Stretch wizard (this example assumes that you have configured the Department table as a system-versioned temporal table in an otherwise empty database). In SQL Server 2016 (13.x)SQL Server 2016 (13.x)können Sie nicht mit der rechten Maustaste auf die temporale Verlaufstabelle selbst klicken und dann auf „Stretch“ klicken.In SQL Server 2016 (13.x)SQL Server 2016 (13.x), you cannot right-click the temporal history table itself and click Stretch.

  1. Klicken Sie mit der rechten Maustaste auf die Datenbank, und zeigen Sie auf Aufgaben, zeigen Sie auf Stretch, und klicken Sie dann auf Aktivieren , um den Assistenten zu starten.Right-click your database and point to Tasks, point to Stretch, and then click Enable to launch the wizard.

  2. Aktivieren Sie im Fenster Tabellen auswählen das Kontrollkästchen für die temporale Verlaufstabelle, und klicken Sie auf „Weiter“.In the Select tables window, select the checkbox for the temporal history table and click Next.

    Auswählen der Verlaufstabelle auf der Seite „Tabellen auswählen“Selecting the history table on the Select tables page

  3. Geben Sie im Fenster Azure konfigurieren Ihre Anmeldeinformationen an.In the Configure Azure window, provide your login credentials. Melden Sie sich bei Microsoft Azure an, oder registrieren Sie sich für ein Konto.Sign in to Microsoft Azure or sign-up for an account. Wählen Sie das zu verwendende Abonnement und die Azure-Region aus.Select the subscription to use, select the Azure region. Erstellen Sie dann einen neuen Server, oder wählen Sie einen vorhandenen Server aus.Then either create a new server or select an existing server. Klicken Sie auf Weiter.Click Next.

    Erstellen eines neuen Azure-Servers – Stretch Database-AssistentCreate new Azure server - Stretch Database wizard

  4. Geben Sie im Fenster Sichere Anmeldeinformationen ein Kennwort für den Datenbankhauptschlüssel an, um Ihre Anmeldeinformationen für die SQL Server-Quelldatenbank zu schützen, und klicken Sie dann auf „Weiter“.In the Secure credentials window, provide a password for the database master key to secure your source SQL Server database credential and click Next.

    Seite „Sichere Anmeldeinformationen“ des Stretch Database-AssistentenSecure credentials page of the Stretch Database wizard

  5. Geben Sie im Fenster IP-Adresse auswählen den IP-Adressbereich für Ihre SQL Server-Instanz an, um Ihrem Azure-Server die Kommunikation mit SQL Server zu ermöglichen (bei Auswahl eines vorhandenen Servers, für den bereits eine Firewallregel vorhanden ist, klicken Sie hier einfach auf „Weiter“, um die vorhandene Firewallregel zu verwenden).In the Select IP address window, provide the IP address range for your SQL Server to let your Azure server communicate with your SQL Server (if you select an existing server for which a firewall rule already exists, simply click Next here to use the existing firewall rule). Klicken Sie zunächst auf Weiter und dann auf Fertig stellen, um Stretch Database zu aktivieren und ein Stretching für die temporale Verlaufstabelle durchzuführen.Click Next and then click Finish to enable Stretch Database and stretch the temporal history table.

    Seite „IP-Adresse auswählen“ des Stretch Database-AssistentenSelect IP address page of the Stretch Database wizard

  6. Überprüfen Sie nach Abschluss des Assistenten, ob die Datenbank erfolgreich für Stretch aktiviert wurde.When the wizard completes, verify that your database was successfully stretch-enabled. Beachten Sie die Symbole im Objekt-Explorer, die angeben, dass für die Datenbank ein Stretching durchgeführt wurde.Notice the icons in Object Explorer indicating the database was stretched.

Hinweis

Wenn das Aktivieren der Datenbank für Stretch fehlschlägt, überprüfen Sie das Fehlerprotokoll.If the Enable Database for Stretch fails, review the error log. Ein häufiger Fehler ist eine fehlerhafte Konfiguration der Firewallregel.A common error is improperly configuring the firewall rule.

Weitere Informationen:See also:

Verwenden von Transact-SQL zum Durchführen eines Stretchings der gesamten VerlaufstabelleUsing Transact-SQL to stretch the entire history table

Sie können alternativ Transact-SQL verwenden, um die Streckung für den lokalen Server zu aktivieren, und Stretch Database für eine Datenbank zu aktivieren.You can also use Transact-SQL to enable Stretch on the local server and Enable Stretch Database for a database. Sie können dann Transact-SQL verwenden, um Stretch Database für eine Tabelle zu aktivieren.You can then use Transact-SQL to enable Stretch Database on a table. Führen Sie mit einer Datenbank, die zuvor für Stretch Database aktiviert wurde, das folgende Transact-SQL-Skript aus, um für eine vorhandene temporale Verlaufstabelle mit Systemversionsverwaltung ein Stretching durchzuführen:With a database previously enabled for Stretch Database, execute the following Transact-SQL script to stretch an existing system-versioned temporal history table:

ALTER TABLE <history table name>
SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND));

Verwenden von Transact-SQL zum Durchführen eines Stretchings für einen Teil der VerlaufstabelleUsing Transact-SQL to stretch a portion of the history table

Um nur für einen Teil der Verlaufstabelle ein Stretching durchzuführen, erstellen Sie zunächst eine Inlineprädikatfunktion.To stretch only a portion of the history table, you start by creating an inline predicate function. In diesem Beispiel gehen wir davon aus, dass Sie zum ersten Mal am 1. Dezember 2015 die Inlineprädikatfunktion konfiguriert haben und dass für alle Verlaufsdaten, die vor dem 1. November 2015 gespeichert wurden, ein Stretching auf Azure durchgeführt werden soll.For this example, let's assume that you configured inline predicate function for the first time on December 1, 2015 and want to stretch to Azure all history date older than November 1, 2015. Um dies zu erreichen, erstellen Sie zunächst die folgende Funktion:To accomplish this, start by creating the following function:

CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151101(@systemEndTime datetime2)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS is_eligible
  WHERE @systemEndTime < CONVERT(datetime2, '2015-11-01T00:00:00', 101) ;

Als Nächstes verwenden Sie das folgende Skript, um das Filterprädikat der Verlaufstabelle hinzuzufügen und den Migrationsstatus auf OUTBOUND festzulegen und so eine prädikatbasierte Datenmigration für die Verlaufstabelle zu aktivieren.Next, use the following script to add the filter predicate to the history table and set the migration state to OUTBOUND to enable predicate based data migration for the history table.

ALTER TABLE <history table name>
SET (
      REMOTE_DATA_ARCHIVE = ON
        (
          FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151101 (SysEndTime)
            , MIGRATION_STATE = OUTBOUND
        )
    )
;

Die Prädikatfunktion muss täglich exakt sein, um ein gleitendes Fenster beizubehalten. Ändern Sie also die Filterzeilenbedingung jeden Tag um einen Tag.To maintain a sliding window, you need to make predicate function to be accurate every day (i.e. change filtering row condition every day by one day). Das folgende Skript ist das Skript, dass Sie am 2. Dezember 2015 ausführen müssten:The following script is the script that you would you need to execute on December 2, 2015:

BEGIN TRAN
/*(1) Create new predicate function definition */
  CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151102(@systemEndTime datetime2)
   RETURNS TABLE
    WITH SCHEMABINDING
      AS
        RETURN SELECT 1 AS is_eligible
          WHERE @systemEndTime < CONVERT(datetime2,'2015-11-02T00:00:00', 101)
  GO
 
/*(2) Set the new function as filter predicate */
  ALTER TABLE <history table name>
    SET
      (
        REMOTE_DATA_ARCHIVE = ON
          (
            FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151102(SysEndTime),
              MIGRATION_STATE = OUTBOUND
          )
      )
COMMIT ;

Verwenden Sie den SQL Server-Agent oder einen anderen Planungsmechanismus, damit eine gültige Definition der Prädikatfunktion immer sichergestellt ist.Use SQL Server Agent or some other scheduling mechanism to ensure valid predicate function definition all the time.

Verwenden des TabellenpartitionierungsansatzesUsing table partitioning approach

DieTabellenpartitionierung kann bewirken, dass sich große Tabellen besser verwalten und skalieren lassen.Table partitioning can make large tables more manageable and scalable. Wenn Sie den Ansatz mit Tabellenpartitionierung verwenden, können Sie Verlaufstabellenpartitionen nutzen, um eine angepasste Datenbereinigung oder Offlinearchivierung basierend auf einer Zeitbedingung zu implementieren.Using the table partitioning approach, you can use history table partitions to implement custom data cleanup or offline archival based on a time condition. Durch die Tabellenpartitionierung mithilfe der Partitionsentfernung auch Leistungsvorteile beim Abfragen von temporalen Tabellen für einen Teil des Datenverlaufs.Table partitioning will also give you performance benefits when querying temporal tables on a subset of data history by using partition elimination.

Mit der Tabellenpartitionierung können Sie den Ansatz mit einem gleitenden Fenster implementieren, um den ältesten Teil der Verlaufsdaten aus der Verlaufstabelle zu verschieben und die Größe des beibehaltenen Teils im Hinblick auf das Alter konstant zu halten. So verwalten Sie die Daten in der Verlaufstabelle entsprechend der erforderlichen Beibehaltungsdauer.With table partitioning, you can implement a sliding window approach to move out oldest portion of the historical data from the history table and keep the size of the retained part constant in terms of age - maintaining data in the history table equal to required retention period. Der Vorgang des Austauschens von Daten aus der Verlaufstabelle wird unterstützt, wenn SYSTEM_VERSIONING auf ON festgelegt ist. Dies bedeutet, dass ein Teil der Verlaufsdaten bereinigt werden kann, ohne ein Wartungsfenster einzurichten oder normale Arbeitsauslastungen zu blockieren.The operation of switching data out from the history table is supported while SYSTEM_VERSIONING is ON, which means that you can clean a portion of the history data without introducing a maintenance windows or blocking your regular workloads.

Hinweis

Für einen Partitionswechsel muss der gruppierte Index für die Verlaufstabelle am Partitionierungsschema ausgerichtet werden („SysEndTime“ muss enthalten sein).In order to perform partition switching, your clustered index on history table must be aligned with the partitioning schema (it has to contain SysEndTime). Die vom System erstellte Standardverlaufstabelle enthält einen gruppierten Index, der die Spalten „SysEndTime“ und „SysStartTime“ aufweist. Dies ist optimal für die Partitionierung, das Einfügen neuer Daten und normale temporale Abfragen.The default history table created by the system contains a clustered index that includes the SysEndTime and SysStartTime columns, which is optimal for partitioning, inserting new history data, and typical temporal querying. Weitere Informationen finden Sie unter Temporal Tables.For more information, see Temporal Tables.

Für den Ansatz mit einem gleitenden Fenster müssen Sie zwei Sätze von Aufgaben ausführen:A sliding window approach has two sets of tasks that you need to perform:

  • Eine PartitionierungskonfigurationsaufgabeA partitioning configuration task
  • Aufgaben für die wiederholte PartitionswartungRecurring partition maintenance tasks

Zur Veranschaulichung gehen wir davon aus, dass wir Verlaufsdaten für sechs Monate beibehalten möchten und dass die Daten der einzelnen Monate jeweils in einer separaten Partition gespeichert werden sollen.For the illustration, let's assume that we want to keep historical data for 6 months and that we want to keep every month of data in a separate partition. Außerdem nehmen wir an, dass wir im September 2015 die Systemversionsverwaltung aktiviert haben.Also, let's assume that we activated system-versioning in September of 2015.

Mit einer Partitionierungskonfigurationsaufgabe wird die erste Partitionierungskonfiguration für die Verlaufstabelle erstellt.A partitioning configuration task creates the initial partitioning configuration for the history table. In diesem Beispiel würden wir eine Anzahl von Partitionen erstellen, die der Größe des gleitenden Fensters entspricht (in Monaten). Zusätzlich wird eine leere Partition vorbereitet (dies wird weiter unten erläutert).For this example, we would create the same number partitions as the size of sliding window, in months, plus one additional empty partition pre-prepared (explained below). Mit dieser Konfiguration wird sichergestellt, dass das System neue Daten ordnungsgemäß speichern kann, wenn die Aufgabe für die wiederholte Partitionswartung das erste Mal ausgeführt wird. Zudem wird gewährleistet, dass wir Partitionen mit Daten nicht unterteilen, um teure Datenverschiebungen zu vermeiden.This configuration ensures that the system will be able to store new data correctly when we start the recurring partition maintenance task for the first time and guarantees that we never split partitions with data to avoid expensive data movements. Sie sollten diese Aufgabe mit Transact-SQL und dem weiter unten aufgeführten Beispielskript ausführen.You should perform this task using Transact-SQL using the example script below.

Die folgende Abbildung zeigt die erste Partitionierungskonfiguration, mit der Daten von 6 Monaten beibehalten werden.The following picture shows initial partitioning configuration to keep 6 months of data.

Diagramm: Anfängliche Partitionierungskonfiguration, bei der die Daten sechs Monate lang aufbewahrt werdenDiagram showing initial partitioning configuration to keep six months of data.

Hinweis

Unter „Überlegungen zur Leistung bei der Tabellenpartitionierung“ weiter unten finden Sie Informationen zu Leistungseinbußen bei der Verwendung von RANGE LEFT oder RANGE RIGHT beim Konfigurieren der Partitionierung.See Performance considerations with table partitioning below for the performance implications of using RANGE LEFT versus RANGE RIGHT when configuring partitioning.

Beachten Sie, dass die untere bzw. die obere Grenze bei der ersten bzw. der letzten Partition „offen“ ist, um sicherzustellen, dass für jede neue Zeile eine Zielpartition vorhanden ist, unabhängig vom Wert in der Partitionierungsspalte.The first and last partition are "open" on lower and upper boundaries respectively to ensure that every new row has destination partition regardless of the value in partitioning column. Im Laufe der Zeit werden neue Zeilen in der Verlaufstabelle in höhere Partitionen aufgenommen.As time goes by, new rows in history table will land in higher partitions. Wenn die 6. Partition gefüllt wird, haben wir die vorgesehene Beibehaltungsdauer erreicht.When 6th partition gets filled up, we will have reached the targeted retention period. Dies ist der Zeitpunkt, an dem die Aufgabe für die wiederholte Partitionswartung zum ersten Mal gestartet wird (sie muss in regelmäßigen Abständen ausgeführt werden, in diesem Beispiel einmal pro Monat).This is the moment to start the recurring partition maintenance task for the first time (it needs to be scheduled to run periodically, once per month in this example).

Die folgende Abbildung veranschaulicht die Aufgabe für die wiederholte Partitionswartung (die genauen Schritte werden im Folgenden erläutert).The following picture illustrates the recurring partition maintenance tasks (see detailed steps below).

Diagramm: Regelmäßige PartitionswartungstasksDiagram showing the recurring partition maintenance tasks.

Die genauen Schritte für die Aufgabe für die wiederholte Partitionswartung:The detailed steps for the recurring partition maintenance tasks are:

  1. SWITCH OUT: Erstellen Sie eine Stagingtabelle, und wechseln Sie dann eine Partition zwischen der Verlaufstabelle und der Stagingtabelle. Verwenden Sie dazu die Anweisung ALTER TABLE (Transact-SQL) mit dem Argument SWITCH PARTITION (siehe Beispiel C: „Wechseln von Partitionen zwischen Tabellen“).SWITCH OUT: Create a staging table and then switch a partition between the history table and the staging table using the ALTER TABLE (Transact-SQL) statement with the SWITCH PARTITION argument (see Example C. Switching partitions between tables).

    ALTER TABLE <history table> SWITCH PARTITION 1 TO <staging table>
    

    Nach dem Partitionswechsel können Sie optional die Daten aus der Stagingtabelle archivieren und dann die Stagingtabelle löschen oder kürzen, damit Sie vorbereitet sind, wenn Sie diese Aufgabe für die wiederholte Partitionswartung das nächste Mal ausführen müssen.After the partition switch, you can optionally archive the data from staging table and then either drop or truncate the staging table to be ready for the next time you need to perform this recurring partition maintenance task.

  2. MERGE RANGE: Führen Sie die leere Partition 1 mit der Partition 2 zusammen. Verwenden Sie dazu ALTER PARTITION FUNCTION (Transact-SQL) mit MERGE RANGE (siehe Beispiel B).MERGE RANGE: Merge the empty partition 1 with partition 2 using the ALTER PARTITION FUNCTION (Transact-SQL) with MERGE RANGE (See example B). Durch Entfernen der untersten Grenze mit dieser Funktion führen Sie effektiv die leere Partition 1 mit der ehemaligen Partition 2 zusammen, sodass eine neue Partition 1 entsteht.By removing the lowest boundary using this function, you effectively merge the empty partition 1 with the former partition 2 to form new partition 1. Bei den anderen Partitionen werden ebenfalls die Ordinalzahlen geändert.The other partitions also effectively change their ordinals.

  3. SPLIT RANGE: Erstellen Sie eine neue leere Partition 7. Verwenden Sie dazu ALTER PARTITION FUNCTION (Transact-SQL) mit SPLIT RANGE (siehe Beispiel A).SPLIT RANGE: Create a new empty partition 7 using the ALTER PARTITION FUNCTION (Transact-SQL) with SPLIT RANGE (See example A). Durch das Hinzufügen einer neuen oberen Grenze mit dieser Funktion erstellen Sie effektiv eine separate Partition für den kommenden Monat.By adding a new upper boundary using this function, you effectively create a separate partition for the upcoming month.

Verwenden von Transact-SQL zum Erstellen von Partitionen in der VerlaufstabelleUse Transact-SQL to create partitions on history table

Verwenden Sie das Transact-SQL-Skript im folgenden Codefenster, um die Partitionsfunktion und das Partitionsschema zu erstellen, und erstellen Sie den gruppierten Indexes so neu, dass die Partitionierung am Partitionsschema und den Partitionen ausgerichtet ist.Use the Transact-SQL script in the code window below to create the partition function, the partition schema, and recreate the clustered index to be partition-aligned with the partition schema, partitions. In diesem Beispiel erstellen wir ein gleitendes Fenster für sechs Monate mit monatlichen Partitionen ab September 2015.For this example, we will creating a six-month sliding window approach with monthly partitions beginning September, 2015.

BEGIN TRANSACTION
/*Create partition function*/
    CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime] (datetime2(7))
        AS RANGE LEFT FOR VALUES
          (
            N'2015-09-30T23:59:59.999'
          , N'2015-10-31T23:59:59.999'
          , N'2015-11-30T23:59:59.999'
          , N'2015-12-31T23:59:59.999'
          , N'2016-01-31T23:59:59.999'
          , N'2016-02-29T23:59:59.999'
          )
/*Create partition scheme*/
    CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_SysEndTime]
        AS PARTITION [fn_Partition_DepartmentHistory_By_SysEndTime]
            TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
/*Re-create index to be partition-aligned with the partitioning schema*/
    CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory]
        (
            [SysEndTime] ASC
          , [SysStartTime] ASC
        )  
    WITH
        (
            PAD_INDEX = OFF
          , STATISTICS_NORECOMPUTE = OFF
          , SORT_IN_TEMPDB = OFF
          , DROP_EXISTING = ON
          , ONLINE = OFF
          , ALLOW_ROW_LOCKS = ON
          , ALLOW_PAGE_LOCKS = ON
          , DATA_COMPRESSION = PAGE
        )
    ON [sch_Partition_DepartmentHistory_By_SysEndTime] ([SysEndTime])

COMMIT TRANSACTION;

Verwenden von Transact-SQL zum Verwalten von Partitionen im Szenario mit gleitendem FensterUsing Transact-SQL to maintain partitions in sliding window scenario

Verwenden Sie das Transact-SQL-Skript im folgenden Codefenster, um Partitionen im Szenario mit gleitendem Fenster zu verwalten.Use the Transact-SQL script in the code window below to maintain partitions in the sliding window scenario. In diesem Beispiel lagern wir die Partition für September 2015 mit MERGE RANGE aus und fügen dann eine neue Partition für März 2016 mit SPLIT RANGE hinzu.For this example, we will switch out the partition for September of 2015 using MERGE RANGE and then add a new partition for March of 2016 using SPLIT RANGE.

BEGIN TRANSACTION
/*(1) Create staging table */
    CREATE TABLE [dbo].[staging_DepartmentHistory_September_2015]
        (
            [DeptID] [int] NOT NULL
          , [DeptName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
          , [ManagerID] [int] NULL
          , [ParentDeptID] [int] NULL
          , [SysStartTime] [datetime2](7) NOT NULL
          , [SysEndTime] [datetime2](7) NOT NULL
        ) ON [PRIMARY]
    WITH
        (
            DATA_COMPRESSION = PAGE
        )
/*(2) Create index on the same filegroups as the partition that will be switched out*/
    CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2015]
        ON [dbo].[staging_DepartmentHistory_September_2015]
        (
            [SysEndTime] ASC
          , [SysStartTime] ASC
        )
    WITH
        (
            PAD_INDEX = OFF
          , SORT_IN_TEMPDB = OFF
          , DROP_EXISTING = OFF
          , ONLINE = OFF
          , ALLOW_ROW_LOCKS = ON
          , ALLOW_PAGE_LOCKS = ON
        )
    ON [PRIMARY]
/*(3) Create constraints matching the partition that will be switched out*/
    ALTER TABLE [dbo].[staging_DepartmentHistory_September_2015] WITH CHECK
        ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]
            CHECK ([SysEndTime]<=N'2015-09-30T23:59:59.999')
    ALTER TABLE [dbo].[staging_DepartmentHistory_September_2015]
        CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]
/*(4) Switch partition to staging table*/
    ALTER TABLE [dbo].[DepartmentHistory]
        SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2015]
        WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE))
/*(5) [Commented out] Optionally archive the data and drop staging table
      INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]
      SELECT * FROM [dbo].[staging_DepartmentHistory_September_2015];
      DROP TABLE [dbo].[staging_DepartmentHIstory_September_2015];
*/
/*(6) merge range to move lower boundary one month ahead*/
    ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime]()
        MERGE RANGE(N'2015-09-30T23:59:59.999')
/*(7) Create new empty partition for "April and after" by creating new boundary point and specifying NEXT USED file group*/
    ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_SysEndTime] NEXT USED [PRIMARY]
        ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime]() SPLIT RANGE(N'2016-03-31T23:59:59.999')
COMMIT TRANSACTION

Sie können das obige Skript etwas ändern und im regelmäßigen monatlichen Wartungsprozess verwenden:You can slightly modify script above and use it in regular monthly maintenance process:

  1. Erstellen Sie in Schritt (1) eine neue Stagingtabelle für den Monat, den Sie entfernen möchten (Oktober wäre der nächste in unserem Beispiel).In step (1) create new staging table for the month you want to remove (October would be next one in our example).
  2. Erstellen Sie in Schritt (3) eine CHECK-Einschränkung, die dem Monat entspricht, dessen Daten Sie entfernen möchten: [SysEndTime]<=N'2015-10-31T23:59:59.999' für die Oktober-Partition.In step (3) create and check constraint that matches the month of data you want to remove: [SysEndTime]<=N'2015-10-31T23:59:59.999' for October partition.
  3. Ändern Sie in Schritt (4) Partition 1 in die neu erstellte Stagingtabelle.In step (4) SWITCH partition 1 to newly created staging table.
  4. Ändern Sie in Schritt (6) die Partitionsfunktion durch Zusammenführen der unteren Grenze: MERGE RANGE(N'2015-10-31T23:59:59.999' nach dem Auslagern der Daten für Oktober.In step (6) alter partition function by merging lower boundary: MERGE RANGE(N'2015-10-31T23:59:59.999' after you moved out data for October.
  5. Teilen Sie in Schritt (7) die Partitionsfunktion durch Erstellen der neuen oberen Grenze: SPLIT RANGE (N'2016-04-30T23:59:59.999' nach dem Auslagern der Daten für Oktober.In step (7) split partition function creating new upper boundary: SPLIT RANGE (N'2016-04-30T23:59:59.999' after you moved out data for October.

Die optimale Lösung wäre jedoch, regelmäßig ein generisches Transact-SQL-Skript auszuführen, das die entsprechende Aktion monatlich ohne Skriptänderungen durchführen kann.However, the optimal solution would be to regularly run a generic Transact-SQL script that is a capable of performing the appropriate action every month without script modification. Es ist möglich, das oben angeführte Skript zu verallgemeinern, um die bereitgestellten Parameter zu bearbeiten (die untere Grenze, die zusammengeführt werden muss, und die neue Grenze, die durch Teilen der Partition erstellt wird).It is possible to generalize the script above to act upon provided parameters (lower boundary that needs to be merged and new boundary that will be created by with partition split). Um zu vermeiden, dass jeden Monat Stagingtabellen erstellt werden, können Sie eine Stagingtabelle im Voraus erstellen und wiederverwenden, indem Sie die CHECK-Einschränkung entsprechend der Partition ändern, die ausgelagert wird. Sehen Sie sich die folgenden Seiten an, um Ideen für die vollständige Automatisierung eines gleitenden Fensters mithilfe eines Transact-SQL-Skripts zu bekommen.In order to avoid staging table creation every month, you can create one beforehand and reuse by changing check constraint to match partition that will be switched out. Take a look at the following pages to get ideas on how sliding window can be fully automated using a Transact-SQL script.

Überlegungen zur Leistung bei der TabellenpartitionierungPerformance considerations with table partitioning

Es ist äußerst wichtig, MERGE und SPLIT RANGE-Vorgänge durchzuführen, um das Verschieben von Daten zu vermeiden, da dies einen erheblichen Verarbeitungsaufwand verursachen kann.It is important to perform the MERGE and SPLIT RANGE operations to avoid any data movement as data movement can incur significant performance overhead. Weitere Informationen finden Sie unter Ändern einer Partitionsfunktion. Verwenden Sie dazu RANGE LEFT statt RANGE RIGHT, wenn Sie CREATE PARTITION FUNCTION (Transact-SQL) verwenden.For more information, see Modify a Partition Function.You accomplish this by using RANGE LEFT rather than RANGE RIGHT when you CREATE PARTITION FUNCTION (Transact-SQL).

Lassen Sie uns zuerst visuell die Bedeutung der Optionen RANGE LEFT und RANGE RIGHT erläutern:Let's first visually explain meaning of the RANGE LEFT and RANGE RIGHT options:

Diagramm: Optionen RANGE LEFT und RANGE RIGHTDiagram showing the RANGE LEFT and RANGE RIGHT options.

Beim Definieren einer Partitionsfunktion als RANGE LEFT sind die angegebenen Werte die oberen Grenzen der Partitionen.When you define a partition function as RANGE LEFT, the specified values are the upper boundaries of the partitions. Wenn Sie RANGE RIGHT verwenden, sind die angegebenen Werte die unteren Grenzen der Partitionen.When you use RANGE RIGHT, the specified values are the lower boundaries of the partitions. Wenn Sie den MERGE RANGE-Vorgang verwenden, um eine Grenze aus der Definition der Partitionsfunktion zu entfernen, entfernt die zugrunde liegende Implementierung auch die Partition, die die Grenze enthält.When you use the MERGE RANGE operation to remove a boundary from the partition function definition, the underlying implementation also removes the partition which contains the boundary. Wenn diese Partition nicht leer ist, werden Daten in die Partition verschoben, die das Ergebnis des MERGE RANGE-Vorgangs ist.If that partition is not empty, data will be moved to the partition that is result of MERGE RANGE operation.

Im Szenario mit gleitendem Fenster entfernen wir immer die unterste Partitionsgrenze.In sliding window scenario, we always remove lowest partition boundary.

  • RANGE LEFT-Fall: Im RANGE LEFT-Fall gehört die unterste Partitionsgrenze zu Partition 1, die leer ist (nach dem Auslagern der Partition), sodass MERGE RANGE keine Datenverschiebungen verursacht.RANGE LEFT case: In RANGE LEFT case, the lowest partition boundary belongs to partition 1, which is empty (after partition switch out), so MERGE RANGE won't incur any data movement.
  • RANGE RIGHT-Fall: Im RANGE RIGHT-Fall gehört die unterste Partitionsgrenze zu Partition 2, die nicht leer ist, da wir davon ausgehen, dass Partition 1 durch Auslagern geleert wurde. In diesem Fall entstehen durch MERGE RANGE Datenverschiebungen (Daten von Partition 2 werden in Partition 1 verschoben).RANGE RIGHT case: In RANGE RIGHT case, the lowest partition boundary belongs to partition 2, which is not empty as we assumed that partition 1 was emptied by switch out. In this case MERGE RANGE will incur data movement (data from partition 2 will be moved to partition 1). Um dies zu vermeiden, muss RANGE RIGHT im Szenario mit gleitendem Fenster eine Partition 1 aufweisen, die immer leer ist.To avoid this, RANGE RIGHT in the sliding window scenario needs to have partition 1, which is always empty. Dies bedeutet, wenn wir RANGE RIGHT verwenden, sollten wir eine zusätzliche Partition im Vergleich mit dem RANGE LEFT-Fall erstellen und verwalten.This means that if we use RANGE RIGHT, we should create and maintain one additional partition compared to RANGE LEFT case.

Zusammenfassung: Die Verwendung von RANGE LEFT in einer gleitenden Partition ist viel einfacher für die Partitionsverwaltung und vermeidet Datenverschiebungen.Conclusion: Using RANGE LEFT in sliding partition is much simpler for the partition management and avoids data movement. Das Definieren der Partitionsgrenzen mit RANGE RIGHT ist jedoch etwas einfacher, da Sie sich nicht um Probleme mit dem datetime-Zeittakt kümmern müssen.However, defining partition boundaries with RANGE RIGHT is slightly simpler as you don't have to deal with datetime time tick issues.

Verwenden des Ansatzes mit einem benutzerdefiniertem BereinigungsskriptUsing custom cleanup script approach

In Fällen, in denen Stretch Database und Tabellenpartitionierung keine geeigneten Optionen sind, besteht der dritte Ansatz darin, die Daten mit einem benutzerdefinierten Bereinigungsskript aus der Verlaufstabelle zu löschen.In cases when the Stretch Database and table partitioning approaches are not viable options, the third approach is to delete the data from history table using a custom cleanup script. Das Löschen von Daten aus einer Verlaufstabelle ist nur möglich, wenn SYSTEM_VERSIONING = OFF gilt.Deleting data from history table is possible only when SYSTEM_VERSIONING = OFF. Um Dateninkonsistenz zu vermeiden, führen Sie die Bereinigung während des Wartungsfensters (wenn Arbeitsauslastungen, bei denen Daten geändert werden, nicht aktiv sind) oder innerhalb einer Transaktion (sodass andere Arbeitsauslastungen blockiert sind) durch.In order to avoid data inconsistency, perform cleanup either during the maintenance window (when workloads that modify data are not active) or within a transaction (effectively blocking other workloads). Dieser Vorgang erfordert die CONTROL -Berechtigung für aktuelle Tabellen und Verlaufstabellen.This operation requires CONTROL permission on current and history tables.

Um reguläre Anwendungen und Benutzerabfragen in möglichst geringem Umfang zu blockieren, löschen Sie Daten in kleineren Blöcken mit einer Verzögerung, wenn Sie das Bereinigungsskript innerhalb einer Transaktion ausführen.To minimally block regular applications and user queries, delete data in smaller chunks with a delay when performing the cleanup script inside a transaction. Es gibt zwar keine optimale Größe für jeden zu löschenden Datenblock für alle Szenarios, aber das Löschen von mehr als 10.000 Zeilen in einer einzigen Transaktion kann erhebliche Auswirkungen haben.While there is no optimal size for each data chunk to be deleted for all scenarios, deleting more than 10,000 rows in a single transaction may impose a significant impact.

Die Bereinigungslogik ist für jede temporale Tabelle identisch, sodass relativ einfach eine Automatisierung über eine generische gespeicherte Prozedur möglich ist. Sie können die regelmäßige Ausführung dieser gespeicherten Prozedur für jede Tabelle planen, für die Sie den Datenverlauf einschränken möchten.The cleanup logic is the same for every temporal table, so it can be automated relatively easily through a generic stored procedure that you schedule to run periodically for every temporal table for which you want to limit data history.

Das folgende Diagramm veranschaulicht, wie Ihre Bereinigungslogik für eine einzelne Tabelle strukturiert sein sollte, um die Auswirkungen auf die aktiven Arbeitsauslastungen zu verringern.The following diagram illustrates how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

Diagramm, das zeigt, wie die Bereinigungslogik für eine einzelne Tabelle strukturiert sein sollte, um die Auswirkungen auf die aktiven Arbeitsauslastungen zu verringernDiagram showing how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

Im Folgenden finden Sie einige allgemeine Richtlinien für die Implementierung des Prozesses.Here are some high-level guidelines for implementing the process. Planen Sie eine tägliche Ausführung der Bereinigungslogik, und wenden Sie sie auf alle temporalen Tabellen an, für die eine Datenbereinigung erforderlich ist.Schedule cleanup logic to run every day and iterate over all temporal tables that need data cleanup. Verwenden Sie den SQL Server-Agent oder ein anderes Tool, um diesen Prozess zu planen:Use SQL Server Agent or different tool to schedule this process:

  • Löschen Sie Verlaufsdaten in allen temporalen Tabellen. Beginnen Sie dabei mit den ältesten Zeilen, und arbeiten Sie sich in mehrere Iterationen in kleinen Blöcken zu den letzten Zeilen vor. Vermeiden Sie wie in der obigen Abbildung veranschaulicht das Löschen aller Zeilen in einer einzigen Transaktion.Delete historical data in every temporal table starting from the oldest to the most recent rows in several iterations in small chunks and avoid deleting all rows in a single transaction as shown on the picture above.
  • Implementieren Sie jede Iteration als Aufruf der generischen gespeicherten Prozedur, die einen Teil der Daten aus der Verlaufstabelle entfernt (diese Prozedur ist im Codebeispiel weiter unten dargestellt).Implement every iteration as an invocation of generic stored procedure that removes a portion of data from the history table (see code example below for this procedure).
  • Berechnen Sie jedes Mal, wenn Sie den Prozess aufrufen, wie viele Zeilen Sie für eine einzelne temporale Tabelle löschen müssen.Calculate how many rows you need to delete for an individual temporal table every time you invoke the process. Basierend darauf und auf der Anzahl der Iterationen müssen Sie dynamische Teilungspunkte für jeden Prozeduraufruf bestimmen.Based on that and number of number of iterations you want to have, determine dynamic split points for every procedure invocation.
  • Planen Sie eine Verzögerung zwischen Iterationen für eine einzelne Tabelle ein, um die Auswirkungen auf die Anwendungen zu reduzieren, die auf die temporale Tabelle zugreifen.Plan to have a period of delay between iterations for a single table to reduce impact on applications that access the temporal table.

Eine gespeicherte Prozedur, die die Daten für eine einzelne temporale Tabelle löscht, kann wie der folgende Codeausschnitt aussehen (prüfen Sie diesen Code sorgfältig, und passen Sie ihn an, bevor Sie ihn in Ihrer Umgebung anwenden):A stored procedure that deletes the data for a single temporal table might look like in the following code snippet (review this code carefully and adjust it before apply in your environment):

DROP PROCEDURE IF EXISTS sp_CleanupHistoryData;
GO

CREATE PROCEDURE sp_CleanupHistoryData
        @temporalTableSchema sysname
      , @temporalTableName sysname
      , @cleanupOlderThanDate datetime2
AS
    DECLARE @disableVersioningScript nvarchar(max) = '';
    DECLARE @deleteHistoryDataScript nvarchar(max) = '';
    DECLARE @enableVersioningScript nvarchar(max) = '';

DECLARE @historyTableName sysname
DECLARE @historyTableSchema sysname
DECLARE @periodColumnName sysname

/*Generate script to discover history table name and end of period column for given temporal table name*/
EXECUTE sp_executesql
    N'SELECT @hst_tbl_nm = t2.name, @hst_sch_nm = s2.name, @period_col_nm = c.name
        FROM sys.tables t1
            JOIN sys.tables t2 on t1.history_table_id = t2.object_id
        JOIN sys.schemas s1 on t1.schema_id = s1.schema_id
        JOIN sys.schemas s2 on t2.schema_id = s2.schema_id
           JOIN sys.periods p on p.object_id = t1.object_id
           JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id
                  WHERE
                 t1.name = @tblName and s1.name = @schName'
                , N'@tblName sysname
                , @schName sysname
                , @hst_tbl_nm sysname OUTPUT
                , @hst_sch_nm sysname OUTPUT
                , @period_col_nm sysname OUTPUT'
                , @tblName = @temporalTableName
                , @schName = @temporalTableSchema
                , @hst_tbl_nm = @historyTableName OUTPUT
                , @hst_sch_nm = @historyTableSchema OUTPUT
                , @period_col_nm = @periodColumnName OUTPUT
  
IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1

/*Generate 3 statements that will run inside a transaction:
  (1) SET SYSTEM_VERSIONING = OFF,
  (2) DELETE FROM history_table,
  (3) SET SYSTEM_VERSIONING = ON
  On SQL Server 2016, it is critical that (1) and (2) run in separate EXEC statements, or SQL Server will generate the following error:
  Msg 13560, Level 16, State 1, Line XXX
  Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.
*/
SET @disableVersioningScript = @disableVersioningScript + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)'
SET @deleteHistoryDataScript = @deleteHistoryDataScript + ' DELETE FROM [' + @historyTableSchema + '].[' + @historyTableName + ']
    WHERE ['+ @periodColumnName + '] < ' + '''' + convert(varchar(128), @cleanupOlderThanDate, 126) + ''''
SET @enableVersioningScript = @enableVersioningScript + ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '

BEGIN TRAN
    EXEC (@disableVersioningScript);
    EXEC (@deleteHistoryDataScript);
    EXEC (@enableVersioningScript);
COMMIT;

Verwenden eines Ansatzes für die Richtlinie zur Beibehaltung temporaler VerlaufsdatenUsing temporal history retention policy approach

Hinweis

Der Ansatz mit der Richtlinie zur Beibehaltung temporaler Verlaufsdaten kann bei Azure SQL-DatenbankAzure SQL Database und SQL Server 2017 ab Version CTP 1.3 verwendet werden.Using the Temporal History Retention Policy approach applies to Azure SQL-DatenbankAzure SQL Database and SQL Server 2017 starting from CTP 1.3.

Die Beibehaltung temporaler Verlaufsdaten kann auf den einzelnen Tabellenebenen konfiguriert werden, sodass Benutzer flexible Ablaufrichtlinien erstellen können.Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. Das Anwenden der temporalen Beibehaltung ist einfach: Sie erfordert nur einen Parameter, der bei der Tabellenerstellung oder einer Schemaänderung festgelegt werden muss.Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

Nachdem Sie Beibehaltungsrichtlinien festgelegt haben, überprüft Azure SQL-Datenbank in regelmäßigen Abständen, ob Zeilen mit Verlaufsdaten existieren, die für die automatische Datenbereinigung infrage kommen.After you define retention policy, Azure SQL Database starts checking regularly if there are historical rows that are eligible for automatic data cleanup. Die Ermittlung übereinstimmender Zeilen und ihre Entfernung aus der Verlaufstabelle erfolgen transparent mithilfe eines vom System geplanten und ausgeführten Hintergrundtasks.Identification of matching rows and their removal from the history table occur transparently, in the background task that is scheduled and run by the system. Die Ablaufbedingungen für die Zeilen der Verlaufstabelle werden basierend auf der Spalte überprüft, die das Ende des SYSTEM_TIME-Zeitraums repräsentiert.Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. Wenn die Beibehaltungsdauer beispielsweise auf sechs Monate festgelegt ist, erfüllen die für die Bereinigung infrage kommenden Zeilen folgende Bedingung:If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

Im vorherigen Beispiel sind wir davon ausgegangen, dass die Spalte „ValidTo“ dem Ende des SYSTEM_TIME-Zeitraums entspricht.In the preceding example, we assumed that ValidTo column corresponds to the end of SYSTEM_TIME period.

Konfigurieren der AufbewahrungsrichtlinieHow to configure retention policy

Bevor Sie die Aufbewahrungsrichtlinie für eine temporale Tabelle konfigurieren, überprüfen Sie zunächst, ob die Beibehaltung temporaler Verlaufsdaten auf Datenbankebene aktiviert ist:Before you configure retention policy for a temporal table, check first whether temporal historical retention is enabled at the database level:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Das Datenbankflag Is_temporal_history_retention_enabled ist standardmäßig auf „ON“ festgelegt, aber Benutzer können dies mit der Anweisung ALTER DATABASE ändern.Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. Es wird ebenfalls nach einer Point-In-Time-Wiederherstellung auf OFF festgelegt.It is also automatically set to OFF after point in time restore operation. Um die Bereinigung der Beibehaltung temporaler Verlaufsdaten in Ihrer Datenbank zu aktivieren, führen Sie folgende Anweisung aus:To enable temporal history retention cleanup for your database, execute the following statement:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION ON

Die Beibehaltungsrichtlinien werden während der Tabellenerstellung konfiguriert, indem ein Wert für den Parameter HISTORY_RETENTION_PERIOD angegeben wird:Retention policy is configured during table creation by specifying value for the HISTORY_RETENTION_PERIOD parameter:

CREATE TABLE dbo.WebsiteUserInfo
(
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
 WITH
 (
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
    )
 );

Sie können den Beibehaltungszeitraum mithilfe verschiedener Zeiteinheiten angeben: DAYS, WEEKS, MONTHS und YEARS.You can specify retention period by using different time units: DAYS, WEEKS, MONTHS, and YEARS. Wenn HISTORY_RETENTION_PERIOD weggelassen wird, wird von einer unbegrenzten (INFINITE) Beibehaltung ausgegangen.If HISTORY_RETENTION_PERIOD is omitted, INFINITE retention is assumed. Sie können das Schlüsselwort INFINITE auch explizit verwenden.You can also use INFINITE keyword explicitly. In manchen Szenarios sollten Sie die Beibehaltung erst nach der Tabellenerstellung konfigurieren oder den zuvor konfigurierten Wert ändern.In some scenarios, you may want to configure retention after table creation, or to change previously configured value. Verwenden Sie für diesen Fall die Anweisung ALTER TABLE:In that case use ALTER TABLE statement:

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Zum Prüfen des aktuellen Status der Beibehaltungsrichtlinie verwenden Sie die folgende Abfrage, die das Flag für die Aktivierung der temporalen Beibehaltung auf Datenbankebene mit den Beibehaltungszeiträumen für die einzelnen Tabellen verknüpft:To review current state of the retention policy, use the following query that joins temporal retention enablement flag at the database level with retention periods for individual tables:

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName, SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

Wie löscht SQL-Datenbank veraltete Zeilen?How SQL Database deletes aged rows

Der Bereinigungsprozess hängt vom Indexlayout der Verlaufstabelle ab.The cleanup process depends on the index layout of the history table. Zu beachten ist, dass eine Richtlinie für die begrenzte Beibehaltung nur für Verlaufstabellen mit einem gruppierten Index (B-Struktur oder Columnstore) konfiguriert werden können.It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured. Es wird ein Hintergrundtask erstellt, um die Bereinigung veralteter Daten für alle temporalen Tabellen mit begrenztem Beibehaltungszeitraum auszuführen.A background task is created to perform aged data cleanup for all temporal tables with finite retention period. Die Bereinigungslogik für den gruppierten Index für Rowstore (B-Struktur) löscht die veralteten Zeilen in kleineren Blöcken (max. 10K), was die Belastung des Datenbankprotokolls und des E/A-Subsystems minimiert.Cleanup logic for the rowstore (B-tree) clustered index deletes aged rows in smaller chunks (up to 10K) minimizing pressure on database log and I/O subsystem. Die Bereinigungslogik verwendet zwar den benötigten B-Strukturindex, jedoch kann die Löschreihenfolge der Zeilen, die den Beibehaltungszeitraum übersteigen, nicht sicher garantiert werden.Although cleanup logic utilizes required B-tree index, order of deletions for the rows older than retention period cannot be firmly guaranteed. Verwenden Sie in Ihren Anwendungen daher keine Abhängigkeit von der Bereinigungsreihenfolge.Hence, do not take any dependency on the cleanup order in your applications.

Der Bereinigungstask für den gruppierten Columnstore entfernt ganze Zeilengruppen auf einmal (die üblicherweise jeweils 1 Mio. Reihen enthalten). Dies ist sehr effizient, vor allem wenn Verlaufsdaten mit einer hohen Geschwindigkeit generiert werden.The cleanup task for the clustered columnstore removes entire row groups at once (typically contain 1 million of rows each), which is very efficient, especially when historical data is generated at a high pace.

Beibehaltung des gruppierten ColumnstoreClustered columnstore retention

Die hervorragende Datenkompression und die effiziente Beibehaltungsbereinigung machen den gruppierten Columnstore-Index zur perfekten Lösung für Szenarios, bei denen Ihre Workload in kürzester Zeit eine große Menge an Verlaufsdaten generiert.Excellent data compression and efficient retention cleanup makes clustered columnstore index a perfect choice for scenarios when your workload rapidly generates high amount of historical data. Dieses Muster ist typisch für intensive Transaktionsverarbeitungs-Workloads, die temporale Tabellen verwenden, um die Änderungsnachverfolgung und -überwachung, Trendanalysen oder die Erfassung von IoT-Daten durchzuführen.That pattern is typical for intensive transactional processing workloads that use temporal tables for change tracking and auditing, trend analysis, or IoT data ingestion.

Weitere Details finden Sie unter: Verwalten von Verlaufsdaten in temporalen Tabellen mit Beibehaltungsrichtlinien.Please check Manage historical data in Temporal Tables with retention policy for more details.

Nächste SchritteNext steps