Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 AlwaysOn vs репликация для построения отчетов  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
Гуру, научите как жить )
Ситуация следующая. Есть самописное приложение и БД, спроектированная когда то давно для, скажем так, обслуживания 10 объектов. Теперь объектов 600 и рост продолжается. И все бы даже ничего, наращивание серверных мощностей и апгрейд с standard sql2008r2 до enterprise 2016 вполне позволяли поддерживать приемлемую производительность. Если бы не отчетность... Понацепляли коннектов от оракла и сапа, тянут данные, ладно бы текущие, а то за месяц или год. Т.е. классический конфликт olap и oltp.
Сначала отшлифовали запросы. Где надо - хинты с нолок, где индексов добавили. Но... Структура БД такова, что не избежать конфликтов. Из "центральной" таблицы с миллиардом записей несколько отчетов хотят прочитать сотню миллионов строк и в то же время идет пакетная (и не только пакетная) вставка 10-50млн записей. Где могли - административными методами разнесли процессы, но ночь не очень то длинная, а наутро бизнесу надо вот прям видеть все и сразу. По железу - все что можно допилили: СХД, сервера, группы, пулы и ты ды - только SSD отдельных нет, точнее они в самой схд работают. Обслуживание БД (индексы, статистики) проводится регулярно. Бэкап аккуратный, полный раз в месяц, дифы, логи. Ессно, регулярно восстанавливаем, для проверки.
Пошли дальше. "Самостоятельная" репликация. Второй сервер, поднята зеркальная копия БД и с нее делается снапшот утром. Со снапом на этом втором сервере уже и работает отчетность. Для аналитических отчетов (за месяц/год), в целом, вполне рабочее решение. Но для отчетов по текущим данным - плохой вариант. Более частый снапшот, в течении дня, сбивает запущенные аналитические (которые и по 2-4 часа работают).
Почему самостоятельная. Опять же - структура БД. На некоторых таблицах просто нет индексов. Примари кей - совсем редко. "Центральная" таблица - миллиард записей, более 600гб в куче. Повесили пару некластерных индексов - плюс почти сотка гб и замедление времени вставки данных. Общий размер БД - около 2.5тб, т.е. репликация снапшотами - не вариант, где столько места взять, да и нагрузка на сеть. Транзакционная репликация жестко требует примари кей. Тут проблема, что по той же "центральной" таблице, примари ключ получится только составным, минимум по трем полям. Как это скажется на производительности - не берусь сказать. Хотя, если некластерный, то вроде и ничего должно быть? Или можно, наверное, добавить новое uid поле, допустим через мастера создания репликации слиянием это вроде делается автоматически. Но это все несет риски вмешательства в структуру БД, разработчики (сторонняя организация) и так ноют, что мы не умеем их прекрасную систему пользовать.
Вот и получается, что потихоньку пришли к выбору AlwaysOn vs репликация. Благо, что теперь есть два sql сервера энтепрайз2016.
Повторю вводные. OS - win2012r2, sql enterprise 2016sp1, RAM 80 и 140гб, CPU 24 core. СХД - дисков много, все разнесено, пулы по 24 шпинделя минимум. Размер БД 2500 Гб, 250 таблиц, топы - около десятка таблиц от 300 до 1000 млн записей. Сервера между собой могут до 4 гигабит.
Хотелось бы понять ряд вопросов, прежде чем начинать работы по тестированию.
1. Какое из этих двух решений потенциально более "нагрузочно" (по всем параметрам - сервера, сеть) в наших условиях? Допустим, синхронизация при репликации - раз в 30 минут.
2. Доступность БД "реплики" в обоих вариантах постоянная? Т.е. при накатывании данных на копию нет монопольного доступа/отключения коннектов и всего такого?
12 ноя 18, 12:23    [21731619]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7752
JohnAl,

самый дешевый по деньгам вариант - создать моментальный снимок базы, пересоздавайте раз в сутки или как надо. Всех потребителей - на снимок. Если диски быстрые - разницы не заметите. Однако, tempdb могут нагрузить в зависимости от запросов. Работает замечательно, пока умники не начинают разовые отчеты формировать через временные таблицы.
12 ноя 18, 14:11    [21731788]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
L_argo
Member

Откуда:
Сообщений: 1209
Откройте для себя BI.
Он специально заточен под отчеты и аналитику, аппетиты на которые имеют обыкновение быстро расти.
12 ноя 18, 14:37    [21731834]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
boltnik
Member

Откуда: Калуга/Москва
Сообщений: 144
Так же, если не нужны данные в реальном времени можно обойтись и лог шипингом. Ночью база восстанавливается, а в рабочий день строятся отчеты.
12 ноя 18, 14:38    [21731837]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
Eleanor
Member

Откуда:
Сообщений: 2854
JohnAl
Опять же - структура БД. На некоторых таблицах просто нет индексов. Повесили пару некластерных индексов - плюс почти сотка гб и замедление времени вставки данных.

Если вам для отчетов пригодятся индексы, но на основном сервере они сильно замедляют работу, можете создать их только на отчетном сервере. Тогда придется остановиться на репликации.
Минус решения - в репликации потом вручную будете добавлять новые таблицы, а с AlwaysOn такой проблемы нет.
12 ноя 18, 14:41    [21731840]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
Я ж говорю, моментальный снимок сейчас и используется. Но, для оперативных отчетов мало одного снимка в сутки, бизнесу хочется иметь информацию "вот прям что сейчас". Несколько снимков днем - рубят коннекты, т.е. уже запущенные, собирающиеся отчеты. Вариант с лог шиппингом имеет тот же недостаток. Ночью то информация актуальна, а вот днем...
BI у нас на оракле. Для него как раз таки и тянут из sql данные для аналитических отчетов. Понаписали вьюх...
12 ноя 18, 15:08    [21731876]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7752
JohnAl,

Вы писали о другом снимке, насколько я понял, т.к. вы его восстанавливаете на втором сервере. Если нет, то Вы можете делать несколько моментальных снимков базы и никакие соединения они не сбивают. Один делайте посуточно, второй - каждый час.
12 ноя 18, 15:15    [21731887]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
Eleanor
Member

Откуда:
Сообщений: 2854
Владислав Колосов
самый дешевый по деньгам вариант - создать моментальный снимок базы, пересоздавайте раз в сутки или как надо

ТС не нужно дешево, у него "два sql сервера энтепрайз 2016"
12 ноя 18, 15:36    [21731923]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
Еще раз. Для того, чтобы вынести нагрузку, на другом сервере поднята зеркальная копия боевой БД. С этой зеркальной копии делается снимок (снапшот может и с рековери состояния сделаться). С этим то снимком уже и работает bi - отчетность. Т.е. все запросы через снап идут к зеркальной копии, тем самым боевой сервер не нагружается.
Несколько снимков с разными именами (я правильно понял?) - не очень удобный вариант. Ибо приложение, тянущее данные в отчеты, ориентируется на имя БД. В теории программистов оракл можно озадачить, "давайте этот отчет будет идти через снап1, а этот - снап2". Но, на практике - выигрыша от возросшей частоты снапов может не случиться. Ну вот захочет бизнес видеть данные с отставанием в час. А в этом же снапе другой отчет работает полтора часа. Вот и срубит его.
Да и так зоопарк. Разные системы тащат данные к себе разными способами: промежуточные БД для вьюх, варианты БД на разные периоды времени, зеркало, снимки... И тебе в этом зоопарке жить, следить за бэкапами, целостностью и прочее. Хотелось бы уже иметь максимально простое, автоматизированное и устойчивое решение, требующее минимум обслуживания. Раз уж убедил докупить вторую лицензию на энтерпрайз...
12 ноя 18, 16:37    [21732025]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
Eleanor
Member

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

На самом деле, если вы читаете со второго, отчетного сервера, то вы уже должны покупать вторую лицензию. Хоть зеркалирование у вас, хоть AlwaysOn, хоть репликация.

Если не хотите менять структуру таблиц, создавать PK, guid, и нужно читать данные с минимальными задержками - ваш выбор AlwaysOn.
Разрывания коннектов для уже работающих отчетов быть не должно, блокировки избегаются с помощью неявной snapshot изоляции, а подсказки запросов игнорируются.

ИМХО, по сложности настройки-администрирования репликация и AlwaysOn близки.
12 ноя 18, 16:58    [21732058]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
В рамках агримента с ms у нас год на "поиграться" с любым продуктом. Ну, не в продуктиве ) Да и так то, зеркало с энтерпрайз на стандарт вполне работает.
А вот с точки зрения "нагруженности" при передаче данных, оба решения равны или кто то имеет преимущество?
12 ноя 18, 17:20    [21732089]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
komrad
Member

Откуда:
Сообщений: 5244
JohnAl
А вот с точки зрения "нагруженности" при передаче данных, оба решения равны или кто то имеет преимущество?

AlwaysOn по факту тот же мирроринг, но только у вас может быть несколько read-only копий боевой базы как синхронных, так и асинхронных
12 ноя 18, 19:43    [21732295]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
Eleanor
Member

Откуда:
Сообщений: 2854
JohnAl
В рамках агримента с ms у нас год на "поиграться" с любым продуктом. Ну, не в продуктиве )

С любым, но не в продуктиве - это MSDN / VS подписка что ли? :)

JohnAl
Да и так то, зеркало с энтерпрайз на стандарт вполне работает.

Это-то да, но тогда не совсем понятно, зачем вообще энтерпрайз, если по использованию ресурсов вы укладываетесь в стандарт.
CPU 24 core, RAM 80 и 140гб (как понимаю, на одном сервере 80Гб, на другом 140Гб - ?)

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

JohnAl
А вот с точки зрения "нагруженности" при передаче данных, оба решения равны или кто то имеет преимущество?

Сложно сказать без тестирования. Может зависеть от характера нагрузки.
С AlwaysOn на отчетный сервер будут непрерывно передаваться ровно те же транзакции, что на основном сервере.
А в merge репликации периодически будут передаваться пачки отслеженных изменений, не обязательно совпадающие с выполненной исходно транзакцией.
Главное, AlwayOn использовать в асинхронном режиме, чтобы основной сервер не ждал подтверждений от отчетного.
12 ноя 18, 22:47    [21732382]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
SERG1257
Member

