Справочник Бухгалтера

Сегодня рассмотрим один из вариантов обслуживания баз 1С в СУБД MS SQL.
1. Немного теории по планам обслуживания
2. Постановка задачи по созданию планов обслуживания
3. Создание плана обслуживания (Полная копия)
4. Создание плана обслуживания (Разностная копия)
5. Создание плана обслуживания (Резервная копия журналов транзакций)
6. Мониторинг планов обслуживания
1. Немного теории по планам обслуживания
Может многие со мой не согласятся, но для меня главной целью использования Планов обслуживания в MS SQL является создание резервных копий. Местные ITишники либо еще не делают резервные копии, либо уже делают, после печальных последствий отсутствия резервных копий. Да, не спорю, Планы обслуживания также нужны для оптимизации БД и выгрузки журналов транзакций, в последнем случаи, если не выполнять выгрузку журналов транзакций, у вас может вырасти база данных и занять все пространство на диске, 1С встанет колом и пользователи не смогут работать с базой, а вам придется выполнять шринк (Shrink) базы, это наверно самое страшное для ITишники после поломки базы и отсутствии резервных копий. Но об шринке (Shrink) поговорим в другой раз.
MS SQL Server поддерживает три модели восстановления:
1) Simple (Простая) — хранится только необходимый для жизни остаток журнала транзакций.
2) Full (Полная) — хранится весь журнал транзакций с момента последнего резервного копирования журнала транзакций.
3) Bulk logged (С неполным протоколированием) — часть операций записываются в очень компактном формате. В остальном идентична Full.
Модель восстановления базы можно посмотреть, в свойствах базы данных, на вкладке Параметры. Там же ее можно поменять. На практике я использую Full (Полная).

MS SQL поддерживает три типа формирования резервных копий:
1) Full (Полная копия)
2) Differential (Дифференциальная копия, Разностная копия)
3) Log (Резервная копия журналов транзакций)
Не путайте понятия: полная модель восстановления и полная резервная копия — разные вещи.
Рассмотрим подробно три типа формирования резервных копий.
1) Полная резервная копия
Позволяет восстановить состояние базы данных на некоторый момент времени. Состоит из копии файлов данных и журнала транзакций на момент завершения формирования резервной копии.
2) Разностная резервная копия
Хранит данных, изменившиеся с момента последней Полной резервной копии. При восстановлении нужно сначала восстановить Полную резервную копию в режиме NORECOVERY, потом можно применить любую из последующих Разностных копий. За счет этого можно значительно снизить объём дискового пространства для хранения резервной копии. Обратите внимание: без предыдущей Полной резервной копии Разностная копия бесполезна. Каждая последующая Разностная копия будет хранить все данные, входящие в предыдущую Разностную резервную копию, сделанную после предыдущей Полной копии. Поэтому каждая следующая Разностная копия больше предыдущих, пока снова не сделать Полную копию. Соответственно для восстановления на какой-то момент времени достаточно последней Полной резервной копии и последней Разностной копии. Промежуточные копии для восстановления не нужны.

3) Резервная копия журналов транзакций
Содержит копию журналов транзакций за некоторый период. Обычно с момента прошлой Резервной копии журналов транзакций до момента формирования текущей Резервной копии журналов транзакций. За счет этого Резервные копии журналов транзакций позволяют (с учетом Полной и Разностной копий) восстановить базу данных на любой момент времени. Резервная копия журналов транзакций высвобождает место в файле журнала транзакций, что позволяет ITишники избавиться от шринка базы данных.
Обратите внимание: набор Резервных копий журналов транзакций по сути бесполезен, если он не является непрерывной цепочкой, причем момент начала последнего успешного Полного или Разностного резервного копирования должен быть внутри периода этой цепочки.
2. Постановка задачи по созданию планов обслуживания
В организации N работают по шестидневке с 8:00 до 17:00. Обед с 12:00 до 13:00.
Имеется в MS SQL база данных с именем Moodle.
Что нужно сделать:
1) Проверить модель восстановления базы данных, должна быть Полная.
2) Создать план обслуживания, который будет создавать Полную резервную копию базы данных каждое воскресение в 17:00. Очищать хранилище от устаревших резервных копий старше 15 дней.
3) Создать план обслуживания, который будет создавать Разностную копию базы данных каждый день в 21:00 кроме воскресения.
4) Создать план обслуживания, который будет создавать Резервную копию журналов транзакций два раза в день, в 12:00 и в 17:00, кроме воскресения.
3. Создание плана обслуживания (Полная копия)
Запускаем SQL Server Management Studio, в Обозревателе объектов проходим по ветке Управление — Планы обслуживания.
Правой кнопкой по пункту Планы обслуживания и в контекстном меню выбираем Создать план обслуживания… Указываем имя, к примеру: Moodle. В открывшемся конструкторе будем создавать вложенные планы обслуживания. щелкните два раза по строке ВложенныйПлан _1
Задайте Имя, Описание и обязательно настройте Расписание выполнения вложенного плана обслуживания: еженедельно в воскресение 17:00:00Используя Панель элементов создадим первый вложенный план. Достаточно нужный элемент в панели ухватить, перенести на рабочую область и там бросить. Для открытия мастера настройки элемента достаточно два раза щелкнуть по элементу.

