Použití PowerShellu k synchronizaci dat mezi SQL Database a SQL Serverem

Článek
09/27/2024

Důležité

Synchronizace dat SQL budou vyřazeny 30. září 2027. Zvažte migraci na alternativní řešení replikace nebo synchronizace dat.

Tento příklad Azure PowerShellu konfiguruje Synchronizace dat pro synchronizaci dat mezi Azure SQL Database a SQL Serverem.

Pokud ještě nemáte předplatné Azure, vytvořte si bezplatný účet Azure před tím, než začnete.

Poznámka:

Tento článek používá modul Azure Az PowerShell, což je doporučený modul PowerShellu pro interakci s Azure. Pokud chcete začít s modulem Az PowerShell, projděte si téma věnované instalaci Azure PowerShellu. Informace o tom, jak migrovat na modul Az PowerShell, najdete v tématu Migrace Azure PowerShellu z AzureRM na Az.

Použití služby Azure Cloud Shell

Azure hostí interaktivní prostředí Azure Cloud Shell, které můžete používat v prohlížeči. Pro práci se službami Azure můžete v prostředí Cloud Shell použít buď Bash, nebo PowerShell. Předinstalované příkazy Cloud Shellu můžete použít ke spuštění kódu v tomto článku, aniž byste museli instalovat cokoli do místního prostředí.

Spuštění služby Azure Cloud Shell:

Možnost	Příklad nebo odkaz
Zvolte Vyzkoušet v pravém horním rohu bloku kódu. Výběr Vyzkoušet automaticky nekopíruje kód do služby Cloud Shell.
Přejděte na adresu https://shell.azure.com nebo výběrem tlačítka Spustit Cloud Shell otevřete Cloud Shell v prohlížeči.
Zvolte tlačítko Cloud Shell v pruhu nabídky v pravém horním rohu webu Azure Portal.

Pokud chcete spustit kód uvedený v tomto článku ve službě Azure Cloud Shell, postupujte takto:

Spusťte Cloud Shell.
Vyberte tlačítko Kopírovat na bloku kódu a kód zkopírujte.
Vložte kód do relace Cloud Shellu tak, že ve Windows a Linuxu vyberete Ctrl+Shift+V nebo vyberete Cmd+Shift+V v macOS.
Spusťte kód stisknutím klávesy Enter.

Pokud se rozhodnete nainstalovat a používat PowerShell místně, tento kurz vyžaduje Az PowerShell 1.4.0 nebo novější. Pokud potřebujete upgrade, přečtěte si téma Instalace modulu Azure PowerShell. Pokud používáte PowerShell místně, je také potřeba spustit příkaz Connect-AzAccount pro vytvoření připojení k Azure.

Přehled Synchronizace dat SQL najdete v tématu Co je Synchronizace dat SQL pro Azure?

Synchronizace dat SQL nepodporuje službu Azure SQL Managed Instance ani Azure Synapse Analytics.

Požadavky

Vytvořte databázi ve službě Azure SQL Database z ukázkové databáze AdventureWorksLT jako databázi centra.
Vytvořte databázi ve službě Azure SQL Database ve stejné oblasti jako synchronizační databáze.
Vytvořte databázi v instanci SQL Serveru jako členovou databázi.
Před spuštěním příkladu aktualizujte zástupné symboly parametrů.

Příklady

using namespace Microsoft.Azure.Commands.Sql.DataSync.Model
using namespace System.Collections.Generic

# hub database info
$subscriptionId = "<subscriptionId>"
$resourceGroupName = "<resourceGroupName>"
$serverName = "<serverName>"
$databaseName = "<databaseName>"

# sync database info
$syncDatabaseResourceGroupName = "<syncResourceGroupName>"
$syncDatabaseServerName = "<syncServerName>"
$syncDatabaseName = "<syncDatabaseName>"

# sync group info
$syncGroupName = "<syncGroupName>"
$conflictResolutionPolicy = "HubWin" # can be HubWin or MemberWin
$intervalInSeconds = 300 # sync interval in seconds (must be no less than 300)

# member database info
$syncMemberName = "<syncMemberName>"
$memberServerName = "<memberServerName>"
$memberDatabaseName = "<memberDatabaseName>"
$memberDatabaseType = "SqlServerDatabase" # can be AzureSqlDatabase or SqlServerDatabase
$syncDirection = "Bidirectional" # can be Bidirectional, Onewaymembertohub, Onewayhubtomember

