Configurer la liaison avec des scripts – Azure SQL Managed Instance

S’applique à : Azure SQL Managed Instance

Cet article vous explique comment configurer une liaison entre SQL Server et Azure SQL Managed Instance avec Transact-SQL et PowerShell ou des scripts Azure CLI. Grâce à cette liaison, les bases de données de votre système principal initial sont répliquées sur votre réplica secondaire en quasi-temps réel.

Une fois la liaison créée, vous pouvez basculer sur votre réplica secondaire à des fins de migration ou de récupération d’urgence.

Vue d’ensemble

Utilisez la fonctionnalité de liaison pour répliquer les bases de données de votre réplica principal initial vers votre réplica secondaire. Pour SQL Server 2022, le réplica principal initial peut être soit SQL Server, soit Azure SQL Managed Instance. Pour SQL Server 2019 et versions antérieures, le réplica principal initial doit être SQL Server. Une fois la liaison configurée, la base de données du réplica principal initial sont répliquées vers le réplica secondaire.

Vous pouvez choisir de laisser la liaison en place pour une réplication continue des données dans un environnement hybride entre le réplica principal et le réplica secondaire, ou vous pouvez basculer la base de données vers le réplica secondaire, pour migrer vers Azure ou pour une récupération d’urgence. Pour SQL Server 2019 et les versions antérieures, le basculement vers Azure SQL Managed Instance rompt la liaison et le retour n’est pas pris en charge. Avec SQL Server 2022, vous avez la possibilité de conserver la liaison et de basculer entre les deux réplicas.

Si vous prévoyez d’utiliser votre Managed Instance secondaire uniquement pour la récupération d’urgence, vous pouvez réduire les coûts de licence en activant l’avantage du basculement hybride.

Suivez les instructions de cet article pour configurer manuellement la liaison entre SQL Server et Azure SQL Managed Instance. Une fois la liaison créée, votre base de données source reçoit une copie en lecture seule sur votre réplica secondaire cible.

Prérequis

Pour répliquer vos bases de données, vous devez disposer des conditions préalables suivantes :

• Un abonnement Azure actif. Si vous n’en avez pas, créez un compte gratuit.
• Une version prise en charge de SQL Server avec la mise à jour de service requise installée.
• Azure SQL Managed Instance. Prise en main si ce n’est déjà fait.
• Le module PowerShell Az.SQL version 3.9.0 ou supérieure ou Azure CLI version 2.47.0 ou supérieure. Vous pouvez également utiliser Azure Cloud Shell en ligne à partir du serveur web pour exécuter les commandes, car il est toujours à jour avec les versions de module les plus récentes.
• Un environnement correctement préparé.

Tenez compte des éléments suivants :

• La fonctionnalité de liaison prend en charge une base de données par liaison. Pour répliquer plusieurs bases de données sur une instance, créez une liaison pour chaque base de données individuelle. Par exemple, pour répliquer 10 bases de données sur SQL Managed Instance, créez 10 liaisons individuelles.
• Le classement entre SQL Server et SQL Managed Instance doit être identique. Une incohérence dans le classement peut entraîner une incohérence de la casse du nom de serveur, et empêcher la connexion de SQL Server à SQL Managed Instance.
• L’erreur 1475 sur votre SQL Server principal initial indique que vous devez démarrer une nouvelle chaîne de sauvegarde en créant une sauvegarde complète sans l’option COPY ONLY.
• Pour établir un lien ou basculer, entre SQL Managed Instance et SQL Server 2022, votre instance managée doit être configurée avec la stratégie de mise à jour SQL Server 2022. La réplication et le basculement des données de SQL Managed Instance vers SQL Server 2022 ne sont pas pris en charge par les instances configurées avec la stratégie de mise à jour permanente.
• Bien que vous puissiez établir un lien entre SQL Server 2022 et une instance managée SQL configurée avec la stratégie de mise à jour permanente, après le basculement vers SQL Managed Instance, vous ne pourrez plus répliquer des données ou effectuer une restauration automatique vers SQL Server 2022.