Ниже на рисунке представлен результат настройки, который должен у нас получится, но все по порядку.
Размещаем задачу «Проверка целостности базы данных», двойным щелчком мыши открываем диалог настройки задачи, в первую очередь в свойстве Базы данных отмечаем нужную базу, а остальное настраиваем как показано на рисунке. При желании можно посмотреть T-SQL код полученной задачи.
Размещаем следующую задачу «Перестроение индекса» она у нас будет выполнятся только после успешно выполненной предыдущей задачи. Настраиваем как показано на рисунке, не забываем указать конкретную базу данных.
Для связи двух задач щелкните по первой задаче «Проверка целостности базы данных» у этой задачи появится стрелка, щелкните по ней и не отпуская соедините со второй задачей «Перестроение индекса». Для изменения значения условия выполнение следующей задачи, щелкните два раза по линии и в открывшемся диалоговом окне выполните необходимые настройки.
Размещаем задачу «Обновление статистики» которая будет выполнятся после завершения предыдущей. Настраиваем эту задачу как на рисунке, не забываем выбрать базу данных. Размещаем задачу «Выполнение инструкции T-SQL» с кодом: DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = ‘Moodle’) DBCC FLUSHPROCINDB (@intDBID) Инструкция DBCC FREEPROCCACHE используется для аккуратной очистки кэша планов. Освобождение кэша планов приводит, например, к тому, что хранимая процедура повторно компилируется, а не используется из кэша. При настройки для своей базы не забываем изменить имя БД Moodle. Размещаем следующую задачу «Резервное копирование базы данных» она у нас будет выполнятся полную резервную копию базы данных. Размещать резервные копии желательно на на СХД, если нет, то на физически другом диске, но не в коем случаи на том же диске где сама база данных, иначе теряется весь смысл резервных копий. Настраиваем как показано на рисунке, не забываем указать конкретную базу данных.
Размещаем следующую задачу «Очистка журнала» она у нас будет выполнятся очистку журналов. Настраиваем как показано на рисунке. Размещаем следующую задачу «Очистка после обслуживания» она у нас будет выполнятся удаление старых файлов резервных копий, так как свойстве Расширение файла указана маска *.*, то удаляются будут все файлы, и полной резервной копии, и разностной, и журнала транзакций. Настраиваем как показано на рисунке. Обратите внимание, две последние задачи выполняются после выполнения задачи «Резервное копирование базы данных» и самое главное, задачу «Очистка после обслуживания» нужно выполнять только после успешно выполненной задачи «Резервное копирование базы данных». Что бы не получилось, что у вас уже который раз не создаются резервные копии, а вы задачей «Очистка после обслуживания» удаляете последние актуальные копии.
4. Создание плана обслуживания (Разностная копия)
Добавим вложенный план обслуживания, на рисунке ниже красной рамкой выделена данная кнопка и показан результат схемы обслуживания, который должен получится, но все по порядку. Заполним поля свойств и настроим расписание как показано на рисунке. Размещаем две задачи «Проверка целостности базы данных» и «Резервное копирование базы данных», обратите внимание последняя задача выполняется только после успешного завершения предыдущей. Иначе какой смысл делать резервную копию если она не корректна. На рисунке представлена настройка задачи «Проверка целостности базы данных». На рисунках представлены настройки задачи «Резервное копирование базы данных». Обратите внимание на Тип резервной копии, должен стаять Разностное. И не забудьте указать конкретную базу данных.
5. Создание плана обслуживания (Резервная копия журналов транзакций)
Добавим два вложенных плана обслуживания, один настроим на 12:00 второй на 17:00. На рисунке представлен результат плана обслуживания на 12:00, на 17:00 отличатся ничем не будет, только временем выполнения.
Разместим одну задачу «Резервное копирование базы данных». Обратите внимание на Тип резервной копии, должен стаять Журнал тарнзакций. И не забудьте указать конкретную базу данных.
6. Мониторинг планов обслуживания
После создания всех Планов обслуживания они появятся в ветке Агент SQL Server. Откройте Мониторинг активности заданий, в этом мониторинге можно увидеть какие задачи, когда выполнялись, когда следующее выполнение и успешно ли они выполнялись. Для запуска определенного плана, достаточно в контекстном меню выбрать пункт Запустить задание на шаге…

В этой статье мы рассмотрим, как настроить самый распространений тип репликации в SQL Server – транзакционную репликацию. Этот тип репликации SQL Server используется для копирования данных в режиме реального времени, то есть данные на подписчиках появляются практически сразу (с учетом времени которое тратится на копирование данных по сети).

Репликация транзакций проста в настройке и доступна во всех версиях SQL Server. Данный тип репликации используется для двух целей:

• Репликация данных между несколькими серверами для read доступа (например, для разгрузки серверов OLTP типа);
• Как решение для избыточности данных отдельных объектов.

Хотя у SQL Server есть много решений для балансировки нагрузки select запросов и средств обеспечения отказоустойчивости, транзакционная репликация это самый простой и быстрый способ, так как вы можете реплицировать отдельные объекты. Так же этот вид репликации полностью доступен в Standard лицензии SQL Server ( в отличии от групп доступности Always On, которые полноценно доступны только в Enterprise).

Преимущество репликации перед Always ON и зеркалированием баз данных в том, что с помощью репликации вы можете скопировать отдельные объекты (отдельные таблицы/представления), а не базу данных целиком.

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

SQL Server: основы технологии репликации

В любом типе репликации SQL Server есть 3 типа серверов:

• Publisher (издатель) – основной экземпляр-источник, который публикует статьи;
• Distributor (распространитель) – экземпляр который распространяет статьи на сервера-подписчики. Этот тип экземпляра не хранит у себя данные издателя на постоянной основе, а распространяет их подписчикам;
• Subscriber (подписчик) – экземпляр который получает распространяемые статьи.

Роли могу пересекаться между собой. Например, один экземпляр может быть и издателем, и подписчиком (но не самого себя).

Работа репликации транзакций осуществляется через внутренние агенты SQL Server’а:

• Агент чтения журналов;
• Агент моментальных снимков;
• Агент распространения.

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

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

Транзакционная репликация работает асинхронно, так же как и асинхронные режимы Always On и зеркалирования баз данных. То есть, данные, которые были записаны на издатель, будут отправлены на подписчики без гарантии доставки в случае сбоя во время передачи данных. Это нужно учитывать, если вы собираетесь использовать транзакционную репликацию для избыточности и высокой доступности данных в SQL Server.

Схема связи агентов между собой из официальной документации:

Рассмотрим, как настроить репликацию в SQL Server на следующем тестовом стенде:

• 2 виртуальные машины с Windows Server 2019 в одной сети;
• 2 установленных экземпляра SQL Server 2019.

Топология репликации:

В этом примере мы будем реплицировать одну таблицу с testnode1\node1 на testnode\node2. В роли распространителя будет выступать testnode2\node2.

Настройка распространителя в SQL Server

Для начала нужно настроить экземпляр распространителя. В разделе Replication, в контекстном меню нажмите Configure Distribution…

Так как мы хотим использовать этот экземпляр в качестве распространителя, выбираем первый пункт (testnove2 will act as its own Distributor; SQL Server will create a distribution databasse and log).

Указываем директорию для моментальных снимков. Я оставлю стандартный путь.

Указываем директорию для базы данных Distribution. Если есть такая возможность, то лучше разместить файлы базы данных distribution на отдельном массиве дисков. Особенно это важно, если планируется большой объём реплицируемых данных.

На этом шаге можно указать экземпляры, которые смогут использовать данный сервер как распространитель. Я сразу добавлю testnode1\node1. Это можно сделать и позже, после начальной конфигурации.

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

После этого можно жать Finish. На этом настройка распространителя завершена.

Если вы хотите изменить пароль распространителя или разрешить другим издателям использовать этот распространитель, то можно это сделать через Distributor Properties…

Настройка издателя репликации в SQL Server

Теперь переходим к настройкам издателя репликации. Запустите тот же мастер Configure Distributuin.

Выберите второй пункт, указываем сервер распространитель – testnode2\node2

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

Теперь можно создать новую публикацию: Replication -> Local Publication -> New Publication.

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

Выберите тип репликации. Доступны:

• Snapshot publication;
• Transactional publication (выберите этот тип репликации);
• Peer-to-Peer publication;
• Merge publication.

Выберите таблицы, которые нужно реплицировать. С помощью транзакционной репликации так же можно реплицировать пользовательские процедуры, функции и представления. Реплицируемые объекты называются Articles (Статьи).

На следующем шаге можете указать фильтр для публикации.

Чтобы мастер сразу создал моментальный снимок, выберите опцию «Create a snapshot immediately and keep the snapshot available to initialize subscriptions”.

Укажите аккаунты, из-под которых будут выполняться агенты. Нажмите Security Settings и выберите «Run under SQL Server Agent service account”.

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

Настройка подписчика репликации в SQL

На testnode2\node2 в разделе Replication -> Local Subscriptions создайте новую подписку.

Выберите издателя, базу данных и публикацию в ней.

Выберите пункт «Run all agents at the Distributor”, чтобы агенты выполнялись на распространителе. В моём случае подписчик и распространитель совпадают, но обычно это разные сервера.

Если выбрать второй пункт («Run each agent at its Subscriber”), то агенты будут выполняться на подписчике. Этот вариант предпочтителен, если распространитель у вас «формальный” и находится на одном сервере с издателем или подписчиком

Укажите базу данных, в которую будут реплицироваться данные из Subscription Database.

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

