Копия базы 1С для отчетов. Или как выжить с тяжелой отчетностью

YPermitinвSQL Server

2019-04-19

#1С:Предприятие

#оптимизация

#производительность

#SQL Server

#базы данных

#репликация

#масштабирование

#AlwaysOn

Проблема на пустом месте?

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

Сами отчеты бывают разные. От простых оборотно-сальдовых ведомостей по счету, до замудренных отчетов с анализом данных за предыдущие года, при этом с применением различных формул и параметров расчета.

Не удивительно, что в периоды отчетности или закрытия месяца многие компании сталкиваются с замедлением работы своих систем на базе 1С или НЕ 1С. Ведь один тяжелый отчет при формировании может “съесть” больше ресурсов, чем работа всей системы за неделю и даже больше.

В мире энтерпрайза за пределами экосистемы 1С принято разделять базы на две категории:

Операционные (OLTP), где ведется основная работа бизнеса. Работа этих систем критична для бизнеса, а остановка в случае нештатных ситуаций может стоить компании значительных средств.
Отчетные (OLAP), предназначены для сбора различных видов отчетов. За счет изолирования от операционной базы, формирование тяжелой аналитической отчетности не повлияет на производительность и стабильность ее работы. Обычно остановка этих баз не так сильно отзывается на работе компании.

Создать отдельную базу для отчетов можно несколькими путями:

Сделать копию операционной базы данных.
Организовать хранилище данных.

Мы же сегодня рассмотрим несколько подходов по созданию копии базы 1С для отчетов. Начнем с простого, а закончим чем-нибудь хардкорным.

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

Ах да, все примеры для клиент-серверных баз в контексте работы со SQL Server. Что-то будет актуально и для PostgreSQL.

Классический подход

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

Формирование и последующее разворачивание бэкапа делается через SQL Server Managment Studio в несколько простых шагов.

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

Создаем резервную копию

Сделать бэкап можно через удобный интерфейс с помощью SQL Managment Studio (SSMS), который также поставляется с некоторыми дистрибутивами SQL Server в комплекте, но можно использовать и последнюю версию с сайта Microsoft.

Для этого нужно иметь соответствующие права доступа на инстансе SQL Server, зайти в SSMS и перейти к настройкам резервного копирования.

Откроется окно настроек резервного копирования в разделе “Общие”. Поскольку пример у нас простейший, то тут достаточно указать путь сохранения файла бэкапа и опцию “Архивная копия только для копирования”. Последняя настройка нужна для того, чтобы SQL Server не учитывал этот бэкап в последовательности резервных копий при работе основной стратегии бэкапирования.

Далее запускам и ждем завершения операции создания резервной копии.

Когда увидим заветное окно о завершении, то можно говорить о грандиозном успехе! Все получилось!

Разворачиваем резервную копию

С помощью SQL Managment Studio (SSMS) также развернем копию базы. Для этого перейдем к разделу “Базы данных” в SQL Managment Studio (SSMS).

Далее укажем основные параметры: путь к файлу резервной копии и имя базы данных. Если база данных уже существует и ее нужно полностью обновить, то на вкладке “Параметры” установите “Перезаписать существующую базу данных (WITH REPLACE)”.

Ожидаем процесс восстановления базы.

И … готово!

Отлично, копия базы готова!

Вот и все! У нас есть актуальная (пока что!) база, для которой можно создать новую информационную базу на сервере 1С и использовать изолированно от рабочего окружения.

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

Небольшой итог

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

Плюсы:

Простота реализации и настройки.

Минусы:

Копия базы быстро теряет актуальность.
Подходит только для формирования отчетов в закрытом периоде, где данные уже не изменяются. В открытом периоде данные могут быть некорректные / неактуальные.
Актуализация только по требованию, когда нужны свежие данные “еще вчера”.

Просто, эффективно, но медленно!

Скриптуем все!

Но что, если нужно обновлять отчетную базу чаще? Например, раз в сутки?

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

Например, так мы сформируем бэкап.


BACKUP DATABASE [SuperDatabase] 
	TO  DISK = N'F:\DBs\MSSQL14.MSSQLSERVER\MSSQL\Backup\SuperDatabase.bak' 
	-- Указываем флаг "Архивная копия только для копирования"
	-- Подробнее: docs.microsoft.com/ru-ru/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-2017
	WITH  COPY_ONLY, 
	NOFORMAT, 
	NOINIT,  
	NAME = N'SuperDatabase-Полная База данных Резервное копирование', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD, 
	STATS = 10

А потом обновим отчетную базу.


RESTORE DATABASE [SuperDatabase_ForReports] 
	FROM  DISK = N'F:\DBs\MSSQL14.MSSQLSERVER\MSSQL\Backup\SuperDatabase.bak' WITH  FILE = 1,  
	MOVE N'SuperDatabase' TO N'F:\DBsMSSQL14.MSSQLSERVER\MSSQL\DATA\SuperDatabase_ForReports.mdf',  
	MOVE N'SuperDatabase_log' TO N'F:\DBs\MSSQL14.MSSQLSERVER\MSSQL\DATA\SuperDatabase_ForReports_log.ldf',  
	NOUNLOAD,
	-- Перезаписываем базу данных, если она уже была создана ранее
	REPLACE,
	STATS = 5

Чтобы этот процесс выполнялся автоматически раз в сутки создадим задание на SQL Server.

Просто добавим задание, которое автоматически будет создавать резервную копию и разворачивать вне рабочее время.


USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'AutoUpdateDatabaseForReport', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'AutoUpdateDatabaseForReport', @server_name = N'YY-COMP'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AutoUpdateDatabaseForReport', @step_name=N'Сформировать бэкап', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'BACKUP DATABASE [SuperDatabase] 
	TO  DISK = N''F:DBsMSSQL14.MSSQLSERVERMSSQLBackupSuperDatabase.bak'' 
	-- Указываем флаг "Архивная копия только для копирования"
	-- Подробнее: docs.microsoft.com/ru-ru/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-2017
	WITH  COPY_ONLY, 
	NOFORMAT, 
	NOINIT,  
	NAME = N''SuperDatabase-Полная База данных Резервное копирование'', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD, 
	STATS = 10', 
		@database_name=N'master', 
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AutoUpdateDatabaseForReport', @step_name=N'Обновить базу для отчетности', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'RESTORE DATABASE [SuperDatabase_ForReports] 
	FROM  DISK = N''F:\DBs\MSSQL14.MSSQLSERVER\MSSQL\Backup\SuperDatabase.bak'' WITH  FILE = 1,  
	MOVE N''SuperDatabase'' TO N''F:\DBs\MSSQL14.MSSQLSERVER\MSSQL\DATA\SuperDatabase_ForReports.mdf'',  
	MOVE N''SuperDatabase_log'' TO N''F:\DBs\MSSQL14.MSSQLSERVER\MSSQL\DATA\SuperDatabase_ForReports_log.ldf'',  
	NOUNLOAD,
	-- Перезаписываем базу данных, если она уже была создана ранее
	REPLACE,
	STATS = 5', 
		@database_name=N'master', 
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'AutoUpdateDatabaseForReport', 
		@enabled=1, 
		@start_step_id=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@description=N'', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'', 
		@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'AutoUpdateDatabaseForReport', @name=N'Каждую ночь', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20190421, 
		@active_end_date=99991231, 
		@active_start_time=20000, 
		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

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

Плюсы:

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

Минусы:

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

Реплицируем через репликацию

Другой способ - это репликация стандартными средствами SQL Server. На самом деле, это не самый подходящий вариант передачи изменений баз 1С в их копию, потому что для его эффективной работы необходимо было бы добавить первичные ключи во все таблицы. Платформа 1С этого не делает. Конечно, можно было бы добавить ключи самостоятельно и, о боже, поддерживать при реструктуризации. Но, даже это бы не помогло, потому что для некоторых таблиц ключ просто невозможно добавить. Например, для таблицы “DBSchema”, в которой только одно поле с типом “varbinary(max)”.