autorisations

Pour SQL Server, vous devez disposer des autorisations sysadmin.

Pour Azure SQL Managed Instance, vous devez être membre du Collaborateur SQL Managed Instance ou disposer des autorisations suivantes pour un rôle personnalisé :

Terminologie et conventions d’affectation de noms

Lors de l’exécution de scripts à partir de ce guide de l’utilisateur, il est important de ne pas confondre les noms de SQL Server et de SQL Managed Instance avec leurs noms de domaine complets. Le tableau suivant explique ce que les différents noms représentent exactement, et comment obtenir leurs valeurs :

¹ La configuration d’Azure SQL Managed Instance comme principal initial est prise en charge à partir de SQL Server 2022 CU10.

Configurer la récupération et la sauvegarde de base de données

Si SQL Server est votre réplica principal initial, les bases de données qui seront répliquées via la liaison doivent être dans le mode de récupération complète et disposer d’au moins une sauvegarde. Azure SQL Managed Instance effectue des sauvegardes automatiquement. Il est alors recommandé d’ignorer cette étape si SQL Managed Instance est votre réplica principal initial.

Exécutez le code suivant sur SQL Server pour toutes les bases de données que vous souhaitez répliquer. Remplacez <DatabaseName> par le nom de votre base de données.

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

Pour plus d’informations, consultez Créer une sauvegarde complète de base de données.

Établir l’approbation entre des instances

Tout d’abord, vous devez établir la confiance entre les deux instances et sécuriser les points de terminaison utilisés pour communiquer et chiffrer les données sur le réseau. Les groupes de disponibilité distribués utilisent le point de terminaison de mise en miroir de bases de données du groupe de disponibilité existant, au lieu d’avoir leur propre point de terminaison dédié. À ce titre, la sécurité et la confiance doivent être configurées entre les deux instances par l’intermédiaire du point de terminaison de mise en miroir de la base de données du groupe de disponibilité.

L’approbation basée sur un certificat est le seul moyen pris en charge pour sécuriser les points de terminaison de mise en miroir de bases de données pour SQL Server et SQL Managed Instance. Si vous avez des groupes de disponibilité qui utilisent l’authentification Windows, vous devez ajouter l’approbation basée sur un certificat au point de terminaison de mise en miroir existant en guise d’option d’authentification secondaire. Vous pouvez le faire à l’aide de l’instruction ALTER ENDPOINT, comme expliqué plus loin dans cet article.

La liste suivante présente une vue d’ensemble du processus de sécurisation des points de terminaison de la mise en miroir des bases de données pour SQL Server et SQL Managed Instance :

1. Générez un certificat sur SQL Server et obtenez sa clé publique.
2. Obtenez la clé publique du certificat SQL Managed Instance.
3. Échangez les clés publiques entre SQL Server et SQL Managed Instance.
4. Importer des clés d’autorité de certification racine approuvée par Azure dans SQL Server

Les sections suivantes décrivent ces étapes en détail.

Créer un certificat sur SQL Server et importer sa clé publique vers SQL Managed Instance

Tout d’abord, créez la clé principale de la base de données dans la base de données master, si elle n’existe pas déjà. Insérez votre mot de passe à la place de <strong_password> dans le script suivant et conservez-le dans un lieu confidentiel et sûr. Exécutez ce script T-SQL sur 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

Ensuite, générez un certificat d’authentification sur SQL Server. Dans le script suivant, remplacez :

• @cert_expiry_date par la date d’expiration du certificat souhaitée (date future).

Enregistrez cette date et définissez un rappel pour la rotation (mise à jour) du certificat du serveur SQL avant sa date d’expiration afin d’assurer l’opération continue de la liaison.

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

Ensuite, utilisez la requête T-SQL suivante sur SQL Server pour vérifier que le certificat a été créé :

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

