Настройка ссылки с помощью скриптов — Управляемый экземпляр SQL Azure

Область применения: Управляемый экземпляр SQL Azure

В этой статье описано, как настроить связь между SQL Server и Управляемый экземпляр SQL Azure с помощью скриптов Transact-SQL и PowerShell или Azure CLI. С помощью ссылки базы данных из исходного первичного источника реплицируются в вторичную реплику практически в режиме реального времени.

После создания ссылки можно выполнить отработку отказа на вторичную реплику для миграции или аварийного восстановления.

Обзор

Используйте функцию ссылки, чтобы реплицировать базы данных из исходного источника в вторичную реплику. Для SQL Server 2022 исходный основной может быть SQL Server или Управляемый экземпляр SQL Azure. Для SQL Server 2019 и более ранних версий исходный основной должен быть SQL Server. После настройки ссылки база данных из исходного первичного источника реплицируется в вторичную реплику.

Вы можете оставить ссылку для непрерывной репликации данных в гибридной среде между первичной и вторичной репликой или выполнить отработку отказа базы данных на вторичную реплику, перенести в Azure или аварийное восстановление. Для SQL Server 2019 и более ранних версий отработка отказа Управляемый экземпляр SQL Azure разрывает ссылку и не поддерживается отработка отказа. При использовании SQL Server 2022 у вас есть возможность поддерживать ссылку и выполнять отработку отказа между двумя репликами . Эта функция в настоящее время находится в предварительной версии.

Если вы планируете использовать вторичный управляемый экземпляр только для аварийного восстановления, вы можете сэкономить на затратах на лицензирование, активировав преимущество гибридной отработки отказа.

Используйте инструкции, описанные в этой статье, чтобы вручную настроить связь между SQL Server и Управляемый экземпляр SQL Azure. После создания ссылки база данных-источник получает копию только для чтения в целевой вторичной реплике.

Предварительные требования

Чтобы реплицировать базы данных, вам потребуется следующее:

• Активная подписка Azure. Если ее нет, создайте бесплатную учетную запись.
• Поддерживаемая версия SQL Server с установленным необходимым обновлением службы.
• Управляемый экземпляр SQL Azure Руководство по началу работы (при необходимости).
• Модуль PowerShell Az.SQL 3.9.0 или более поздней версии или Azure CLI 2.47.0 или более поздней версии. Или предпочтительнее использовать Azure Cloud Shell из веб-браузера для выполнения команд, так как они всегда обновляются с использованием последних версий модулей.
• Правильно подготовленная среда.

Рассмотрим следующий пример.

• Функция связи поддерживает одну базу данных для каждого канала. Чтобы реплицировать несколько баз данных в экземпляре, создайте ссылку для каждой отдельной базы данных. Например, чтобы реплицировать 10 баз данных в Управляемый экземпляр SQL, создайте 10 отдельных связей.
• Параметры сортировки между SQL Server и Управляемым экземпляром SQL должны быть одинаковыми. Несоответствие параметров сортировки может привести к несоответствию регистра имен серверов и помешать успешному подключению SQL Server к Управляемому экземпляру SQL.
• Ошибка 1475 на исходном первичном сервере SQL Server указывает, что необходимо запустить новую цепочку резервных копий, создав полную резервную копию без COPY ONLY параметра.
• Чтобы установить ссылку или выполнить отработку отказа, с Управляемый экземпляр SQL до SQL Server 2022 управляемый экземпляр необходимо настроить с помощью политики обновления SQL Server 2022. Репликация данных и отработка отказа из Управляемый экземпляр SQL в SQL Server 2022 не поддерживается экземплярами, настроенными с помощью политики обновления Always-up.
• Хотя вы можете установить ссылку из SQL Server 2022 на управляемый экземпляр SQL, настроенный с помощью политики обновления Always-up-up, после отработки отказа на Управляемый экземпляр SQL, вы больше не сможете реплицировать данные или выполнить отработку отказа в SQL Server 2022.

Разрешения

Для SQL Server у вас должны быть разрешения sysadmin .

Для Управляемый экземпляр SQL Azure вы должны быть членом участника Управляемый экземпляр SQL или иметь следующие разрешения пользовательской роли:

Терминология и соглашения об именовании

При выполнении сценариев из этого руководства пользователя важно не перепутать имена экземпляра SQL Server и Управляемого экземпляра SQL с их полными доменными именами (FQDN). В следующей таблице объясняется, что именно представляют разные имена и как получить их значения.

¹ Настройка Управляемый экземпляр SQL Azure в качестве исходного основного в настоящее время находится в предварительной версии и поддерживается только с SQL Server 2022 CU10.

Настройка восстановления и резервного копирования базы данных

