Självstudie: Konfigurera speglade databaser från Microsoft Fabric i SQL Server

I SQL Server 2025 är de behörigheter som krävs för fabric-inloggningen:

• Medlemskap i serverrollen ##MS_ServerStateReader##
• Följande behörigheter i användardatabasen:
  • SELECT
  • ÄNDRA EVENTUELL EXTERN SPEGLING

1. Anslut till SQL Server-instansen med ett T-SQL-frågeverktyg som SQL Server Management Studio (SSMS) eller mssql-tillägget med Visual Studio Code.

2. Anslut till master databasen. Skapa en serverinloggning och tilldela lämpliga behörigheter.

   Viktigt!

   För SQL Server-instanser i en AlwaysOn-tillgänglighetsgrupp måste inloggningen skapas i alla SQL Server-instanser. Huvudnamnet fabric_login måste ha samma SID i varje replikinstans.

   • Skapa en SQL-autentiserad inloggning med namnet fabric_login. Du kan välja valfritt namn för den här inloggningen. Ange ditt eget starka lösenord. Kör följande T-SQL-skript i master databasen:

   --Run in the master database
   USE [master];
   CREATE LOGIN [fabric_login] WITH PASSWORD = '<strong password>';
   
   ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [fabric_login];
   

   • Eller logga in som Microsoft Entra-administratör och skapa en autentiserad inloggning med Microsoft Entra-ID från ett befintligt konto (rekommenderas). Kör följande T-SQL-skript i master databasen:

   --Run in the master database
   USE [master];
   CREATE LOGIN [bob@contoso.com] FROM EXTERNAL PROVIDER;
   
   ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [bob@contoso.com];
   

3. Anslut till användardatabasen som du planerar att spegla till Microsoft Fabric. Skapa en databasanvändare som är ansluten till inloggningen och bevilja minsta möjliga behörighet:

   • För en SQL-autentiserad inloggning:

   --Run in the user database
   CREATE USER [fabric_user] FOR LOGIN [fabric_login];
   
   GRANT SELECT, ALTER ANY EXTERNAL MIRROR
      TO [fabric_user];
   

   • Eller för en Microsoft Entra-autentiserad inloggning (rekommenderas):

   --Run in the user database
   CREATE USER [bob@contoso.com] FOR LOGIN [bob@contoso.com];
   
   GRANT SELECT, ALTER ANY EXTERNAL MIRROR
      TO [bob@contoso.com];
   

För SQL Server-versioner 2016-2022 behöver en administratör medlemskap i sysadmin-serverrollen för att först konfigurera CDC. Framtida CDC-underhåll kräver medlemskap i sysadmin-serverrollen.

När CDC har konfigurerats kräver aktivering av spegling endast CONNECT på servernivå och SELECT- och CONNECT-behörigheter på databasnivå för att replikera data.

1. Anslut till SQL Server-instansen med ett T-SQL-frågeverktyg som SQL Server Management Studio (SSMS) eller mssql-tillägget med Visual Studio Code.

2. Anslut till master databasen. Skapa en serverinloggning och tilldela lämpliga behörigheter.

   Viktigt!

   För SQL Server-instanser i en AlwaysOn-tillgänglighetsgrupp måste inloggningen skapas i alla SQL Server-instanser. Upprepa följande steg för varje replikinstans. Huvudnamnet fabric_login måste ha samma SID i varje replikinstans.

   Du kan välja valfritt namn för den här inloggningen. Medlemskap i sysadmin-serverrollen för SQL Server 2016-2022-instansen krävs för att aktivera eller inaktivera insamling av ändringsdata.

   • Kör följande T-SQL-skript i databasen för att skapa en SQL-autentiserad master inloggning med namnet fabric_login. Ange ditt eget starka lösenord.

   --Run in the master database
   USE [master];
   CREATE LOGIN [fabric_login] WITH PASSWORD = '<strong password>';
   GRANT CONNECT SQL TO [fabric_login];
   ALTER SERVER ROLE [sysadmin] ADD MEMBER [fabric_login];
   

   • Eller logga in som Microsoft Entra-administratör och skapa en autentiserad inloggning med Microsoft Entra-ID från ett befintligt konto. Kör följande T-SQL-skript i master databasen:

   --Run in the master database
   USE [master];
   CREATE LOGIN [bob@contoso.com] FROM EXTERNAL PROVIDER;
   GRANT CONNECT SQL TO [fabric_login];
   ALTER SERVER ROLE [sysadmin] ADD MEMBER [bob@contoso.com];
   

