Установка служб SQL Server R Services (в базе данных). Сетевые настройки SQL Server. Основные возможности QMB

Ниже будет рассказано, как создать план обслуживания в с помощью программы «Среда SQL Sever Management Studio». В данной статье я просто постараюсь наглядно описать алгоритм создания плана обслуживания с помощью Мастера планов обслуживания, не вдаваясь в теоретическую часть вопроса. Получить больше информации по данной области можно изучив электронную документацию по SQL Server на сайте MSDN .

В описанный ниже план будут входить всего 2 задачи.

• Резервное копирование базы данных.
• Проверка целостности базы данных.

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

1. Исходные данные

1. Операционная система семейства Windows (в моем примере используется )
2. Установленный Microsoft SQL Server 2008 R2 (об установке SQL Server можно прочитать )
3. Существующая база данный в SQL Server (о создании баз данных в SQL Server читайте )
4. Настроенная компонента Database Mail, в случае если требуется уведомлять по электронной почте операторов о результатах выполнения плана обслуживания (о том как настроить компоненту Database Mail и создать оператора системы я писал ).

2. Проверка работы Агента SQL Server

Первое что нам необходимо сделать, это убедиться что Агент SQL Server установлен и работает. Для этого запустим оснастку «Службы » («Пуск » (Start ) — «Администрирование » (Administrative Tools ) — «Службы » (Services )) и в списке служб найдем службу «Агент SQL сервер » (SQL Server Agent ).

Откроем свойства этой службы (кликнув по ней 2 раза) и убедимся что:

• Тип запуска стоит «Автоматически » (Startup type: Automatic);
• Состояние «Работает » (Service status: Started);

В противном случае, необходимо изменить параметры как на скриншоте выше и сохранить настройки нажав «Применить » (Apply) .

Теперь запустим программу «Среда SQL Sever Management Studio» («Пуск » (Start ) — «Все программы » (All programs) — «Microsoft SQL Server 2008 R2 » — «Средства SQL Server 2008 R2 «) и введем данные для авторизации.

