Log shipping что это

Сведения о доставке журналов (SQL Server)

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

В этом разделе.

Преимущества

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

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

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

Термины и определения

сервера-источника
Экземпляр SQL Server , являющийся рабочим сервером.

базы данных-источника
База данных сервера-источника, резервную копию которой нужно создать на другом сервере. Все администрирование конфигурации доставки журналов через среду SQL Server Management Studio производится на базе данных-источнике.

сервера-получателя
Экземпляр SQL Server , на котором нужно хранить «горячую» копию базы данных-источника.

база данных-получатель
«Горячая» копия базы данных-источника. База данных-получатель может находиться в состояниях RECOVERING или STANDBY, что означает, что она доступна в ограниченном режиме только для чтения.

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

когда резервная копия журнала транзакций на базе данных-источнике была сохранена в последний раз;

когда серверы-получатели в последний раз копировали и восстанавливали файлы резервных копий;

сведения о любых предупреждениях о сбоях.

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

задание резервного копирования
Задание агента SQL Server , которое выполняет операцию резервного копирования, ведет журнал на локальном сервере и на сервере мониторинга и удаляет старые файлы резервных копий и записи своего журнала. При включении доставки журналов на экземпляре сервера-источника создается задание категории «Резервное копирование доставки журналов».

задание копирования
Задание агента SQL Server , которое копирует файлы резервных копий журналов с сервера-источника на указанный сервер-получатель, ведет журнал на сервере-получателе и сервере мониторинга. При включении доставки журналов в базе данных на каждом сервере-получателе в конфигурации доставки журналов создается задание категории «Копирование доставки журналов».

задание восстановления
Задание агента SQL Server , которое восстанавливает резервные копии журналов в базах данных-получателях. Оно ведет журнал на локальном сервере и на сервере мониторинга и удаляет старые файлы и записи своего журнала. При включении доставки журналов для базы данных на экземпляре сервера-получателя создается задание категории «Восстановление доставки журналов».

задание предупреждения
Задание агента SQL Server , которое создает предупреждения для баз данных-источников и получателей, если операции резервного копирования или восстановления не были завершены успешно в течение указанного срока. Если в базе данных включена доставка журналов, на экземпляре сервера мониторинга создается задание категории «Предупреждение доставки журналов».

Для каждого предупреждения необходимо задать номер. Также необходимо настроить предупреждение для оповещения оператора при появлении этого предупреждения.

Общие сведения о доставке журналов

Доставка журналов состоит из этих трех операций:

резервное копирование журнала транзакций на экземпляре сервера-источника;

копирование резервных копий журнала транзакций на экземпляр сервера-получателя;

восстановление из резервных копий журнала транзакций на экземпляре сервера-получателя.

Журнал может доставляться нескольким экземплярам серверов-получателей. В этих случаях операции 2 и 3 повторяются для каждого экземпляра сервера-получателя.

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

Сервер-получатель можно использовать для построения отчетов.

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

Типичная конфигурация доставки журналов

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

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

каждый из трех экземпляров сервера-получателя выполняет задачу копирования файла-источника резервной копии журнала в его локальную целевую папку;

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

Экземпляры сервера-источника и сервера-получателя посылают собственный журнал и состояние экземпляру сервера мониторинга.

Взаимодействие

Доставка журналов может использоваться вместе со следующими функциями и компонентами SQL Server.

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

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

date 26.02.2020
user insci
directory SQL Server
comments Один комментарий

В этой статье мы рассмотрим все технологии 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). Принцип работы таков:

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

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

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

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

SQL-Ex blog

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

Модели восстановления и журналы транзакций

SQL Server поддерживает три модели восстановления — полную (Full), простую (Simple) и с неполным протоколированием (Bulk Logged).

При полной модели восстановления ВСЕ транзакции записываются в журнал. Таким образом, база данных может быть полностью восстановлена после сбоя. Это также означает, что резервная копия базы данных может быть восстановлена к заданному моменту времени, если доступен журнал транзакций или соответствующий бэкап. При моделях восстановления Full и Bulk Logged журналы транзакций усекаются всякий раз, когда выполняется бэкап журнала.

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

Контрольная точка устанавливается, когда SQL Server сбрасывает «грязные» буферы в файл данных. Грязные буферы это дисковые страницы, хранящиеся в памяти, которые были изменены транзакциями, в результате чего состояние памяти не соответствует состоянию на диске. Но мы не будем здесь это обсуждать. В простой модели восстановления SQL Server сохраняет все эти изменения в журнале транзакций до тех пор, пока они не будут сброшены на диск.

Структура журнала транзакций

Журнал транзакций — это физический файл, видимый в операционной системе, где находится база данных SQL Server. Каждая база данных имеет один журнал транзакций, но возможно сконфигурировать и больше. Дело в том, что наличие нескольких журналов транзакций не дает каких-либо преимуществ с точки зрения производительности. SQL Server выполняет запись в журнал транзакций последовательно — один файл должен быть заполнен, прежде чем использовать следующий. Однако несколько файлов, размещаемых на разных дисках, могут спасти положение, если первый файл заполнится.

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

Что вызывает рост журнала?

Давайте создадим небольшую базу данных, используя код в листинге 1. Файл данных вначале имеет размер 4Мб, файл журнала — 2Мб. Ваши промышленные базы данных никогда не будут иметь такой размер, особенно при популярной практике pre-allocation (предварительное выделение). Мы выбрали такой размер просто в демонстрационных целях.

В этой базе данных мы создаем единственную таблицу (листинг 2) для последующего выполнения операторов языка манипуляции данными (DML).

Выполнив код в листинге 3, проверим, что мы сделали.

Рис.1: Результаты выполнения кода в листинге 3, но до DML

Обратите внимание на столбец File size. Приступаем к наблюдению за ростом журнала транзакций при выполнении операторов INSERT и DELETE 100000 раз (листинг 4).

В листинге 4 выполняется вставка одной строки в таблицу txn_log с последующим её удалением; это действие повторяется 100000 раз.

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

Рис.2: Результаты выполнения кода из листинга 3 после операторов DML

Журнал транзакций вырос с 4Мб до 40Мб в результате этих манипуляций, хотя файл данных не изменился в размерах. Это ясно показывает, что размер журнала транзакций имеет мало общего с размером данных. На размер журнала оказывает влияние интенсивность, с которой происходит изменение (DML) базы данных.

Как обслуживать журнал транзакций?

Администраторы баз данных, которые обслуживают экземпляры SQL Server установок IaaS, должны регулярно делать резервные копии журналов транзакций. Полезно иметь конфигурации аварийного восстановления, такие как Log Shipping или AlwaysOn AG. Подобные конфигурации выполняют резервирование автоматически.

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

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

Рис. 3: Вывод кода в листинге 6

Мы можем также сжать физический журнал транзакций с помощью кода, приведенного в листинге 7. Перед сжатием проверьте, что у вас имеется резервная копия журнала транзакций. В условиях производства лучше сделать расписание создания бэкапов журнала, чтобы избежать неконтролируемого роста файла журнала транзакций и гарантировать сохранение данных. При сконфигурированной опции аварийного восстановления типа Log Shipping или AlwaysOn AG это уже гарантируется.

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

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

Рис.4: Используемое пространство после выполнения кода в листинге 7

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

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

Местоположение файла журнала транзакций должно иметь надлежащий размер, чтобы удовлетворять длительным транзакциям, которые происходят время от времени. В противном случае журнал транзакций может заполнить все дисковое пространство. На рис.4 показано, что происходит с журналом транзакций, когда делается бэкап. Обратите внимание, что физический файл по-прежнему имеет размер 40Мб, но теперь у нас есть около 37Мб свободного пространства.

Рис.5: Журнал транзакций после создания резервной копии

Что происходит при простой модели восстановления?

Теперь давайте установим для базы данных tranlogexperiment простой режим восстановления.

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

На рис.6 показан рост журнала транзакций при простом режиме восстановления, когда мы выполняем код из листинга 4. Размер физического файла журнала всего 15Мб. Это вдвое меньше, чем он был ранее при использовании полной модели восстановления. Также заметим, что свободное пространство составляет 11,5Мб.

Рис.6: Рост журнала после выполнения кода в листинге 4 при простом режиме восстановления

Означает ли это меньший рост журнала?

Нет. На рис.7 показано, что в процессе выполнения сессии SQL Server установил несколько контрольных точек. Это произвело усечение журнала и дало возможность транзакциям возобновлять рост журнала через определенные промежутки времени.

Рис.7: Захват контрольных точек при помощи расширенных событий

Заключение

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

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