# sync agent info
$syncAgentName = "<agentName>"
$syncAgentResourceGroupName = "<syncAgentResourceGroupName>"
$syncAgentServerName = "<syncAgentServerName>"

# temp file to save the sync schema
$tempFile = $env:TEMP+"\syncSchema.json"

# list of included columns and tables in quoted name
$includedColumnsAndTables =  "[SalesLT].[Address].[AddressID]",
                             "[SalesLT].[Address].[AddressLine2]",
                             "[SalesLT].[Address].[rowguid]",
                             "[SalesLT].[Address].[PostalCode]",
                             "[SalesLT].[ProductDescription]"
$metadataList = [System.Collections.ArrayList]::new($includedColumnsAndTables)

Connect-AzAccount
Select-AzSubscription -SubscriptionId $subscriptionId

# use if it's safe to show password in script, otherwise use PromptForCredential
# $user = "username"
# $password = ConvertTo-SecureString -String "password" -AsPlainText -Force
# $credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $user, $password

$credential = $Host.ui.PromptForCredential("Need credential",
              "Please enter your user name and password for server "+$serverName+".database.windows.net",
              "",
              "")

# create a new sync agent
Write-Host "Creating new Sync Agent..."
New-AzSqlSyncAgent -ResourceGroupName $resourceGroupName -ServerName  $serverName -SyncDatabaseName $syncDatabaseName -SyncAgentName $syncAgentName

# generate agent key
Write-Host "Generating Agent Key..."
$agentKey = New-AzSqlSyncAgentKey -ResourceGroupName $resourceGroupName -ServerName $serverName -SyncAgentName $syncAgentName
Write-Host "Use your agent key to configure the sync agent. Do this before proceeding."
$agentkey

# DO THE FOLLOWING BEFORE THE NEXT STEP
# Install the on-premises sync agent on your machine and register the sync agent using the agent key generated above to bring the sync agent online.
# Add the SQL Server database information including server name, database name, user name, password on the configuration tool within the sync agent.  

# create a new sync group
Write-Host "Creating Sync Group "$syncGroupName"..."
New-AzSqlSyncGroup -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -Name $syncGroupName `
    -SyncDatabaseName $syncDatabaseName -SyncDatabaseServerName $syncDatabaseServerName -SyncDatabaseResourceGroupName $syncDatabaseResourceGroupName `
    -ConflictResolutionPolicy $conflictResolutionPolicy -DatabaseCredential $credential

# use if it's safe to show password in script, otherwise use PromptForCredential
#$user = "username"
#$password = ConvertTo-SecureString -String "password" -AsPlainText -Force
#$credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $user, $password

$credential = $Host.ui.PromptForCredential("Need credential",
              "Please enter your user name and password for server "+$memberServerName,
              "",
              "")

# get information from sync agent and confirm your SQL Server instance was configured (note the database ID to use for the sqlServerDatabaseID in the next step)
$syncAgentInfo = Get-AzSqlSyncAgentLinkedDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -SyncAgentName $syncAgentName

# add a new sync member
Write-Host "Adding member"$syncMemberName" to the sync group..."

New-AzSqlSyncMember -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName `
    -SyncGroupName $syncGroupName -Name $syncMemberName -MemberDatabaseType $memberDatabaseType -SyncAgentResourceGroupName $syncAgentResourceGroupName `
    -SyncAgentServerName $syncAgentServerName -SyncAgentName $syncAgentName  -SyncDirection $syncDirection -SqlServerDatabaseID  $syncAgentInfo.DatabaseId

# refresh database schema from hub database, specify the -SyncMemberName parameter if you want to refresh schema from the member database
Write-Host "Refreshing database schema from hub database..."
$startTime = Get-Date
Update-AzSqlSyncSchema -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -SyncGroupName $syncGroupName

# waiting for successful refresh
$startTime = $startTime.ToUniversalTime()
$timer=0
$timeout=90

# check the log and see if refresh has gone through
Write-Host "Check for successful refresh..."
$isSucceeded = $false
while ($isSucceeded -eq $false) {
    Start-Sleep -s 10
    $timer=$timer+10
    $details = Get-AzSqlSyncSchema -SyncGroupName $syncGroupName -ServerName $serverName -DatabaseName $databaseName -ResourceGroupName $resourceGroupName
    if ($details.LastUpdateTime -gt $startTime) {
        Write-Host "Refresh was successful"
        $isSucceeded = $true
    }
    if ($timer -eq $timeout) {
        Write-Host "Refresh timed out"
        break;
    }
}

