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

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

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

• Доступно в редакциях: Standard (только синхронный режим) , Enterprise, Web/express – только режим Witness
• Работает на уровне: Базы данных
• Версия SQL Server: SQL Server 2005, 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). Принцип работы таков:

1. Первое задание отвечает за резервное копирование журнала транзакций на основном сервере
2. Второе задание отвечает за распространение бекапа на все сервера-получатели
3. Третье задание восстанавливает журналы во все базы данных получателей. Восстановление доступно в режимах 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 не позиционирует репликацию как средство для достижения высокой доступности, она вполне может выполнять эту роль.

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

Рассмотрим 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 запросов. Также резервные копии рекомендуется делать именно с вторичных реплик, это еще одно применение групп доступности.

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

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