Partager via

Migrer des données de tables régulières vers des tables de registre

  • Article

S’applique à : SQL Server 2022 (16.x)base de données Azure SQL Azure SQL Managed Instance

La conversion de tables régulières en tables de registre n’est pas possible, mais vous pouvez migrer les données d’une table régulière existante vers une table de registre, puis remplacer la table d’origine par la table de registre.

Lorsque vous effectuez une vérification du registre de base de données, le processus doit commander toutes les opérations au sein de chaque transaction. Si vous utilisez une instruction SELECT INTO ou BULK INSERT pour copier quelques milliards de lignes d’une table régulière vers une table de registre, elle sera effectuée en une seule transaction. Cela signifie qu’un grand nombre de données doivent être entièrement triées et dans un seul thread. L’opération de tri prend beaucoup de temps.

Pour convertir une table régulière en table de registre, Microsoft recommande d’utiliser la procédure stockée sys.sp_copy_data_in_batches. Cette option permet de diviser l’opération de copie en lots de 10 à 100 000 lignes par transaction. Par conséquent, la vérification du registre de la base de données comporte des transactions plus petites qui peuvent être triées en parallèle. Cela augmente énormément le temps de la vérification du registre de base de données.

Remarque

Le client peut toujours utiliser d’autres commandes, services ou outils pour copier les données de la table source vers la table cible. Veillez à éviter les transactions volumineuses, car cela aura un impact sur les performances de la vérification du registre de base de données.

Cet article vous montre comment convertir une table régulière en table de registre.

Prérequis

Créer une table de registre d’ajout uniquement ou pouvant être mise à jour

Avant de pouvoir utiliser la procédure stockée sys.sp_copy_data_in_batches, vous devez créer une table de registre d’ajout uniquement ou une table de registre pouvant être mise à jour avec le même schéma que la table source. Le schéma doit être identique en termes de nombre de colonnes, de noms de colonnes et de types de données. Les colonnes TRANSACTION ID, SEQUENCE NUMBER et GENERATED ALWAYS sont ignorées car elles sont générées par le système. Les index entre les tables peuvent être différents, mais la table cible ne peut être qu’une table de segment de mémoire ou avoir un index cluster. Les index non cluster doivent être créés ultérieurement.

Supposons que nous avons la table régulière Employees suivante dans la base de données.

CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	[SSN] [char](11) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Salary] [money] NOT NULL
	);

Le moyen le plus simple de créer une table de registre d’ajout uniquement ou une table de registre pouvant être mise à jour consiste à scripter la table d’origine et à ajouter la clause LEDGER = ON. Dans le script ci-dessous, nous créons une table de registre pouvant être mise à jour, appelée Employees_LedgerTable en fonction du schéma de la table Employees.

	CREATE TABLE [dbo].[Employees_LedgerTable](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	[SSN] [char](11) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Salary] [money] NOT NULL
	)
    WITH 
    (
      SYSTEM_VERSIONING = ON,
      LEDGER = ON
    );

Copier des données d’une table régulière vers une table de registre

La procédure stockée sys.sp_copy_data_in_batches copie les données de la table source vers la table cible après avoir vérifié que leur schéma est identique. Les données sont copiées par lots dans des transactions individuelles. Si l’opération échoue, la table cible est partiellement remplie. La table cible doit également être vide.

Dans le script ci-dessous, nous copions les données de la table régulière Employees dans la nouvelle table de registre pouvant être mise à jour, Employees_LedgerTable.

sp_copy_data_in_batches @source_table_name = N'Employees' , @target_table_name = N'Employees_LedgerTable'

Contenu connexe