Dans les résultats de la requête, vous verrez que le certificat a été chiffré avec la clé principale.

Vous pouvez maintenant obtenir la clé publique du certificat généré sur SQL Server :

-- 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;

Enregistrez les valeurs de SQLServerCertName et de SQLServerPublicKey à partir de la production, car vous en aurez besoin à l’étape suivante lorsque vous importerez le certificat.

Commencez par vérifier que vous êtes connecté à Azure et que vous avez sélectionné l’abonnement dans lequel votre instance gérée est hébergée. La sélection de l’abonnement approprié est particulièrement importante si vous avez plusieurs abonnements Azure sur votre compte.

Remplacez <SubscriptionID> par l’identifiant de votre abonnement 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

Utilisez ensuite la commande New-AzSqlInstanceServerTrustCertificate PowerShell ou Créer az sql mi partner-cert Azure CLI pour charger la clé publique du certificat d’authentification de SQL Server vers Azure, comme l’exemple PowerShell suivant.

Renseignez les informations utilisateur nécessaires, copiez-les, collez-les, puis exécutez le script. Remplacez :

• <SQLServerPublicKey> par la partie publique du certificat SQL Server au format binaire, que vous avez enregistré à l’étape précédente. Il s’agit d’une valeur de chaîne longue qui commence par 0x.
• <SQLServerCertName> par le nom de certificat SQL Server que vous avez enregistré à l’étape précédente.
• <ManagedInstanceName> par le nom abrégé de votre instance managée.

# 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 

Le résultat de cette opération est un récapitulatif du certificat SQL Server chargé dans Azure.

Si vous avez besoin de voir tous les certificats SQL Server chargés dans une Managed Instance, utilisez la commande Get-AzSqlInstanceServerTrustCertificate PowerShell ou Lister az sql mi partner-cert Azure CLI dans Azure Cloud Shell. Pour supprimer un certificat SQL Server chargé dans une SQL Managed Instance, utilisez la commande Remove-AzSqlInstanceServerTrustCertificate PowerShell ou Supprimer az sql mi partner-cert Azure CLI dans Azure Cloud Shell.

Obtenir la clé publique du certificat à partir de SQL Managed Instance et l’importer dans SQL Server

Le certificat destiné à sécuriser le point de terminaison de liaison est généré automatiquement sur Azure SQL Managed Instance. Obtenez la clé publique du certificat à partir de SQL Managed Instance et importez-la dans SQL Server en utilisant la commande Get-AzSqlInstanceEndpointCertificate PowerShell ou Afficher az sql mi endpoint-cert Azure CLI comme l’exemple PowerShell suivant.

Exécutez le script suivant. Remplacez :

• <SubscriptionID> par votre ID d’abonnement Azure.
• <ManagedInstanceName> par le nom abrégé de votre instance managée.

# 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   

Copiez l’intégralité de la sortie PublicKey (commence par 0x), car vous en aurez besoin à l’étape suivante.

Si vous rencontrez des problèmes pour copier-coller la PublicKey, vous pouvez aussi exécuter la commande T-SQL EXEC sp_get_endpoint_certificate 4 sur l’instance managée pour obtenir sa clé publique pour le point de terminaison de liaison.

Ensuite, importez la clé publique obtenue du certificat de sécurité d’instance managée dans SQL Server. Exécutez la requête suivante sur SQL Server pour créer le certificat de point de terminaison MI. Remplacez :

• <ManagedInstanceFQDN> par le nom de domaine complet de l’instance gérée.
• <PublicKey> par la valeur PublicKey obtenue à l’étape précédente (d’Azure Cloud Shell, commençant par 0x). Vous n’avez pas besoin d’utiliser des guillemets.

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

Importer des clés d’autorité de certification racine approuvée par Azure dans SQL Server

L’importation de clés de certificat racine publiques des autorités de certification Microsoft et DigiCert dans SQL Server est requise pour que votre SQL Server approuve les certificats émis par Azure pour les domaines database.windows.net.