Вообще, есть несколько основных видов репликации:

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

По этой ссылке можно найти подробную документацию по кажодй из них.

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

Мне удавалось запустить репликацию базы данных 1С через метод “Репликация транзакций”, который был бы идеальным, если бы платформа корректно создавала первичные ключи для таблиц. Но мир не идеален, поэтому пришлось выполнять несколько дополнительных действий:

Добавляем первичные ключи во все таблицы где есть кластерный индекс.
После добавляем ключи в таблицы “кучи”, если это возможно.
Для таблиц, где первичный ключ добавить нельзя (например, та же таблица “DBSchema”) добавляем свое числовое поле “ID” с автоинкрементом, которое и будет первичным ключом. Платформа 1С ничего об этом поле знать не будет.

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

И так, что имеем.

Плюсы:

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

Минусы:

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

Таким образом, это для лютых извращенцев, которые не ищут легких путей! :)

Копия в реальном времени

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

Репликация при AlwaysOn может быть двух типов:

Синхронная - когда транзакция фиксируется сразу же на двух узлах базы данных.
Асинхронная - когда транзакции фиксируются на основной реплике и изменения асинхронно передаются на второй узел с некоторой задержкой.

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

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

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

SQL Server 2012 и выше редакции Enterprise Edition. Также функционал доступен для Standard Edition, но с ограничениями:
- Максимум 2 реплики (первичная и вторичная)
- Нет доступа на чтение для второй реплики
- Только одна база в каждой группе доступности.
Операционная система Windows Server 2012 и выше.
Настроенный отказоустойчивый кластер Windows (WSFC).

Таким образом, нужен квалифицированный администратор, лицензии на Windows Server и SQL Server редакции Enterprise. Цена может подходить не для всех. Процесс настройки рассматривать в статье не будем, но ознакомиться с самой простой конфигурацией по шагам Вы можете по следующим ссылкам:

Сейчас же остановимся на некоторых особенностях работы баз 1С с репликами AlwaysOn. Допустим, у нас сделана настройка асинхронной реплики. База данных на втором узле практически всегда находится в актуальном состоянии. Но находится она в режиме только для чтения! Мы развернули отдельный сервер 1С, добавили эту базу и попытались зайти в нее в режиме 1С:Предприятие. Первое, что мы увидим будет…

Невосстановимая ошибка Ошибка при выполнении запроса POST к ресурсу /e1cib/modules/call: по причине: Соединение с сервером баз данных непригодно для использования после разрыва соединения администратором и будет переустановлено. Microsoft SQL Server Native Client 11.0: Failed to update database “YourDatabaseName” because the database is read-only. HRESULT=80004005, SQLSrvr: SQLSTATE=25000, state=2, Severity=10, native=3906, line=1

Платформа 1С не поддерживает работу с базой в режиме “Только для чтения”, т.к. пытается сохранять различные настройки форм, отчетов, историю работы в служебные таблицы. В этом случае есть несколько решений:

Использовать толстый клиент обычного приложения с некоторыми модификациями конфигурации.
Формировать отчеты через интеграцию (COM-соединение, веб-сервисы, HTTP-сервисы, OData-интерфейс).

Готовые решения тут отсутствуют, т.к. случаи очень разные, но общий подход должен быть понятен. Есть и другие нюансы при работе с AlwaysOn, не только в контексте 1С.

Подробнее Вы можете прочитать здесь.

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

Мысли напоследок

Вы дочитали до конца и задались вопросом: “Почему не использовать стандартные возможности платформы?”. Например, создать базу и наполнять ее через обмены или вообще сделать узел УРБД. Справедливый вопрос! Но и ответ простой - скорость и производительность!

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

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

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

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

Хорошим и дешевым был бы способ репликации транзакций, но из-за особенностей баз 1С его сложно применять.

Самым доступным и распространенным остается способ через бэкапирование и разворачивание резервных копий со всеми вытекающими недостатками и ограничениями.

Все вышеперечисленное не является готовым решением или истиной, а лишь поверхностно отражает накопленный опыт автора.