- Зеркалирование баз данных на MS SQL
- Подготовка зеркальной базы данных к зеркальному отображению (SQL Server)
- Перед началом
- Требования
- Ограничения
- Рекомендации
- безопасность
- Permissions
- Подготовка существующей зеркальной базы данных к повторному запуску зеркального отображения
- Подготовка новой зеркальной базы данных
- Примеры (Transact-SQL)
- Дальнейшие действия. После подготовки зеркальной базы данных
Зеркалирование баз данных на MS SQL
Доброго дня. Решил я описать здесь свой опыт настройки зеркалирования БД. Не имея, до недавнего времени, подобного профита, я начал сёрфить интернет в поисках информации на этот счёт. И постараюсь оформить пост как пошаговая инструкция рассказать об основных моментах, в общем что бы ничего лишнего.
Интро
Для резервирования БД рассматривали 2 варианта:
— репликации
— зеркалирование
Репликации отпали, потому, как некоторые таблицы не могут реплицироваться и вообще специально под это дело надо сразу предусматривать структуру базу данных.
Зеркалирование работает на ура! В результате тестов клал основную базу, переводил зеркальную в главную. После того как поднимал главную — та автоматом становилась зеркальной, менял их местами. Всё прошло без сучка, без задоринки. (Дай бог ей долгого здравия!)
Вообще есть 3 режима зеркалирования:
— защищённый с автоматическим восстановлением
— защищённый с ручным восстановлением
— не защищённый/асинхронный
Защищённый отличаются от асинхронного тем, что не ждут подтверждения принятия транзакции на зеркальном сервере, а продолжают работать и набрасывают в очередь новые и новые транзакции.
Защищённый с автоматическим восстановлением требует для автоматического восстановления использовать 3-й сервер (следящий) и в принципе полезен только если у вас в приложении можно указать резервный сервер для переключения в случае когда не работает основной. Поскольку мне было жалкао засарять следящими серверами информационное пространство и приложения работающие с базой тоже не имело возможности переключаться самостоятельно.
Я настраивал базы на работу в защищённом режиме с ручным восстановлением.
Вот хорошая инструкция на TechNet’е.
А здесь в картинках показано как это сделать через GUI.
Часть 1. Настройка связи сервера.
Для связи серверов друг с другом на обоих машинах создаются контрольные точки, открываются порты на соединение, создаются пользователи, сертификаты и пр.
Создадим контрольные точки, для авторизации мы будем использовать сертификат сгенерированный MS SQL сервером (так же можно использовать и другие сертификаты).
1. Создаём сертификат на главном сервере и сохраним его в паку D:\Certs
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = ‘##MS_DatabaseMasterKey##’)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘секретный пароль’
GO
IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1)
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = ‘PrincipalServerCert’)
CREATE CERTIFICATE PrincipalServerCert
WITH SUBJECT = ‘Principal Server Certificate’,
START_DATE = ’08/15/2011′,
EXPIRY_DATE = ’08/15/2021′;
GO
BACKUP CERTIFICATE PrincipalServerCert TO FILE = ‘D:\Certs\PrincipalServerCert.cer’
2. Создадим контрольную точку DBMirrorEndPoint на главном сервере.
USE MASTER
GO
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirrorEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE PrincipalServerCert, ENCRYPTION = REQUIRED
,ROLE = ALL
)
3. Создаём сертификат и контрольную точку DBMirrorEndPoint на зеркале, по аналогии с главным.
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = ‘##MS_DatabaseMasterKey##’)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘секретный пароль’
GO
IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1)
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = ‘MirrorServerCert’)
CREATE CERTIFICATE MirrorServerCert
WITH SUBJECT = ‘Mirror Server Certificate’,
START_DATE = ’08/15/2011′,
EXPIRY_DATE = ’08/15/2021′;
GO
BACKUP CERTIFICATE MirrorServerCert TO FILE = ‘D:\Certs\MirrorServerCert.cer’
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirrorEndPoint
STATE=STARTED AS TCP (LISTENER_PORT = 5023)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MirrorServerCert, ENCRYPTION = REQUIRED
,ROLE = ALL
)
Сертификаты и контрольные точки мы создали. Теперь чтобы серверы могли между собой общаться, на каждом серваке нужно создать учётные записи и привязать их к сертификатам.
4. Копируем сертификаты с одного на другой сервак, чтобы в папке D:\Certs лежало по 2 сертификата.
5. Создадим на главном сервере пользователя MirrorServerUser, этого пользователь привязываем к сгенерированному и скопированному с зеркального сервера сертификату MirrorDBCertPub
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = ‘MirrorServerUser’)
CREATE LOGIN MirrorServerUser WITH PASSWORD = ‘секретныйпароль2’
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = ‘MirrorServerUser’)
CREATE USER MirrorServerUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = ‘MirrorDBCertPub’)
CREATE CERTIFICATE MirrorDBCertPub AUTHORIZATION MirrorServerUser
FROM FILE = ‘D:\Certs\MirrorServerCert.cer’
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerUser
GO
6. Создадим на резервном сервере пользователя PrincipalServerUser, этого пользователь привязываем к сгенерированному и скопированному с главного сервера сертификату PrincipalDBCertPub
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = ‘PrincipalServerUser’)
CREATE LOGIN PrincipalServerUser WITH PASSWORD = ‘секретныйпароль2’
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = ‘PrincipalServerUser’)
CREATE USER PrincipalServerUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = ‘PrincipalDBCertPub’)
CREATE CERTIFICATE PrincipalDBCertPub AUTHORIZATION PrincipalServerUser
FROM FILE = ‘D:\Certs\PrincipalServerCert.cer’
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerUser
GO
Связь между серверами настроена!
Часть 2. Настройка баз данных.
Здесь нам надо будет снять бэкап с рабочей базы, поднять его на зеркальном сервере в режиме NORECOVERY и включить режим зеркалирования.
Зеркалируемая база данных должна иметь модель восстановления FULL.
1. Снимаем бэкап рабочей БД.
BACKUP DATABASE [MIRROR_TEST] TO DISK = N’D:\MIRROR_TEST.bak’
WITH FORMAT, INIT, NAME = N’MIRROR_TEST-Full Database Backup’,STATS = 10
2. Поднимаем его на зеркальном (скрипт подразумевает, что файл бэкапа перенесён на зеркальный сервак на диск D)
RESTORE DATABASE [MIRROR_TEST]
FROM DISK = ‘D:\MIRROR_TEST.bak’ WITH NORECOVERY
,MOVE N’MIRROR_TEST’ TO N’D:\MSSQL_DB\MIRROR_TEST.mdf’
,MOVE N’MIRROR_TEST_log’ TO N’D:\MSSQL_DB\MIRROR_TEST_log.ldf’
3. Для запуска зеркалирования на зеркальном сервере выполняем:
ALTER DATABASE MIRROR_TEST SET PARTNER = ‘TCP://MSSQLMAINSERV:5022’
4. Затем на главном:
ALTER DATABASE MIRROR_TEST SET PARTNER = ‘TCP://MSSQLMIRRORSERV:5023’
Если вылезет ошибка типа:
The mirror database, “MIRROR_TEST”, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)
The remote copy of database «DBmirrorTest» has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
Сделайте бэкап журнала с базы на главном сервере и восстановите его на зеркальном (опять же в режиме NORECOVERY).
Бэкап:
BACKUP LOG MIRROR_TEST TO DISK = ‘D:\MIRROR_TEST.trn’
Восстановление:
RESTORE LOG MIRROR_TEST
FROM DISK = ‘D:\MIRROR_TEST.trn’ WITH NORECOVERY
Часть 3. Восстановление после сбоев. Изменение ролей.
Изменить роли сервера, чтобы зеркальный стал главным и наобород можно через GUI кликнов правой кнопкой по базе — Task — Mirror — Failover или же через команду T-SQL
ALTER DATABASE MIRROR_TEST SET PARTNER FAILOVER
Если грохнулась зеркальная база, главная продолжает работать в незащищённом режиме (на клиентах это никак не отражается). После возобновления работы зеркала, резервная база автоматически подключается и догоняет главную.
Если же грохнулась главная база, то чтобы оживить резервную нужно выполнить принудительное восстановление
ALTER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
правда в этом случае существует риск потерять некоторые данные (про это много написано здесь)
При выполнении принудительного восстановления зеркальная база становится главной, а бывшая главная после восстановления автоматически станет зеркальной, ожидающей разрешения продолжить сеанс зеркалирования. Для чего нужно выполнить
ALTER DATABASE MIRROR_TEST SET PARTNER RESUME
Вот вроде и всё! Пока работает 😎
Источник
Подготовка зеркальной базы данных к зеркальному отображению (SQL Server)
Применимо к: SQL Server (все поддерживаемые версии)
Перед началом сеанса зеркального отображения базы данных ее владелец или системный администратор должен убедиться, что зеркальная база данных создана и готова к работе. Чтобы создать новую зеркальную базу данных, требуется как минимум наличие полной резервной копии основной базы данных и последующих резервных копий журналов. Они восстанавливаются на экземпляре зеркального сервера с параметром WITH NORECOVERY.
В этом разделе описано, как подготовить зеркальную базу данных в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL.
Перед началом работы
Перед началом
Требования
На основном сервере и на экземплярах зеркального сервера должна работать одна и та же версия SQL Server. Для зеркального сервера возможна более поздняя версия SQL Server, но эта конфигурация рекомендуется только во время процесса тщательно спланированного обновления. В такой конфигурации есть риск автоматического перехода на другой ресурс, в котором движение данных автоматически приостанавливается, так как данные нельзя переместить на более раннюю версию SQL Server. Дополнительные сведения см. в статье Upgrading Mirrored Instances.
На основном сервере и на экземплярах зеркального сервера должен работать один и тот же выпуск SQL Server. Сведения о поддержке зеркального отображения базы данных в SQL Server см. в разделе Функции, поддерживаемые различными выпусками SQL Server 2017.
База данных должна использовать модель полного восстановления.
Имя зеркальной базы данных должно совпадать с именем основной базы данных.
Для работы зеркального отображения необходимо, чтобы зеркальная база данных находилась в состоянии RESTORING. При подготовке зеркальной базы данных необходимо использовать инструкцию RESTORE WITH NORECOVERY для каждой операции восстановления. Как минимум необходимо восстановить с параметром WITH NORECOVERY полную резервную копию основной базы данных, а затем все последующие резервные копии журналов.
В системе, где предполагается создать зеркальную базу данных, должен быть жесткий диск, на котором достаточно свободного места.
Ограничения
Зеркальное отображение системных баз данных master, msdb, temp и model невозможно.
Невозможно зеркальное отображение базы данных, принадлежащей группе доступности AlwaysOn.
Рекомендации
Используйте либо самую последнюю полную копию базы данных, либо разностную резервную копию основной базы данных.
Если запланировано частое выполнение задания резервного копирования журналов основной базы данных, то, возможно, его придется отключить до запуска зеркального отображения.
Желательно, чтобы путь зеркальной базы данных (включая имя диска) был идентичен пути основной базы данных.
Если пути к файлам должны различаться, например если основная база данных расположена на диске «F:», а в зеркальной системе нет диска «F:», необходимо включить в инструкцию RESTORE параметр MOVE.
Добавление файлов во время сеанса зеркального отображения без влияния на сеанс требует, чтобы путь к файлам существовал на обоих серверах. Поэтому перемещение файлов базы данных во время создания зеркального отображения может привести к его ошибке или остановке при выполнении операции добавления файла. Дополнительные сведения об обработке ошибок операции создания файла см. в статье Диагностика конфигурации зеркального отображения базы данных (SQL Server).
Если основная база данных содержит любые полнотекстовые каталоги, см. статью Зеркальное отображение баз данных и полнотекстовые каталоги (SQL Server).
Для производственной базы данных необходимо всегда создавать резервные копии на разных устройствах.
безопасность
Параметр TRUSTWORTHY устанавливается в значение OFF каждый раз при создании резервной копии базы данных. Таким образом, в новой зеркальной базе данных он всегда имеет значение OFF. Если после отработки отказа необходимо, чтобы база данных снова стала надежной, следует выполнить дополнительные действия. Дополнительные сведения см. в статье Настройка зеркальной базы данных на использование свойства TRUSTWORTHY (Transact-SQL).
Сведения о включении автоматической расшифровки главного ключа базы данных в зеркальной базе данных см. в статье Настройка зашифрованной зеркальной базы данных.
Permissions
Владелец базы данных или системный администратор.
Подготовка существующей зеркальной базы данных к повторному запуску зеркального отображения
Если зеркальное отображение было удалено, а зеркальная база данных остается в состоянии RECOVERING, то зеркальное отображение можно запустить повторно.
Создайте хотя бы одну резервную копию журналов основной базы данных. Дополнительные сведения см. в статье Создание резервной копии журнала транзакций (SQL Server)).
В зеркальной базе данных следует восстановить с помощью инструкции RESTORE WITH NORECOVERY все резервные копии журналов, созданные в основной базе данных с момента удаления зеркальной базы данных. Дополнительные сведения см. в статье Восстановление резервной копии журнала транзакций (SQL Server).
Подготовка новой зеркальной базы данных
Подготовка зеркальной базы данных
Пример этой процедуры на языке Transact-SQL см. в подразделе Пример (Transact-SQL)ниже в этом разделе.
Установите соединение с основным экземпляром на сервере.
Создайте либо полную копию базы данных, либо разностную резервную копию основной базы данных.
Как правило, необходимо создать хотя бы одну резервную копию журналов основной базы данных. Однако резервная копия журналов может не понадобиться, если база данных только что создана и в ней еще не было создано ни одной резервной копии журналов либо если модель восстановления только что изменена с SIMPLE на FULL.
Если резервная копия находится не на сетевом диске, доступном для обеих систем, то скопируйте базу данных и резервные копии журналов на компьютер, где размещен экземпляр зеркального сервера.
Установите соединение с зеркальным экземпляром сервера.
С помощью инструкции RESTORE WITH NORECOVERY создайте зеркальную базу, восстановив полную резервную копию и, возможно, последнюю разностную резервную копию базы данных, на экземпляре зеркального сервера.
При восстановлении файловой группы базы данных по файловой группе следует восстановить базу данных целиком.
С помощью инструкции RESTORE WITH NORECOVERY примените все необработанные резервные копии и резервные копии журналов на зеркальной базе данных.
Примеры (Transact-SQL)
Перед тем как начать сеанс зеркального отображения базы данных, нужно создать зеркальную базу данных. Это нужно сделать непосредственно перед запуском сеанса зеркального отображения.
В этом примере используется образец базы данных AdventureWorks2012 , в котором по умолчанию применяется простая модель восстановления.
Чтобы включить зеркальное отображение базы данных AdventureWorks2012 , переключите базу данных на модель полного восстановления.
После изменения модели восстановления с SIMPLE на FULL создайте полную резервную копию, с помощью которой затем можно будет создать зеркальную базу данных. Так как модель восстановления только что была изменена, указывается параметр WITH FORMAT для создания нового набора носителей. Это полезно для отделения резервных копий при модели полного восстановления от резервных копий, сделанных при простой модели восстановления. В данном примере файл резервной копии ( C:\AdventureWorks.bak ) создается на том же диске, на котором находится база данных.
Для производственной базы данных необходимо всегда делать резервные копии на разные устройства.
На экземпляре основного сервера ( PARTNERHOST1 ) создайте полную резервную копию основной базы данных следующим образом.
Создайте полную резервную копию на зеркальном сервере.
Восстановите полную резервную копию на экземпляр зеркального сервера с помощью инструкции RESTORE WITH NORECOVERY. Команда восстановления зависит от того, идентичны ли пути основной и зеркальной баз данных.
Если пути идентичны:
на экземпляре зеркального сервера ( PARTNERHOST5 ) выполните восстановление из полной резервной копии следующим образом:
Если пути отличаются:
Если путь зеркальной базы данных отличается от пути основной базы данных (например, отличаются имена дисков), то при создании зеркальной базы данных в операцию восстановления нужно будет добавить предложение MOVE.
Если отличаются пути основной и зеркальной баз данных, то добавлять файлы нельзя. Это происходит потому, что при появлении в журнале записи об операции добавления файла экземпляр зеркального сервера пытается поместить новый файл в местоположение, указанное для основной базы данных.
Например, следующая команда восстанавливает резервную копию основной базы данных, которая находится в каталоге «C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data», в другое расположение — «D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Dat a\ «, где должна находиться зеркальная база данных.
После создания полной резервной копии обязательно создается резервная копия журналов для основной базы данных. В следующем примере с помощью инструкции Transact-SQL создается резервная копия журналов для того же файла, который использовался в предыдущей полной резервной копии.
Перед тем как приступать к зеркальному отображению, необходимо применить требуемую резервную копию журналов (и все последующие резервные копии журналов).
Например, следующая инструкция Transact-SQL восстанавливает первый журнал из C:\AdventureWorks.bak :
Если перед запуском зеркального отображения создавались дополнительные резервные копии журналов, то необходимо последовательно восстановить их на зеркальном сервере с параметром WITH NORECOVERY.
Например, следующая инструкция Transact-SQL восстанавливает два дополнительных журнала из C:\AdventureWorks.bak :
Подробный пример настройки зеркального отображения базы данных, в котором показана настройка защиты, подготовка зеркальной базы данных, настройка партнеров и добавление следящего сервера, см. в статье Настройка зеркального отображения базы данных (SQL Server).
Дальнейшие действия. После подготовки зеркальной базы данных
Если были сняты какие-либо дополнительные резервные копии журналов с момента самой последней операции RESTORE LOG, то необходимо вручную применить каждую дополнительную резервную копию с параметром RESTORE WITH NORECOVERY.
Если задание резервного копирования в основной базе данных отключено, то необходимо снова включить его.
Если для базы данных после отработки отказа с переходом на другой ресурс требуется доверие, это потребует дополнительных действий по настройке после начала зеркального отображения. Дополнительные сведения см. в статье Настройка зеркальной базы данных на использование свойства TRUSTWORTHY (Transact-SQL).
Источник