Commencez par importer le certificat d’autorité racine d’infrastructure de clés publiques (PKI) Microsoft sur 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

Ensuite, importez le certificat d’autorité racine d’infrastructure de clés publiques (PKI) DigiCert sur 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

Pour finir, vérifiez tous les certificats créés à l’aide de la vue de gestion dynamique (DMV) suivante :

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

Valider le certificat

Une fois que vous avez créé les certificats, vérifiez que le certificat de point de terminaison MI est configuré correctement.

Tout d’abord, déterminez le certificate_id du certificat MI exporté en remplaçant la valeur de <ManagedInstanceFQDN> puis en exécutant la requête suivante sur 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 

Ensuite, validez le certificat en remplaçant la valeur de <certificate_id> du résultat de la requête précédente, puis en exécutant la requête suivante sur SQL Server :

-- Run on SQL Server 

USE MASTER 
GO 

EXEC sp_validate_certificate_ca_chain <certificate_id> 
GO 

Une réponse de Commands completed successfully. Completion time: … indique que le certificat de point de terminaison MI a été correctement validé.

Si une erreur se produit, veuillez exclure le certificat et suivez les étapes de la section Obtenir la clé publique du certificat de SQL Managed Instance et l’importer dans le SQL Server pour réimporter le certificat.

Pour exclure le certificat, exécutez la requête suivante sur SQL Server :

-- Run on SQL Server 

USE MASTER 
GO 

DROP CERTIFICATE [<ManagedInstanceFQDN>] 
GO 

Sécuriser le point de terminaison de mise en miroir de bases de données

Si vous ne disposez pas d’un groupe de disponibilité existant ou d’un point de terminaison de mise en miroir de la base de données sur le SQL Server, l’étape suivante consiste à créer un point de terminaison de mise en miroir de la base de données sur SQL Server et à le sécuriser à l’aide du certificat SQL Server généré précédemment. Si vous disposez d’un groupe de disponibilité ou d’un point de terminaison de mise en miroir existant, passez à la section Modifier un point de terminaison existant.

Créer et sécuriser le point de terminaison de mise en miroir de bases de données sur SQL Server

Pour vérifier que vous n’avez pas de point de terminaison de mise en miroir de bases de données, utilisez le script suivant :

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

Si la requête précédente montre qu’il n’existe pas de point de terminaison de mise en miroir de bases de données, exécutez le script suivant sur SQL Server pour obtenir le nom du certificat SQL Server généré précédemment.

-- 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'

Enregistrez SQLServerCertName à partir de la sortie, car vous en aurez besoin à l’étape suivante.

Utilisez le script suivant pour créer un nouveau point de terminaison de mise en miroir de la base de données sur le port 5022 et sécuriser le point de terminaison avec le certificat SQL Server. Remplacez :

• <SQL_SERVER_CERTIFICATE> par le nom SQLServerCertName obtenu à l’étape précédente.

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

Confirmez que le point de terminaison de mise en miroir a été créé en exécutant le script suivant sur 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

La colonne state_desc du point de terminaison créé avec succès devrait indiquer STARTED.

Le nouveau point de terminaison de mise en miroir a été créé avec l’authentification par certificat et le chiffrement AES activés.

Modifier un point de terminaison existant

Si vous utilisez des groupes de disponibilité existants pour la liaison, ou s’il existe déjà un point de terminaison de mise en miroir de bases de données, commencez par vérifier qu’il remplit les conditions obligatoires suivantes pour la liaison :

• Le type doit être DATABASE_MIRRORING.
• L’authentification de la connexion doit être CERTIFICATE.
• Le chiffrement doit être activé.
• L’algorithme de chiffrement doit être AES.

Exécutez la requête suivante sur SQL Server pour afficher les détails d’un point de terminaison de mise en miroir de bases de données existant :

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

