Configurare il collegamento con gli script - Istanza gestita di SQL di Azure

Si applica a: Istanza gestita di SQL di Azure SQL

Questo articolo illustra come configurare un collegamento tra SQL Server e Istanza gestita di SQL di Azure con script Transact-SQL e PowerShell o dell'interfaccia della riga di comando di Azure. Con il collegamento, i database del database primario iniziale vengono replicati nella replica secondaria near real-time.

Dopo aver creato il collegamento, è possibile effettuare il failover nella replica secondaria allo scopo della migrazione o del ripristino di emergenza.

Panoramica

Usare il collegamento per replicare i database dal database primario iniziale alla replica secondaria. Per SQL Server 2022, il database primario iniziale può essere uno tra SQL Server o Istanza gestita di SQL di Azure. Per SQL Server 2019 e versioni precedenti, il database primario iniziale può essere soltanto SQL Server. Dopo aver configurato il collegamento, il database del database primario iniziale viene replicato nella replica secondaria.

È possibile scegliere di mantenere il collegamento per la replica continua dei dati in un ambiente ibrido tra la replica primaria e secondaria oppure è possibile effettuare il failover del database nella replica secondaria, eseguire la migrazione ad Azure o per il ripristino di emergenza. Per SQL Server 2019 e versioni precedenti, effettuare il failover a Istanza gestita di SQL di Azure interrompe il collegamento e il failback non è supportato. Con SQL Server 2022 è possibile mantenere il collegamento ed eseguire il failback tra le due repliche.

Se si prevede di usare l’istanza gestita secondaria solo per il ripristino di emergenza, è possibile risparmiare sui costi di licenza attivando il vantaggio di failover ibrido.

Usare le istruzioni contenute in questo articolo per configurare manualmente il collegamento tra SQL Server e Istanza gestita di SQL di Azure. Dopo aver creato il collegamento, il database di origine ottiene una copia di sola lettura nella replica secondaria di destinazione.

Prerequisiti

Per replicare i database, sono necessari i prerequisiti seguenti:

• Una sottoscrizione di Azure attiva. Se non se ne ha una, creare un account gratuito.
• Versione supportata di SQL Server con l’aggiornamento del servizio richiesto installato.
• Istanza gestita di SQL di Azure. Attività iniziali se non è disponibile.
• Il modulo PowerShell Az.SQL 3.9.0 o versione successiva oppure l'interfaccia della riga di comando di Azure 2.47.0 o versione successiva. In alternativa, usare Azure Cloud Shell online dal Web browser per eseguire i comandi, perché è sempre aggiornato con le versioni più recenti del modulo.
• Ambiente preparato correttamente.

Considerare quanto segue:

• Il collegamento supporta un database per ogni collegamento. Per replicare più database in un'istanza, creare un collegamento per ogni singolo database. Ad esempio, per replicare 10 database in Istanza gestita di SQL, creare 10 singoli collegamenti.
• Il confronto tra SQL Server e Istanza gestita di SQL devono essere uguali. Una mancata corrispondenza nel confronto potrebbe causare una mancata corrispondenza nelle maiuscole e minuscole dei nomi del server e impedire una connessione corretta da SQL Server a Istanza gestita di SQL.
• L’errore 1475 nella replica primaria iniziale di SQL Server indica che è necessario avviare una nuova catena di backup creando un backup completo senza l’opzione COPY ONLY.
• Per stabilire un collegamento o effettuare il failover, da Istanza gestita di SQL a SQL Server 2022, l'istanza gestita deve essere configurata con i criteri di aggiornamento di SQL Server 2022. La replica dei dati e il failover da Istanza gestita di SQL a SQL Server 2022 non sono supportati dalle istanze configurate con i criteri di aggiornamento sempre aggiornati.
• Sebbene sia possibile stabilire un collegamento da SQL Server 2022 a un'istanza gestita di SQL configurata con i criteri di aggiornamento sempre aggiornati, dopo il failover a Istanza gestita di SQL non sarà più possibile replicare i dati o eseguire il failback su SQL Server 2022.

Autorizzazioni

Per SQL Server è necessario disporre delle autorizzazioni amministratore di sistema.

Per Istanza gestita di SQL di Azure, è necessario essere membri del Contributore Istanza gestita di SQL oppure disporre delle autorizzazioni personalizzate seguenti:

Terminologia e convenzioni di denominazione

Quando si eseguono script da questa guida utente, è importante non scambiare i nomi di SQL Server e Istanza gestita di SQL con i nomi di dominio completi (FQDN). La tabella seguente spiega cosa rappresentano esattamente i vari nomi e come ottenere i relativi valori:

¹ La configurazione di Istanza gestita di SQL di Azure come primario iniziale è supportata a partire da SQL Server 2022 CU10.

Configurare il backup e il ripristino del database

Se SQL Server è il database primario iniziale, i database che verranno replicati tramite il collegamento devono trovarsi nel modello di recupero con registrazione completa e disporre di almeno un backup. Poiché Istanza gestita di SQL di Azure esegue automaticamente i backup, ignorare questo passaggio se Istanza gestita di SQL è il database primario iniziale.

Eseguire il seguente codice in SQL Server per tutti i database da replicare. Sostituire <DatabaseName> con il nome effettivo del database.

-- Run on SQL Server
-- Set full recovery model for all databases you want to replicate.
ALTER DATABASE [<DatabaseName>] SET RECOVERY FULL
GO

-- Execute backup for all databases you want to replicate.
BACKUP DATABASE [<DatabaseName>] TO DISK = N'<DiskPath>'
GO

Per altre informazioni, vedere Creazione di un backup completo del database.

Stabilire un trust tra istanze

Prima di tutto, è necessario stabilire un trust tra le due istanze e proteggere gli endpoint usati per comunicare e crittografare i dati in rete. I gruppi di disponibilità distribuiti usano l'endpoint del mirroring del database del gruppo di disponibilità esistente, anziché avere il proprio endpoint dedicato. Di conseguenza, la sicurezza e l'attendibilità devono essere configurate tra le due istanze tramite l'endpoint del mirroring del database del gruppo di disponibilità.

L'attendibilità basata su certificati è l'unico modo supportato per proteggere gli endpoint del mirroring del database per SQL Server e Istanza gestita di SQL. Se si dispone di gruppi di disponibilità esistenti che usano l'autenticazione di Windows, è possibile aggiungere l'attendibilità basata su certificati all'endpoint di mirroring esistente come opzione di autenticazione secondaria. A tale scopo, è possibile usare l'istruzione ALTER ENDPOINT, come illustrato più avanti in questo articolo.

Di seguito è possibile trovare una panoramica del processo per proteggere gli endpoint di mirroring del database protetti per SQL Server e Istanza gestita di SQL:

1. Generare un certificato in SQL Server e recuperarne la chiave pubblica.
2. Recuperare la chiave pubblica del certificato dell’Istanza gestita di SQL.
3. Scambiare le chiavi pubbliche fra SQL Server e Istanza gestita di SQL.
4. Importare le chiavi dell'autorità di certificazione radice attendibile di Azure in SQL Server

Le sezioni seguenti descrivono questi passaggi in dettaglio.

Creare un certificato in SQL Server e importarne la chiave pubblica in Istanza gestita di SQL

Creare prima di tutto la chiave master del database master, se non è già presente. Inserire la password al posto di <strong_password> nello script seguente e mantenerla in un luogo riservato e sicuro. Eseguire questo script T-SQL in SQL Server:

-- Run on SQL Server
-- Create a master key encryption password
-- Keep the password confidential and in a secure place
USE MASTER
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
BEGIN
    PRINT 'Creating master key.' + CHAR(13) + 'Keep the password confidential and in a secure place.'
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong_password>'
END
ELSE
    PRINT 'Master key already exists.'
GO

Generare, quindi, un certificato di autenticazione in SQL Server. Nello script seguente, sostituire:

• @cert_expiry_date con la data di scadenza del certificato desiderata (data futura).

Registrare questa data e impostare un promemoria per ruotare (aggiornare) il certificato di SQL Server prima della data di scadenza per garantire il funzionamento continuo del collegamento.

-- Create the SQL Server certificate for the instance link
USE MASTER

-- Customize SQL Server certificate expiration date by adjusting the date below
DECLARE @cert_expiry_date AS varchar(max)='03/30/2025'

