Edit

Share via

Start and stop SQL database mirroring with the Fabric REST API

Applies to: ✅ SQL database in Microsoft Fabric

You can use the Fabric REST API to start and stop mirroring from a SQL database to the OneLake in Fabric. This article and sample script demonstrate how to use PowerShell to call the Fabric REST API start or stop mirroring.

SQL database mirroring to the OneLake is always running, by default. There are scenarios where mirroring for SQL database in Fabric might need to be stopped. For example, to enable creation clustered column indexes on an existing table, which cannot be created when mirroring is running.

Prerequisites

Stop mirroring of SQL database to OneLake in Fabric

The following PowerShell examples stop mirroring of a SQL database to the OneLake in Fabric.

This example script uses Connect-AzAccount, an alias of az login to prompt for credentials. It uses those credentials to obtain an access token to use for the REST API calls. SQLCMD uses the context of the account that was given to Connect-AzAccount.

In the following script, you need to provide the workspace ID and database ID. Both can be found in the URL. https://powerbi.com/groups/<fabric_workspace_id>/sqldatabases/<fabric_sql_database_id>. The first string in the URL is the Fabric workspace ID, and the second string is the SQL database ID.

  • Replace <your workspace id> with your Fabric workspace ID. You can find the ID of a workspace easily in the URL, it's the unique string inside two / characters after /groups/ in your browser window.
  • Replace <your database id> with your SQL database in Fabric database ID. You can find the ID of the database item easily in the URL, it's the unique string inside two / characters after /sqldatabases/ in your browser window.

This script demonstrates:

  1. Retrieve an access token using Get-AzAccessToken and convert it from a secure string. If using PowerShell 7, ConvertFrom-SecureString is also an option.
  2. Assemble API call.
  3. Invoke API call.
Import-Module Az.Accounts

az login

$workspaceid = '<your workspace id>' # Find in the URL
$databaseid = '<your database id>' # Find in the URL

$headers = $null

# 1. Get the access token and add it to the headers

$access_token = (Get-AzAccessToken -AsSecureString -ResourceUrl https://api.fabric.microsoft.com)

$ssPtr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($access_token.Token)

try {
$headers = @{ 
       Authorization = $access_token.Type + ' ' + ([System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($ssPtr))
    }

$access_token.UserId -match('^[^@]+') | Out-Null

$stopMirroringUri = "https://api.fabric.microsoft.com/v1/workspaces/$workspaceid/sqlDatabases/$databaseid/stopMirroring"

$parameters = @{
        Method="Post"
        Headers=$headers
        Uri = $stopMirroringUri
    }

Invoke-RestMethod @parameters -ErrorAction Stop

 } finally {
    # The following lines ensure that sensitive data is not left in memory.
    $headers = [System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($ssPtr)
}

Start mirroring of SQL database to OneLake in Fabric

The following PowerShell examples start mirroring of a SQL database to the OneLake in Fabric.

This example script uses Connect-AzAccount, an alias of az login to prompt for credentials. It uses those credentials to obtain an access token to use for the REST API calls. SQLCMD uses the context of the account that was given to Connect-AzAccount.

In the following script, replace <your workspace id> with your Fabric workspace ID. You can find the ID of a workspace easily in the URL, it's the unique string inside two / characters after /groups/ in your browser window. For example, 11aa111-a11a-1111-1abc-aa1111aaaa in https://fabric.microsoft.com/groups/11aa111-a11a-1111-1abc-aa1111aaaa/.

This script demonstrates:

  1. Retrieve an access token using Get-AzAccessToken and convert it from a secure string. If using PowerShell 7, ConvertFrom-SecureString is also an option.
  2. Assemble API call.
  3. Invoke API call.
Import-Module Az.Accounts

az login

$workspaceid = '<your workspace id>' # Find in the URL
$databaseid = '<your database id>' # Find in the URL

$headers = $null

# 1. Get the access token and add it to the headers

$access_token = (Get-AzAccessToken -AsSecureString -ResourceUrl https://api.fabric.microsoft.com)

$ssPtr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($access_token.Token)

try {
$headers = @{ 
       Authorization = $access_token.Type + ' ' + ([System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($ssPtr))
    }

$access_token.UserId -match('^[^@]+') | Out-Null

$startMirroringUri = "https://api.fabric.microsoft.com/v1/workspaces/$workspaceid/sqlDatabases/$databaseid/startMirroring"

$parameters = @{
        Method="Post"
        Headers=$headers
        Uri = $startMirroringUri
    }

Invoke-RestMethod @parameters -ErrorAction Stop

 } finally {
    # The following lines ensure that sensitive data is not left in memory.
    $headers = [System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($ssPtr)
}

Related content

  • Last updated on