Если вы хотите, чтобы данные реплицировались постоянно, выбирайте режим Agent Schedule -> Run continuously.

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

При включении параметра «Memory Optimized” таблицы на подписчике с этой публикации будут созданы как «In memory”. Если вы не планируете эти таблицы как таблицы для использования в оперативной памяти, то не отмечайте этот параметр.

Нажмите Finish.

На этом настройка подписки завершена. Теперь необходимо проверить работоспособность публикации и корректность выполнения репликации таблицы.

Мониторинг и управление репликацией в SQL Server

Практически всю настройку существующих публикаций можно провести через Replication Monitor.

Добавьте издателей через распространителя (Add Publisher -> Specify a Distributor and Add its Publishers).

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

Убедимся, что агент моментальных снимков отработал и доставил снимок на распространителя. В моём случае сначала была ошибки о том, что аккаунту из-под которого работают агенты, не хватает прав на базе TestDatabase1. Для решения этой проблемы я добавил сервисному аккаунту (из-под которого работает SQL Server) роль db_owner в базе TestDatabase1 на обоих экземплярах.

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

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

Для начала добавим новую запись в таблицу.

Проверяем, что эта запись реплицировалась на testnode2\node2.

На этом базовая настройка репликации транзакций в SQL Server закончена.

Для диагностики проблем с репликацией в основном используется Replication Monitor, но можно использовать и дополнительные инструменты диагностики SQL Server.

Документация VOGBIT — система управления производством

В настоящей инструкции кратко описан порядок установки бесплатной системы управления данными Microsoft SQL Server 2017 Express

Эта инструкция может быть полезной для начинающих пользователей, не имеющих опыта работы с СУБД Microsoft SQL Server. Она поможет вам быстро и правильно установить на ваш компьютер Microsoft SQL Server Express в той конфигурации, которая требуется для работы программы VOGBIT – система управления производством.

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

Обратите особенное внимание на Рис. 2 и Рис. 12. По статистике, если в дальнейшем возникают проблемы с развертыванием системы VOGBIT, то в большинстве случаев, из-за неверно выбранных параметров на этих этапах установки.

1. Установка SQL Server Express

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

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

Microsoft® SQL Server® 2017 Express Поддерживаемые ОС: Windows 8 и выше или Windows Server 2012 и выше
Microsoft® SQL Server® 2014 Express Поддерживаемые ОС: Windows 7-8.1, Windows Server 2008 R2-Windows Server 2012 R2
Требования к системе:
Процессор: совместимый с Intel, с частотой 1 ГГц или выше
ОЗУ: не менее 512Мb, место на жестком диске 4,2Gb
Ограничения: Microsoft SQL Server Express поддерживает 1 физический процессор, 1 ГБ памяти и размер базы данных не более 10 ГБ

Скачайте с сайта microsoft.com пакет для установки Microsoft SQL Server Express под вашу операционную систему.

Запустите скачанный исполняемый файл (Рис. 1).

Рис. 1. Программа для установки Microsoft SQL Server.

На стартовой странице процесса установки выберите тип установки «Пользовательский» (Рис. 2).

Рис. 2. Выберите тип установки «Пользовательский»

Нажмите «Установить» (Рис. 3).

Рис. 3. Начало установки

Программа начнёт скачивание файлов для установки Microsoft SQL Server (Рис. 4).

Рис. 4. Скачивание пакета установки.

После завершения процесса скачивания, на экране появится окно «Центра установки SQL Server». Выберите в нём пункт «Новая установка изолированного экземпляра SQL Server … » (Рис. 5).

Рис. 5. Выберите установку нового экземпляра SQL Server.

Для продолжения установки нужно согласиться с условиями лицензионного соглашения (Рис. 6).

Рис. 6. Лицензионное соглашение.

Выберите, хотите ли вы, чтобы в дальнейшем ваш SQL Server автоматически обновлялся через Центр обновления Майкрософт, или нет, и нажмите «Далее» (Рис. 7)

Рис. 7. Настройка обновления SQL Server.

После этого, на экране появится окно «Правила установки». Скорее всего, оно будет выглядеть, как на Рис. 8. Программа определяет потенциальные проблемы, которые могут возникнуть установке SQL Server и показывает результаты этой проверки.

Символ предупреждения в графе «Брандмауэр Windows» показывает, что если вы хотите, чтобы данный SQL Server использовался в сети (к базе данных, расположенной на этом компьютере, могли подключаться по сети пользователи с других компьютеров), то вам придётся для этого выполнить определённые дополнительные настройки безопасности на вашем компьютере после установки SQL Server.

Рис. 8. Окно «Правила установки»

Нажмите «Далее», чтобы перейти к окну выбора компонентов для установки (Рис. 9). Все «галочки» в этом окне можно оставить по умолчанию. Можно, если хотите, снять в разделе «Компоненты экземпляра» все галочки, кроме первой – «Службы ядра СУБД» (остальное не требуется для VOGBIT).

Рис. 9. Выбор компонентов для установки.

В окне Настройка экземпляра выберите вариант Именованный экземпляр и введите имя, как будет называться ваш экземпляр SQL сервера. Например, STP (Рис. 10). Затем нажмите Далее.

Рис. 10. Задайте имя своего экземпляра SQL сервера.

В окне «Конфигурация сервера» на закладке «Учётные записи служб» установите для служб «Ядро СУБД SQL Server» и «Обозреватель SQL Server» Тип запуска = Авто (Рис. 11) и нажмите Далее.

Рис. 11. Для использования в сети установите автоматический запуск служб SQL сервер.

ВАЖНО!
На закладке «Настройка ядра СУБД» обязательно выберите Смешанный режим (проверка подлинности SQL Server и Windows), как на Рис. 12. Введите пароль администратора SQL сервера. Например, 0 (Ноль). Запомните пароль администратора, который вы установили! Этот пароль устанавливается для администратора SQL SERVER (имя уч.записи администратора sa), и в дальнейшем будет использован для авторизации в VOGBIT при подключении с правами администратора. После того, как вы ввели и подтвердили пароль администратора, можно нажимать Далее.

Рис. 12. Выберите смешанный тип аутентификации, задайте пароль администратора SQL сервера.

После этого начнётся установка SQL сервера (Рис. 13). Программа выполнит все необходимые операции автоматически. Просто подождите завершения процесса.

Рис. 13. Выполняется установка и настройка SQL сервера.

Когда установка и настройка будут закончены, появится окно, как на Рис. 14. Нажмите в нём Закрыть. Затем закройте и окно «Центр установки SQL Server» (Рис. 15).

Рис. 14. Установка SQL Server завершена. Рис. 15. Окно Центра установки SQL Server можно закрыть.

Убедиться, что SQL Server успешно установлен и работает, можно следующим образом. Нажмите правую кнопку мыши на значке Мой компьютер, чтобы вызвать контекстное меню. Выберите Управление (Рис. 16).

Рис. 16. Откройте окно Управление компьютером.

Выберите Службы и приложения – Службы (Рис. 17).

Рис. 17. Выберите Службы и приложения — Службы.

В списке должна присутствовать служба SQL Server и напротив неё должно высветится Работает (Рис. 18).

Рис. 18. Служба SQL Server работает.

Теперь на вашем компьютере установлена мощная система управления данными Microsoft SQL Server Express. Вы можете создать базу данных VOGBIT и работать с ней.

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

2. Установка SQL Server Management Studio

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

SQL Server Management Studio – это полезная, свободно распространяемая, программа для управления SQL Server. С её помощью можно выполнять множество действий, в том числе, например, создавать резервные копии базы данных или восстанавливать базу данных из ранее созданной резервной копии.

Если вы планируете только ознакомиться с демо-версией VOGBIT, то установка ПО SQL Server Management Studio не требуется. Если вы планируете полноценно работать с VOGBIT, то мы рекомендуем вам при установке SQL Server сразу же установить и среду SQL Server Management Studio.

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

Для установки английской версии нажмите на ссылку «Скачать SQL Server Management Studio» (Рис. 19).

Рис. 19. Выберите для установки английской версии SQL Server Management Studio.

Если вы хотите установить русскую версию, то промотайте страницу немного ниже и в разделе «Доступные языки» нажмите на ссылку «Русский» (Рис. 20).

Рис. 20. Выберите для установки русской версии SQL Server Management Studio

Запустите скачанный исполняемый файл (Рис. 21).

Рис. 21. Программа для установки SQL Server Management Studio.

Выберите «Установить» (Рис. 22).

Рис. 22. Запуск процесса установки SQL Server Management Studio.

Начнётся процесс установки среды SQL Server Management Studio (Рис. 23).

Рис. 23. Установка SQL Server Management Studio

По завершении установки нажмите Закрыть (Рис. 24).

Рис. 24. Среда SQL Server Management Studio установлена.

Теперь вы можете использовать среду SQL Server Management Studio для резервного копирования-восстановления базы данных, для выполнения запросов и т.п.

3. Настройка SQL Server для работы в сети

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

Нажимаем правой кнопкой мыши на кнопке «Пуск» из контекстного меню выбираем «Управление компьютером».

Рис. 25. Управление компьютером В дереве компонентов диспетчера (слева) выбрать раздел: Службы и приложения — Диспетчер конфигурации SQL Server – Сетевая конфигурация SQL Server – Протоколы для SQLEXPRESS (возможно MSSQL зависит от версии) – TCP/IP. Во вкладке «Протокол» устанавливаем «Да», во вкладке «IP-адреса» мотаем вниз до раздела «IPAll» и устанавливаем параметр «TCP-порт» равный 1433. Рис. 26. Сетевая конфигурация SQL Server Рис. 27. Сетевая конфигурация SQL Server

Далее необходимо настроить «Брандмауэр Windows»