Si la sortie indique que le point de terminaison DATABASE_MIRRORING existant connection_auth_desc n’est pas CERTIFICATE, ou que encryption_algorthm_desc n’est pas AES, le point de terminaison doit être modifié pour répondre aux exigences.

Sur SQL Server, le même point de terminaison de mise en miroir de bases de données est utilisé à la fois pour les groupes de disponibilité et pour les groupes de disponibilité distribués. Si votre point de terminaison connection_auth_desc est NTLM (authentification Windows) ou KERBEROS, et que vous avez besoin de l’authentification Windows pour un groupe de disponibilité existant, il est possible de modifier le point de terminaison de façon à utiliser plusieurs méthodes d’authentification en basculant l’option d’authentification sur NEGOTIATE CERTIFICATE. Cette modification permet au groupe de disponibilité existant d’utiliser l’authentification Windows, tout en utilisant l’authentification par certificat pour SQL Managed Instance.

De même, si le chiffrement n’inclut pas AES et que vous avez besoin d’un chiffrement RC4, il est possible de modifier le point de terminaison pour utiliser les deux algorithmes. Pour plus d’informations sur les options possibles pour la modification des points de terminaison, consultez la page de documentation relative à sys.database_mirroring_endpoints.

Le script suivant illustre comment modifier votre point de terminaison de mise en miroir de bases de données existant sur SQL Server. Remplacez :

• <YourExistingEndpointName> par le nom de votre point de terminaison existant.
• <SQLServerCertName> par le nom du certificat SQL Server généré (obtenu dans l’une des étapes précédentes ci-dessus).

Selon votre configuration spécifique, vous devrez peut-être personnaliser davantage le script. Vous pouvez également utiliser SELECT * FROM sys.certificates pour obtenir le nom du certificat créé sur 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

Après avoir exécuté la requête de point de terminaison ALTER et défini le mode d’authentification double sur Windows et Certificat, utilisez à nouveau cette requête sur SQL Server pour afficher les détails du point de terminaison de mise en miroir de bases de données :

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

Vous avez correctement modifié votre point de terminaison de mise en miroir de bases de données pour une liaison SQL Managed Instance.

Créer un groupe de disponibilité sur SQL Server

Si vous ne disposez pas de groupe de disponibilité existant, l’étape suivante consiste à en créer un sur SQL Server, quel que soit le réplica principal initial.

Les commandes pour créer le groupe de disponibilité sont différentes si votre SQL Managed Instance est le réplica principal initial, ce qui n’est pris en charge qu’à partir de SQL Server 2022 CU10.

Bien qu’il soit possible d’établir plusieurs liaisons pour la même base de données, la liaison ne prend en charge que la réplication d’une seule base de données par liaison. Si vous souhaitez créer plusieurs liaisons pour la même base de données, utilisez le même groupe de disponibilité pour toutes les liaisons, mais créez ensuite un nouveau groupe de disponibilité distribué pour chaque liaison de base de données entre SQL Server et SQL Managed Instance.

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

Vérifier les groupes de disponibilité

Utilisez le script suivant pour lister tous les groupes de disponibilité et groupes de disponibilité distribués sur l’instance de SQL Server. À ce stade, l’état de votre groupe de disponibilité doit être connected, et l’état de vos groupes de disponibilité distribués doit être disconnected. L’état du groupe de disponibilité distribué passe à connected uniquement une fois qu’il est joint à SQL Managed Instance.

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

Vous pouvez également utiliser l’Explorateur d’objets SSMS pour rechercher des groupes de disponibilité et des groupes de disponibilité distribués. Développez le dossier Haute disponibilité AlwaysOn puis le dossier Groupes de disponibilité.

Créer un lien

Enfin, vous pouvez créer la liaison. Les commandes diffèrent en fonction de l’instance principale initiale. Utilisez la commande New-AzSqlInstanceLink PowerShell ou Créer la liaison az sql mi Azure CLI pour créer la liaison, comme l’exemple PowerShell dans cette section. La création d’une liaison à partir d’une SQL Managed Instance principale n’est pas actuellement prise en charge par Azure CLI.