Откуда:
Сообщений: 2727
мое мнение - репликация погибче будет.
При AlwaysOn у вас одна и та же база на обоих серверах, то бишь все объекты, пользователи индексы и т.д.
При репликации можно создать/удалить свои объекты в базе и послать сторонних разработчиков на мороз.
JohnAl
"Центральная" таблица - миллиард записей, более 600гб в куче
Кластеризовать и секционировать по дате хотя бы на реплике. Применить columnstore.
JohnAl
Транзакционная репликация жестко требует примари кей.
Правильно
JohnAl
можно, наверное, добавить новое uid поле
или identity. В любом варианте это будет долго, поэтому надо это сделать только один раз.
JohnAl
через мастера создания репликации слиянием
Или скриптом
JohnAl
По железу - все что можно допилили:
Допилите в базе, наймите/найдите DBA
JohnAl
Допустим, синхронизация при репликации - раз в 30 минут.
Неверная постановка вопроса. Оно будет синхронизироваться сразу, а сколько это займет 30 минут или больше покажет опыт.
JohnAl
Доступность БД "реплики" в обоих вариантах постоянная?
Да. При AlwaysOn сервер будет накатывать записи из журнала на реплике сразу, а при репликации использовать лог майнер чтобы генерировать DML из журнала и накатывать этот поток на реплику. Движущихся частей поболее будет, если вы будете реплицировать все.
13 ноя 18, 06:06    [21732496]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
PizzaPizza
Member

Откуда:
Сообщений: 365
JohnAl,

Если не совсем секрет, в какой области деятельности вы решение это строите? Мне интересно для общего развития в каких областях такие объемы данных нужны на вставку и особенно на выборку постоянно.
Пока я могу только представить довольно большую соцсеть, очень крупный новостной сайт, рекламную сеть или Московский общественный транспорт. Кто у вас генерит такой объем данных?
13 ноя 18, 07:14    [21732515]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
L_argo
Member

Откуда:
Сообщений: 1209
Кто у вас генерит такой объем данных?
Например продуктовая розничная сеть. Даже не очень большая.
13 ноя 18, 10:30    [21732677]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
Eleanor
Member

Откуда:
Сообщений: 2854
SERG1257
JohnAl
Допустим, синхронизация при репликации - раз в 30 минут.
Неверная постановка вопроса. Оно будет синхронизироваться сразу, а сколько это займет 30 минут или больше покажет опыт.

ТС подразумевал PollingInterval, который он хочет сделать равным 30 минут вместо дефолтных 60 сек.
13 ноя 18, 10:43    [21732697]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
PizzaPizza
Member

Откуда:
Сообщений: 365
L_argo
Кто у вас генерит такой объем данных?
Например продуктовая розничная сеть. Даже не очень большая.


Я прикинул по данным Магнита за 17 год - в принципе порядок цифр похож. Сеть в половину или треть Магнита (8000 магазинов и 2 млрд покупателей в год) может генерировать 50 миллионов записей в день.
Большие проекты.
13 ноя 18, 11:20    [21732755]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
L_argo, именно ) Пока 600 магазинов, но в планах еще и еще... А решение писалось более 10 лет назад под совсем небольшие сетки.
В целом, если зеркалирование не вызывает каких то проблем, думается и AlwaysOn не должно нагрузить систему. Поэтому склоняюсь к этому варианту. Конечно, в репликации очень соблазнительна возможность "допила" БД-реплик под требования отчетности. Но и возможные риски при измении исходных таблиц - тоже есть. А следить за такой целостностью пока как то и некому (
13 ноя 18, 11:49    [21732789]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
Berg
Member

Откуда: Кр-ск
Сообщений: 3480
PizzaPizza,

В магните Terradata & ORACLE.
на MS SQL там какие-то писульки да без индексов и кириллическими адскими названиями таблиц
13 ноя 18, 12:23    [21732839]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
Berg
Member

Откуда: Кр-ск
Сообщений: 3480
JohnAl
... А следить за такой целостностью пока как то и некому (

Тогда да. Точно Магнит.
13 ноя 18, 12:26    [21732843]     Ответить | Цитировать Сообщить модератору
 Re: AlwaysOn vs репликация для построения отчетов  [new]
SERG1257
Member

Откуда:
Сообщений: 2727
Да и еще такой момент
Если вы сделаете репликацию, то вам по любому нужно решение HA/DR.
Кстати как у вас с DR? Если у вас куплен software assurance то вы можете иметь еще одну реплику бесплатно. Правда читать ее уже будет нельзя (по условиям лицензионного соглашения), она будет только "ждать катастрофы".

А смотрели ли вы в сторону real time operational analytics?
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/get-started-with-columnstore-for-real-time-operational-analytics?view=sql-server-2016
Раз у вас есть некие разработчики то и пинайте их. На ваших объемах каждое изменение может занимать часы (а может и дни), так что надо семь раз отмерять.
13 ноя 18, 16:43    [21733272]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить