Периодическое копирование, синхронизация, пересылка и слияние таблиц базы данных SQL с использованием MSSQL?

Содержание

Вопрос или проблема
Мой предварительный план
На каждой рабочей станции:
На централизованной рабочей станции:
Ответ или решение
Теория
Пример
Применение
Заключение

Вопрос или проблема

В нашем процессинговом заводе идет проект по модификации, связанный с приложением SCADA (система автоматизации процессов), и я ищу надежные советы/рекомендации от опытных участников, касающиеся управления базами данных SQL.

Контекст: У нас есть несколько рабочих станций, каждая с установленным автономным приложением SCADA и SQL Express для записи аварий в реальном времени в базу данных SQL и периодического чтения. Нам нужно пересылать эти таблицы аварий с SQL-базы данных всех станций на централизованную станцию, объединять все таблицы аварий в одну и затем импортировать в SQL Express на централизованной станции для целей только чтения. Эти задачи необходимо автоматизировать и запускать с подходящей частотой (например, каждые 1 час).
Обратите внимание, что все рабочие станции являются рабочими группами и автономными.

Также обратите внимание, что связь между рабочими станциями и централизованной станцией строго односторонняя, т.е. Рабочая станция -> Централизованная станция (поэтому нет возможности для репликации SQL Server).

Мой предварительный план

На каждой рабочей станции:

Дублирование Production_DB в Offline_DB (в том же экземпляре SQL Server)
Синхронное/асинхронное обновление таблицы аварий из Production_DB в Offline_DB.
Периодическая конвертация таблицы аварий из Offline_DB в CSV.
Пересылка CSV на централизованную рабочую станцию.

На централизованной рабочей станции:

Создание Workstation_DB для каждой рабочей станции в экземпляре SQL Server.
Конвертация и импорт CSV-файла в Workstation_DB в таблицу аварий.
Создание одного Central_DB и объединение всех таблиц аварий рабочих станций в одну таблицу.
Назначение Central_DB приложению SCADA на центральной рабочей станции.

Пожалуйста, посоветуйте, нужно ли откорректировать или оптимизировать этот предварительный план.

Буду благодарен, если кто-то также проведет меня через выполнение каждого шага в SQL (за исключением шага 4 и шага 8).

Ответ или решение

Приветствую! Рассмотрим ваш проект по управлению SQL базами данных в контексте модификации приложения SCADA (Системы управления технологическими процессами). Основная задача – это перенос, синхронизация и слияние таблиц с уведомлениями (Alarm Tables) из различных рабочих станций на центральную станцию для последующего анализа.

Теория

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

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

Основные шаги:

Обеспечить дублирование и обновление данных в локальной базе данных на рабочей станции.
Экспортировать таблицы с уведомлениями в формат CSV.
Передать CSV файлы на центральную станцию.
Импортировать данные с различных станций в централизованную базу данных.

Пример

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

Для выполнения экспорта в CSV можно использовать скрипты T-SQL в сочетании с агентом SQL Server (если поддерживается) или планировщиком задач Windows, чтобы запускать их с заданной периодичностью.

Пример скрипта T-SQL, экспортирующего данные в CSV:

sqlcmd -S <ServerName>\<InstanceName> -d Offline_DB -Q "SELECT * FROM AlarmsTable" -o "C:\path\to\output.csv" -h-1 -s","

Применение

На каждой рабочей станции:
- Дублирование Базы данных: Создаем Offline_DB и настроиваем триггер или задачу для регулярного обновления данных из основной базы (Production_DB).
- Экспорт в CSV: Настраиваем экспорт с помощью sqlcmd или других утилит, чтобы выгружать таблицу с уведомлениями каждые 1 час.
- Передача CSV: Для надежной передачи файлов на центральную станцию используйте механизмы защищенного копирования, такие как SFTP или SCP, если возможно.
На центральной станции:
- Импорт CSV в индивидуальные базы данных для каждой рабочей станции (‘Workstation_DB’): Загрузите CSV данные в соответствующую таблицу используем BULK INSERT T-SQL.
```
BULK INSERT Workstation_DB..AlarmsTable
FROM 'C:\path\to\workstationCSV.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
```
- Слияние таблиц в центральной базе данных (‘Central_DB’): Убедитесь, что данные со всех рабочих станций правильно объединены. Важным шагом будет удаление дубликатов и согласование данных.
```
INSERT INTO Central_DB..ConsolidatedAlarmsTable
SELECT * FROM Workstation_DB1..AlarmsTable
UNION ALL
SELECT * FROM Workstation_DB2..AlarmsTable;
-- и так далее для каждой рабочей станции...
```
- Назначение новой базы данных в SCADA: Убедитесь, что SCADA на вашей центральной станции подключается к Central_DB и корректно интерпретирует объединенные данные для ваших нужд.

Заключение

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

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