Найдите, например, через «Проводник Windows» по пути C:\Windows\System32 файл cmd.exe и запустите его с повышенными правами администратора. Для этого на файле cmd.exe нажмите правой кнопкой мыши и выберите «Запуск от имени администратора».

Если у вас установлен Windows 10, то для открытия порта достаточно выполнить команду:

Если у вас установлен Windows 7, то для открытия порта запускаем команду WF.msc

Откроется окно «Брандмауэр Windows»

Рис. 28. Окно «Брандмауэр Windows»

Создаем правило для входящих подключений

В диалоговом окне «Тип правила» выберите «Порт» и нажмите кнопку «Далее»

В диалоговом окне «Протокол и порты» выберите протокол «TCP».

Выберите «Определенные локальные порты» и введите номер порта экземпляра по умолчанию введите 1433.

Нажмите кнопку Далее.

В диалоговом окне Имя введите имя и описание для этого правила, например, SQLPort, а затем нажмите кнопку Готово.

Настройка завершена

4. Создание резервной копии базы данных

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

Для создания резервной копии базы данных, можно воспользоваться свободно распространяемой, программой SQL Server Management Studio.

Подробная инструкция по установке SQL Server Management Studio есть на нашем сайте.

Через меню «Пуск» запустите программу запускаем программу SQL Server Management Studio.

Выберите нужный сервер (экземпляр), введите имя и пароль администратора SQL Server’а и подключитесь к серверу.

Рис. 29. Подключение к серверу

Если ваш SQL SERVER настроен на не стандартный порт, отличный от 1433, то в строке соединение необходимо указать его после запятой. Например: 10.0.0.30\SQLEXPRESS2019, 18544 (где SQLEXPRESS2019 — имя именованного экземпляра, если имеется, а 18544 — порт сервера)

В дереве в левой части экрана выберите раздел «Базы данных» и установите курсор на нужную базу данных.

Рис. 30. Обозреватель объектов

Откройте правой кнопкой мыши контекстное меню. Выберите «Задачи» — «Создать резервную копию. Если в области «Назначение» ещё не указан файл, в который будет сохранена резервная копия, то его нужно указать. Для этого нажмите «Добавить».

Рис. 31. Создание резервной копии Рис. 32. Создание резервной копии

Если Вы сохраняете резервную копию базы данных в выбранный файл уже не первый раз, то в разделе «Параметры носителя» Вы можете выбрать один из двух вариантов:

• Добавить в существующий резервный набор данных;
• Перезаписать все существующие резервные наборы данных.

Рис. 33. Создание резервной копии

Для создания резервной копии нажмите «Ок».

Рис. 34. Создание резервной копии

5. Восстановление базы данных из резервной копии

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

Через меню «Пуск» запустите программу запускаем программу SQL Server Management Studio.

Выберите нужный сервер (экземпляр), введите имя и пароль администратора SQL Server’а и подключитесь к серверу.

Рис. 35. Восстановление базы данных

В дереве в левой части экрана выберите раздел «Базы данных» откройте правой кнопкой мыши контекстное меню. Выберите «Восстановить базу данных».

Рис. 36. Восстановление базы данных

В области «Назначение — База данных» выберите имя восстанавливаемой базы. В поле «Источник» выберите «Устройство» и нажмите «…» для выбора места расположения файла с резервной копией.

Рис. 37. Восстановление базы данных

В окне «Выбор устройства резервного копирования» выберите «Файл» и нажмите кнопку «Добавит».

Рис. 38. Восстановление базы данных

Выберите файл с резервной копией базы данных.

Рис. 39. Восстановление базы данных

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

Рис. 40. Восстановление базы данных

Нажмите «Ок» для начала восстановления базы данных.

Рис. 41. Восстановление базы данных

Для сохранения целостности структуры баз данных и обеспечения нормальной производительности необходимо проводить периодическое обслуживание. В этой статье рассмотрим какие задания по обслуживанию необходимо выполнять для баз данных 1С Предприятия, размещенных в MS SQL.

Настройка плана обслуживания баз данных MS SQL Server выполняется через программу Microsoft SQL Management Studio. Рассмотрим задачи, которые мы будем выполнять в рамках регулярного обслуживания баз данных:

В чем отличие полного бэкапа от разностного?

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

Разностное резервное копирование сохраняет все изменения созданные в базе данных с момента последнего полного бэкапа.

Такой подход к резервному копированию позваляет экономить свободное пространство на носителях информации.

Создание полного бэкапа базы.

В обозревателе объектов переходим к пункту «Управление \ Планы обслуживания». В контекстном меню выбираем «Создать план обслуживания».

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

В созданном плане нажимаем кнопку «Добавление вложенного плана»

Вводим название «Полный бэкап» и описание. Задаем расписание для выполнения задания: Раз в неделю в воскресенье в 2:00.

Добавляем в созданный план задание. Для этого с панели элементов перетаскиваем в поле заданий вложенного плана элемент с названием Задача «Резервное копирование базы данных».

Открываем задание на редактирование: правой клавишей мыши по заданию, выбираем пункт «Изменить».

• Тип резервной копии: Полное;
• Базы данных: если выбрать «Все пользовательские базы данных», то будет выполняться бэкап всех созданных вами баз данных, но есть возможность указать на конкретные базы;
• Создать файл резервной копии для каждой базы данных: отмечаем пункт «Создавать вложенный каталог для каждой базы данных», чтобы удобнее было ориентироваться в бэкапах и указываем путь как папке, в которой будут храниться резервные копии;
• Отмечаем пункт «Проверять целостнойсть резервной копии»;
• Устанавливаем параметр «Сжимать резервные копии».

Создание разностного бэкапа.

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

Отметим некоторые отличия в настройке:

• Расписание выполнения заданий: с понедельника по субботу в 2:00;
• Тип резервной копии выбираем «Разностное»

Очистка устаревших бэкапов.

Для очистки устаревших бэкапов баз 1С Предприятия в MS SQL выбираем на панели элементов плана обслуживания Задачу «Очистка после обслуживания».

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

Перетаскиваем задачу с Панели элементов в план и задаем такие настройки:

• Удалить файлы следующего типа: Файлы резервных копий;
• Удалить из папки файлы с определенным расширением: указываем папку хранения бэкапов баз 1С;
• Включить вложенные папки первого уровня: отмечаем галочкой, потому-что у нас для бэкапов баз создаются отдельные папки
• Удалить файлы на основе возраста во время выполнения задачи: здесь все ограничивается лишь вашими потребностями и объемом жесткого диска, а мне достаточно 4 недель.

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

Через стрелки можно задавать условие, при котором будет выполнять следующее задание: ошибка, успешное завершение, выполнение. Изменить условие можно щелкнув правой клавишей мыши по стрелке.

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

Переходим к очень важному и ответственному пункту: Перестроение индекса и обновление статистики.

Дефрагментация индекса (реорганизация или перестроение).

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

Для устранения фрагментированных областей баз данных в MS SQL существует возможность проведения Реорганизации индекса и Перестроение индекса.

В чем разница между реорганизацией и перестроением?

Перестроение индекса означает, что фрагментация будет устранена путем удаления и пересоздания индексов.

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

В каких случаях требуется реорганизация индекса?

• Уровень фрагментации от 5% до 30%, то проводим реорганизацию.
• Фрагментация свыше 30% необходимо проводить перестроение индекса

Под выполнение этих задач очень подходит инструкция Transact-SQL со следующим содержимым:

DECLARE @SQL NVARCHAR(MAX) DECLARE @MIN_IND_SIZE integer = 128 DECLARE @MIN_FRAGMENTATION_LEVEL integer = 10 DECLARE @CRITICAL_FRAGMENTATION_LEVEL integer = 30 DECLARE currentIndex CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT ‘ALTER INDEX ON ) + ‘]. ‘ + CASE WHEN stat.avg_fragmentation_in_percent > @CRITICAL_FRAGMENTATION_LEVEL THEN ‘REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)’ ELSE ‘REORGANIZE’ END + ‘;’ FROM ( SELECT stat., stat.index_id, avg_fragmentation_in_percent = MAX(stat.avg_fragmentation_in_percent) FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) stat WHERE stat.page_count > @MIN_IND_SIZE AND stat.index_id > 0 AND stat.avg_fragmentation_in_percent > @MIN_FRAGMENTATION_LEVEL GROUP BY stat., stat.index_id ) stat JOIN sys.indexes ind WITH(NOLOCK) ON stat. = ind. AND stat.index_id = ind.index_id JOIN sys.objects obj WITH(NOLOCK) ON obj. = stat. OPEN currentIndex FETCH NEXT FROM currentIndex INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN print @sql EXEC sys.sp_executesql @SQL FETCH NEXT FROM cur INTO @SQL END CLOSE currentIndex DEALLOCATE currentIndex

Создаем вложенный план с названием «Дефрагментация индекса и обновление статистики» с расписанием раз в день в 4:00 и перетаскиваем в него из Панели элементов Задачу «Выполнение инструкции T-SQL».

Вставляем в задачу приведенную выше инструкцию T-SQL.

Обновление статистики.

Обновление статистики в базах данных MS SQL, как и дефрагментация индекса, имеет большое значение для повышения производительности работы SQL сервера. Благодаря обновлению статистики SQL Server способен более эффективно выполнять планы запроса.

Выбираем на панели элементов Задача «Обновление статистики» и добавляем ее во вложенный план «Дефрагментация индекса и обновление статистики».

• Базы данных: все пользовательские базы данных;
• Обновить: вся собранная статистика;
• Тип просмотра: полный просмотр.

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

Не забываем сохранить созданный план обслуживания…

… и убедиться, что запущен Агент SQL Server.

Сегодня мы поговорим о настройках самой распространенной СУБД, это Microsoft SQL Server.

Установка порога потребляемой памяти

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

