В SQL server есть замечательная технология - AlwaysOn. Она используется для:

• DR - disaster recovery, асинхронная репликация данных

• HA - high availability, часто с automatic failover, что возможно при синхронной репликации

• И для того, что мы обсудим с статье: readonly replica для DWH/OLAP/Reporting workload.

Никто не совершенен (хотя я восхищаюсь простотой установки некоторых решений в MS SQL по сравнению с Postgre и Oracle. Хотя бы бэкапы... А AlwaysOn для маленьких баз заводится буквально в пару кликов). Из подводных камней можно упомянуть, например:

• 'Нежность' кластерного сервиса, который может упасть по недостатку памяти или нехватки CPU, в результате чего базы переходят в состояние 'RESOLVING' и недоступны. SQL так предохраняется от split brain, но получается, что базы недоступны, пока не поднимется Cluster Service и ноды не договорятся, кто primary, кто secondary

• В некоторых случаях работа Cluster service так нестабильна по непонятным причинам (я наблюдал такое в наших облаках), что приходится строить безкластерную AlwaysOn

• При failover вопрос переназначения SQL agent jobs лежит на DBA. Есть некоторые решения, но они не автоматические

Но сегодня мы рассмотрим проблемы при использовании AlwaysOn для DWH/OLAP/Reporting.

Для чего это нужно

Сама идея просто замечательная. Мы выносим на отдельный сервер нагрузку OLAP/Reporting и другие тяжелые квери. Тем самым мы разгружаем основной сервер и особенно его кэш (тяжелые запросы перенастраивают кэш под себя).

Все это работает так хорошо, что многие воспринимают эту технологию, как серебряную пулю. Вот вам online копия PROD и делайте с ней все, что хотите. Между тем в технологии есть проблемы, которые в некоторых случаях проявляются. Давайте построим тестовый макет, базу test, заведем ее в AlwaysOn и в ней создадим две таблицы:

-- primary
create table A (str varchar(128))
create table B (str varchar(128))
GO
insert into A select 'old'
insert into B select 'old'
insert into A select 'old'
insert into B select 'old'
insert into A select 'old'
insert into B select 'old'
GO

Я везде буду писать, на каком сервере - primary или secondary, выполняется скрипт. После выполнения скрипта почти мгновенно на secondary возникают обе таблицы с нужными данными.

Выполнив:

-- secondary
select * from B
select * from A

Мы увидим все данные с primary.

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

Ууу, сказали суровые сибирские мужики. И засунули веточку

Давайте создадим две коннекции на secondary, я их буду называть secondary 1 и 2. В secondary 1 выполним:

-- secondary 1
begin transaction
select * from B
select * from A

Обратите внимание, что транзакцию я не закрыл. Далее на primary делаем:

-- primary
update A set str='new1'
update B set str='new1'

Дальше выполняем select из обеих таблиц в окнах secondary 1 и 2 (на secondary 1 закомментим begin transaction). Что мы увидим?

На secondary 2 мы увидим значение 'new1'. А вот на secondary 1 мы увидим старое значение: 'old'. Почему? Потому что AlwaysOn под капотом принудительно включает snapshot isolation, чтобы readonly replica не читала грязные данные.

Но 'дельта' к snapshot хранится в tempdb. Поэтому сделав begin tran select * from TableUnderHeavyWorkload и отправившись на обед или на викенд, мы рискуем положить сервер DWH - у него просто забьется tempdb. Несмотря на то, что наша нагрузка readonly.

Честно говоря, не знаю, что произойдет дальше, когда кончится tempdb - сбросится snapshot (это хороший исход) или остановится передача данных в AlwaysOn. Если второе, то primary будет копить неподтвержденные данные в LDF, в через какое то время упадет уже primary.

Вывод - даже на readonly replica нужна гигиена, как на prod.

... и суровые сибирские мужики положили бревно

Но ведь snapshot держит дельту данных под схему таблицы. А что, если таблица меняется?

Закроем транзакцию на secondary 1. Потом снова выполним команду, начиная с begin transaction. Далее на primary:

-- primary
alter table A add col varchar(10) null
update A set str='new2'
update B set str='new2'

На secondary 2 видим 'new2'. А вот на secondary 1:

Мы получаем по рукам. Сообщение полностью, так как оно подробно объясняет случившееся:

Snapshot isolation transaction failed in database 'test' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.

... и засунули железный ломик

А что, если на secondary у нас select будет работать очень долго? Мне не хотелось закачивать в базу test гигабайты данных, я в соседней базе (не test, так как база test readonly) создал функцию:

-- secondary 1, база DBA
create function dbo.waiter(@str varchar(1000), @n int)
returns int
as
  begin
  while @n>0 set @n=@n-1
  return 1
  end

Теперь на secondary 1 запустим запрос:

-- secondary 1, в базе DBA
select * from test.dbo.A where dbo.waiter(str,50000000)=1

Команда выше работает около 100 секунд. Вы можете варьировать продолжительность с помощью второго параметра. Тем временем на primary добавим еще одну колонку:

-- primary
alter table A add secondcol varchar(10) null
update A set str='new3'
update B set str='new3'

secondary 1 продолжает выполнять запрос, так что пойдем на secondary 2 и увидим, что значение str не изменилось, а в A нет колонки secondcol. Мы остановили передачу данных AlwaysOn!

Мы можем увидеть заблокированный 'накатчик' лога:

Мы видим 'накатчик' (cmd='DB STARTUP'), заблокированный нашим select (119 это его spid), по schema lock.

Вот так-то, ..., сказали суровые сибирские мужики

То есть, долгий запрос на DWH сервере может заблокировать накатку, если запрос активен. AlwaysOn не будет пытаться прибить запрос. Он будет просто его ждать (это верно только для данной базы, накатчики на разные базы независимы). Все это время (возможно часы!) база secondary будет все больше и больше отставать.

При асинхронной AlwaysOn существует две очереди: send queue на primary, и redo queue на secondary. Можно было бы ожидать, что при заблокированном накатчике будет копиться очередь redo на secondary. Но похоже заблокированный накатчик не может передать подтверждение приема на primary ('hardened point'), так что primary не может очистить лог (LDF)

LDF файл на primary начнет расти, бэкап лога не поможет. Если запрос закончится, то ситуация разрешится сама собой (но реплика сильно отстанет). Но если не повезет - место в LDF закончится и вы услышите визг и ор.

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