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

В 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 (в базе данных).

Мы сравнивали цены при использовании сервисов отчетов, которые доступны как сервис в Microsoft Azure (SQL Reporting), с вариантом развертывания обычной виртуальной машины с SQL Server (SSRS).
Опять же, я не берусь утверждать, что один сервис лучше или хуже. В большинстве случаев решение о том, какой из сервисов использовать в приложении, необходимо принимать согласно тем задачам, которые стоят перед приложением, и финансовыми требованиями заказчика. Я лишь хочу показать, что для построения решения с использованием сервисов отчетов есть два пути.

Варианты использования

Предположим, что наше приложение работает в Microsoft Azure и реализовано как Cloud Service (PaaS). Оно использует в качестве источника данных базу данных SQL Azure. Необходимо сконфигурировать сервисы построения отчетов для использования в приложении. Как уже было рассмотрено ранее, сервисы построения отчетов для приложения Microsoft Azure могут быть построены двумя способами:

1. PaaS: SQL Azure + SQL Reporting;
   
   SQL Reporting будет использован как сервис.
2. Гибридное решение: SQL Azure + SQL Server Reporting Services;
   SQL Azure будет использован как сервис;
   SQL Reporting Services должны быть настроены на отдельной виртуальной машине SQL Server (IaaS).

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

Вариант PaaS: SQL Azure + SQL Reporting

Настройка SQL Reporting сервиса

Настройки проекта отчетов

Гибридное решение: SQL Azure + SQL Server Reporting Services

Создание виртуальной машины

Настройка SQL Server

Netsh advfirewall firewall add rule name="SQL Server 1433" dir=in action=allow protocol=TCP localport=1433 netsh advfirewall firewall add rule name="HTTP 80" dir=in action=allow protocol=TCP localport=80

Настройка Reporting Services

Настройка Microsoft Azure Firewall

Заключение

После выполнения всех действий SQL Server Reporting Services будут доступны по URL, указанному при создании виртуальной машины:
http://.cloudapp.net/ReportServer

Используйте этот URL как значение свойства “TargetServerURL” при публикации проекта отчетов через SQL Server Business Intelligent Development Studio.

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

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-сервере уведомить того или иного клиента. Ещё чаще это является розово-голубой мечтой заказчика, чтобы разработчик реализовал такой механизм. Например, при превышении лимитов отгрузки какому-либо потребителю, должны быть немедленно уведомлены менеджеры, работающие с этим потребителем. Некоторые заказчики систем требуют (а мечтают об этом все заказчики без исключения), чтобы при изменении каких-то данных, у остальных пользователей системы эта информация автоматически обновлялась, причем незамедлительно. Здесь не будет обсуждаться целесообразность такого требования (оно имеет много оснований для критики), здесь будут обсуждаться только пути решения. 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 надо вызвать с клиента из отдельного потока (приоритет потока можно выбрать самый низкий). При получении события, процедуру надо перезапустить. Тайм-аут исполнения запроса надо задать бесконечный.