Si vous avez besoin d’afficher toutes les liaisons d’une Managed Instance, utilisez la commande Get-AzSqlInstanceLink PowerShell ou Afficher la liaison az sql mi Azure CLI dans 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 

Le résultat de cette opération est un horodatage de l’exécution réussie de la demande de création de liaison.

Vérifier la liaison

Pour vérifier la connexion entre SQL Managed Instance et SQL Server, exécutez la requête suivante sur SQL Server. La connexion ne sera pas instantanée. La DMV peut prendre jusqu’à une minute pour commencer à montrer une connexion réussie. Continuez à actualiser la DMV jusqu’à ce que la connexion apparaisse comme CONNECTED pour le réplica SQL Managed Instance.

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

Une fois la connexion établie, Explorateur d’objets dans SSMS peut initialement afficher la base de données répliquée sur le réplica secondaire dans un état de Restauration, car la phase d’essaimage initiale déplace et restaure la sauvegarde complète de la base de données. Une fois la base de données restaurée, la réplication doit combler le retard afin que les deux bases de données soient dans un état synchronisé. La base de données n’est plus En cours de restauration une fois l’amorçage initial terminé. Il est possible que l’amorçage de petites bases de données soit suffisamment rapide pour que l’état En cours de restauration initial ne s’affiche pas dans SSMS.

Effectuez la première sauvegarde du journal des transactions

Si SQL Server est votre principal initial, il est important d’effectuer la première sauvegarde du journal des transactions sur SQL Server une fois l’amorçage initial terminé, lorsque la base de données n’est plus dans l’état Restauration... sur Azure SQL Managed Instance. Prenez ensuite régulièrement les sauvegardes du journal des transactions SQL Server pour réduire la croissance excessive des journaux pendant que SQL Server est dans le rôle principal.

Si SQL Managed Instance est votre instance principale, vous n’avez pas besoin d’effectuer d’action, car Azure SQL Managed Instance effectue automatiquement des sauvegardes de fichiers journaux.

Supprimer une liaison

Si vous souhaitez supprimer la liaison, soit parce qu’elle n’est plus nécessaire, soit parce qu’elle est dans un état irréparable et doit être recréée, vous pouvez le faire avec PowerShell et T-SQL.

Tout d’abord, utilisez la commande PowerShell Remove-AzSqlInstanceLink pour supprimer la liaison, comme dans l’exemple suivant :

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

Exécutez ensuite le script T-SQL suivant sur SQL Server pour supprimer le groupe de disponibilité distribué. Remplacez <DAGName> par le nom du groupe de disponibilité distribué utilisé pour créer la liaison :

USE MASTER 
GO 

DROP AVAILABILITY GROUP <DAGName>  
GO 

Enfin, si vous le souhaitez, vous pouvez supprimer le groupe de disponibilité si vous n’en avez plus l’utilité. Pour ce faire, remplacez le <AGName> par le nom du groupe de disponibilité, puis exécutez-le sur l’instance respective :

DROP AVAILABILITY GROUP <AGName>  
GO 

Résolution des problèmes

En cas de message d’erreur lors de la création de la liaison, consultez le message d’erreur dans la fenêtre sortie de la requête pour en savoir plus.

Contenu connexe

Pour utiliser la liaison :

• Préparer un environnement pour une liaison Managed Instance
• Configurer la liaison entre SQL Server et SQL Managed Instance avec SSMS
• Basculez le lien
• Migrer avec la liaison
• Meilleures pratiques pour préserver la liaison

Pour en savoir plus sur la liaison :

• Liaison Managed Instance – Vue d’ensemble
• Récupération d’urgence avec la liaison Managed Instance

Pour d’autres scénarios de réplication et de migration, considérez :

• Réplication transactionnelle avec SQL Managed Instance
• Service LRS