Средства обеспечения высокой доступности в MS SQL Server

Лига сисадминов

Зеркалирование баз данных (Database mirroring) в SQL Server

Доступно в редакциях: Standard (только синхронный режим), Enterprise, Web/express – только режим Witness
Работает на уровне: Базы данных
Версия SQL Server: SQL Server 2005, SQL Server 2008

Примечание. Database mirroring находится в режиме обслуживания и может быть удален в будущих версиях SQL Server, поэтому не рекомендуется использовать эту технологию на версиях выше чем SQL Server 2008.

Зеркалирование работает на уровне базы данных (может еще быть на уровне объектов) и обеспечивает автоматический/ручной переход между серверами в случае отказа. Резервная база в любом из режимов работы зеркалирования будет находиться в состоянии постоянного восстановления, поэтому обращаться к ней не выйдет.

У зеркалирования есть 2 режима работы: Синхронный и асинхронный.

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

В синхронном режиме есть возможность использовать Witness сервер. Сервер в режиме свидетеля следит за работоспособностью серверов зеркалирования и может инициировать отработку отказа, то есть переход резервного сервера в активное состояние.

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

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

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

Зеркалирование стоит использовать только если у вас совпадение по всем условиям

SQL Server 2008 или SQL Server 2005
Низкая сетевая задержка (latency) между основным сервером и резервным
Вам критична потеря даже одной транзакции

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

Доставка журналов (Log shipping) в SQL Server

Доступно в редакциях: Standard, Web, Enterprise
Работает на уровне: Базы данных
Версия SQL Server: SQL Server 2005 и выше

Технология доставки журналов (Log shipping) позволяет автоматически отправлять резервные копии журналов транзакций из базы данных источника в одну или более баз данных получателей и затем восстанавливает их в базах данных получателей. Опционально может быть третий сервер, который будет выполнять роль службы мониторинга – отслеживать выполнение операций резервного копирования и восстановления журналов.

После настройки доставки журналов создаются Задания (jobs). Принцип работы таков:

Первое задание отвечает за резервное копирование журнала транзакций на основном сервере
Второе задание отвечает за распространение бекапа на все сервера-получатели
Третье задание восстанавливает журналы во все базы данных получателей. Восстановление доступно в режимах No recovery mode или Standby mode

Это более простая технология, относительно зеркалирования и Always On. Доставку журналов стоит использовать, когда:

Допустима разница в данных между основным сервером и серверами получателями. Стандартное расписание выполнение заданий – каждые 15 минут. Можно поставить и меньше, но нужно учитывать скорость передачи данных по сети и время на восстановление журналов.
Вы хотите обращаться к базам данных получателей для read доступа. Это возможно, когда режим восстановления установлен в Standby mode. Но имейте в виду, обращаться к базе вы сможете только в промежутках между восстановлением журнала.

Репликация в Microsoft SQL Server: обзор методов

Доступно в редакциях: Standard и Web – с ограничениями, Enterprise
Работает на уровне: Объекта базы данных

Версия SQL Server: SQL Server 2000 и выше

Существует различные типы репликации:

Репликация транзакций
Одноранговая репликация транзакций
Репликация моментальных снимков
Репликация слиянием

Есть ещё 2 топологии, основанные на репликации транзакций:

Двунаправленная репликация транзакций
Обновляемые подписки для репликации транзакций (функция поддерживается в версиях SQL Server с 2012 по 2016)

Репликация может применяться для различных целей, но в основном её используют для разгрузки OLTP серверов select запросами и для высокой доступности. Хотя Microsoft не позиционирует репликацию как средство для достижения высокой доступности, она вполне может выполнять эту роль.

Заметка: в модели репликации SQL Server есть 3 типа серверов:Publisher (издатель) – сервер который издаёт статьи
Distributor (распространитель) – сервер который распространяет статьи на сервера-подписчики
Subscriber (подписчик) – сервер который получает распространяемые статьи

Изменение которые проходят в выбранных объектах на издателе, отправляются сначала на распространителя, затем распространитель рассылает эти изменения подписчикам.

Рассмотрим 4 основные типа репликации

Репликация транзакций (Transactional Replication)

Этот тип репликации используется для «near real time» репликации данных, то есть данные на подписчиках появляются практически сразу, с учетом времени копирования данных по сети.

Транзакции с издателя отправляются на распространитель, распространитель отправляет эти транзакции на подписчиков. Распространитель может отправлять данные подписчикам немедленно, либо по определенному расписанию. Объекты на подписчике, которые участвуют в репликации должны использоваться только для read only доступа, иначе данные станут несогласованные и возникнет конфликт.

Одноранговая репликация транзакций (Peer-To-Peer Transactional Replication)

Одноранговая репликация или Peer-To-Peer Transactional Replication похожа на обычную репликацию транзакций, но она может работать сразу с несколькими серверами.

Одноранговую репликацию можно назвать master-master репликацией (для обычной транзакционной репликации было бы master-slave). Рассмотрим схему из документации Microsoft

Каждый экземпляр SQL Server который участвует в одноранговой репликации может обрабатывать read и write операции. Так же в таком типе репликации предусмотрен механизм разрешения конфликтов, когда на несколько серверах одновременно приходит одна и та же операция, например, update запрос. Но даже с учетом этого механизма не рекомендуется записывать данные в несколько экземпляров одновременно.

Такой тип репликации может использоваться для балансировки нагрузки, в том числе для update/insert/delete операций.

Репликация моментальных снимков (Snapshot replication)

Это особый тип репликации, который не отслеживает изменение данных на издателе, а по определенному расписанию создаёт моментальный снимок и отправляет его подписчикам (через распространителя).

Репликация снимков не применяет все транзакции последовательно, как в случае с доставкой журналов и транзакционной репликацией, а копирует данные через bcp.

Этот вид репликации стоит использовать когда:

Данные редко меняются
Допустима разница в данных между издателем и подписчиком
Большой объём изменений за короткий период времени

Репликация слиянием (Merge replication)

Механизм работы похож на одноранговую репликацию транзакций, но есть несколько важных отличий:

Репликация слиянием может иметь только одного издателя и несколько подписчиков, когда как в peer-to-peer репликации все экземпляры равны между собой (одновременно являются и издателями, и подписчиками
В репликации слиянием подписчики могут получать разные данные, когда в одноранговой репликации все сервера имеют одни данные
Репликация слиянием может разрешать конфликты, одноранговая – нет
Одноранговая репликация доступна только в Enterprise редакции

Репликацию слиянием стоит применять тогда, когда вам нужно консолидировать данные.

Двунаправленная репликация транзакций и обновляемые подписки для репликации транзакций

Двунаправленная репликация (Bidirectional Transactional) это топология, когда обычная репликация транзакций настроена на репликацию одни тех же данных. Параметр @loopback_detection parameter в sp_addsubscription должен быть выставлен в TRUE

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

Группы доступности Always On в SQL Server

Доступно в редакциях: Standard (с ограничениями), Enterprise (
Работает на уровне: Базы данных
Версия SQL Server: SQL Server 2012 и выше

Always On availability groups появились в релизе SQL Server 2012. Это альтернатива (хотя скорее развитие) технологии зеркалирования баз данных.

Группы доступности Always On работают на основе Windows Server Failover Cluster, но начиная с 2017 версии появилась возможность использовать Always On без WSFC. Always on похож на зеркалирование баз данных (синхронный и асинхронный режимы) но вторичных реплик может быть до 8 штук. Always On поддерживает автоматическую отработку отказа (то есть, при падении основного экземпляра кластера WSCF выбирает новую основную реплику и перенаправляет write запросы на неё).

Каждый экземпляр в группе доступности может быть либо primary (основным), либо secondary (вторичным). Вторичные реплики могут быть либо в read-only, либо в режиме No recovery. Каждый экземпляр в группе доступности содержит в себе копии баз данных группы доступности. Имейте в виду, что в синхронном режиме скорость проведения транзакций будет зависеть от самого «медленного» участника группы доступности.

В базовой настройке Always On прост, после установки SQL Server всё можно настроить с помощью мастера (WSFC через оснастку в Windows, а сами группы доступности через мастер в SSMS). Но при большом количестве серверов и сложной инфраструктуре придется хорошо изучить документацию.

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

Более подробно мы рассмотрим группы доступности Always On в SQL Server в следующей статье.

SQL Server предоставляет много разнообразных решений для обеспечения высокой доступности данных. При наличии Enterprise редакции и SQL Server 2012 (и выше) лучше использовать Always On. Репликацию можно использовать для разгрузки OLTP систем select запросами и для частичной избыточности (хотя одноранговая репликация позиционируется как полноценное средство избыточности данных). Доставку журналов транзакций и зеркалирование баз данных можно использовать в более старых версиях SQL Server или если условия вынуждают использовать именно эти технологии.

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