3. Medlemskap i db_owner databasrollen för källdatabasen för spegling krävs för att hantera CDC.

   Anslut till användardatabasen som du planerar att spegla till Microsoft Fabric. Skapa en databasanvändare som är ansluten till inloggningen och bevilja minsta möjliga behörighet.

   • För en SQL-autentiserad inloggning:

   --Run in the user database
   CREATE USER [fabric_user] FOR LOGIN [fabric_login];
   GRANT CONNECT, SELECT TO [fabric_user];
   

   • Eller för en Microsoft Entra-autentiserad inloggning (rekommenderas):

   --Run in the user database
   CREATE USER [bob@contoso.com] FOR LOGIN [bob@contoso.com];
   GRANT CONNECT, SELECT TO [fabric_user];
   

4. När CDC är aktiverat kan du ta bort fabric_login från sysadmin-serverrollen.

   • För en SQL-autentiserad inloggning:

   --Run in the master database
   USE [master];
   ALTER SERVER ROLE [sysadmin] DROP MEMBER [fabric_login];
   

   • Eller för en Microsoft Entra-autentiserad inloggning (rekommenderas):

   --Run in the master database
   USE [master];
   ALTER SERVER ROLE [sysadmin] DROP MEMBER [bob@contoso.com];
   

Ansluta servern till Azure Arc och aktivera hantera identitet

För att konfigurera Fabric Mirroring måste du konfigurera Azure Arc för din SQL Server 2025-instans.

1. Anslut servern till Azure Arc. Följ stegen i Snabbstart – Ansluta hybriddatorn med Azure Arc-aktiverade servrar.

   För SQL Server-instanser som körs i en AlwaysOn-tillgänglighetsgrupp eller redundansklusterinstanskonfiguration måste alla noder vara anslutna till Azure Arc.