(Всего памяти на сервере – 1024 * Всего памяти на сервере / Всего памяти на сервере * 0,5) – оставить для ОС 1 гб на каждые 16 гб общего размера памяти.

Приоритет MS SQL сервера

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

Включение протокола Shared Memory

Если сервер 1С: Предприятия расположен вместе с Microsoft SQL Server — включить протокол Shared Memory. Данный протокол обменивается через оперативную память, минуя сетевое соединение.

Настройка находится в оснастке «Sql Server Configuration Manager”

Ограничить запросы по времени выполнения

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

На вкладке Database Setting можно задать пути файлов базы данных, логов транзакций и копий по умолчанию.

На этой же странице есть параметр – Default index fill factor. Это так называемый процент заполнения индекса по умолчанию. Когда мы создаем индекс, у него по умолчанию все страницы заполнены и у нас бывает такая проблема, как фрагментация индекса. Проблема фрагментации индексов – это ситуация, когда страницы индекса не до конца заполнены. Такой индекс увеличивается в размере, но при этом у него много страниц. Которые заполнены частично. Это может происходить например, когда страница некого индекса заполнена полностью и мы на нее хотим что-то вставить. Тогда страница разбивается на 2 части. Первая часть страницы остается на своем месте и создается новая страница, на которую переносится половина оставшихся данных. Если такая ситуация происходит часто, то это дополнительная нагрузка для расщепления данных. В итоге получается индекс с большим количеством не до заполненных страниц. Чтобы его прочитать или прочитать его диапазон, необходимо потратить больше времени. А если этот индекс после этого перестроить, либо выполнить операцию дефрагментации, индекс может начать работать быстрее за счет того, что он уменьшается в размере и все страницы располагаются физически ровно на диске.

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

Max Degree of Parallelism – параллельное выполнение плана запроса ядрами процесора. Значение 1 означает, что параллелизм выключен, а значение 0 означает, что оптимизатор сам решит, сколько ядер ему использовать. Есть рекомендация Microsoft, которая говорит, что нельзя ставить значение в данном параметре больше, чем число ядер, находящимся в одном процессорном сокете. Это справедливо для NUMA архитектуры. С данным параметром можно экспериментировать, лучше сразу не ставить большое значение, поставьте, к примеру для начала значение 4. Если вы видите, что нагрузка слишком высокая, попробуйте уменьшить число параллельных потоков, а значение параметра «Cost Threshold for Parallelism” наоборот увеличить, к примеру значение 300. А если вы видите, что процессор, наоборот, не нагруженный и запас производительности высокий, параметр «Cost Threshold for Parallelism” уменьшите, к примеру, до 120, а параметр «Max Degree of Parallelism” увеличить. Если значение параметра равно не единице (1), то оптимизатор строит 2 плана запроса. Один план запроса для однопоточного выполнения, второй план запроса для параллельного выполнения.

Cost Threshold for Parallelism – время в секундах, после которого запрос начнет выполняться в параллельном режиме.

Ниже приведены картинки данных параметров и картинка плана запроса, который выполняется параллельно.

Системные базы данных SQL

Tempdb — хранит временные таблицы, промежуточные планы запросов, промежуточные данные при перестроении индексов. Данная база может становиться «узким местом”. Так как мы часто используем временные таблицы. Данную базу иногда выносят на отдельные диски SSD, иногда даже на RAM диск. За счет этого можно получить выигрыш в производительности. Есть рекомендация, разбивать tempdb на части, обычно на 4 файла. Единственное условие, чтобы данные файлы были одинакового размера. На небольших системах прирост производительности будет не заметной.

Master – база всех настроек MS SQL сервера.

Model – шаблон для создания новых баз данных. Все настройки, заданные в данной базе, при создании новой базы, будут взяты отсюда.

Msdb – служебная база, её использует агент MS SQL сервера для запуска и работы заданий.

Перемещение баз данных

Особенность всех системных баз, что их просто так не переместить. Их необходимо перемещать скриптами. Пример перемещения базы данных tempdb скриптом:

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

1. Правой кнопкой мыши на перемещаемую базу> tasks> detach…

1. Поставить галочку напротив базы данных «Drop Connections”

1. После этого нажать «ОК”

Чтобы присоединить базу, необходимо сделать Attach… и выбрать файл базы данных

Флаги трассировки MS SQL

1. Только в режиме отладки! Могут замедлить работу программы.

Т2301 — более глубокая оптимизация

Т8780, Т8788 (аналог) — отключение таймаута оптимизации

Т8671 — отключает отбрасывание неэффективных ветвей для поиска Good Enough Plan

2. ХОТФИКСЫ ДЛЯ ОПТИМИЗАТОРА ЗАПРОСОВ

Т4199 — свободно собраны хотфиксы для оптимизатора запросов для SQL Server 2005 — 2014

Эти улучшения (хотфиксы) по умолчанию выключены,

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

По умолчанию Т4199 включен в SQL Server 2016, если база в режиме совместимости 130 или выше.

Пример команд включения флагов трассировок: