Как мне создать и синхронизировать объединенную базу данных только для отчетов из двух живых баз данных?

Question

Мне нужно быстро реализовать базу данных только для чтения, содержащую данные, извлеченные из двух идентично структурированных живых баз данных.

Живые базы данных на самом деле являются корпоративными базами данных из системы учета Dynamics, поэтому я рад любым советам, связанным с Dynamics, но в основном это вопрос SQL. Это довольно старая версия Dynamics до того, как Microsoft приобрела Great Plains. Это на SQL Server 2000.

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

Мне кажется, что самое простое решение состоит в том, чтобы создавать отчеты только БД с объединенными данными. Предпочтительно, нам нужен эффективный способ обновления этой базы данных с изменениями несколько раз в день.

Я разработчик, а не эксперт по БД, но вот мой план:

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

Кажется, что все таблицы Dynamics имеют столбец с идентификационной информацией int, называемый DEX_ROW_ID. Я не уверен, для чего он используется (он не индексируется), но это кажется очевидным универсальным способом уникальной идентификации строк. На БД отчетности я изменю его на обычный int (не тождество). Я создам уникальный индекс на DEX_ROW_ID во всех БД.

В Dynamics нет меток времени, поэтому я добавлю столбец меток времени в таблицы в динамических БД и соответствующий двоичный столбец (8) в БД отчетности. Я предполагаю и надеюсь, что динамика не будет расстроена дополнительным индексом и столбцом.

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

Предполагая, что моя база данных отчетов называется "Отчеты", "живые" базы данных - "Live1" и "Live2", столбец меток времени - "TS", а все базы данных - на одном и том же сервере... вот моя первая попытка сценария обновления для копирования изменений в одну таблицу с именем MyTable. в Live1 к отчетности дб.

USE Reports

CREATE TABLE #Changes
(
ReportId int,
LiveId int
)

/* Collect in a temp table the ids or rows which have been deleted or changed
in the live db L.DEX_ROW_ID will be null if the row has been deleted */

INSERT INTO #Changes
SELECT R.DEX_ROW_ID, L.DEX_ROW_ID
FROM MyTable R LEFT OUTER JOIN Live1.dbo.MyTable L ON L.DEX_ROW_ID = R.DEX_ROW_ID
WHERE R.CompanyId = 1 AND L.DEX_ROW_ID IS NULL OR L.TS <> R.TS

/* Delete rows that have been deleted or changed on the live db 
I wonder if using join syntax would run better than the subquery. */
DELETE FROM MyTable
WHERE CompanyId = 1 AND DEX_ROW_ID IN (SELECT ReportId FROM #Changes)

/* Recopy rows that have changed in the live db */
INSERT INTO MyTable
SELECT 1 AS CompanyId, * FROM Live1.dbo.MyTable L
WHERE L.DEX_ROW_ID IN (SELECT ReportId FROM #Changes WHERE LiveId IS NOT NULL)

/* Copy the rows that are new in the live db */
INSERT INTO MyTable
SELECT 1 AS CompanyId, * FROM Live1.dbo.MyTable
WHERE DEX_ROW_ID > (SELECT MAX(DEX_ROW_ID) FROM MyTable WHERE CompanyId = 1)

Затем сделайте то же самое для Live2 db. Повторите для каждой таблицы в отчетах. Я знаю, что должен использовать параметр @CompanyId вместо литерала, но я не могу сделать это для живого имени БД, я мог бы сгенерировать их динамически с помощью программы на C# или чего-то еще.

Я ищу любые советы, предложения или критику в отношении того, что я делаю здесь. Я знаю, что это не будет атомным. Во время работы этого сценария на live db может происходить нечто. Я думаю, что мы можем жить с этим. Мы, вероятно, сделаем полную копию либо ночью, либо еженедельно, когда на живых базах данных ничего не происходит.

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

На самом деле мы не хотим иметь дело с файлами журналов в базе данных отчетов. Можем ли мы просто установить это для простой модели восстановления и забыть о журналах?

Спасибо

Answer 1

Я думаю, что здесь есть пара открытых вопросов.

1. Вам нужны эти отчеты почти в реальном времени? Или это такая отчетность, которая может жить с ежедневными обновлениями? Но предположим, что вам нужны самые свежие данные.

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

3. Тридцать секунд (IMHO) неприемлемы для любого отдельного запроса к производственной базе данных. Может быть много причин, связанных с настройкой, чтобы занять это время, но это по крайней мере означает, что вам понадобятся серьезные профессиональные ресурсы по оптимизации SQL Server (то есть люди). И если это является проблемой для запросов к отчетам, это не сулит ничего хорошего для поддержания отдельной базы данных для отчетов.

4. Запомните, что, если вам нужно объединиться в единую базу данных, стоит подумать о том, следует ли вам сделать это базой данных OLAP, а не зеркалом. Зеркало будет быстрее и проще, но OLAP будет гораздо более гибким и мощным в долгосрочной перспективе; и было бы хорошо пройти весь путь с самого начала.

Answer 2

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

1. Будем надеяться, что ваши производственные базы данных будут сохранены. Восстанавливайте эти резервные копии каждую ночь на сервере отчетов. Вы можете автоматизировать восстановление с помощью команды RESTORE, которая будет работать с файлом на сетевом сервере.
2. Используйте репликацию SQL Server для передачи данных с активных серверов на серверную часть.
3. Планируйте пакет DTS каждую ночь, чтобы импортировать всю производственную базу данных.

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

Метод 1 обладает дополнительным преимуществом, служа в качестве подтверждения резервного копирования.:)