Как сделать зеркало ms sql
Настройка зеркалирования в Microsoft SQL Server 2014
Зеркалирование или mirroring в MS SQL Server 2014 всё ещё есть. Это очень полезное решение для повышения доступности базы данных. Если ваш сервер упал, посыпались винты, сгорел ЦОД, то возможность быстро переключиться на резервный сервер, не занимаясь восстановлением сервера или БД из бэкапа, экономит кучу времени, денег и нервов. Однако, не следует рассматривать зеркалирование как замену резервному копированию, так как оно не спасает от случайного удаления данных.
Режимы зеркалирования
В Microsoft SQL Server 2014 есть три режима зеркалирования:
Асинхронный
Для работы в асинхронном режиме достаточно двух серверов: главного (principal) и зеркального (mirror). В данном режиме работы данные сначала записываются на главном сервере, а потом передаются на зеркальный. Этим достигается высокая производительность работы. В случае сбоя часть данных может не успеть записаться на зеркальном сервере, поэтому есть риск утери данных. Рекомендуется для использования в проектах, где потеря данных не критична или допустима.
Синхронный без автоматического восстановления
Для работы в синхронном режиме без автоматического восстановления достаточно двух серверов: главного (principal) и зеркального (mirror). Если главный сервер упал, то на зеркальный можно переключиться в ручном режиме. В данном режиме работы данные записываются сразу на два сервера в режиме транзакции. Тем самым обеспечивается синхронность данных и мы можем быть уверены в том, что в случае падения главного сервера мы не потеряем данные на зеркальном.
Синхронный с автоматическим восстановлением
Для работы в синхронном режиме с автоматическим восстановлением требуется три сервера: главный (principal), зеркальный (mirror) и свидетель (witness). Если главный сервер упал, то свидетель автоматически сделает failover на зеркальный сервер. В данном режиме работы данные записываются сразу на два сервера в режиме транзакции. Тем самым обеспечивается синхронность данных и мы можем быть уверены в том, что в случае падения главного сервера мы не потеряем данные на зеркальном.
Дальше следует инструкция по настройке зеркалирования в синхронном режиме без автоматического восстановления.
Модель восстановление FULL
Зеркалируемая база данных должна иметь модель восстановления FULL. При этом постоянно растёт лог транзакций. Чтобы в один прекрасный момент не кончилось место на сервере, необходимо настроить регулярное резервное копирование (full backup), при этом лог транзакций жмётся.
Переводим необходимые нам БД на модель восстановления FULL. Делаем полный бэкап. После бэкапа модель восстановления применится. Если БД уже настроена на FULL, то пропускаем этот пункт.
Настройка серверов
Версия MS SQL Server на обоих серверах должна быть одинаковая. Для настройки зеркалирования обеспечиваем доступ между серверами.
На обоих серверах создаём папку:
На главном сервере создаём сертификат:
На главном сервере создаём контрольную точку DBMirrorEndPoint на порту 5022:
На зеркале создаём сертификат:
На зеркале создаём контрольную точку DBMirrorEndPoint на порту 5023:
Копируем сертификаты с одного сервера на другой, чтобы на каждом сервере в папке C:\certs было по два сертификата:
На главном сервере создаём пользователя MirrorServerUser:
На зеркале создаём пользователя PrincipalServerUser:
Настраиваем зеркало базы данных
Делаем полный бэкап базы данных, потом делаем бэкап лога транзакций. Восстанавливаем на зеркальном сервере в режиме NORECOVERY и включаем режим зеркалирования. Это можно также сделать в GUI.
Делаем полный бэкап:
Восстанавливаем его на зеркале:
Выполняем на зеркале:
Выполняем на главном сервере:
Если произошла ошибка:
то нужно сделать бэкап лога транзакций на главном сервере:
и восстановить его на зеркале в режиме NORECOVERY:
потом повторить на зеркале:
Восстановление после сбоев
Если необходимо изменить роли сервера:
Если сломалась зеркальная база, то после возобновления её работы зеркало восстанавливается автоматически.
Если сломалась главная база то принудительно оживить зеркало можно так:
После этого зеркальная база становится главной, а главный сервер после запуска автоматически станет зеркальным, ожидающим разрешения продолжить сеанс зеркалирования. Для этого нужно выполнить:
Зеркалирование баз данных на MS SQL
Вообще есть 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 Database mirroring in SQL Server
Зеркальное отображение базы данных в SQL Server позволяет сохранять копию или зеркальную копию базы данных SQL Server на резервном сервере. Database mirroring in SQL Server allows you to keep a copy, or mirror, of a SQL Server database on a standby server. Зеркальное отображение гарантирует, что две отдельные копии данных постоянно существуют, обеспечивая высокий уровень доступности и избыточности данных. Mirroring ensures that two separate copies of the data exist at all times, providing high availability and complete data redundancy. Поставщик Microsoft SqlClient для SQL Server предоставляет неявную поддержку зеркального отображения базы данных, поэтому разработчику не надо предпринимать никаких действий или писать код, если он настроен для работы с базой данных SQL Server. The Microsoft SqlClient Provider for SQL Server provides implicit support for database mirroring, so that the developer does not need to take any action or write any code once it has been configured for a SQL Server database. Кроме того, объект SqlConnection поддерживает явный режим подключения, который позволяет указать имя сервера-партнера по обеспечению отработки отказа в ConnectionString. In addition, the SqlConnection object supports an explicit connection mode that allows supplying the name of a failover partner server in the ConnectionString.
Следующая упрощенная последовательность событий возникает для объекта SqlConnection, предназначенного для базы данных, которая настроена для зеркального отображения: The following simplified sequence of events occurs for a SqlConnection object that targets a database configured for mirroring:
Клиентское приложение успешно подключается к основной базе данных, а сервер отправляет обратно имя сервера-партнера, который затем кэшируется на клиенте. The client application successfully connects to the principal database, and the server sends back the name of the partner server, which is then cached on the client.
Если на сервере с основной базой данных происходит сбой или подключение прерывается, состояние подключения и транзакции будет утеряно. If the server containing the principal database fails or connectivity is interrupted, connection and transaction state is lost. Клиентское приложение пытается восстановить подключение к основной базе данных, и происходит сбой. The client application attempts to re-establish a connection to the principal database and fails.
Затем клиентское приложение прозрачно пытается установить подключение к зеркальной базе данных на сервере-партнере. The client application then transparently attempts to establish a connection to the mirror database on the partner server. В случае успешной установки подключение перенаправляется в зеркальную базу данных, которая затем становится новой основной базой данных. If it succeeds, the connection is redirected to the mirror database, which then becomes the new principal database.
Указание партнера по обеспечению отработки отказа в строке подключения Specifying the failover partner in the connection string
Если в строке подключения указать имя сервера-партнера по обеспечению отработки отказа, клиент будет прозрачно пытаться подключиться к партнеру по обеспечению отработки отказа в случае, если основная база данных недоступна при первом подключении клиентского приложения. If you supply the name of a failover partner server in the connection string, the client will transparently attempt a connection with the failover partner if the principal database is unavailable when the client application first connects.
Если не указано имя сервера-партнера по обеспечению отработки отказа, а основная база данных недоступна при первом подключении клиентского приложения, возникает SqlException. If you omit the name of the failover partner server and the principal database is unavailable when the client application first connects then a SqlException is raised.
При успешном открытии SqlConnection имя партнера по обеспечению отработки отказа возвращается сервером и заменяет все значения, указанные в строке подключения. When a SqlConnection is successfully opened, the failover partner name is returned by the server and supersedes any values supplied in the connection string.
Получение текущего имени сервера Retrieving the current server name
В случае отработки отказа можно получить имя сервера, к которому на самом деле подключено текущее подключение, с помощью свойства DataSource объекта SqlConnection. In the event of a failover, you can retrieve the name of the server to which the current connection is actually connected by using the DataSource property of a SqlConnection object. Следующий фрагмент кода извлекает имя активного сервера, предполагая, что переменная подключения ссылается на открытый элемент SqlConnection. The following code fragment retrieves the name of the active server, assuming that the connection variable references an open SqlConnection.
При возникновении события отработки отказа и переключении подключения на зеркальный сервер свойство DataSource обновляется для отображения имени этого сервера. When a failover event occurs and the connection is switched to the mirror server, the DataSource property is updated to reflect the mirror name.
Поведение SqlClient при зеркальном отображении SqlClient mirroring behavior
Клиент всегда пытается подключиться к текущему основному серверу. The client always tries to connect to the current principal server. В случае сбоя он пытается обратиться к партнеру по обеспечению отработки отказа. If it fails, it tries the failover partner. Если зеркальная база данных уже переключена на основную роль на сервере-партнере, подключение будет установлено успешно, а новое сопоставление зеркального отображения-субъекта отправляется клиенту и кэшируется на время существования вызова AppDomain. If the mirror database has already been switched to the principal role on the partner server, the connection succeeds and the new principal-mirror mapping is sent to the client and cached for the lifetime of the calling AppDomain. Оно не сохраняется в постоянном хранилище и недоступно для последующих соединений в другом домене приложения AppDomain или процессе. It is not stored in persistent storage and is not available for subsequent connections in a different AppDomain or process. Однако оно доступно для последующих соединений внутри того же домена приложения AppDomain. However, it is available for subsequent connections within the same AppDomain. Обратите внимание, что другой домен приложения AppDomain или процесс, выполняемый на том же или другом компьютере, всегда имеет свой пул соединений и эти соединения не сбрасываются. Note that another AppDomain or process running on the same or a different computer always has its pool of connections, and those connections are not reset. В этом случае, если база данных-источник выходит из строя, каждый процесс или домен приложения AppDomain заканчивается ошибкой и пул автоматически очищается. In that case, if the primary database goes down, each process or AppDomain fails once, and the pool is automatically cleared.
Поддержка зеркального отображения на сервере настраивается отдельно для каждой базы данных. Mirroring support on the server is configured on a per-database basis. Если операции обработки данных выполняются для других баз данных, не входящих в основной или зеркальный набор, с помощью составных имен либо путем изменения текущей базы данных, изменения в этих базах данных не распространяются в случае сбоя. If data manipulation operations are executed against other databases not included in the principal/mirror set, either by using multipart names or by changing the current database, the changes to these other databases do not propagate in the event of failure. При изменении данных в незеркальной базе данных ошибка не возникает. No error is generated when data is modified in a database that is not mirrored. Разработчик должен оценить возможное воздействие таких операций. The developer must evaluate the possible impact of such operations.
Дальнейшие действия Next steps
Ресурсы по зеркальному отображению баз данных Database mirroring resources
Документацию и сведения о настройке, развертывании и администрировании зеркального подключения см. в приведенных ниже ресурсах документации на SQL Server. For conceptual documentation and information on configuring, deploying and administering mirroring, see the following resources in SQL Server documentation.