Если SQL Server является исходным источником, базы данных, которые будут реплицированы по ссылке, должны находиться в полной модели восстановления и иметь по крайней мере одну резервную копию. Так как Управляемый экземпляр SQL Azure автоматически выполняет резервное копирование, пропустите этот шаг, если Управляемый экземпляр SQL является основным. первичный

Выполните следующий код в SQL Server для всех баз данных, которые вы хотите реплицировать. Замените <DatabaseName> фактическим именем базы данных.

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

Дополнительные сведения см. в статье "Создание полной резервной копии базы данных".

Установка отношений доверия между экземплярами

Сначала необходимо установить доверие между двумя экземплярами и защитить конечные точки, используемые для обмена данными и шифрования данных в сети. Распределенные группы доступности используют существующую конечную точку зеркального отображения базы данных группы доступности, а не собственную выделенную конечную точку. Таким образом, необходимо настроить безопасность и доверие между двумя экземплярами через конечную точку зеркального отображения базы данных группы доступности.

Доверие на основе сертификатов — единственный поддерживаемый способ защиты конечных точек зеркального отображения базы данных для SQL Server и Управляемый экземпляр SQL. Если у вас есть группы доступности, использующие проверку подлинности Windows, вам необходимо добавить отношение доверия на основе сертификата к существующей конечной точке зеркального отображения в качестве вторичного варианта проверки подлинности. Это можно сделать с помощью инструкции ALTER ENDPOINT , как показано далее в этой статье.

Ниже приведен обзор процесса защиты конечных точек зеркального отображения базы данных для SQL Server и Управляемый экземпляр SQL.

1. Создайте сертификат в SQL Server и получите соответствующий открытый ключ.
2. Получите открытый ключ сертификата Управляемого экземпляра SQL.
3. Обеспечьте обмен открытыми ключами между SQL Server и Управляемым экземпляром SQL.
4. Импорт ключей доверенного корневого центра сертификации Azure в SQL Server

В следующих разделах подробно описаны эти действия.

Создание сертификата в SQL Server и импорт его открытого ключа в Управляемый экземпляр

Сначала создайте главный ключ базы данных в master базе данных, если он еще не присутствует. Вставьте пароль вместо следующего скрипта <strong_password> и сохраните его в конфиденциальном и безопасном месте. Запустите этот скрипт T-SQL в 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

Затем создайте сертификат проверки подлинности в SQL Server. В следующем скрипте замените следующее:

• @cert_expiry_date с требуемой датой окончания срока действия сертификата (дата будущего).

Запишите эту дату и задайте напоминание о смене (обновлении) сертификата SQL Server до даты окончания срока действия, чтобы обеспечить непрерывную работу ссылки.

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

Затем используйте следующий запрос T-SQL в SQL Server, чтобы проверить, был создан сертификат:

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

В результатах запроса вы найдете сертификат и увидите, что он зашифрован с помощью главного ключа.

Теперь вы можете получить открытый ключ созданного сертификата в 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;

Сохраните значения и SQLServerPublicKey из выходных SQLServerCertName данных, так как при импорте сертификата потребуется следующее действие.

Сначала убедитесь, что вы вошли в Azure и выбрали подписку, в которой размещен управляемый экземпляр. Выбор правильной подписки особенно важен, если у вас несколько подписок Azure на вашей учетной записи.

Замените <SubscriptionID> идентификатором своей подписки 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

Затем используйте команду New-AzSqlInstanceServerTrustCertificate PowerShell или az sql mi partner-cert, чтобы отправить открытый ключ сертификата проверки подлинности из SQL Server в Azure, например следующий пример PowerShell.

Заполните необходимые сведения о пользователе, скопируйте его, вставьте его, а затем запустите скрипт. Замена:

• <SQLServerPublicKey> с общедоступной частью сертификата SQL Server в двоичном формате, который вы записали на предыдущем шаге. Это длинное строковое значение, начинающееся с 0x.
• <SQLServerCertName> с именем сертификата SQL Server, записанным на предыдущем шаге.
• <ManagedInstanceName> коротким именем управляемого экземпляра;

# 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 

Результатом этой операции является сводка отправленного сертификата SQL Server в Azure.

Если вам нужно просмотреть все сертификаты SQL Server, отправленные в управляемый экземпляр, используйте команду Get-AzSqlInstanceServerTrustCertificate PowerShell или az sql mi partner-cert list Azure CLI в Azure Cloud Shell. Чтобы удалить сертификат SQL Server, отправленный в управляемый экземпляр SQL, используйте команду Remove-AzSqlInstanceServerTrustCertificate PowerShell или az sql mi partner-cert delete Azure CLI в Azure Cloud Shell.

Получение открытого ключа сертификата из Управляемого экземпляра SQL и его импорт в SQL Server

Сертификат для защиты конечной точки ссылки автоматически создается на Управляемый экземпляр SQL Azure. Получите открытый ключ сертификата из Управляемый экземпляр SQL и импортируйте его в SQL Server с помощью команды Get-AzSqlInstanceEndpointCertificate PowerShell или az sql mi endpoint-cert, например следующую команду PowerShell.

Запустите указанный ниже скрипт. Замена:

• <SubscriptionID> идентификатором своей подписки Azure;
• <ManagedInstanceName> коротким именем управляемого экземпляра;

# 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   

Скопируйте все выходные данные PublicKey (начинается с 0x) по мере его использования на следующем шаге.

Кроме того, при возникновении проблем при копировании общедоступного ключа можно также запустить команду EXEC sp_get_endpoint_certificate 4 T-SQL в управляемом экземпляре, чтобы получить его открытый ключ для конечной точки ссылки.

Затем импортируйте полученный открытый ключ сертификата безопасности управляемого экземпляра в SQL Server. Выполните следующий запрос на SQL Server. Замена:

• <ManagedInstanceFQDN> с полным доменным именем управляемого экземпляра.
• <PublicKey> значение PublicKey, полученное на предыдущем шаге (начиная с Azure Cloud Shell 0x). Вам не нужно использовать кавычки.

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

Импорт ключей доверенного корневого центра сертификации Azure в SQL Server

Импорт ключей открытых корневых сертификатов центра сертификации Майкрософт и DigiCert в SQL Server требуется для того, чтобы SQL Server доверял сертификатам, выданным Azure для database.windows.net доменов.

Сначала импортируйте сертификат корневого центра Microsoft PKI в 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

Затем импортируйте сертификат корневого центра DigiCert PKI в 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

Наконец, проверьте все созданные сертификаты с помощью следующего динамического административного представления.

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

Защита конечной точки зеркального отображения базы данных

Если у вас нет существующей группы доступности или конечной точки зеркального отображения базы данных на SQL Server, необходимо создать конечную точку зеркального отображения базы данных на SQL Server и защитить ее с помощью ранее созданного сертификата SQL Server. Если у вас есть существующую группу доступности или конечную точку зеркального отображения, перейдите к разделу Alter a existing endpoint .

Создание и защита конечной точки зеркального отображения базы данных в SQL Server

Чтобы убедиться, что конечная точка зеркального отображения базы данных не создана, используйте следующий скрипт.

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

Если предыдущий запрос не отображает существующую конечную точку зеркального отображения базы данных, выполните следующий сценарий на SQL Server, чтобы получить имя ранее созданного сертификата 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'
SELECT @sqlserver_certificate_name as 'SQLServerCertName'

Сохраните SQLServerCertName из выходных данных, так как вам потребуется на следующем шаге.

Используйте следующий сценарий, чтобы создать новую конечную точку зеркального отображения базы данных через порт 5022 и защитить конечную точку с помощью сертификата SQL Server. Замена:

• <SQL_SERVER_CERTIFICATE> с именем SQLServerCertName, полученным на предыдущем шаге.

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

Чтобы убедиться, что конечная точка зеркального отображения создана, выполните следующий скрипт в 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

Успешно созданная конечная точка state_desc столбца должна иметь состояние STARTED.

Новая конечная точка зеркального отображения была создана с проверкой подлинности на основе сертификата, и для нее было включено шифрование AES.

Изменение существующей конечной точки

Если вы используете существующие группы доступности для функции связи или существующая конечная точка зеркального отображения базы данных существует, сначала убедитесь, что она удовлетворяет следующим обязательным условиям для функции связи.

• Тип — DATABASE_MIRRORING
• Проверка подлинности подключения — CERTIFICATE.
• Должно быть включено шифрование.
• Алгоритм шифрования — AES.

Выполните следующий запрос к 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

Если выходные данные показывают, что существующая конечная точка DATABASE_MIRRORING connection_auth_desc не представляет CERTIFICATE или encryption_algorthm_desc не представляет AES, конечная точка должна быть изменена в соответствии с требованиями.

В SQL Server одна конечная точка зеркального отображения базы данных используется как для групп доступности, так и для распределенных групп доступности. Если ваша конечная точка connection_auth_desc представляет NTLM (проверка подлинности Windows) или KERBEROS, и вам требуется проверка подлинности Windows для существующей группы доступности, можно изменить конечную точку для использования нескольких методов проверки подлинности, переключив параметр проверки подлинности на NEGOTIATE CERTIFICATE. Это изменение позволяет существующей группе доступности использовать проверка подлинности Windows при использовании проверки подлинности сертификата для Управляемый экземпляр SQL.

Аналогично: если шифрование не включает AES и требуется шифрование RC4, можно изменить конечную точку для использования обоих алгоритмов. Дополнительные сведения о возможных вариантах изменения конечных точек см. на странице документации по sys.database_mirroring_endpoints.

