Skip to content

Latest commit

 

History

History
199 lines (137 loc) · 8 KB

ledger-how-to-configure-ledger-database.md

File metadata and controls

199 lines (137 loc) · 8 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.custom ms.topic zone_pivot_groups monikerRange
Configure a ledger database
This article discusses how to configure a ledger database in Azure SQL Database and SQL Server 2022
VanMSFT
vanto
mathoma
11/14/2023
sql
security
devx-track-azurecli, ignite-2023
how-to
as1-azuresql-sql
= azuresqldb-current||>= sql-server-ver16||>= sql-server-linux-ver16||=azuresqldb-mi-current

Configure a ledger database

[!INCLUDE SQL Server 2022 Azure SQL Database Azure SQL Managed Instance]

::: zone pivot="as1-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 [!INCLUDE sssql22-md] or Azure SQL Managed Instance, use the switch at the top of this page.

Prerequisites

Enable ledger database

Note

Enabling the ledger functionality at the database level will make all tables in this database updatable ledger tables. This option cannot be changed after the database is created. Creating a table with the option LEDGER = OFF will throw an error message.

Portal

  1. Open the Azure portal and create an Azure SQL Database .

  2. On the Security tab, select Configure ledger.

    :::image type="content" source="media/ledger/ledger-portal-manage-ledger.png" alt-text="Screenshot that shows the Azure portal with the Security Ledger tab selected.":::

  3. On the Configure ledger pane, select Enable for all future tables in this database.

    :::image type="content" source="media/ledger/enable-ledger-database.png" alt-text="Screenshot that shows the selection for enabling a ledger 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), Azure Data Studio or SQL Server Data Tools (SSDT) in Visual Studio and 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

::: zone-end

::: zone pivot="as1-azure-sql-managed-instance"

This article provides information on configuring a ledger database using T-SQL, PowerShell, or the Azure CLI for Azure SQL Managed Instance. For information on creating a ledger database in [!INCLUDE sssql22-md] or Azure SQL Database, use the switch at the top of this page.

Prerequisites

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

Enable ledger database

Note

Enabling the ledger functionality at the database level will make all tables in this database updatable ledger tables. This option cannot be changed after the database is created. Creating a table with the option LEDGER = OFF will throw an error message.

T-SQL

Enable ledger database using T-SQL

  1. Sign into your managed instance using SQL Server Management Studio (SSMS), Azure Data Studio or SQL Server Data Tools (SSDT).

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

PowerShell

Enable ledger database using PowerShell

Create a single ledger database with the New-AzSqlInstanceDatabase cmdlet. The following example creates a ledger database on a specified instance. The parameter -EnableLedger creates the ledger database. Make sure you modify the parameters ResourceGroupName, InstanceName and Name.

Write-host "Creating a ledger database..."
$database = New-AzSqlInstanceDatabase -ResourceGroupName "ResourceGroup01" `
    -InstanceName  "ManagedInstance1" `
    -Name "Database01" `
    -EnableLedger
$database

Azure CLI

Enable ledger database using the Azure CLI

Create a ledger database with the az sql midb create command. The below example creates a ledger database on a specified instance. Make sure you modify the parameters resource-group, managed-instance and name.

az sql midb create \
    --resource-group ResourceGroup01 \
    --managed-instance Server01 \
    --name Database01 \
    --ledger-on

::: zone-end

::: zone pivot="as1-sql-server"

This article provides information on creating a ledger database by using T-SQL in [!INCLUDE sssql22-md]. For information on creating a ledger database in Azure SQL Database or Azure SQL Managed Instance, use the switch at the top of this page.

Prerequisites

Create a ledger database using T-SQL

  1. Sign into your [!INCLUDE sssql22-md] instance using SSMS, Azure Data Studio or SSDT.

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

::: zone-end

Related content