2. Tre registernycklar krävs på Windows Server som är värd för källinstansen av SQL Server för Fabric Mirroring. Registernycklarna innehåller information om den systemtilldelade hanterade identiteten (SAMI) för Windows Server. Följande PowerShell-skript lägger till tre registernycklar, nödvändiga filsystembehörigheter och hanterade identiteter.

   Anmärkning

   Det här avsnittet innehåller ett skript för att ändra Windows-registret. Se till att du följer dessa steg noggrant. Säkerhetskopiera registret innan du ändrar det för extra skydd. Sedan kan du återställa registret om ett problem uppstår. Mer information om hur du säkerhetskopierar och återställer registret finns i Hur du säkerhetskopierar och återställer registret i Windows.

   De tre registernycklarna läggs till på följande plats:

   • För en standardinstans: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\FederatedAuthentication
   • För en namngiven instans: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.Instancename\MSSQLServer\FederatedAuthentication

   Skriptet lägger till följande nycklar:

   • ArcServerManagedIdentityClientId
   • ArcServerSystemAssignedManagedIdentityClientId
   • ArcServerSystemAssignedManagedIdentityTenantID

   Kör följande PowerShell-skript för att konfigurera den systemtilldelade hanterade identiteten (SAMI) och nödvändiga registernycklar på Den Windows Server som är värd för SQL Server-källinstansen.

    $apiVersion = "2020-06-01"
    $resource = "https://storage.azure.com/"
    $ep = $env:IDENTITY_ENDPOINT
    if (!$ep) {
        throw "Azure Arc service is not installed, Microsoft Fabric Mirroring cannot be enabled."
    }
    $endpoint = "{0}?resource={1}&api-version={2}" -f $ep,$resource,$apiVersion
    $secretFile = ""
    try {
        Invoke-WebRequest -Method GET -Uri $endpoint -Headers @{Metadata='True'} -UseBasicParsing > $null
    } catch {
        if ($_.Exception.Response.Headers) {
            $wwwAuthHeader = $_.Exception.Response.Headers["WWW-Authenticate"]
            if ($wwwAuthHeader -match "Basic realm=.+") {
                $secretFile = ($wwwAuthHeader -split "Basic realm=")[1]
            }
        }
    }
   
    if (!$secretFile) {
        throw "Secret file path not found."
    }
   
    $secret = cat -Raw $secretFile
   
    try {
        $response = Invoke-WebRequest -Method GET -Uri $endpoint -Headers @{Metadata='True'; Authorization="Basic $secret"} -UseBasicParsing
    } catch {
        throw "Can not establish communication with IMDS service. You need to have Azure Arc service installed"
    }
   
    if ($response) {
        $parts = (ConvertFrom-Json -InputObject $response.Content).access_token -split "\."
        $padLength = 4 - ($parts[1].Length % 4)
        if ($padLength -ne 4) { $parts[1] += "=" * $padLength }
        $payload = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($parts[1])) | ConvertFrom-Json
        $regPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
        $instance = ""
        $regKey = Get-Item -Path $regPath
        $regKey.GetValueNames() | Where-Object { $regKey.GetValue($_) -match 'MSSQL17' } | ForEach-Object {
            $instance = $_
            $service = if ($instance -eq "MSSQLSERVER") { "MSSQLSERVER" } else { "MSSQL$" + $instance }
            $reginst = $regKey.GetValue($_)
            $regFed = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$($reginst)\MSSQLServer\FederatedAuthentication"
            if (-not (Test-Path -Path $regFed)) {
                New-Item -Path $regFed -Force > $null
            }
            Write-Host "Registering Azure Arc MSI service for SQL Server instance: " $instance `n
            Set-ItemProperty -Path $regFed -Name "ArcServerManagedIdentityClientId" -Value ""
            Set-ItemProperty -Path $regFed -Name "ArcServerSystemAssignedManagedIdentityClientId" -Value $($payload.appid)
            Set-ItemProperty -Path $regFed -Name "ArcServerSystemAssignedManagedIdentityTenantId" -Value $($payload.tid)
            $svcPath = "HKLM:\SYSTEM\CurrentControlSet\Services\$($service)"
            if (Test-Path -Path $svcPath) {
                $keyPath = Split-Path $secretFile
                $svcKey = Get-Item -Path $svcPath
                $sqlAccount = $svcKey.GetValue("ObjectName")
                if ($sqlAccount -ne "LocalSystem") {
                    Write-Host "Permissioning folder" $keyPath "for SQL Server account" $sqlAccount `n
                    icacls $keyPath /grant "$($sqlAccount):(OI)(CI)R"
                    $group = "Hybrid agent extension applications"
                    $isMember = Get-LocalGroupMember -Group $group | Where-Object { $_.Name -eq $sqlAccount }
                    if (-not $isMember) {
                        Write-Host "Also adding SQL running account to local group: $group" `n
                        Add-LocalGroupMember -Group $group -Member $sqlAccount
                    } else {
                        Write-Host ""
                    }
                }
            }
        }
        Write-Host "Registration complete for:" `n "Client ID: " $($payload.appid) `n "Tenant ID: " $($payload.tid) `n
    }
   

   Viktigt!

   För SQL Server-instanser som körs i en AlwaysOn-tillgänglighetsgrupp eller redundansklusterinstanskonfiguration kör du PowerShell-skriptet lokalt på varje nod.

3. Anslut till din lokala SQL Server 2025-instans. När du ansluter väljer du Betrodd servercertifikat.

4. Visa hanterade identiteter:

   --Run in the master database
   USE [master];
   SELECT *
   FROM sys.dm_server_managed_identities;
   

   Detta bör returnera 1 rad med rätt client_id och tenant_id. Identity_type ska vara "Systemtilldelad".

Lägga till behörigheter för hanterade identiteter i Microsoft Fabric

Den hanterade identiteten för SQL Server skapas och beviljas behörigheter av Microsoft Fabric automatiskt.

För SQL Server-instanser som körs i en Always On-tillgänglighetsgrupp eller konfiguration av redundansklusterinstans, måste dock den systemtilldelade hanterade identiteten (SAMI) för varje sekundär nod tilldelas deltagarbehörighet till Fabric-arbetsytan. En hanterad identitet skapas av PowerShell-skriptet som tillhandahålls för varje sekundär nod, och som måste beviljas Fabric-behörigheter manuellt.

1. I Fabricportalen beviljar du Fabric-behörigheter till varje sekundär nods hanterade identitet.

   1. På arbetsytan Fabric väljer du Hantera åtkomst.

      

   2. Välj Lägg till personer eller grupper.

   3. I dialogrutan Lägg till personer letar du reda på servernamnen för varje nod i tillgänglighetsgruppen eller redundansklustret.

   4. Bevilja varje medlemskap rollen Bidragsgivare.

      

Konfigurera den lokala datagatewayen

Kontrollera dina nätverkskrav för Fabric för att få åtkomst till din SQL Server. Du måste installera en lokal datagateway för att spegla data. Kontrollera att den lokala gatewaydatorns nätverk kan ansluta till SQL Server-instansen. Mer information finns i How to: Secure data Microsoft Fabric mirrored databases From SQL Server (Skydda data, Microsoft Fabric-speglade databaser från SQL Server).

1. Ladda ned data gateway för lokalt bruk från Microsofts officiella nedladdningscenter.
2. Starta installationen. Följ anvisningarna i Installera en lokal datagateway.
   • Ange din e-postadress för Ditt Microsoft-konto.
   • Namn: MyOPDG eller något namn du önskar.
   • Ange en säker återställningsnyckel.

Skapa en speglad SQL Server

1. Öppna Fabric-portalen.
2. Använd en befintlig arbetsyta eller skapa en ny arbetsyta.
3. Gå till panelen Skapa . Välj ikonen Skapa.
4. Rulla för att välja Speglad SQL Server-databas.
5. Ange namnet på din SQL Server-databas som ska speglas och välj sedan Skapa.

Anslut Fabric till din SQL Server-instans

För att aktivera spegling måste du ansluta till SQL Server-instansen från Fabric för att initiera anslutningen från Fabric. Följande steg vägleder dig genom processen att skapa anslutningen till DIN SQL Server:

1. Under Nya källor väljer du SQL Server-databas. Eller välj en befintlig SQL Server-anslutning från OneLake-hubben.

2. Om du har valt Ny anslutning anger du anslutningsinformationen till SQL Server-instansen.

   • Server: Den fullständigt kvalificerade sökvägen för servernamnet som Fabric använder för att nå din SQL Server-instans, samma som du skulle använda för SSMS.

   Tips/Råd

   SQL Server-instanser i en AlwaysOn-tillgänglighetsgrupp använder alltid på-lyssnaren för server. Om SQL Server körs i en konfiguration av en redundansklusterinstans använder du det virtuella nätverksnamnet för server.

   • Databas: Ange namnet på din SQL Server.
     • Anslutning: Skapa ny anslutning.
     • Anslutningsnamn: Ett automatiskt namn anges. Du kan ändra det.
     • Datagateway: Välj den lokala datagateway som du har konfigurerat enligt ditt scenario.
     • Typ av autentisering: Välj autentiseringsmetod och ange det huvudnamn som du konfigurerade i Använda en inloggnings- och mappad databasanvändare.
     • Markera kryssrutan Använd krypterad anslutning .

3. Välj Anslut.

Förbereda SQL Server-instansen

1. SQL Server Agent-tjänsten måste köras. Vi rekommenderar starkt att du konfigurerar automatisk start.
2. Cdc (Changed Data Capture) aktiveras automatiskt och konfigureras av Fabric Mirroring för varje önskad tabell i databasen. Granska kända problem och fel med CDC. CDC kräver att varje tabell har en primärnyckel.

Konfigurera den lokala datagatewayen

Kontrollera dina nätverkskrav för Fabric för att få åtkomst till din SQL Server. Du måste installera en lokal datagateway för att spegla data. Kontrollera att den lokala gatewaydatorns nätverk kan ansluta till SQL Server-instansen. Mer information finns i How to: Secure data Microsoft Fabric mirrored databases From SQL Server (Skydda data, Microsoft Fabric-speglade databaser från SQL Server).

1. Ladda ned den lokala datagatewayen, se Ladda ned den lokala datagatewayen från det officiella Microsoft Download Center.
2. Starta installationen. Följ anvisningarna i Installera en lokal datagateway.
   • Ange din e-postadress för Ditt Microsoft-konto.
   • Namn: MyOPDG eller något namn du önskar.
   • Ange en säker återställningsnyckel.

Skapa en speglad SQL Server

1. Öppna Fabric-portalen.
2. Använd en befintlig arbetsyta eller skapa en ny arbetsyta.
3. Gå till panelen Skapa . Välj ikonen Skapa.
4. Rulla för att välja Speglad SQL Server-databas.
5. Ange namnet på din SQL Server-databas som ska speglas och välj sedan Skapa.

Anslut Fabric till din SQL Server-instans

För att aktivera spegling måste du ansluta till SQL Server-instansen från Fabric för att initiera anslutningen från Fabric. Följande steg vägleder dig genom processen att skapa anslutningen till DIN SQL Server:

1. Under Nya källor väljer du SQL Server-databas. Eller välj en befintlig SQL Server-anslutning från OneLake-hubben.

2. Om du har valt Ny anslutning anger du anslutningsinformationen till SQL Server-instansen.

   • Server: Den fullständigt kvalificerade sökvägen för servernamnet som Fabric använder för att nå din SQL Server-instans, samma som du skulle använda för SSMS.

   Tips/Råd

   För SQL Server-instanser i en AlwaysOn-tillgänglighetsgrupp använder du AlwaysOn-lyssnaren för Server. Om SQL Server körs i en konfiguration av en redundansklusterinstans använder du det virtuella nätverksnamnet för server.

   • Databas: Ange namnet på din SQL Server.
     • Anslutning: Skapa ny anslutning.
     • Anslutningsnamn: Ett automatiskt namn anges. Du kan ändra det.
     • Datagateway: Välj namnet på den lokala datagateway som du har konfigurerat enligt ditt scenario.
     • Typ av autentisering: Välj autentiseringsmetod och ange det huvudnamn som du konfigurerade i Använda en inloggnings- och mappad databasanvändare.

3. Välj Anslut.

Konfigurera sekundära repliker av AlwaysOn-tillgänglighetsgrupper

Det här avsnittet krävs endast om källdatabasen för SQL Server-spegling till Fabric är medlem i en Always On-tillgänglighetsgrupp.

Om källdatabasen finns i en AlwaysOn-tillgänglighetsgrupp krävs ytterligare steg för att konfigurera de sekundära replikerna. Upprepa de här stegen för varje sekundär replik så att hela tillgänglighetsgruppen är förberedd. Varje replik kräver att SQL-agentjobb konfigureras så att CDC fungerar korrekt när repliken är primär.

1. Växla över tillgänglighetsgruppen till en sekundär replik.

2. Använd det angivna skriptet för att skapa rensningsjobb och infångningsjobb i den sekundära replikinstansens msdb systemdatabas, om de inte redan existerar. Dessa jobb är viktiga för att behålla historiska data som behålls av CDC.

   I följande skript ersätter du <YOUR DATABASE NAME> med namnet på den användardatabas som ska speglas. Kör följande T-SQL-exempel på användardatabasen:

   DECLARE @db nvarchar(128) = '<YOUR DATABASE NAME>';
   
   DECLARE @capture nvarchar(128) = N'cdc.' + @db + N'_capture';
   DECLARE @cleanup nvarchar(128) = N'cdc.' + @db + N'_cleanup';
   -- Names may differ. Run `SELECT * FROM msdb.dbo.sysjobs WHERE category_id = 13 or category_id = 16` to see if these names exist.
   
    IF NOT EXISTS (SELECT name, job_id FROM msdb.dbo.sysjobs WHERE name = @capture)
    BEGIN
        -- Create the capture job
        EXEC sys.sp_cdc_add_job @job_type = N'capture';
        PRINT 'CDC capture job has been created.';
    END
    ELSE
    BEGIN
        PRINT 'CDC capture job already exists.';
        -- Start capture job is running
        IF NOT EXISTS (SELECT j.name, j.enabled, ja.start_execution_date, ja.stop_execution_date
            FROM msdb.dbo.sysjobs AS j
            LEFT JOIN msdb.dbo.sysjobactivity AS ja ON j.job_id = ja.job_id
            WHERE j.name = @capture and ((ja.start_execution_date IS NOT NULL and ja.stop_execution_date IS NULL)) or j.enabled <> 0)
        BEGIN
            EXEC msdb.dbo.sp_start_job @job_name = @capture;
            PRINT 'CDC capture job started running.';
        END
        ELSE
        BEGIN
            PRINT 'CDC capture job already running.';
        END
    END
   
    IF NOT EXISTS (SELECT name,job_id FROM msdb.dbo.sysjobs WHERE name = @cleanup)
    BEGIN
        -- Create the cleanup job
        EXEC sys.sp_cdc_add_job @job_type = N'cleanup';
        PRINT 'CDC cleanup job has been created.';
    END
    ELSE
    BEGIN
        -- Ensure that the cleanup job is properly scheduled (default schedule)
        IF NOT EXISTS (SELECT j.name, j.enabled, ja.start_execution_date, ja.stop_execution_date
            FROM msdb.dbo.sysjobs AS j
            LEFT JOIN msdb.dbo.sysjobactivity AS ja ON j.job_id = ja.job_id
            WHERE j.name = @cleanup and j.enabled <> 0)
        BEGIN
            EXEC msdb.dbo.sp_update_job @job_name = @cleanup, @enabled = 1;
            PRINT 'CDC cleanup job updated to enabled.';
        END
        ELSE
        BEGIN
            PRINT 'CDC cleanup job already enabled.';
        END
        PRINT 'CDC cleanup job already exists.';
    END
   

   Cdc-jobben för insamling och rensning startar omedelbart och skapas med standardinställningar. Mer information om jobben finns i sys.sp_cdc_add_job (Transact-SQL).

3. Varje jobb körs på varje tillgänglighetsgruppreplik som standard, även om det är en sekundär replik. Detta orsakar felmeddelanden i loggarna eftersom användardatabaserna på de sekundära replikerna inte kan skrivas. Aktivera de två CDC-jobben på primärreplikerna och inaktivera dem på sekundärreplikerna. Följ vägledningen i Ändra datainsamling i AlwaysOn-tillgänglighetsgrupper.