После чего, еще раз убедимся что Агент SQL Server работает (в обозревателе объектов должна быть вкладка «Агент SQL Server » (SQL Server Agent) с зеленой иконкой слева.

3. Создание плана обслуживания

Теперь перейдем непосредственно к созданию плана обслуживания. В обозревателе объектов (Object Explorer) раскроем вкладку «Управление » (Management), кликнем правой кнопкой мыши по вкладке «Планы обслуживания » (Maintenance Plans) и в контекстном меню выберем «Мастер планов обслуживания » (Maintenance Plan Wizard) .

В запустившемся мастере планов обслуживания на странице приветствия нажимаем «Далее » (Next) и в следующем окне вводим имя и описание нового плана.

Затем необходимо определиться с расписанием, по которому будет выполняться данный план обслуживания. Для этого установим переключатель на «Единое расписание для всего плана или без расписания » (Single schedule for the entire plan ore no schedule ) и нажмем «Изменить… » (Change…) для назначения расписания.

Откроется окно «Свойства расписания задания » . Здесь зададим те параметры, согласно которым должен выполняться план обслуживания и нажмем «ОК » . В моем примере это:

• Выполняется — «Еженедельно » (Occurs — Weekly);
• Повторяется каждые — «1 нед. » в «Воскресенье » (Recurs every: 1 week(s) on Sunday);
• Выполняться один раз в день в: — «2:00:00» (Occurs onсe at: «2:00:00»);

Еще раз убедимся, что расписание задано верно, и нажмем «Далее » (Next) .

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

1. Проверка целостности базы данных (Check Database Integrity);
2. Резервное копирование базы данных (полное) (The Back Up Database (Full));

Заметьте, что для каждой задачи приводится ее краткое описание в поле снизу. Выбрав необходимые задачи, жмем «Далее » (Next) .

Теперь необходимо задать порядок выполнения задач, используя кнопки «Вверх… » (Move Up) и «Вниз… » (Move Down). Установив порядок, жмем «Далее » (Next) .

Здесь требуется задать параметры для каждой задачи в плане. Первая задача в нашем списке это «Копирование БД (полное) » (Back Up Database (Full)).

Прежде всего необходимо выбрать базы данных для резервного копирования, нажав на кнопку выбора списка «Определенные базы данных » (Select one ore more). Выбрав необходимые для резервного копирования базы данных, нажимаем «ОК » .

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

1. Если установить переключатель «Создать файл резервной копии для каждой базы данных » (Create a backup file for every database) , то при выполнении задания в выбранной директории будет создаваться несколько файлов резервных копий с именами, соответствующими названиям баз данных. Ну а установка флага «Создавать вложенный каталог для каждой базы данных » (Create a sub-directory for each database) разложит файлы по отдельным папкам. Обратите внимание, что необходимо оставить заполненным расширение файла резервной копии.
2. Установка флага «Срок действия резервного набора данных истекает » (Backup set will expire) указывает SQL-серверу, когда этот набор может быть перезаписан без явного пропуска проверки на истечение срока.
3. Для наибольшей надежности, можно установить флаг «Проверять целостность резервной копии » (Verify backup integrity).
4. Также рекомендую выбрать режим «Сжимать резервные копии » (Compress backup) для экономии дискового пространства, если используемая версия SQL Server поддерживает данную функцию.

Если дисковое пространство ограничено, можно также выбрать один файл для хранения резервной копии, который будет перезаписываться после каждого выполнения плана обслуживания. Для этого установим соответствующий переключатель на «Создать резервную копию баз данных в одном или нескольких файлах » (Back up databases across one ore more files) и указжем соответствующее имя файла (будьте внимательны, файл резервной копии следует задавать с расширением.bak), а также выберем режим «Перезаписать » в случае, если файлы резервной копии существуют (If backup files exist: Overwrite).

Теперь очередь задачи «Проверка целостности базы данных » (Database Check Integrity). Для нее всего лишь необходимо выбрать базу данных. В моем примере это все та же база данных, что и на предыдущем шаге. Определившись с базами, жмем «Далее » (Next).

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

Проверим еще раз все настройки плана обслуживания, и если все верно, нажимаем «Готово » (Finish).

Мастер начнет построение плана обслуживания. Если мастер не обнаружит ошибок, то увидим сообщение об успешном построении плана. В противном случае необходимо устранить ошибки и повторить процедуру снова. Закроем окно, нажав «Закрыть » (Close).

4. Запуск выполнения плана обслуживания

Для запуска выполнения плана обслуживания перейдем в программу «Среда Microsoft SQL Server Management Studio». Здесь, раскрыв вкладку «Планы обслуживания » (Maintenance Plans) увидим наш только что созданный план. Чтобы проверить его работу, кликнем по нему правой кнопкой мыши, и в контекстном меню выберем пункт «Выполнить » (Execute) .

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

А в соответствующих директориях должны появиться файл резервной копии

и файл лога выполнения плана.

Открыв, этот файл, вы должны увидеть примерно следующее:

Если все так, поздравляю! План обслуживания SQL Server создан и работает.

Помогла ли Вам данная статья?

Достаточно нередко у разработчиков клиент-серверных приложений возникает необходимость организовать некий механизм, позволяющий по событию на sql-сервере уведомить того или иного клиента. Ещё чаще это является розово-голубой мечтой заказчика, чтобы разработчик реализовал такой механизм. Например, при превышении лимитов отгрузки какому-либо потребителю, должны быть немедленно уведомлены менеджеры, работающие с этим потребителем. Некоторые заказчики систем требуют (а мечтают об этом все заказчики без исключения), чтобы при изменении каких-то данных, у остальных пользователей системы эта информация автоматически обновлялась, причем незамедлительно. Здесь не будет обсуждаться целесообразность такого требования (оно имеет много оснований для критики), здесь будут обсуждаться только пути решения. microsoft sql-сервер имеет штатное средство для организаций уведомлений — alerts, но это средство имеет весьма ограниченное применение, по большому счету не дающее возможность создать на его основе гарантированно работающий механизм. И вот почему: Связь с клиентской программой может быть осуществлена путем посылки e-mail или эмуляцией посылки "net send". И то, и другое неудобно для получения уведомления.

Средство e-mail неудобно по причинам:

a) нет гарантии доставки, почта может теряться.
b) почта может "застрять" на промежуточных узлах.
c) требуется обязательно наличие протокола tcp/ip
d) требуется наличие smtp-сервера и настройка почтового профиля.
e) требуется особая настройка sql-сервера, чтобы он смог посылать письма.
f) требуется наличие у каждого клиента, ждущего события, почтового ящика.
g) в клиентской программе требуется организовать почтовый клиент.

Посылка путем «net send» неудобна по следующим причинам:

a) нет гарантии доставки, так как это организовано через средство mailslot, не имеющее такой гарантии.
b) требуется наличие корректного разрешения имен netbios в сети.
c) требуется наличие на клиенте "Клиент для сетей Микрософт".
d) задействован стандартный mailslot, это может иметь пересечение с другими программами.

И в целом средство alerts неудобно необходимостью регистрации каждого клиента в качестве оператора и соответствующей настройкой. Т.е. для простейших случаев alerts применить можно. Но для большинства случаев оно неприменимо.

Известные реализации и концепции

Широкой общественности известны несколько вариантов реализации механизма уведомления сервером клиента. Это:

1. Создание объекта (extended stored procedure или activex), посредством которого sql-сервер уведомляет клиента через сокеты tcp/ip. При этом на клиенте организована прослушка, т.е. клиентская программа стала сервером tcp/ip.
Недостатки этого метода:
a) Привязка к протоколу tcp/ip. В сети, где используется только ipx, netbeui или appletalk, такой механизм не применить.
b) Нет асинхронности. Если это событие генерируется из триггера, будут проблемы производительности.

2. Создание объекта (extended stored procedure или activex), посредством которого sql-сервер уведомляет клиента через named pipes или mailslots. При этом на клиенте организована прослушка того или другого.
Недостатки этого метода:
a) требуется наличие корректного разрешения имен netbios в сети.
b) требуется наличие на клиенте "Клиент для сетей Микрософт".
c) в случае использования mailslot нет гарантии доставки.
d) в случае использования named pipes, это нельзя применить на клиентских компьютерах с операционной системой windows 95/98/me, так как named pipe можно создать только в операционной системе на архитектуре nt.
e) Нет асинхронности. Если это событие генерируется из триггера, будут проблемы производительности.

3. Периодический опрос sql-сервера клиентом (периодическое чтение специальной таблички евентов). Это очень простой путь, но, тем не менее, свободный от большинства вышеперечисленных недостатков. К сожалению, этот метод имеет свои специфичные 2 недостатка: a) получение уведомления может быть задержано на величину таймаута опроса и b) при маленьком таймауте возникает существенный трафик. Тем не менее, при небольшом кол-ве сессий, этот метод вполне пригоден и незаслуженно обойден вниманием.

Предлагаемый вариант решения

Вашему вниманию предлагается вариант решения проблемы, свободный от вышеперечисленных (всех вышеперечисленных!) проблем, но вместе с тем достаточно простой. Идея такова: на сервер помещается некий двоичный объект, который sql-сервер может вызывать (а это может быть только extended stored procedure или activex-объект), имеющий два невзаимосвязанных метода.
Первый метод создает с помощью функции win32api createevent объект ядра win32, именуемый "event" с уникальным наименованием, переданным параметром. Далее вызывается функция win32api waitforsingleobject, наткнувшись на которую, поток останавливается и стоит в ожидании, пока этот объект ядра не засигналит. Обращаю ваше внимание, на тот факт, что таких объектов ядра может быть создано сколько угодно. Это ограничено только кол-вом хендлов в системе.
Второй метод вызывает объект ядра event по имени, заданным параметром, с помощью функции win32api setevent и выставляет ему свойство "signaled". Как только это произойдет, поток с первым методом пробуждается и возвращает управление вызвавшему процессу. Второй метод, не ожидает результата, а возвращает управление своему вызвавшему процессу сразу же после выставления свойства "signaled". Таким образом достигается асинхронность.
Теперь остается только сделать хранимые процедуры t-sql, управляющие этим объектом и нужная функциональность "у нас в кармане". Клиентская программа в отдельном потоке запускает хранимую процедуру ожидания события, передавая параметром уникальный признак-адрес. Это может быть и имя пользователя, и имя компьютера, и любая строка. Главное, чтобы это была уникальный идентификатор в пределах клиент-серверной системы. Хранимая процедура вернет результат только в случае, если для этого адресата будет сгенерировано событие. При получении события, процедура перезапускается. При закрытии программы поток ожидания события просто прибивается через terminatethread.
На первый взгляд эта метода обладает "ужасным" недостатком — существует постоянный коннект с sql-сервером, который большую часть времени ничего не делает. Но это только первое впечатление. На самом деле, задействуются ресурсы здесь только на поддержание коннекта — это что-то несколько килобайт на сессию. И все! Больше никаких осязаемых ресурсов не тратиться, особенно на фоне преимуществ, которые описаны ниже. О дополнительных лицензиях можно тоже не беспокоиться, если выбрана модель лицензирования "per server". В этом случае с одной машины может быть сколько угодно коннектов к sql-серверу, это все равно будет занимать ровно одну клиентскую лицензию.

Готовое решение

Решение состоит из activex-объекта в виде файла algoevt.dll и двух хранимых процедур spwaitforevent и spraiseevent. Перед использованием этот файл надо поместить на сервер и зарегистрировать activex-объект с помощью системной утилиты regsvr32.exe. Дальше вся работа будет производиться через хранимые процедуры. В готовом решении реализована несколько бОльшая функциональность, чем в описанной концепции. Кроме самого факта события, можно передать также произвольную информацию в виде строки в размере до 250 символов. Каждая процедура имеет два параметра. Первая — это уникальный идентификатор-адрес, о котором говорилось выше, а второй параметр — дополнительная передающаяся информация. spwaitforevent надо вызвать с клиента из отдельного потока (приоритет потока можно выбрать самый низкий). При получении события, процедуру надо перезапустить. Тайм-аут исполнения запроса надо задать бесконечный.

Итак в продолжении темы обслуживания баз 1С присмотримся к системе управления реляционными базами данных Microsoft SQL Server. Этот продукт предоставляет нам большие возможности обработки, хранения, резервирования и восстановления баз. Я начну небольшой цикл статей, посвященных этой теме. Все, что будет написано ниже, является личным мнением по данному вопросу и подлежит критике.

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

В тестовой лаборатории у нас следующее:

• Сервер Windows Server 2008 Enterprise: SRV-1C-TEST .
• Microsoft SQL Server 2008: SRV-1C-TEST .
• Тестовая база BuhFirma .

Как обычно, поставим перед собой задачу:

Проводить обслуживание базы в период 00:30 - 01:00, при этом обслуживание не должно быть заметным (либо слабозаметным) для пользователей базы.

Начнём с важных моментов. MS SQL база данных может иметь один из трех типов модели восстановления:

• Простая.
• Полная.
• С неполным протоколированием.

Так же при резервном копировании нам предоставляется на выбор три варианта копирования:

• Полное.
• Разностное.
• Копирование журнала транзакций (логов).

При полном варианте копирования происходит сохранение базы mdf и журнала транзакций. Разностное копирование (по-другому дифференциальное) производит копирование данных, изменившихся с момента создания последней полной резервной копии. Копирование журнала транзакций соответственно производит сохранение только самого журнала транзакций.

При выборе простой модели восстановить базу данных можно с момента создания последней разностной или полной резервной копии. При выборе полной модели восстановления мы можем восстанавливать базу до минуты, создав полную резервную копию, например, ночью, и в течение дня создавать копии журнала транзакции. Ниже мы увидим, где всплывает этот момент. Хотелось так же привести некоторые выдержки из MSDN : "Модель восстановления с неполным протоколированием предназначена исключительно как дополнение к модели полного восстановления. В общем случае модель восстановления с неполным протоколированием похожа на модель полного восстановления, за исключением того, что протоколирование большинства массовых операций в ней производится в минимальной степени".

Модель восстановления своей базы вы можете посмотреть, зайдя в свойства базы данных, например BuhFirma и перейдя на строку - Параметры.

В MSSQL 2008 по умолчанию в созданных базах данных модель восстановления Полная .

Как выбрать модель восстановления? Надо лишь ответить на вопрос: смертельна ли потеря информации за время, прошедшее после полного резервного копирования? Если ответ да, тогда выбираем полную модель восстановления, если нет, простую. Модель с неполным протоколированием стоит применять только на время массовых операций в БД.

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

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

• Проверка целостности базы
• Перестроение индекса
• Обновление статистики
• Очистка процедурного кэша СУБД
• Резервное копирование базы данных
• Очистка после обслуживания
• Очистка журнала

Для этого подключимся к MSSQL серверу с помощью среды Microsoft SQLServer Management Studio . Запустить среду можно перейдя в Пуск - Все программы - Microsoft SQL Server 2008 .

Подключимся с серверу SQL и перейдем в Управление - Планы Обслуживания . Кликнем правой кнопкой по Планы обслуживания и выберем Создать план обслуживания . Дадим ему имя: SRV1CTEST .

Перед нами окно SRV1CTEST, в котором мы и будем создавать последовательность действий, обозначенных раннее. Сразу видим появившейся Вложенный_План1 . Справа от названия вложенного плана вы увидите иконку в виде таблички. Нажимаем на нее и попадаем в свойства расписания задания. Здесь можно менять название вложенного плана, выставить частоту повторения в Ежедневно и установить время. И так теперь осталось наполнить наш план заданиями. Для этого с Панели инструментов, которая находится справой стороны, перетаскиваем задания.

Начнем с Проверки целостности базы данных .

После того, как вы перетащили задание, щелкните по нему два раза. Откроется окно, в котором в строке Базы данных мы выбираем созданную нашу базу BuhFirma . Далее таким же образом добавляем задания Перестроение индекса и Обновление статистики , не забыв выбрать в них нужную базу данных.

Процедура Перестроение индекса пересоздает индекс с новым коэффициентом заполнения. За счет этого мы увеличиваем производительность работы в БД.

Задача Обновление статистики обновляет сведения о данных таблиц для MS SQL. Что тоже повышает производительность. Но после этой операции надо обязательно проводить очистку кэша.

Пока остановимся и поговорим о настройке связей между заданиями. Связи отражают последовательность выполнения. Что бы провести связь между заданиями надо нажать один раз на задание и увидите появившуюся стрелку. Её надо перетащить на следующее задание. У связи может быть 3 цвета: синий, зеленый и красный, каждый из которых означает три типа срабатывания перехода: при простом завершении предыдущего задания - Завершение , в случае успешного завершения - Успех , а в случае возникновения ошибки при выполнение предыдущего задания - Ошибка . Все эти параметры вы можете увидеть, нажав правой кнопкой мыши на проведенную между заданиями стрелку. Таким образом, если нам надо, чтобы Перестроение индекса срабатывало только после успешного завершения задания Проверка целостности базы данных , мы должны связать их стрелкой. Нажав правой кнопкой мыши на стрелку, сменим ее режим на Успешно , как видим, ее цвет изменился на зеленый.

На данный момент мы имеем 3 созданных задания в нашем вложенном плане. Как вы могли заметить, задания Очистка процедурного кэша СУБД в панели элементов нету. Мы воспользуемся задачей Выполнение инструкции T-SQL . Перетащим ее в план, и щелкнем на ней два раза. Мы видим окно, в которое впишем следующее:

23/09/2015

Типичные ошибки настройки планов обслуживания СУБД MS SQL Server для 1С

Добрый день, коллеги.

В сегодняшней статье мы бы хотели рассмотреть достаточно востребованную и популярную тему, как настройка планов обслуживания MS SQL Server. В результате проведения аудитов мы д остаточно часто (более чем в 60% случаев) обнаруживаем некорректности в настройке СУБД MS SQL Server, используемой для работы с продуктами фирмы “1С”. Практика показывает, что эта СУБД является наиболее распространенной, поэтому в данной статье рассмотрим основные нюансы работы именно с ней.

Итак, с чего начинается настройка плана обслуживания? Конечно же с бэкапа! Первое правило DBA гласит: "Ничего не начинай делать без бэкапа". Ну и мы не будем. Давайте рассмотрим два основных варианта создания бэкапов, а точнее две модели резервного копирования, или модели восстановления ( https://msdn.microsoft.com/ru-ru/library/ms189275(v=sql.120).aspx )

Восстановление по модели simple

Ваша база данных находится в SIMPLE режиме восстановления. Что это означает? Это означает, что бэкапы бывают только полные, журналы транзакций бэкапировать не нужно, производительность в этом смысле максимальная, но восстановиться можно только на точку бэкапа. Восстановление базы “на указанный момент времени” невозможно.
Следовательно, еженочно (или чаще, в зависимости от потребности) мы должны снимать свеженькую копию нашей базы данных и складывать ее в надежное место, и обязательно не в то, в котором лежит наша основная база данных
В целом, использование модели SIMPLE для реальных рабочих баз оправданно только в случаях исключительно высокой нагрузки и незначительности события потери данных с момента последнего бэкапа.

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

Усечение данных может быть проведено, как стандартным мастером настройки плана обслуживания, так и с помощью несложно скрипта на T-SQL:

DBCC SHRINKFILE (DatabaseName, 1);
GO

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

Восстановление по модели full

Давайте рассмотрим основные принципы настройки резервного копирования и управления размером журнала лога транзакций с точки зрения самого массового варианта - полной модели восстановления БД.

Полная модель восстановления отличается от простой тем, что в течение всей работы базы данных мы можем (а еще точнее - ДОЛЖНЫ!) делать бэкапы лога транзакций, тем самым обеспечивая возможность восстановления БД между точками основных бэкапов или откаты на конкретные промежутки времени функционирования базы, а также обеспечивая освобождение места в файле журнала (усечение). Если этого не делать, он будет расти постоянно до тех пор, пока однажды не заполнит все доступное ему место (либо на диске, либо до ограничения, заданного в СУБД). Последствия кажутся очевидными, и не самыми приятными.

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

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

Пересчет статистики и работа с индексами

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

Правильная последовательность действий выглядит так:

Определяем степень фрагментированности индекса

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

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

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

Если рассмотреть мини-скрипт для пересчета статистики и перестроения индексов (не претендуем на супер полноту и универсальность), то выглядеть он будет примерно так (с перебором индексов через курсор):

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 [" + ind.name + N"] ON [" +

SCHEMA_NAME(obj.) + "].[" + obj.name + "] " +

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

CLOSE currentIndex

DEALLOCATE currentIndex

Обратите внимание на использование tempdb, а также на сохранение индекса доступным во время перестроения - в зависимости от редакции вашей СУБД последняя функция может быть недоступна.

Уведомления

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

Если вам все показалось слишком сложным, или вы не уверены в том, что можете сделать подобные настройки самостоятельно - не стесняйтесь, обращайтесь к нам - мы поможем!

В SQL Server 2016 и более поздних версиях можно установить все компоненты, связанные с Службы R Services (в базе данных), с помощью мастера установки SQL Server.

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

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

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

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

Exec sp_configure "external scripts enabled" , 1 Reconfigure with override

Перезапустите службу SQL Server для экземпляра SQL Server. Связанная служба Доверенная панель запуска SQL Server также будет автоматически перезагружена. Перезапустить службу можно с помощью панели "Службы" на панели управления или с помощью диспетчера конфигурации SQL Server.

После того как служба SQL Server станет доступна, проверьте, включен ли компонент R, выполнив следующую команду и проверив, имеет ли свойство run_value значение 1:

Exec sp_configure "external scripts enabled"

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

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

Exec sp_execute_external_script @language =N"R", @script=N"OutputDataSet <-InputDataSet ", @input_data_1 =N"select 1 as hello" with result sets (( int not null)); go

Результаты

hello
1

В процессе установки создаются 20 учетных записей пользователей Windows в целях выполнения задач с токеном безопасности службы Доверенная панель запуска SQL Server. Когда пользователь отправляет скрипт R из внешнего клиента, SQL Server активирует доступную рабочую учетную запись, сопоставляет ее с удостоверением вызывающего пользователя и выполняет скрипт R от имени пользователя. Это новая служба ядра СУБД, которая обеспечивает безопасное выполнение внешних скриптов с помощью механизма, называемого неявной проверкой подлинности .

Учетные записи можно просмотреть в группе пользователей Windows SQLRUserGroup . Если вам нужно выполнять скрипты R из удаленного клиента обработки и анализа данных и вы используете проверку подлинности Windows, этим рабочим учетным записям необходимо предоставить разрешение на вход в экземпляр SQL Server от вашего имени.

1. В среде SQL Server Management Studio в обозревателе объектов разверните узел Безопасность , щелкните правой кнопкой мыши Имена входа и выберите пункт Создать имя входа .
2. В диалоговом окне Создание имени входа нажмите кнопку Поиск .
3. Щелкните Типы объектов и выберите пункт Группы . Отмените выбор всех остальных пунктов.
4. В поле "Введите имя объекта" введите SQLRUserGroup и щелкните Проверить имена .
5. Имя локальной группы, связанной со службой панели запуска экземпляра, должно разрешиться в имя_экземпляра\SQLRUserGroup . Нажмите кнопку ОК .
6. По умолчанию имя входа назначается общедоступной роли и имеет разрешение на подключение к ядру СУБД.
7. Нажмите кнопку ОК .

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

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

USE GO GRANT EXECUTE ANY EXTERNAL SCRIPT TO

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

Изменение числа рабочих учетных записей, используемых Доверенная панель запуска SQL Server

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

Предоставление пользователям или именам входа R необходимых разрешений на чтение, запись или DDL в дополнительных базах данных

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

Каждая четная запись пользователя, выполняющая скрипты R, должна иметь разрешения db_datareader , db_datawriter или db_ddladmin для определенной базы данных.

Например, приведенная ниже инструкция Transact-SQL предоставляет имени входа SQL MySQLLogin права на выполнение запросов T-SQL в базе данных RSamples . Для выполнения этой инструкции имя входа SQL уже должно существовать в контексте безопасности сервера.

USE RSamples GO EXEC sp_addrolemember "db_datareader" , "MySQLLogin"

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

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

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

При использовании имени входа SQL оно должно иметь соответствующие разрешения на доступ к базе данных, из которой будут считываться данные. Для этого можно добавить разрешения Подключиться к и SELECT или добавить имя входа к роли db_datareader . Если вам нужно создавать объекты, вам потребуются права DDL_admin . Чтобы сохранять данные в таблицах, добавьте имя входа к роли db_datawriter .

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

Дополнительные сведения см. в разделе .

Оптимизация сервера для R

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

Чтобы задачам R назначались соответствующие приоритеты и выделялись необходимые ресурсы, рекомендуем использовать средство Resource Governor с целью настройки внешнего пула ресурсов для операций R. Кроме того, можно изменить размер памяти, выделенный ядру СУБД SQL Server, или увеличить количество учетных записей в службе Доверенная панель запуска SQL Server.

Совместимо с версией RC2: скачать архив rre-gpl-src.8.0.2.tar.gz

Совместимо с версией RC3: скачать архив rre-gpl-src.8.0.3.tar.gz

Совместимо с версией RTM: скачать архив rre-gpl-src.8.0.3.tar.gz

Возникли проблемы? Ознакомьтесь со списком распространенных проблем при установке предварительных версий Службы R Services (в базе данных).