# get the database schema
Write-Host "Adding tables and columns to the sync schema..."
$databaseSchema = Get-AzSqlSyncSchema -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
    -DatabaseName $DatabaseName -SyncGroupName $SyncGroupName `

$databaseSchema | ConvertTo-Json -depth 5 -Compress | Out-File "C:\Users\OnPremiseServer\AppData\Local\Temp\syncSchema.json"

$newSchema = [AzureSqlSyncGroupSchemaModel]::new()
$newSchema.Tables = [List[AzureSqlSyncGroupSchemaTableModel]]::new();

# add columns and tables to the sync schema
foreach ($tableSchema in $databaseSchema.Tables) {
    $newTableSchema = [AzureSqlSyncGroupSchemaTableModel]::new()
    $newTableSchema.QuotedName = $tableSchema.QuotedName
    $newTableSchema.Columns = [List[AzureSqlSyncGroupSchemaColumnModel]]::new();
    $addAllColumns = $false
    if ($MetadataList.Contains($tableSchema.QuotedName)) {
        if ($tableSchema.HasError) {
            $fullTableName = $tableSchema.QuotedName
            Write-Host "Can't add table $fullTableName to the sync schema" -foregroundcolor "Red"
            Write-Host $tableSchema.ErrorId -foregroundcolor "Red"
            continue;
        }
        else {
            $addAllColumns = $true
        }
    }
    foreach($columnSchema in $tableSchema.Columns) {
        $fullColumnName = $tableSchema.QuotedName + "." + $columnSchema.QuotedName
        if ($addAllColumns -or $MetadataList.Contains($fullColumnName)) {
            if ((-not $addAllColumns) -and $tableSchema.HasError) {
                Write-Host "Can't add column $fullColumnName to the sync schema" -foregroundcolor "Red"
                Write-Host $tableSchema.ErrorId -foregroundcolor "Red"
            }
            elseif ((-not $addAllColumns) -and $columnSchema.HasError) {
                Write-Host "Can't add column $fullColumnName to the sync schema" -foregroundcolor "Red"
                Write-Host $columnSchema.ErrorId -foregroundcolor "Red"
            }
            else {
                Write-Host "Adding"$fullColumnName" to the sync schema"
                $newColumnSchema = [AzureSqlSyncGroupSchemaColumnModel]::new()
                $newColumnSchema.QuotedName = $columnSchema.QuotedName
                $newColumnSchema.DataSize = $columnSchema.DataSize
                $newColumnSchema.DataType = $columnSchema.DataType
                $newTableSchema.Columns.Add($newColumnSchema)
            }
        }
    }
    if ($newTableSchema.Columns.Count -gt 0) {
        $newSchema.Tables.Add($newTableSchema)
    }
}

# convert sync schema to JSON format
$schemaString = $newSchema | ConvertTo-Json -depth 5 -Compress

# workaround a powershell bug
$schemaString = $schemaString.Replace('"Tables"', '"tables"').Replace('"Columns"', '"columns"').Replace('"QuotedName"', '"quotedName"').Replace('"MasterSyncMemberName"','"masterSyncMemberName"')

# save the sync schema to a temp file
$schemaString | Out-File $tempFile

# update sync schema
Write-Host "Updating the sync schema..."
Update-AzSqlSyncGroup -ResourceGroupName $resourceGroupName -ServerName $serverName `
    -DatabaseName $databaseName -Name $syncGroupName -Schema $tempFile

$syncLogStartTime = Get-Date

# trigger sync manually
Write-Host "Trigger sync manually..."
Start-AzSqlSyncGroupSync -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -SyncGroupName $syncGroupName

# check the sync log and wait until the first sync succeeded
Write-Host "Check the sync log..."
$isSucceeded = $false
for ($i = 0; ($i -lt 300) -and (-not $isSucceeded); $i = $i + 10) {
    Start-Sleep -s 10
    $syncLogEndTime = Get-Date
    $syncLogList = Get-AzSqlSyncGroupLog -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName `
        -SyncGroupName $syncGroupName -StartTime $syncLogStartTime.ToUniversalTime() -EndTime $syncLogEndTime.ToUniversalTime()

    if ($synclogList.Length -gt 0) {
        foreach ($syncLog in $syncLogList) {
            if ($syncLog.Details.Contains("Sync completed successfully")) {
                Write-Host $syncLog.TimeStamp : $syncLog.Details
                $isSucceeded = $true
            }
        }
    }
}

if ($isSucceeded) {
    # enable scheduled sync
    Write-Host "Enable the scheduled sync with 300 seconds interval..."
    Update-AzSqlSyncGroup  -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName `
        -Name $syncGroupName -IntervalInSeconds $intervalInSeconds
}
else {
    # output all log if sync doesn't succeed in 300 seconds
    $syncLogEndTime = Get-Date
    $syncLogList = Get-AzSqlSyncGroupLog  -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName `
        -SyncGroupName $syncGroupName -StartTime $syncLogStartTime.ToUniversalTime() -EndTime $syncLogEndTime.ToUniversalTime()

    if ($synclogList.Length -gt 0) {
        foreach ($syncLog in $syncLogList) {
            Write-Host $syncLog.TimeStamp : $syncLog.Details
        }
    }
}

Vyčištění nasazení

Po spuštění ukázkového skriptu můžete spuštěním následujícího příkazu odebrat skupinu prostředků a všechny k ní přidružené prostředky.

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
Remove-AzResourceGroup -ResourceGroupName $syncDatabaseResourceGroupName

Vysvětlení skriptu

Tento skript používá následující příkazy. Každý příkaz v tabulce odkazuje na příslušnou část dokumentace.

Příkaz	Notes
New-AzSqlSyncAgent	Vytvoří nového synchronizačního agenta.
New-AzSqlSyncAgentKey	Vygeneruje klíč agenta přidružený k agentu synchronizace.
Get-AzSqlSyncAgentLinkedDatabase	Získejte všechny informace o agentu synchronizace.
New-AzSqlSyncMember	Přidejte nového člena do skupiny synchronizace.
Update-AzSqlSyncSchema	Aktualizuje informace o schématu databáze.
Get-AzSqlSyncSchema	Získejte informace o schématu databáze.
Update-AzSqlSyncGroup	Aktualizuje skupinu synchronizace.
Start-AzSqlSyncGroupSync	Aktivuje synchronizaci.
Get-AzSqlSyncGroupLog	Zkontroluje protokol synchronizace.

Další informace o Azure PowerShellu najdete v dokumentaci k Azure PowerShellu.

Další ukázkové skripty PowerShellu pro službu SQL Database najdete v tématu Skripty PowerShellu pro službu Azure SQL Database.

Další informace o Synchronizace dat SQL najdete tady:

Přehled – Synchronizace dat napříč několika cloudovými a místními databázemi pomocí Synchronizace dat SQL Azure
Nastavení Synchronizace dat
- Použití webu Azure Portal – kurz: Nastavení Synchronizace dat SQL pro synchronizaci dat mezi databází ve službě Azure SQL Database a místní databází SQL Serveru
- Použití PowerShellu – Použití PowerShellu k synchronizaci mezi několika databázemi ve službě Azure SQL Database
Synchronizace dat Agent – Synchronizace dat Agent pro Synchronizace dat SQL v Azure
Osvědčené postupy – Osvědčené postupy pro Synchronizace dat SQL v Azure
Monitorování – Monitorování Synchronizace dat SQL s využitím protokolů služby Azure Monitor
Řešení potíží – Řešení potíží s Synchronizace dat SQL v Azure
Aktualizace schématu synchronizace
- Použití jazyka Transact-SQL – Automatizace replikace změn schématu v Synchronizace dat SQL v Azure
- Použití PowerShellu – Použití PowerShellu k aktualizaci schématu synchronizace v existující skupině synchronizace

Další informace o službě Azure SQL Database najdete tady:

Sdílet prostřednictvím

Použití PowerShellu k synchronizaci dat mezi SQL Database a SQL Serverem

Použití služby Azure Cloud Shell

Požadavky

Příklady

Vyčištění nasazení

Vysvětlení skriptu

Váš názor

Další materiály

Sdílet prostřednictvím

Použití PowerShellu k synchronizaci dat mezi SQL Database a SQL Serverem

Použití služby Azure Cloud Shell

Požadavky

Příklady

Vyčištění nasazení

Vysvětlení skriptu

Související obsah

Váš názor

Další materiály