Edit

Migrate data from regular tables to ledger tables

  • Article

Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

Converting regular tables to ledger tables isn't possible, but you can migrate the data from an existing regular table to a ledger table, and then replace the original table with the ledger table.

When you're performing a database ledger verification, the process needs to order all operations within each transaction. If you use a SELECT INTO or BULK INSERT statement to copy a few billion rows from a regular table to a ledger table, it will all be done in one single transaction. This means lots of data needs to be fully sorted, which will be done in a single thread. The sorting operation takes a long time to complete.

To convert a regular table into a ledger table, Microsoft recommends using the sys.sp_copy_data_in_batches stored procedure. This splits the copy operation in batches of 10-100 K rows per transaction. As a result, the database ledger verification has smaller transactions that can be sorted in parallel. This helps the time of the database ledger verification tremendously.

Note

The customer can still use other commands, services, or tools to copy the data from the source table to the target table. Make sure you avoid large transactions because this will have a performance impact on the database ledger verification.

This article shows you how can convert a regular table into a ledger table.

Prerequisites

Create an append-only or updatable ledger table

Before you can use the sys.sp_copy_data_in_batches stored procedure, you need to create an append-only ledger table or updatable ledger table with the same schema as the source table. The schema should be identical in terms of number of columns, column names, and their data types. TRANSACTION ID, SEQUENCE NUMBER, and GENERATED ALWAYS columns are ignored since they're system generated. Indexes between the tables can be different but the target table can only be a Heap table or have a clustered index. Non-clustered indexes should be created afterwards.

Assume we have the following regular Employees table in the database.

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
	);

The easiest way to create an append-only ledger table or updatable ledger table is scripting the original table and add the LEDGER = ON clause. In the script below, we're creating a new updatable ledger table, called Employees_LedgerTable based on the schema of the Employees table.

	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
    );

Copy data from a regular table to a ledger table

The stored procedure sys.sp_copy_data_in_batches copies data from the source table to the target table after verifying that their schema is identical. The data is copied in batches in individual transactions. If the operation fails, the target table is partially populated. The target table should also be empty.

In the script below, we're copying the data from the regular Employees table to the new updatable ledger table, Employees_LedgerTable.

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

Related content