Следующий скрипт позволяет изменить существующую конечную точку зеркального отображения базы данных в SQL Server. Замена:

• <YourExistingEndpointName> именем существующей конечной точки.
• <SQLServerCertName> с именем созданного сертификата SQL Server (полученного в одном из предыдущих шагов выше).

В зависимости от конкретной конфигурации вам может потребоваться дополнительно настроить скрипт. Вы также можете использовать SELECT * FROM sys.certificates для получения имени созданного сертификата в 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

После выполнения запроса конечной точки ALTER и установки режима двойной проверки подлинности Windows и сертификата снова используйте этот запрос к 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

Вы успешно изменили конечную точку зеркального отображения базы данных для функции связи Управляемого экземпляра SQL.

Создание группы доступности в SQL Server

Если у вас нет существующей группы доступности, следующим шагом является создание группы доступности на SQL Server независимо от того, какой из них будет первичным. Команды для создания группы доступности отличаются, если Управляемый экземпляр SQL является начальным основным, который поддерживается только с SQL Server 2022 CU10.

Хотя можно установить несколько ссылок для одной базы данных, ссылка поддерживает репликацию только одной базы данных на одну ссылку. Если вы хотите создать несколько ссылок для одной базы данных, используйте одну группу доступности для всех ссылок, но создайте новую распределенную группу доступности для каждой связи базы данных между SQL Server и Управляемый экземпляр 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 

Проверка групп доступности

С помощью следующего скрипта отобразите все группы доступности и распределенные группы доступности в экземпляре SQL Server. На этом этапе состояние группы доступности должно быть connected, а состояние распределенных групп доступности — disconnected. Состояние распределенной группы доступности переходит connected только после соединения с Управляемый экземпляр 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

Кроме того, вы можете использовать обозреватель объектов SSMS для поиска групп доступности и распределенных групп доступности. Разверните папки Высокий уровень доступности Always On и Группы доступности.

Создание ссылки

Наконец, можно создать ссылку. Команды отличаются в зависимости от того, какой экземпляр является начальным первичным. Используйте команду New-AzSqlInstanceLink PowerShell или az sql mi link, чтобы создать ссылку, например пример PowerShell в этом разделе. Создание ссылки из основной Управляемый экземпляр SQL в настоящее время не поддерживается в Azure CLI.

Если вам нужно просмотреть все ссылки в управляемом экземпляре, используйте команду Get-AzSqlInstanceLink PowerShell или az sql mi link, чтобы показать команду Azure CLI в 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 

Результатом этой операции является метка времени успешного выполнения запроса на создание ссылки .

Проверка связи

Чтобы проверить подключение между Управляемый экземпляр SQL и SQL Server, выполните следующий запрос на SQL Server. Подключение не будет мгновенно. Пока соединение отобразится в динамическом административном представлении, может пройти до минуты. Продолжайте обновлять динамическое административное представление, пока подключение не появится как CONNECTED для реплики Управляемый экземпляр 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

После установки подключения обозреватель объектов в SSMS может первоначально отобразить реплицированную базу данных на вторичной реплике в состоянии восстановления, так как начальный этап заполнения перемещает и восстанавливает полную резервную копию базы данных. После восстановления базы данных репликация должна привести две базы данных в синхронизированное состояние. База данных больше не будет восстановлена после завершения начального заполнения. Заполнение небольших баз данных может быть достаточно быстрым, чтобы первоначальное состояние Восстановление не отображалось в SSMS.

Устранение неполадок

В этом разделе приводятся рекомендации по устранению проблем с настройкой и использованием ссылки.

ошибки

Если при создании ссылки или отработке отказа базы данных возникает сообщение об ошибке, просмотрите сообщение об ошибке в окне вывода запроса, чтобы получить дополнительные сведения.

Если при работе с ссылкой возникает ошибка, запрос перестает выполняться на шаге сбоя. После устранения условия ошибки повторно запустите команду, чтобы продолжить работу с действием.

Несогласованное состояние после принудительной отработки отказа

Использование принудительной отработки отказа может привести к несогласованному состоянию между основными и вторичными репликами, что приводит к разбиении мозга из обеих реплик в одной роли. Репликация данных завершается ошибкой в этом состоянии, пока пользователь не решит ситуацию, вручную назначив одну реплику в качестве первичной и другой реплики в качестве вторичной.

Связанный контент

Дополнительные сведения о функции канала см. в следующих ресурсах:

• Отработка отказа ссылки
• Обзор ссылки Управляемый экземпляр
• Настройка связи между SQL Server и Управляемым экземпляром SQL с помощью SSMS
• Аварийное восстановление со ссылкой Управляемый экземпляр