Configure a ledger database

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

This article provides information on configuring a ledger database using the Azure portal, T-SQL, PowerShell, or the Azure CLI for Azure SQL Database. For information on creating a ledger database in SQL Server 2022, use the switch at the top of this page to toggle over to SQL Server.

Prerequisites

• Have an active Azure subscription. If you don't have one, create a free account.
• An Azure SQL Database.

Enable ledger database

Portal

1. Open the Azure portal and create an Azure SQL Database if you don't already have one.

2. In the Security tab, select the Ledger option.

   

3. In the Ledger pane, select Enable for all future tables in this database.

   

4. Select Apply to save this setting.

T-SQL

Enable ledger database using T-SQL

Open a query editor like SQL Server Management Studio (SSMS) or Azure Data Studioand connect to your logical SQL Server. The below example creates a General Purpose database. The WITH LEDGER=ON clause will create the ledger database.

CREATE DATABASE Database01
	(
	  EDITION = 'GeneralPurpose',
	  SERVICE_OBJECTIVE='GP_Gen5_2',
	  MAXSIZE = 2 GB
	)
	WITH LEDGER = ON;
GO 

PowerShell

Enable ledger database using PowerShell

Create a single ledger database with the New-AzSqlDatabase cmdlet. The below example creates a serverless database. The parameter -EnableLedger will create the ledger database. Note: Make sure you modify the parameters ServerName and DatabaseName

Write-host "Creating a gen5 2 vCore serverless ledger database..."
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName "Server01" `
    -DatabaseName "Database01" `
    -Edition GeneralPurpose `
    -ComputeModel Serverless `
    -ComputeGeneration Gen5 `
    -VCore 2 `
    -MinimumCapacity 2 `
    -EnableLedger
$database

Azure CLI

Enable ledger database using the Azure CLI

Create a ledger database with the az sql db create command. The following command creates a serverless database with ledger enabled. Note: Make sure you modify the parameters resource-group, server and name

az sql db create \
    --resource-group ResourceGroup01 \
    --server Server01 \
    --name Database01 \
    --edition GeneralPurpose \
    --family Gen5 \
    --capacity 2 \
    --compute-model Serverless \
    --ledger-on

This article provides information on creating a ledger database using T-SQL in SQL Server 2022. For information on creating a ledger database in Azure SQL Database, use the switch at the top of this page to toggle over to Azure SQL Database.

Prerequisites

• SQL Server 2022
• SQL Server Management Studio (SSMS) or Azure Data Studio

Create a ledger database using T-SQL

1. Sign into your SQL Server 2022 instance using SSMS or Azure Data Studio.

2. Create a ledger database using the following T-SQL statement:

   CREATE DATABASE MyLedgerDB  WITH LEDGER = ON;
   

For more information, see CREATE DATABASE (Transact-SQL).

Next steps

• Ledger overview
• Append-only ledger tables
• Updatable ledger tables
• Enable automatic digest storage