Здравствуйте. Меня зовут Олег Юрченко. Расскажу о своём опыте создания хранилища данных для отчётов с синхронизацией близкой реальному времени.

Дело было в 2008 году, бизнес по доставке потерянного авиакомпаниями багажа в аэропортах США развивался, база данных росла. Использовалcя MS SQL Server 2005 Standard Edition, рабочая и тестовая базы данных на одном сервере, всё скромно, но перспективы роста бизнеса хорошие. Уже есть физическое разделение данных на оперативные и обработанные.

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

Всё прекрасно кроме отчётов, где идут тяжёлые запросы по всем данным. Выполняемые запросы отчётов сильно нагружают сервер и создают "некоторые неудобства", пока ещё терпимые. У заказчика проекта было понимание, что отчёты придётся выносить на отдельный сервер, чтобы не мешали оперативной работе, и надо подготовиться к этому заблаговременно. Так летом 2008 года у меня появилась большая задача сделать хранилище данных с синхронизацией близко к реальному времени (data warehouse with near real-time synchronization).

Я был в некотором изумлении: вдруг внезапно "near real-time" для простого бизнеса по доставке сумок без какой-то производственной необходимости. Только через годы понял, что это был предпродажный маркетинг.

Наличие хранилища данных для отчётов увеличит привлекательность "актива", особенно если всё красиво назвать. Вот и "data warehouse with near real-time synchronization", куда уж красивее!

Этот бизнес потом будет регулярно продаваться и менять собственников, красивое название пригодится ещё не раз.

Начало работы.

Надо проектировать обычную реляционную базу оптимизированную под имеющиеся отчёты. Причём учитывать, что работаю я один. Надо минимизировать объём ручной работы.

Время на синхронизацию ограничивает свободу проектирования. Чем времени меньше, тем меньше преобразований данных возможно. Т.е. много думать над проектированием хранилища не надо, спасибо за "near real-time". Оптимизировать нужно только то, что сильно в этом нуждается и только так, чтобы вписаться в "near real-time".

Уже понятно, что в оптимизации нуждаются только запросы отчётов, использующие большие таблицы обработанных данных. На тот момент в системе было 92 отчета. Из них было 80 отчетов с данными об ордерах, эти отчеты нужно было оптимизировать. Остальные отчеты не нуждаются в оптимизации, а используемые ими таблицы нужно было создать как есть в хранилище данных. Отчёты были реализованы в MS SQL Server Reporting Services (SSRS), каждый отчёт вызывал свою хранимую процедуру.

Для всех этих процедур определил используемые таблицы и представления. Это сделал программой на C#, которая достаёт названия таблиц и представлений из системных таблиц и ищет их в коде процедур (использовали VSS и код каждой процедуры лежал в своём файле). Так получил предварительный список, надо с чего-то начать.

Большинство отчётов по ордерам использовали одно большое представление, соединяющее четыре десятка таблиц, остальные можно перевести на это представление, что я и сделал.

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

Нужно было быстро оценить перспективы упрощения этого представления для хранилища данных.

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

Сделал в местной рабочей базе проекта тестовое представление только с этими колонками и почистил его от ненужных таблиц. Осталось 18 таблиц. (Сложные вычисления стоимости доставки багажа, разные цены для разных типов расчётов. Сложные схемы расчётов нужны для переговоров с потенциальными клиентами, чтобы аналитики авиакомпаний считали деньги/выгоду, чем больше сил потратят на расчёты, тем сложнее им будет отказаться от сотрудничества.)

Далее, на копии тестовой базы (восстановил из бэкапа под другим названием) изменил определение представления на тестовое и выполнил все процедуры отчётов с параметрами, дающими пустой результат ('0' передавал в строки, 0 в числа и 01.01.2000 в даты), с записью в файл сообщений об ошибках (опять программа на C#). Так по ошибкам нашёл недостающие колонки и предварительно протестировал представление.

Подумал над разделением хранилища данных на оперативные и обработанные данные, аналогично рабочей базе данных. Нашёл это нужным и полезным.

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

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

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

Проект хранилища данных был готов.

Вместо всех таблиц с данными ордеров, в хранилище сделал только две таблицы (оперативная и обработанная), которые через UNION ALL объединены в то самое новое представление для отчётов ордеров в хранилище данных (используется partitioned view MS SQL Server).

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

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

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

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

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

И ещё есть "план Б" на случай фатальных проблем с хранилищем данных. Есть возможность переключить источник данных в Reporting Services (SSRS) на рабочую базу данных и гонять отчёты там, пока решаются проблемы с хранилищем. "План Б" не понадобился ни разу, но возможность предусмотрена была сразу, хранимые процедуры отчётов были в обоих базах и давали один и тот же результат.

Подготовка к синхронизации.

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

Таблицы изменений сделал однотипными: внутренний первичный ключ таблицы изменений (int IDENTITY), колонки первичного ключа рабочей таблицы, колонка типа изменения char(1) со значениями 'I','D','U', время изменения datetime, и флаг переноса bit.

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

Триггеры тоже однотипные, по одному на INSERT, UPDATE, DELETE.

Сначала база данных хранилища должна была быть на том же сервере, но с перспективой переноса на другой сервер (когда появится), соединение с которым будет оформлено как связанный сервер (linked server).

Нужно было учитывать эту перспективу, между разными серверами хорошо работает только INSERT. UPDATE и DELETE нужно выполнять командами, работающими на связанном сервере с данными, которые уже находятся на связанном сервере.

В хранилище данных сделал таблицы для переносимых изменений данных и таблицы для первичных ключей удаляемых записей. Добавленные записи решил сразу добавлять в таблицу хранилища прямым INSERT из рабочей базы.

Весь код для таблиц и триггеров сгенерировал программой на C#.

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

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

Алгоритм переноса данных обычной таблицы был такой. Сначала в таблице изменений проставляется флаг переноса изменений в 1 с условием на время изменений не больше серверного времени запуска приложения синхронизации. Эти отмеченные изменения данных будут переноситься в хранилище.

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

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

А вот с изменением данных пришлось подумать. Надо было как-то оптимально достать из представления задействованные данные по изменениям в 18 таблицах изменений и перенести их в таблицу переносимых изменений данных хранилища и потом сделать на хранилище UPDATE вычисляемой таблицы этими перенесёнными данными. Эта часть синхронизации могла быть по времени дольше всего остального вместе взятого, это зависело от объёмов пересчёта.

Теперь про допущенные ошибки.

1. Данные достаются по ключу из рабочей таблицы. Эти данные уже могут быть свежее планового времени переноса, могли успеть измениться. Надо было в таблицах изменений хранить не только ключ, но и данные из триггерных таблиц inserted (для INSERT и UPDATE) и deleted (DELETE). К рабочим таблицам синхронизация не должна обращаться, все данные есть в таблицах изменений.

2. Не надо было сразу делать вычисляемую таблицу для оперативных данных ордеров. В хранилище было бы лучше изначально для оперативных данных иметь представление над 18 таблицами. И только при необходимости заняться оптимизацией. Синхронизация была бы намного легче. А синхронизация должна быть "near real-time", тяжеловато раз в минуту запускать синхронизацию таблицы с зависимостью от изменений в 18 рабочих таблицах. Выгода от оптимизации вычислений отчётов может быть меньше потерь от нагрузки от такой синхронизации. Надо было сначала сделать как проще, а уже по ситуации решать про оптимизацию.

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

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

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

Похоже, идея секционированных представлений появилась из идеи оптимизации запросов из представлений на основе запроса с UNION ALL и константными колонками:

CREATE VIEW SomeView
AS
SELECT
0 as SourceColumn,
.....
UNION ALL
SELECT
1 as SourceColumn,
.....

Если в условии запроса из этого представления указать SourceColumn = 0, то запрос будет работать только на первом подзапросе. Если SourceColumn = 1, то на только на втором.

Т.е. будет оптимизация аналогичная секционированным представлениям. Если в СУБД есть секционированные представления, то такая оптимизация уже должна быть (но обязательно проверить, может это только Microsoft такой умный).

Эти ошибки так и остались. Как объяснить заказчику его выгоду оттого, что отчёты станут работать чуть медленнее, но зато какая-то невидимая синхронизация – быстрее, если она и так раз в минуту усп��вает?! Да тут сплошной убыток!

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

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

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

Блокировки.

Синхронизация идёт через короткие по времени промежутки, а два раза подряд редко что редактируется, потому больших конфликтов блокировок с работой рабочих приложений не было. Главная проблема была с отчётами, возникали блокировки с синхронизацией. Если выполнение отчёта задерживается из-за синхронизации, то это не надолго. А вот наоборот – проблема.

Достаточно было включить на базе хранилища READ_COMMITTED_SNAPSHOT и проблема решена. READ_COMMITTED_SNAPSHOT меняет только поведение уровня изоляции READ COMMITTED. Запрос отчёта не ждёт выполнения транзакции синхронизации и берёт прежние зафиксированные данные. Это как если бы отчёт запустился чуть раньше, до начала синхронизации.

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

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

Или включить уровень изоляции READ UNCOMMITTED на конкретном поиске или включить READ_COMMITTED_SNAPSHOT на всей базе данных.

Что касается поиска. Поиск в изоляции READ UNCOMMITTED может давать данные, которые транзакция откатит. Но транзакции чаще завершаются успешно. А если нет, то это или техническая проблема или какая-то недоработка работника, который чуть позже всё сделает как надо. Эти незафиксированные данные чаще оказываются правильными, чем неправильными.

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

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

Есть побочные эффекты. Включенный READ_COMMITTED_SNAPSHOT влияет на загрузку данных в форму просмотра/редактирования при уровне изоляции READ COMMITTED. Нет ожидания выполняемых транзакций и могут быть неактуальные прошлые данные. Или повышать уровень изоляции или оставить как есть. В моём случае проблем не ожидалось.

Работники бизнеса очень редко одновременно делали одну и ту же работу. Их работа была разделена правами доступа. У работника есть права работать с набором троек (аэропорт, авиакомпания, компания по доставке). Все поиски выдают только разрешённые работнику данные. При сохранении данных всегда проверяется конфликт изменений. Если бы вдруг появились частые конфликты изменений, то надо было бы разделить работу работников. Ошибки про конфликты изменений не только предохраняют данные от перезаписи, но и помогают настроить производственные процессы.

Ещё при READ_COMMITTED_SNAPSHOT могут быть проблемы с разовой проверкой на правильность какого-то утверждения без сохранения блокировок. Например, надо выполнить проверку про необходимость послать какое-то важное письмо или SMS, и только при выполнении условия выполнить отправление. В этом случае изоляции READ COMMITTED будет мало, запрос может работать с прошлой версией каких-то данных и результат может быть неправильным. Надо повышать уровень изоляции даже без необходимости сохранения блокировок. Блокировки сразу освободятся после проверки (отправка письма или SMS должна быть вне транзакции).

Каких-либо проблем с READ_COMMITTED_SNAPSHOT я не видел. Я знал весь T-SQL код проекта и была уверенность, что проблем не будет.

(В 2006 году перевёл базу данных на ANSI стандарт и проверил весь код на соответствие стандарту, где надо – исправлял и тестировал.

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

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

И ещё при переходе на MS SQL Server 2016 не было никаких проблем.

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

Причём, в разных приложениях по-разному. Например, в заданиях SQL Server Agent одна второстепенная опция стандарта, которая на результат не влияет, автоматически не включается и оттого планы выполнения запросов могут быть хуже, чем если выполнять их в MS SQL Server Management Studio.

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

READ_COMMITTED_SNAPSHOT меня заинтересовал как решение проблемы блокировок в синхронизации хранилища данных при извлечении данных из рабочих таблиц. Хоть проблемы небольшие, но они были.

Данные извлекаются по ключам из таблиц изменений с верхним ограничением на время изменений на момент запуска приложения синхронизации. Если где-то какая-то транзакция запущена, то при READ_COMMITTED_SNAPSHOT вместо ожидания будут браться прежние данные. И это будут, вероятно, как раз те данные, которые были после прошлых изменений, которые и переносятся в данный момент синхронизацией. А новые изменения той транзакции появятся в таблице изменений с новым временем и попадут в хранилище при следующем запуске синхронизации.

В итоге опцию READ_COMMITTED_SNAPSHOT включил и на рабочей базе данных.

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

Стандартная изоляция – это отличный вариант для OLTP приложений, как раз для OLTP приложений эти уровни изоляции и сделаны.

READ_COMMITTED_SNAPSHOT – достойная альтернатива стандартному READ COMMITTED.

В MS SQL Server есть оба варианта на выбор. Это очень правильно, что не отказались совсем от стандартного READ_COMMITTED, а просто добавили альтернативный вариант.

Уровни изоляции из стандарта явились миру в первой версии DB2 в 1982 году, потом под другими названиями попали в стандарт. Больше 20 лет этим уровням изоляции не было альтернатив. И неплохо с эти жили. И сейчас неплохо живут спустя 40 лет. Но появилась возможность выбирать вариант по ситуации и это прекрасно.

Ещё в MS SQL Server есть изоляция снимков (SNAPSHOT). В Azure SQL Database включена по умолчанию. Эта изоляция даёт интересные возможности, но и ресурсы потребляет больше остальных.

(Первопроходцы в этой изоляции – пользователи PostgreSQL. Проблемы с PostgreSQL в мире OLTP приложений известны. В OLTP короткие транзакции в больших количествах, которые плодят версии записей/кортежей. Множество версий записей/кортежей "раздувают" таблицу, автоматическое "сдувание" может не успевать и может приключиться переполнение счётчика транзакций, который 32 бит в обычном PostgreSQL.

Как с этим бороться? Локализовать проблему, вывести большую часть данных из много-версионности.

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

Пусть PostgreSQL "надувает" и "сдувает" небольшие таблицы, а в большие с этим не лезет, нет там такой работы. Небольшие таблицы любая СУБД обслуживает быстрее, чем такие же, но большие.

Если PostgreSQL изначально предназначен для работы с небольшими базами данных и небольшими таблицами (32 бит – доказательство), то вот и будут для PostgreSQL привычные условия работы. Скорость автоматического "сдувания" будет выше, риск переполнения 32 бит – ниже.)

Приложение синхронизации и заполнение хранилища данных.

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

Появилась идея расширить эту транзакцию, добавить перенос данных счёта в обработанные данные и в хранилище данных. Но при этом данные берутся не из оперативных данных хранилища, а из рабочей базы. Данные переносимого счёта просто удаляются из оперативной части хранилища. Это для гарантии совпадения выставленных данных счетов в рабочей базе и базе отчётов.

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

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

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

Здесь решил не пытаться сразу всё сделать идеально. Сначала надо было сделать хоть какое-то работающее решение, опасался проблем с транзакциями, могли оказаться слишком большими. И напрашивающееся решение – опять расширить уже имеющееся приложение.

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

Т.е. решил максимально упростить поддержку этого приложения. В таком стиле ранее сделал перенос данных счетов из оперативных данных в обработанные. Уже был хороший опыт. И тут тоже выбрал решение в виде одной распределённой транзакции. Опять были сомнения, но опять решил попробовать сначала простой и идеальный вариант. За короткий промежуток времени изменения бывают далеко не во всех таблицах, потому транзакция обычно небольшая.

Синхронизацию оперативных данных сделал отдельным кодом в рамках этого приложения, чтобы при необходимости бы��о просто разделить. В конфигурационный файл добавил промежуток времени для запуска переноса обработанных данных. Запуск приложения запланировал через Task Scheduler, если ещё работает, то нового запуска не делать.

Когда всё было готово, сделал на сервере заказчика тестовое хранилище для тестовой базы, настроил расписание запуска тестового приложения синхронизации, в MS Reporting Services для тестовых отчётов поменял базу данных в источнике данных (только отредактировал Data Source, сами отчёты менять не надо). И пошло тестирование на стороне заказчика.

После окончания тестирования пришло время заполнения хранилища. Это можно было делать по частям. Сначала создал базу данных хранилища. Несколько дней заполнял таблицу обработанных данных для ордеров. Постепенно счёт за счётом в промежуток с 3:00 до 6:00 серверного времени. Каждый счёт со своими ордерами добавлялся в своей транзакции. Никаких рабочих приложений не останавливал.

Потом в согласованный с заказчиком день с 3:00 до 6:00 серверного времени выполнил остаток работы. Остановил все приложения, сделал в рабочей базе таблицы изменений и триггеры, заполнил оперативную часть хранилища, настроил расписание запуска приложения синхронизации, в MS Reporting Services поменял базу данных в источнике данных для отчётов и запустил все рабочие приложения.

Первоначально обе базы были на одном сервере, связанного сервера не было, работа шла напрямую. Разделение рабочей базы и хранилища для отчётов по разным серверам произошло только при очередном переезде на новые сервера уже c SQL Server 2014 Enterprise и AlwaysOn Availability Groups. Наконец нашёлся покупатель бизнеса с деньгами на отказоустойчивость. 6 лет базы данных были на одном сервере. И вот 3 сервера в кластере... Один резервный сервер на двоих.

Когда обе базы становятся primary на резервном сервере, приложение синхронизации должно работать с базой хранилища напрямую, обращение через связанный сервер даёт ошибку. Надо было сравнивать имена серверов рабочей базы и хранилища данных. Если разные, то работать через связанный сервер (linked server). Если одинаковые, то работать напрямую (сам с собой сервер не связывается).

Но надолго на одном сервере они не оказывались, при нагрузке разбегались по своим "домикам". Та же ситуация с тремя серверами была и при переезде на сервера следующего собственника бизнеса с SQL Server 2016.

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

Читал "кабанчика" Мартина Клеппмана и радовался за счастливчиков: репликация, секционирование и на это всё есть деньги.

- Эх, Киса, - сказал Остап, - мы чужие на этом празднике жизни. (с)

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

Хранилище данных с синхронизацией близкой реальному времени