-- Build the query to generate the certificate
DECLARE @sqlserver_certificate_name NVARCHAR(MAX) = N'Cert_' + @@servername  + N'_endpoint'
DECLARE @sqlserver_certificate_subject NVARCHAR(MAX) = N'Certificate for ' + @sqlserver_certificate_name
DECLARE @create_sqlserver_certificate_command NVARCHAR(MAX) = N'CREATE CERTIFICATE [' + @sqlserver_certificate_name + '] ' + char (13) +
'    WITH SUBJECT = ''' + @sqlserver_certificate_subject + ''',' + char (13) +
'    EXPIRY_DATE = '''+ @cert_expiry_date + ''''+ char (13)
IF NOT EXISTS (SELECT name from sys.certificates WHERE name = @sqlserver_certificate_name)
BEGIN
    PRINT (@create_sqlserver_certificate_command)
    -- Execute the query to create SQL Server certificate for the instance link
    EXEC sp_executesql @stmt = @create_sqlserver_certificate_command
END
ELSE
    PRINT 'Certificate ' + @sqlserver_certificate_name + ' already exists.'
GO

Usare quindi la query T-SQL seguente in SQL Server per verificare che il certificato sia stato creato:

-- Run on SQL Server
USE MASTER
GO
SELECT * FROM sys.certificates WHERE pvt_key_encryption_type = 'MK'

Nei risultati della query si noterà che il certificato è stato crittografato con la chiave master.

Adesso è possibile ottenere la chiave pubblica del certificato di SQL Server generato:

-- Run on SQL Server
-- Show the name and the public key of generated SQL Server certificate
USE MASTER
GO
DECLARE @sqlserver_certificate_name NVARCHAR(MAX) = N'Cert_' + @@servername  + N'_endpoint'
DECLARE @PUBLICKEYENC VARBINARY(MAX) = CERTENCODED(CERT_ID(@sqlserver_certificate_name));
SELECT @sqlserver_certificate_name as 'SQLServerCertName'
SELECT @PUBLICKEYENC AS SQLServerPublicKey;

Salvare i valori di SQLServerCertName e SQLServerPublicKey dall'output, perché sarà necessario per il passaggio successivo quando si importa il certificato.

Assicurarsi prima di tutto di aver eseguito l'accesso ad Azure e di aver selezionato la sottoscrizione in cui è ospitata l'istanza gestita. La selezione della sottoscrizione appropriata è particolarmente importante se si dispone di più sottoscrizioni di Azure nell'account.

Sostituire <SubscriptionID> con l'ID della sottoscrizione di Azure.

# Run in Azure Cloud Shell (select PowerShell console)

# Enter your Azure subscription ID
$SubscriptionID = "<SubscriptionID>"

# Login to Azure and select subscription ID
if ((Get-AzContext ) -eq $null)
{
    echo "Logging to Azure subscription"
    Login-AzAccount
}
Select-AzSubscription -SubscriptionName $SubscriptionID

Usare quindi il comando PowerShell New-AzSqlInstanceServerTrustCertificate o az sql mi partner-cert create dell'interfaccia della riga di comando di Azure per caricare la chiave pubblica del certificato di autenticazione da SQL Server in Azure, ad esempio l'esempio di PowerShell seguente.

Compilare le informazioni utente necessarie, copiarlo, incollarlo e quindi eseguire lo script. Sostituire:

• <SQLServerPublicKey> con la parte pubblica del certificato di SQL Server in formato binario, che è registrata nel passaggio precedente. Si tratta di un valore di stringa lungo che inizia con 0x.
• <SQLServerCertName> con il nome del certificato di SQL Server che è stato registrato nel passaggio precedente.
• <ManagedInstanceName> con il nome breve dell'istanza gestita.

# Run in Azure Cloud Shell (select PowerShell console)
# ===============================================================================
# POWERSHELL SCRIPT TO IMPORT SQL SERVER PUBLIC CERTIFICATE TO SQL MANAGED INSTANCE
# ===== Enter user variables here ====

# Enter the name for the server SQLServerCertName certificate – for example, "Cert_sqlserver1_endpoint"
$CertificateName = "<SQLServerCertName>"

# Insert the certificate public key blob that you got from SQL Server – for example, "0x1234567..."
$PublicKeyEncoded = "<SQLServerPublicKey>"

# Enter your managed instance short name – for example, "sqlmi"
$ManagedInstanceName = "<ManagedInstanceName>"

# ==== Do not customize the below cmdlets====

# Find out the resource group name
$ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName

# Upload the public key of the authentication certificate from SQL Server to Azure.
New-AzSqlInstanceServerTrustCertificate -ResourceGroupName $ResourceGroup -InstanceName $ManagedInstanceName -Name $CertificateName -PublicKey $PublicKeyEncoded 

Il risultato di questa operazione è un riepilogo del certificato di SQL Server caricato in Azure.

Se è necessario visualizzare tutti i certificati di SQL Server caricati in un'istanza gestita, usare il comando di PowerShell Get-AzSqlInstanceServerTrustCertificate o az sql mi partner-cert list dell'interfaccia della riga di comando di Azure in Azure Cloud Shell. Per rimuovere il certificato di SQL Server caricato in un'istanza gestita di SQL, usare il comando PowerShell Remove-AzSqlInstanceServerTrustCertificate o az sql mi partner-cert delete dell'interfaccia della riga di comando di Azure in Azure Cloud Shell.

Importare la chiave pubblica del certificato dall’istanza gestita di SQL e importarla in SQL Server

Il certificato per proteggere l'endpoint di collegamento viene generato automaticamente in Istanza gestita di SQL di Azure. Ottenere la chiave pubblica del certificato da Istanza gestita di SQL e importarla in SQL Server usando il comando Get-AzSqlInstanceEndpointCertificate di PowerShell o az sql mi endpoint-cert show dell'interfaccia della riga di comando di Azure, ad esempio l'esempio di PowerShell seguente.

Eseguire lo script seguente. Sostituire:

• <SubscriptionID> con l'ID della sottoscrizione di Azure.
• <ManagedInstanceName> con il nome breve dell'istanza gestita.

# Run in Azure Cloud Shell (select PowerShell console)
# ===============================================================================
# POWERSHELL SCRIPT TO EXPORT MANAGED INSTANCE PUBLIC CERTIFICATE
# ===== Enter user variables here ====

# Enter your managed instance short name – for example, "sqlmi"
$ManagedInstanceName = "<ManagedInstanceName>"

# ==== Do not customize the following cmdlet ====

# Find out the resource group name
$ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName

# Fetch the public key of the authentication certificate from Managed Instance. Outputs a binary key in the property PublicKey.
Get-AzSqlInstanceEndpointCertificate -ResourceGroupName $ResourceGroup -InstanceName $ManagedInstanceName -EndpointType "DATABASE_MIRRORING" | out-string   

Copiare l'intero output PublicKey (inizia con 0x) perché sarà necessario nel passaggio successivo.

In alternativa, se si verificano problemi durante la procedura di copia-incolla di PublicKey, è anche possibile eseguire il comando EXEC sp_get_endpoint_certificate 4 T-SQL nell'istanza gestita per ottenere la relativa chiave pubblica per l'endpoint di collegamento.

Quindi, importare la chiave pubblica ottenuta del certificato di sicurezza dell’istanza gestita in SQL Server. Eseguire la query seguente in SQL Server per creare il certificato dell'endpoint dell'istanza gestita. Sostituire:

• <ManagedInstanceFQDN> con il nome di dominio completo dell'istanza gestita.
• <PublicKey> con il valore PublicKey ottenuto nel passaggio precedente (da Azure Cloud Shell, che inizia con 0x). Non è necessario usare le virgolette.

-- Run on SQL Server
USE MASTER
CREATE CERTIFICATE [<ManagedInstanceFQDN>]
FROM BINARY = <PublicKey> 

Importare le chiavi dell'autorità di certificazione radice attendibile di Azure in SQL Server

L'importazione delle chiavi del certificato radice pubblico delle autorità di certificazione Microsoft e DigiCert (CA) in SQL Server è necessaria affinché SQL Server consideri attendibili i certificati rilasciati da Azure per i domini di database.windows.net.

Per prima cosa, importare il certificato dell'autorità radice PKI Microsoft in SQL Server:

-- Run on SQL Server
-- Import Microsoft PKI root-authority certificate (trusted by Azure), if not already present
IF NOT EXISTS (SELECT name FROM sys.certificates WHERE name = N'MicrosoftPKI')
BEGIN
    PRINT 'Creating MicrosoftPKI certificate.'
    CREATE CERTIFICATE [MicrosoftPKI] FROM BINARY = 0x308205A830820390A00302010202101ED397095FD8B4B347701EAABE7F45B3300D06092A864886F70D01010C05003065310B3009060355040613025553311E301C060355040A13154D6963726F736F667420436F72706F726174696F6E313630340603550403132D4D6963726F736F66742052534120526F6F7420436572746966696361746520417574686F726974792032303137301E170D3139313231383232353132325A170D3432303731383233303032335A3065310B3009060355040613025553311E301C060355040A13154D6963726F736F667420436F72706F726174696F6E313630340603550403132D4D6963726F736F66742052534120526F6F7420436572746966696361746520417574686F72697479203230313730820222300D06092A864886F70D01010105000382020F003082020A0282020100CA5BBE94338C299591160A95BD4762C189F39936DF4690C9A5ED786A6F479168F8276750331DA1A6FBE0E543A3840257015D9C4840825310BCBFC73B6890B6822DE5F465D0CC6D19CC95F97BAC4A94AD0EDE4B431D8707921390808364353904FCE5E96CB3B61F50943865505C1746B9B685B51CB517E8D6459DD8B226B0CAC4704AAE60A4DDB3D9ECFC3BD55772BC3FC8C9B2DE4B6BF8236C03C005BD95C7CD733B668064E31AAC2EF94705F206B69B73F578335BC7A1FB272AA1B49A918C91D33A823E7640B4CD52615170283FC5C55AF2C98C49BB145B4DC8FF674D4C1296ADF5FE78A89787D7FD5E2080DCA14B22FBD489ADBACE479747557B8F45C8672884951C6830EFEF49E0357B64E798B094DA4D853B3E55C428AF57F39E13DB46279F1EA25E4483A4A5CAD513B34B3FC4E3C2E68661A45230B97A204F6F0F3853CB330C132B8FD69ABD2AC82DB11C7D4B51CA47D14827725D87EBD545E648659DAF5290BA5BA2186557129F68B9D4156B94C4692298F433E0EDF9518E4150C9344F7690ACFC38C1D8E17BB9E3E394E14669CB0E0A506B13BAAC0F375AB712B590811E56AE572286D9C9D2D1D751E3AB3BC655FD1E0ED3740AD1DAAAEA69B897288F48C407F852433AF4CA55352CB0A66AC09CF9F281E1126AC045D967B3CEFF23A2890A54D414B92AA8D7ECF9ABCD255832798F905B9839C40806C1AC7F0E3D00A50203010001A3543052300E0603551D0F0101FF040403020186300F0603551D130101FF040530030101FF301D0603551D0E0416041409CB597F86B2708F1AC339E3C0D9E9BFBB4DB223301006092B06010401823715010403020100300D06092A864886F70D01010C05000382020100ACAF3E5DC21196898EA3E792D69715B813A2A6422E02CD16055927CA20E8BAB8E81AEC4DA89756AE6543B18F009B52CD55CD53396D624C8B0D5B7C2E44BF83108FF3538280C34F3AC76E113FE6E3169184FB6D847F3474AD89A7CEB9D7D79F846492BE95A1AD095333DDEE0AEA4A518E6F55ABBAB59446AE8C7FD8A2502565608046DB3304AE6CB598745425DC93E4F8E355153DB86DC30AA412C169856EDF64F15399E14A75209D950FE4D6DC03F15918E84789B2575A94B6A9D8172B1749E576CBC156993A37B1FF692C919193E1DF4CA337764DA19FF86D1E1DD3FAECFBF4451D136DCFF759E52227722B86F357BB30ED244DDC7D56BBA3B3F8347989C1E0F20261F7A6FC0FBB1C170BAE41D97CBD27A3FD2E3AD19394B1731D248BAF5B2089ADB7676679F53AC6A69633FE5392C846B11191C6997F8FC9D66631204110872D0CD6C1AF3498CA6483FB1357D1C1F03C7A8CA5C1FD9521A071C193677112EA8F880A691964992356FBAC2A2E70BE66C40C84EFE58BF39301F86A9093674BB268A3B5628FE93F8C7A3B5E0FE78CB8C67CEF37FD74E2C84F3372E194396DBD12AFBE0C4E707C1B6F8DB332937344166DE8F4F7E095808F965D38A4F4ABDE0A308793D84D00716245274B3A42845B7F65B76734522D9C166BAAA8D87BA3424C71C70CCA3E83E4A6EFB701305E51A379F57069A641440F86B02C91C63DEAAE0F84

    --Trust certificates issued by Microsoft PKI root authority for Azure database.windows.net domains
    DECLARE @CERTID int
    SELECT @CERTID = CERT_ID('MicrosoftPKI')
    EXEC sp_certificate_add_issuer @CERTID, N'*.database.windows.net'
END
ELSE
    PRINT 'Certificate MicrosoftPKI already exists.'
GO

Quindi, importare il certificato dell'autorità radice PKI DigiCert in SQL Server:

-- Run on SQL Server
-- Import DigiCert PKI root-authority certificate trusted by Azure to SQL Server, if not already present
IF NOT EXISTS (SELECT name FROM sys.certificates WHERE name = N'DigiCertPKI')
BEGIN
    PRINT 'Creating DigiCertPKI certificate.'
    CREATE CERTIFICATE [DigiCertPKI] FROM BINARY = 0x3082038E30820276A0030201020210033AF1E6A711A9A0BB2864B11D09FAE5300D06092A864886F70D01010B05003061310B300906035504061302555331153013060355040A130C446967694365727420496E6331193017060355040B13107777772E64696769636572742E636F6D3120301E06035504031317446967694365727420476C6F62616C20526F6F74204732301E170D3133303830313132303030305A170D3338303131353132303030305A3061310B300906035504061302555331153013060355040A130C446967694365727420496E6331193017060355040B13107777772E64696769636572742E636F6D3120301E06035504031317446967694365727420476C6F62616C20526F6F7420473230820122300D06092A864886F70D01010105000382010F003082010A0282010100BB37CD34DC7B6BC9B26890AD4A75FF46BA210A088DF51954C9FB88DBF3AEF23A89913C7AE6AB061A6BCFAC2DE85E092444BA629A7ED6A3A87EE054752005AC50B79C631A6C30DCDA1F19B1D71EDEFDD7E0CB948337AEEC1F434EDD7B2CD2BD2EA52FE4A9B8AD3AD499A4B625E99B6B00609260FF4F214918F76790AB61069C8FF2BAE9B4E992326BB5F357E85D1BCD8C1DAB95049549F3352D96E3496DDD77E3FB494BB4AC5507A98F95B3B423BB4C6D45F0F6A9B29530B4FD4C558C274A57147C829DCD7392D3164A060C8C50D18F1E09BE17A1E621CAFD83E510BC83A50AC46728F67314143D4676C387148921344DAF0F450CA649A1BABB9CC5B1338329850203010001A3423040300F0603551D130101FF040530030101FF300E0603551D0F0101FF040403020186301D0603551D0E041604144E2254201895E6E36EE60FFAFAB912ED06178F39300D06092A864886F70D01010B05000382010100606728946F0E4863EB31DDEA6718D5897D3CC58B4A7FE9BEDB2B17DFB05F73772A3213398167428423F2456735EC88BFF88FB0610C34A4AE204C84C6DBF835E176D9DFA642BBC74408867F3674245ADA6C0D145935BDF249DDB61FC9B30D472A3D992FBB5CBBB5D420E1995F534615DB689BF0F330D53E31E28D849EE38ADADA963E3513A55FF0F970507047411157194EC08FAE06C49513172F1B259F75F2B18E99A16F13B14171FE882AC84F102055D7F31445E5E044F4EA879532930EFE5346FA2C9DFF8B22B94BD90945A4DEA4B89A58DD1B7D529F8E59438881A49E26D56FADDD0DC6377DED03921BE5775F76EE3C8DC45D565BA2D9666EB33537E532B6

    --Trust certificates issued by DigiCert PKI root authority for Azure database.windows.net domains
    DECLARE @CERTID int
    SELECT @CERTID = CERT_ID('DigiCertPKI')
    EXEC sp_certificate_add_issuer @CERTID, N'*.database.windows.net'
END
ELSE
    PRINT 'Certificate DigiCertPKI already exists.'
GO

Infine, verificare tutti i certificati creati usando la DMV seguente:

-- Run on SQL Server
SELECT * FROM sys.certificates

Convalidare il certificato

Dopo aver creato i certificati, verificare che il certificato dell'endpoint dell'istanza gestita sia configurato correttamente.

Prima di tutto, determinare l'oggetto certificate_id del certificato dell'istanza gestita esportata sostituendo il valore di <ManagedInstanceFQDN> e quindi eseguendo la query seguente in SQL Server:

-- Run on SQL Server 
USE MASTER 
GO 

SELECT name, subject, certificate_id, start_date, expiry_date 
FROM sys.certificates 
WHERE issuer_name LIKE '%Microsoft Corporation%' AND name = '<ManagedInstanceFQDN>' 
GO 

Convalidare quindi il certificato sostituendo il valore di <certificate_id> dal risultato della query precedente e quindi eseguendo la query seguente in SQL Server:

-- Run on SQL Server 

USE MASTER 
GO 

EXEC sp_validate_certificate_ca_chain <certificate_id> 
GO 

Una risposta di Commands completed successfully. Completion time: … indica che il certificato dell'endpoint dell'istanza gestita è stato convalidato correttamente.

Se si verifica un errore, eliminare il certificato e seguire la procedura descritta nella sezione Ottenere la chiave pubblica del certificato da Istanza gestita di SQL e importarla in SQL Server per importare nuovamente il certificato.

Per escludere il certificato, eseguire la query seguente sul server SQL.

-- Run on SQL Server 

USE MASTER 
GO 

DROP CERTIFICATE [<ManagedInstanceFQDN>] 
GO 

Proteggere l’endpoint del mirroring del database

Se non si dispone di un gruppo di disponibilità esistente o di un endpoint del mirroring del database in SQL Server, il passaggio successivo consiste nel creare un endpoint del mirroring del database in SQL Server e proteggerlo con il certificato di SQL Server generato in precedenza. Se si dispone di un gruppo di disponibilità o di un endpoint di mirroring esistente, passare alla sezione Modificare endpoint esistente.

Creare e proteggere l'endpoint del mirroring del database in SQL Server

Per verificare che non sia stato creato un endpoint di mirroring del database esistente, usare lo script seguente:

-- Run on SQL Server
-- View database mirroring endpoints on SQL Server
SELECT * FROM sys.database_mirroring_endpoints WHERE type_desc = 'DATABASE_MIRRORING'

Se la query precedente non mostra un endpoint di mirroring del database esistente, eseguire lo script seguente in SQL Server per ottenere il nome del certificato di SQL Server generato in precedenza.

-- Run on SQL Server
-- Show the name and the public key of generated SQL Server certificate
USE MASTER
GO
DECLARE @sqlserver_certificate_name NVARCHAR(MAX) = N'Cert_' + @@servername  + N'_endpoint'
SELECT @sqlserver_certificate_name as 'SQLServerCertName'

Salvare SQLServerCertName dall'output perché sarà necessario nel passaggio successivo.

Usare lo script seguente per creare un nuovo endpoint del mirroring del database sulla porta 5022 e proteggere l'endpoint con il certificato di SQL Server. Sostituire:

• <SQL_SERVER_CERTIFICATE> con il nome di SQLServerCertName ottenuto nel passaggio precedente.

-- Run on SQL Server
-- Create a connection endpoint listener on SQL Server
USE MASTER
CREATE ENDPOINT database_mirroring_endpoint
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (
        ROLE=ALL,
        AUTHENTICATION = CERTIFICATE [<SQL_SERVER_CERTIFICATE>],
        ENCRYPTION = REQUIRED ALGORITHM AES
    )  
GO

Verificare che l'endpoint del mirroring sia stato creato eseguendo lo script seguente in SQL Server:

-- Run on SQL Server
-- View database mirroring endpoints on SQL Server
SELECT
    name, type_desc, state_desc, role_desc,
    connection_auth_desc, is_encryption_enabled, encryption_algorithm_desc
FROM 
    sys.database_mirroring_endpoints

L'endpoint state_desc colonna creato correttamente dovrebbe essere nello stato STARTED.

È stato creato un nuovo endpoint di mirroring con l'autenticazione del certificato e la crittografia AES abilitata.

Alterare un endpoint esistente

Se si usano gruppi di disponibilità esistenti per il collegamento o se è presente un endpoint di mirroring del database esistente, verificare prima di tutto che soddisfi le condizioni obbligatorie seguenti per il collegamento:

• Il tipo deve essere DATABASE_MIRRORING.
• L’autenticazione della connessione deve essere CERTIFICATE.
• La crittografia deve essere abilitata.
• L'algoritmo di crittografia deve essere AES.

Per ottenere i dettagli per un endpoint di mirroring del database esistente, eseguire la seguente query su SQL Server:

-- Run on SQL Server
-- View database mirroring endpoints on SQL Server
SELECT
    name, type_desc, state_desc, role_desc, connection_auth_desc,
    is_encryption_enabled, encryption_algorithm_desc
FROM
    sys.database_mirroring_endpoints

Se l'output indica che l'endpoint esistente DATABASE_MIRRORING connection_auth_desc non è CERTIFICATE, o encryption_algorthm_desc non è AES, l'endpoint deve essere modificato per soddisfare i requisiti.

In SQL Server lo stesso endpoint di mirroring del database viene usato sia per i gruppi di disponibilità che per i gruppi di disponibilità distribuiti. Se l'endpoint connection_auth_desc è NTLM (autenticazione di Windows) o KERBEROS, ed è necessario autenticazione di Windows per un gruppo di disponibilità esistente, è possibile modificare l'endpoint per usare più metodi di autenticazione passando all'opzione di autenticazione su NEGOTIATE CERTIFICATE. Questa modifica consente al gruppo di disponibilità esistente di usare autenticazione di Windows, usando l'autenticazione del certificato per Istanza gestita di SQL.

Analogamente, se la crittografia non include AES ed è necessaria la crittografia RC4, è possibile modificare l'endpoint per usare entrambi gli algoritmi. Per informazioni dettagliate sulle opzioni possibili per la modifica degli endpoint, vedere la pagina della documentazione per sys.database_mirroring_endpoints.

Lo script seguente è un esempio di come modificare l'endpoint di mirroring del database esistente su SQL Server. Sostituire:

• <YourExistingEndpointName> con il nome dell'endpoint esistente.
• <SQLServerCertName> con il nome del certificato di SQL Server generato (ottenuto in uno dei passaggi precedenti).

A seconda della configurazione specifica, potrebbe essere necessario personalizzare ulteriormente lo script. Inoltre, è possibile usare SELECT * FROM sys.certificates per ottenere il nome del certificato creato in SQL Server.

-- Run on SQL Server
-- Alter the existing database mirroring endpoint to use CERTIFICATE for authentication and AES for encryption
USE MASTER
ALTER ENDPOINT [<YourExistingEndpointName>]   
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (
        ROLE=ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE CERTIFICATE [<SQLServerCertName>],
        ENCRYPTION = REQUIRED ALGORITHM AES
    )
GO

Dopo aver eseguito la query dell'endpoint ALTER e aver impostato la modalità di autenticazione doppia su Windows e sul certificato, usare di nuovo questa query in SQL Server per visualizzare i dettagli per l'endpoint di mirroring del database:

-- Run on SQL Server
-- View database mirroring endpoints on SQL Server
SELECT
    name, type_desc, state_desc, role_desc, connection_auth_desc,
    is_encryption_enabled, encryption_algorithm_desc
FROM
    sys.database_mirroring_endpoints

L'endpoint di mirroring del database è stato modificato correttamente per un collegamento Istanza gestita di SQL.

Creare un gruppo di disponibilità su SQL Server

Se non si dispone di un gruppo di disponibilità esistente, il passaggio successivo consiste nel crearne uno in SQL Server, indipendentemente dal quale sarà il database primario iniziale.

I comandi per creare il gruppo di disponibilità sono diversi se l’Istanza gestita di SQL è il database primario iniziale, supportato solo a partire da SQL Server 2022 CU10.

Sebbene sia possibile stabilire più collegamenti per lo stesso database, il collegamento supporta solo la replica di un database per ogni collegamento. Se si desidera creare più collegamenti per lo stesso database, usare lo stesso gruppo di disponibilità per tutti i collegamenti, ma poi creare un nuovo gruppo di disponibilità distribuito per ogni collegamento di database tra SQL Server e Istanza gestita di SQL.

-- Run on the initial primary
SELECT @@SERVERNAME AS SQLServerName 

-- Run on SQL Server
-- Create the primary availability group on SQL Server
USE MASTER
CREATE AVAILABILITY GROUP [<AGNameOnSQLServer>]
WITH (CLUSTER_TYPE = NONE) -- <- Delete this line for SQL Server 2016 only. Leave as-is for all higher versions.
    FOR database [<DatabaseName>]  
    REPLICA ON   
        N'<SQLServerName>' WITH   
            (  
            ENDPOINT_URL = 'TCP://<SQLServerIP>:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC
            );
GO

-- Run on SQL Server
-- Create a distributed availability group for the availability group and database
-- ManagedInstanceName example: 'sqlmi1'
-- ManagedInstanceFQDN example: 'sqlmi1.73d19f36a420a.database.windows.net'
USE MASTER
CREATE AVAILABILITY GROUP [<DAGName>]
WITH (DISTRIBUTED) 
    AVAILABILITY GROUP ON  
    N'<AGNameOnSQLServer>' WITH 
    (
      LISTENER_URL = 'TCP://<SQLServerIP>:5022',
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
      FAILOVER_MODE = MANUAL,
      SEEDING_MODE = AUTOMATIC,
      SESSION_TIMEOUT = 20
    ),
    N'<AGNameOnSQLMI>' WITH
    (
      LISTENER_URL = 'tcp://<ManagedInstanceFQDN>:5022;Server=[<ManagedInstanceName>]',
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
      FAILOVER_MODE = MANUAL,
      SEEDING_MODE = AUTOMATIC
    );
GO

-- Run on the initial primary
SELECT @@SERVERNAME AS SQLServerName 

-- Run on SQL Server 
-- Create the availability group on SQL Server 

CREATE AVAILABILITY GROUP [<AGNameOnSQLServer>] 
WITH (CLUSTER_TYPE = NONE) 
FOR  
REPLICA ON N'<SQLServerName>' 
WITH ( 
    ENDPOINT_URL = N'TCP://<SQLServerIP>:5022', 
    FAILOVER_MODE = MANUAL, 
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
    SEEDING_MODE = AUTOMATIC); 

GO 

-- Run on SQL Server 
-- Grant permission to the availability group to create databases 

ALTER AVAILABILITY GROUP [<AGNameOnSQLServer>] GRANT CREATE ANY DATABASE; 

-- Run on SQL Server 
-- Create a distributed availability group for the availability group and database 
-- ManagedInstanceName example: 'sqlmi1' 
-- ManagedInstanceFQDN example: 'sqlmi1.73d19f36a420a.database.windows.net' 

USE MASTER 
CREATE AVAILABILITY GROUP [<DAGName>] 
WITH (DISTRIBUTED)  
    AVAILABILITY GROUP ON   
    N'<AGNameOnSQLServer>' WITH  
    ( 
      LISTENER_URL = 'TCP://<SQLServerIP>:5022', 
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
      FAILOVER_MODE = MANUAL, 
      SEEDING_MODE = AUTOMATIC, 
      SESSION_TIMEOUT = 20 
    ), 
    N'<AGNameOnSQLMI>' WITH 
    ( 
      LISTENER_URL = 'tcp://<ManagedInstanceFQDN>:5022;Server=[<ManagedInstanceName>];Database=[<DatabaseName>]', 
      AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
      FAILOVER_MODE = MANUAL, 
      SEEDING_MODE = AUTOMATIC 
    ); 
GO 

Verificare i gruppi di disponibilità

Usare il seguente script per elencare tutti i gruppi di disponibilità e i gruppi di disponibilità distribuiti nell'istanza di SQL Server. A questo punto, lo stato del gruppo di disponibilità deve essere connected, e lo stato dei gruppi di disponibilità distribuiti deve essere disconnected. Lo stato del gruppo di disponibilità distribuito passa a connected solo quando viene unito all’Istanza gestita di SQL.

-- Run on SQL Server
-- This will show that the availability group and distributed availability group have been created on SQL Server.
SELECT * FROM sys.availability_groups

In alternativa, è possibile usare SSMS Esplora oggetti per trovare gruppi di disponibilità e gruppi di disponibilità distribuiti. Espandere la cartella Disponibilità elevata Always On e quindi la cartella Gruppi di disponibilità.

Crea un collegamento

Infine, è possibile creare il collegamento. I comandi differiscono in base all'istanza primaria iniziale. Usare il comando New-AzSqlInstanceLink di PowerShell o az sql mi link create dell'interfaccia della riga di comando di Azure per creare il collegamento, ad esempio l'esempio di PowerShell in questa sezione. La creazione del collegamento da un’Istanza gestita di SQL primario non è attualmente supportata con l'interfaccia della riga di comando di Azure.

Se è necessario visualizzare tutti i collegamenti in un'istanza gestita, usare il comando Get-AzSqlInstanceLink di PowerShell o il comando az sql mi link show dell’interfaccia della riga di comando di Azure in Azure Cloud Shell.

#  Run in Azure Cloud Shell (select PowerShell console)
# =============================================================================
# POWERSHELL SCRIPT TO CREATE MANAGED INSTANCE LINK
# Instructs Managed Instance to join distributed availability group on SQL Server
# ===== Enter user variables here ====

# Enter your managed instance name – for example, "sqlmi1"
$ManagedInstanceName = "<ManagedInstanceName>"

# Enter the availability group name that was created on SQL Server
$AGNameOnSQLServer = "<AGNameOnSQLServer>"

# Enter the availability group name that was created on SQL Managed Instance
$AGNameOnSQLMI = "<AGNameOnSQLMI>"

# Enter the distributed availability group name that was created on SQL Server
$DAGName = "<DAGName>"

# Enter the database name that was placed in the availability group for replication
$DatabaseName = "<DatabaseName>"

# Enter the SQL Server IP
$SQLServerIP = "<SQLServerIP>"

# ==== Do not customize the following cmdlet ====

# Find out the resource group name
$ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName

# Build properly formatted connection endpoint
$SourceIP = "TCP://" + $SQLServerIP + ":5022"

# Create link on managed instance. Join distributed availability group on SQL Server.
New-AzSqlInstanceLink -ResourceGroupName $ResourceGroup -InstanceName $ManagedInstanceName -Name $DAGName |
-PrimaryAvailabilityGroupName $AGNameOnSQLServer -SecondaryAvailabilityGroupName $AGNameOnSQLMI |
-TargetDatabase $DatabaseName -SourceEndpoint $SourceIP

#  Run in Azure Cloud Shell (select PowerShell console) 
# ============================================================================= 
# POWERSHELL SCRIPT TO CREATE MANAGED INSTANCE LINK 
# Instructs Managed Instance to join distributed availability group on SQL Server 
# ===== Enter user variables here ====  

# Enter your managed instance name – for example, "sqlmi1" 
$ManagedInstanceName = "<ManagedInstanceName>" 

# Enter the availability group name that was created on SQL Server
$AGNameOnSQLServer = "<AGNameOnSQLServer>"

# Enter the availability group name that was created on SQL Managed Instance
$AGNameOnSQLMI = "<AGNameOnSQLMI>"

# Enter the distributed availability group name that was created on SQL Server 
$DAGName = "<DAGName>"  

# Enter the database name that was placed in the availability group for replication 
$DatabaseName = "<DatabaseName>"  

# Enter the SQL Server IP 
$SQLServerIP = "<SQLServerIP>" 

# Enter the Azure subscription ID 
$SubscriptionID = "<SubscriptionID>" 
 
# ==== Do not customize the following cmdlet ==== 

# Find out the resource group name 
$ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName 

# Build properly formatted connection endpoint 
$DestinationIP = "TCP://" + $SQLServerIP + ":5022"  

# Create Azure REST API request header 
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile 
$currentAzureContext = Get-AzContext 
$profileClient = New-Object Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient($azProfile) 
$token = $profileClient.AcquireAccessToken($currentAzureContext.Tenant.TenantId) 
$authToken = $token.AccessToken 
$headers = @{ } 
$headers.Add('Authorization', 'Bearer ' + $authToken)  

# Build Azure REST API URI 
$uri = "https://management.azure.com/subscriptions/"+$SubscriptionID+"/resourceGroups/"+$ResourceGroup+"/providers/Microsoft.Sql/managedInstances/"+$ManagedInstanceName+"/distributedAvailabilityGroups/"+$DAGName+"?api-version=2023-05-01-preview" 

# Build Azure REST API request body 
$body = "{ 
'properties': { 
    'Databases': [{ 
            'databaseName': '$DatabaseName' 
        } 
    ], 
    'PartnerEndpoint': '$DestinationIP', 
    'InstanceAvailabilityGroupName': '$AGNameOnSQLMI', 
    'PartnerAvailabilityGroupName': '$AGNameOnSQLServer', 
    'FailoverMode': 'Manual', 
    'SeedingMode': 'Automatic', 
    'InstanceLinkRole': 'Primary' 

}}" 

 
# Send link creation request to Azure REST API 
Invoke-RestMethod -Method PUT -Headers $headers -Uri $uri -ContentType 'application/json' -Body $body 

Il risultato di questa operazione è un indicatore orario dell'esecuzione corretta della richiesta di creazione di un collegamento.

Verificare il collegamento

Per verificare la connessione tra Istanza gestita di SQL e SQL Server, eseguire la query seguente in SQL Server. La connessione non sarà istantanea. L'avvio della DMV può richiedere fino a un minuto. Continuare ad aggiornare la DMV fino a quando la connessione non viene visualizzata come CONNECTED per la replica Istanza gestita di SQL.

-- Run on SQL Server
SELECT
    r.replica_server_name AS [Replica],
    r.endpoint_url AS [Endpoint],
    rs.connected_state_desc AS [Connected state],
    rs.last_connect_error_description AS [Last connection error],
    rs.last_connect_error_number AS [Last connection error No],
    rs.last_connect_error_timestamp AS [Last error timestamp]
FROM
    sys.dm_hadr_availability_replica_states rs
    JOIN sys.availability_replicas r
    ON rs.replica_id = r.replica_id

Dopo aver stabilito la connessione, Esplora oggetti in SSMS potrebbe inizialmente mostrare il database replicato nella replica secondaria in uno stato di Ripristino durante lo spostamento della fase di seeding iniziale e ripristina il backup completo del database. Dopo il ripristino del database, la replica deve recuperare lo stato sincronizzato dei due database. Il database non sarà più in Ripristino al termine del seeding iniziale. Il seeding di database di piccole dimensioni potrebbe essere sufficientemente veloce da non visualizzare lo stato di Ripristino iniziale in SSMS.

Eseguire il primo backup del log delle transazioni

Se SQL Server è il server primario iniziale, è importante eseguire il primo backup del log delle transazioni in SQL Server dopo il completamento del seeding iniziale, quando il database non è più nello stato Ripristino… in Istanza gestita di SQL di Azure. Eseguire quindi regolarmente i backup del log delle transazioni di SQL Server per ridurre al minimo l'aumento eccessivo del log mentre SQL Server è nel ruolo primario.

Se Istanza gestita di SQL è il database primario, non è necessario eseguire alcuna azione perché Istanza gestita di SQL di Azure esegue automaticamente i backup del log.

Eliminare un collegamento

Se si vuole eliminare il collegamento perché non è più necessario o perché è in uno stato irreparabile e deve essere ricreato, è possibile farlo con PowerShell e T-SQL.

Per prima cosa, usare il comando Remove-AzSqlInstanceLink di PowerShell per eliminare il collegamento come nell'esempio seguente:

Remove-AzSqlInstanceLink -ResourceGroupName $ResourceGroup -InstanceName $managedInstanceName -Name $DAGName -Force 

Eseguire quindi il seguente script T-SQL per creare il gruppo di disponibilità distribuito in SQL Server. Sostituire <DAGName> con il nome del gruppo di disponibilità distribuito usato per creare il collegamento:

USE MASTER 
GO 

DROP AVAILABILITY GROUP <DAGName>  
GO 

Infine, facoltativamente, è possibile rimuovere il gruppo di disponibilità se non è più disponibile. A tale scopo, sostituire <AGName> con il nome del gruppo di disponibilità e quindi eseguirlo nella rispettiva istanza:

DROP AVAILABILITY GROUP <AGName>  
GO 

Risoluzione dei problemi

Se viene visualizzato un messaggio di errore quando si crea il collegamento, esaminare il messaggio di errore nella finestra di output della query per altre informazioni.

Contenuto correlato

Per usare il collegamento:

• Allestire l’ambiente per il collegamento a Istanza gestita
• Configurare il collegamento tra SQL Server e Istanza gestita di SQL con SSMS
• Eseguire il failover con il collegamento
• Eseguire la migrazione con il collegamento
• Procedure consigliate per la gestione del collegamento

Per altre informazioni sul collegamento:

• Panoramica del collegamento a Istanza gestita
• Ripristino di emergenza con il collegamento all'istanza gestita

Per altri scenari di replica e migrazione, prendere in considerazione:

• Replica transazionale con Istanza gestita di SQL
• Servizio di riproduzione log (LRS)