Служба SQL Server R – для чего?
Одним из нововведений в SQL Server 2016 – стала новая служба SQL Server R. И если многие знают что представляет из себя SQL Server. Некоторые знают, что же такое язык R и для чего он нужен. Но для чего необходимо было встраивать R в SQL Server?
SQL Server – как платформа
SQL Server всё больше перестает быть простым сервером баз данных. Теперь это скорее целая платформа для обработки данных. Платформа интеллектуальной обработки данных.
Служба SQL Server R – это встроенный в базу данных функционал аналитики, глубоко интегрирующий R в SQL Server. В компании Microsoft стремятся максимально приблизить аналитические возможности представляемые языком R к данным. Создав тем самым платформу интеллектуальной обработки данных. И дело здесь в том, что единая платформа позволяет гораздо легче использовать и управлять R, связывая его с данными на сервере баз данных. Используя эти возможности как напрямую, так и через клиентские приложения для обработки данных.
Проблема открытого исходного кода R
С открытым исходным кодом R есть три основные проблемы. И вот как в Microsoft решают эти проблемы, приближая аналитику к данным на SQL Server.
Проблема первая – передача данных
Передача данных из базы данных в среду выполнения R может оказаться как небезопасным, так и длительным процессом. Для более точного анализа необходимо исследовать значительные объемы данных и чем больше данных, тем дольше будет идти передача данных. Кроме того сам процесс передачи может быть небезопасен и нести риски с точки зрения информационной безопасности. Особенно остро эти два вопроса могут стоять при анализе данных попадающих под классификации персональных данных или данных для служебного пользования. К безопасности которых предъявляются особые требования.
Решение — уменьшить или полностью исключить необходимость передачи данных, встроив аналитику R в саму базу данных.
Проблема вторая – исполнение скриптов R
Разработчики программного обеспечения, которые пробовали использовать R в своих приложениях, знают, как бывает сложно вызвать сторонний скрипт. Что для этого использовать? Библиотеки сторонних разработчиков, которые иногда представляют лишь ограниченные возможности. Переносить скрипты с R на другой язык? Всё это порой тяжело и трудоемко.
Проблема третья – масштабируемость и производительность уровня предприятий
Очень серьёзной проблемой с точки зрения масштабируемости и использования на уровне предприятий являются ограничения среды выполнения R. Чаще всего скрипты выполняются в однопоточном режиме, а результат выполнения может быть размещен только в доступной памяти.
Архитектура масштабируемости SQL Server
Основой этой платформы анализа данных является новая архитектура расширяемости SQL Server 2016.
Способ, которым взаимодействуют R и SQL Server в Microsoft называют архитектурой расширяемости. До этого CLR давала возможность при помощи хранимых процедур запустить внешний код/скрипт на выполнение, но он всё равно запускался в пространстве процесса SQL Server. Возможность запуска таких скриптов могла привести к нарушениям в работе сервера баз данных. Кроме того хоть внешний процесс запускался в пространстве сервера баз данных, SQL Server ни как не мог повлиять на процесс выполнения.
В новой версии была создана новая универсальная расширяемая архитектура. Которая позволяет запускать внешний код, такой как скрипты R уже не внутри процесса SQL Server. Если при установке SQL Server Вы устанавливали и службу R, то в менеджере конфигурации Вы можете увидеть новую службу – Launchpad.
Интерфейс T-SQL: sp_execute_external_script
Так каким же образом внешние скрипты, такие как скрипты R используют новую архитектуру? Для этого была создана новая хранимая процедура sp_execute_external_script. Эта процедура имеет все атрибуты других хранимых процедур. У нее есть параметры, она возвращает значения и может вызываться из любого клиентского приложения.
Запуская процедуру, Вы описываете что и каким образом хотите выполнить. Процедура через именованный канал связывается со службой Launchpad и отправляет ей сообщение. И уже служба запускает на выполнение внешний скрипт. На текущий момент поддерживается выполнение только на языке R.
Henk’s tech blog
Introducing Microsoft SQL Server 2016 R Services
With the release of CTP3 SQL Server 2016 and its native In-database support for the open source R language you can now call both R, RevoScaleR functions and scripts directly from within a SQL query and benefit from multi-threaded and multi-core in-DB computations. Yet another SQLServer milestone! The R integration brings a lot of benefits and new scenarios to your end users; it brings the utility of data science to your applications without the need to ‘export’ the data to your R environment!
Installing & Enabling SQLR integration
To get started you will need to install and configure SQL Server 2016 CTP3, Revolution R Open (RRO) and Revolution E Enterprise (RRE) on the server.
The versions I installed:
– SQL Server 2016 (CTP3.0) – 13.0.700.139 (X64)
– Revolution R Open, The Enhanced Open Source R distribution (R version 3.2.2 (2015-08-14) )
– Revolution R Enterprise for SQL Server 2016 Community Technology Preview (CTP) 3 – (7.5.0)
The R integration uses a new service called ‘SQL Server Launchpad’ to ‘launch’ Advanced Analytics Extensions processes; it enables the integration with Microsoft R Open using standard T-SQL statements. (Disabling this service will make Advanced Analytics features of SQL Server unavailable).
To enable the R integration, you have to run the sp_configure ‘external scripts enabled’ command and grant permissions to users to execute R scripts via the new db_rrerole role:
Connecting to SQL Server from your favorite R environment
Traditionally you would connect from your favorite R IDE to SQL Server to retrieve data for analytics; or you can use the R GUI environment that is part of the installers bits. The default installation path to RGUI is: “C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\bin\x64\Rgui.exe”. Also popular is the R Studio Desktop that is separate downloadable from: www.Rstudio.com. (the version I installed to get started is V0.99.486).
1) Connecting R to SQLServer
To connect your RStudio environment to SQL Server the traditional way to read data (and import it into memory or a local *.xdf file) would like something like this: (it requires to setup a proper connection string;
use the ‘hostname\\instancename’ to connect to a SQLServer named instance and pull some data from a table:)
sqlServerConnString SQL Server;Server=SQL2016EE\\SQL2016CTP3;
Database =Oldskool_DB;Uid=sa;Pwd=MyPassword”
sqlServerDataDS SELECT * FROM LINEITEM_Small”,connectionString = sqlServerConnString )
2) Offloading R scripts to SQLServer
Pulling a large dataset from a database and processing it locally would have required the writing into a local file which is a single threaded process. This can take a long time… So luckily with the CTP3 we can now bring the R script to the Data and process it there! To execute and R script directly from an SQL query use the new ‘sp_execute_external_script’ to, for example, calculate the mean of L_Quantity via R:
SQL Server will execute this external script via its new launchpad service in the BxLserver.exe process (The Revolution Analytics Communication Component for SQL Server).
R Memory allocation
What would an article about ‘R’ be without a note about memory usage!
By default the SQLR integration service in the CTP3 build will use up to 20% of the total physical memory available on the host for all its In-DB running R sessions. You can monitor the Memory usage of the BxLserver.exe process via the Taskmanager Working Set memory column.
If you cross the line your query will fail with a message similar to the one below:
Msg 39004, Level 16, State 20, Line 8
A ‘R’ script error occurred during execution of ‘sp_execute_external_script’ with HRESULT 0x80004004.
Msg 39019, Level 16, State 1, Line 8
An external script error occurred:
Error in sqlSatelliteCall() :
Invalid BXL stream
STDOUT message(s) from external script:
Failed to allocate or reallocate memory.
The resolution for running out of memory like this would be to increase the max. memory allocation which is a setting in the Rlauncher.config file (which is located in C:\Program Files\Microsoft SQL Server\MSSQL13… \MSSQL\Binn directory). Increase the default setting to a value that is still safe so it will not interfere with the sqlserver memory allocation.
The last line in the file ‘MEMORY_LIMIT_PERCENT=20’ controls the maximum percentage of physical memory which gets allocated to all the R sessions. Increase the value to allow the R service to consume more memory:
Wrap-Up
Embracing and operationalizing R functionality via the new SQLServer R Services integration has become very simple! It brings a lot of new possibilities to apply advanced analytics to your SQL data, ranging from data exploration to Predictive Modeling. Getting started with R might be a bit challenging but it will enrich your reports for sure!
Конфигурация службы панели запуска SQL Server
Панель запуска SQL Server — это новая служба, которая управляет и запускает внешние скрипы, аналогично тому, как служба полнотекстового индексирования и обработки запросов запускается на отдельном узле для обработки полнотекстовых запросов.
Разрешения учетной записи
По умолчанию панель запуска SQL Server настроена для работы под учетной записью NT Service\MSSQLLaunchpad, имеющей все необходимые разрешения на выполнение внешних скриптов. Удаление разрешений этой учетной записи может привести к тому, что панель запуска не сможет запуститься или получить доступ к экземпляру SQL Server, где должны выполняться внешние скрипты.
При изменении этой учетной записи службы обязательно используйте консоль локальной политики безопасности.
Разрешения, необходимые для этой учетной записи, перечислены в следующей таблице.
| Параметр групповой политики | Имя константы |
|---|---|
| Назначение квот памяти процессам | SeIncreaseQuotaPrivilege |
| Обход проходной проверки | SeChangeNotifyPrivilege |
| Вход в систему в качестве службы. | SeServiceLogonRight |
| Замена токена уровня процесса | SeAssignPrimaryTokenPrivilege |
Дополнительные сведения о разрешениях, необходимых для запуска служб SQL Server, см. в разделе Права доступа и права Windows.
Свойства конфигурации
Как правило, изменять конфигурацию службы не требуется. Свойства, которые можно изменить: учетная запись службы, число внешних процессов (по умолчанию 20) и политика сброса паролей для учетных записей рабочих ролей.
В разделе «Службы SQL Server» щелкните правой кнопкой мыши «Панель запуска SQL Server» и выберите пункт Свойства.
В ранних версиях служб R SQL Server 2016 можно было изменять некоторые свойства этой службы путем изменения файла конфигурации Службы R (в базе данных). Этот файл больше не используется для изменения конфигураций. Изменять конфигурацию службы, например изменять учетную запись службы или количество пользователей, необходимо в диспетчере конфигурации SQL Server.
Параметры отладки
В следующей таблице перечислены дополнительные параметры для SQL Server, а также их допустимые значения.
| Имя параметра | Тип | Описание |
|---|---|---|
| JOB_CLEANUP_ON_EXIT | Целочисленный тип | Это внутренний параметр. Не изменяйте его значение. Указывает, следует ли очищать временную рабочую папку, создаваемую для каждого сеанса внешней среды выполнения, после завершения этого сеанса. Этот параметр полезен для отладки. Поддерживаемые значения: 0 (Отключено) или 1 (Включено). Значение по умолчанию — 1, т. е. файлы журналов должны удаляться после выхода. |
| TRACE_LEVEL | Целочисленный тип | Задает уровень детализации трассировки MSSQLLAUNCHPAD для целей отладки. Этот параметр влияет на файлы трассировки по пути, заданном параметром LOG_DIRECTORY. Поддерживаемые значения: 1 (Ошибка), 2 (Выполнение), 3 (Предупреждение), 4 (Информация). Значение по умолчанию — 1, т. е. отображаются только ошибки. |
Принудительное применение политики паролей
Если ваша организация имеет политику, которая требует регулярной смены паролей, может потребоваться принудительно запустить службу панели запуска для повторного создания зашифрованных паролей, которые хранит панель запуска для рабочих учетных записей.
Чтобы включить этот параметр и принудительно обновлять пароли, откройте область Свойства службы панели запуска в диспетчере конфигурации SQL Server, щелкните Дополнительно и измените значение параметра Сбросить пароль внешних пользователей на Да. После применения этого изменения пароли немедленно будут созданы повторно для всех учетных записей пользователей. Чтобы выполнить внешний скрипт после этого изменения, необходимо перезапустить службу панели запуска. В это время будут считываться заново созданные пароли.
Для сброса паролей через регулярные интервалы можно вручную установить этот флаг или использовать скрипт.
Настройка языка R в SQL Server 2016
Выполнение кода R внутри среды SQL Server: от установки и распределения ресурсов до этапов настройки
У пользователей, которые пытаются инкорпорировать код на языке R в среду SQL Server, неизбежно возникает множество вопросов относительно того, как все это работает. Известно, что язык R весьма интенсивно потребляет ресурсы памяти. Но означает ли это, что при выполнении кода R необходимо блокировать другие процессы? За какими процессами следует наблюдать, чтобы определить влияние выполняемого кода R на производительность системы SQL Server? Является ли подключение сервера к Интернету необходимым условием для выполнения кода R? Может ли R потреблять серверные ресурсы памяти без передачи нагрузки на сервер? Почему код R должен выполняться в формате внешнего сценария внутри SQL Server? Чтобы получить ответы все на эти вопросы, мы должны разобраться с тем, как код R выполняется внутри среды SQL Server.
Установка языка R в SQL Server
При установке системы SQL Server 2016 используются два компонента R — служба R Services (применяется в базах данных) и компонент R Server (используется автономно). Корпорация Microsoft приобрела компанию Revolution Analytics (и ее флагманский продукт Revolution R Enterprise) 6 апреля 2015 года. Продукт Revolution R Enterprise получил наименование R Server. Вошедший в состав установщика SQL Server пакет R Server выполняется в среде Linux или Windows. Его назначение состоит в том, чтобы ускорить выполнение кода R не только в среде SQL Server, но и при обработке данных средствами Hadoop и Teradata. Если вы хотите выполнять код R на другом сервере так, чтобы не расходовать ресурсы базы данных SQL Server, имейте в виду, что такой вариант возможен.
Однако если используемые при этом данные поступают от SQL Server, то в процессе передачи данных с SQL Server на автономный R Server возникают задержки, и это необходимо учитывать. Перемещение процессов может снизить производительность кода на языке R. О затратах на лицензирование установки автономного сервера R Server следует проконсультироваться с представителем Microsoft. Оценку ресурсной нагрузки на SQL Server со стороны службы R Services (если речь идет о базе данных) необходимо рассчитывать до принятия решения об установке автономного продукта R Server Standalone.
Установка R Components и Internet Access
Впервые корпорация Microsoft включает в пакет SQL Server продукт с открытым исходным кодом и особым лицензионным соглашением. Пользователю выдается приглашение ввести данные по лицензии на использование средств языка R. И в дополнение к этому исходный исполняемый файл доступен из Интернета, а не из пакета SQL Server Install. Если же сервер не имеет доступа к Интернету, компоненты Open Source R Package и Microsoft R Package должны быть загружены на сервер. Это определяется процедурой установки. Создаются две ссылки для установки, а также окно для вывода маршрута установки.
Что устанавливается
Когда в системе SQL Server устанавливается служба R Services, на сервере размещаются компоненты R libraries, R documentation, R tools, ScaleR Libraries и несколько специальных двоичных файлов, в том числе исполняемый файл службы Launchpad.exe. При необходимости быстро проверить, установлена ли служба R Services, просмотрите список функционирующих служб на предмет наличия службы SQL Server Launchpad service. Вызовы R и других языков специалисты Microsoft реализовали в компоненте Launchpad.exe.
Настройка SQL Server для выполнения кода R
По завершении установки оболочки R необходимо произвести ряд этапов настройки, которые обеспечат выполнение кодов R в системе SQL Server. Из окна запросов SSMS выполните следующий сценарий для запуска R:
После успешного завершения этого шага необходимо перезапустить службу SQL Server Services. Выполнив перезапуск, определите, корректно ли выполняется код R. Для этого в окне запросов SSMS запустите код, приведенный в листинге 1.
Если все пройдет нормально, сценарий возвратит единицу. Теперь система SQL Server готова выполнять коды R.
Методы использования ресурсов SQL Server R
Существует два метода выполнения кодов R в среде SQL Server. Первый состоит в вызове системной процедуры sp_execute_external_script, у которой для параметра используемого языка указано значение R (как показано в листинге 1). Код R можно добавить в раздел сценария и выполнить в среде SQL Server.
Второй метод предполагает выполнение кода не на сервере, а на любом клиенте, имеющем доступ к серверу. Функции ScaleR, включенные в состав SQL Server, позволяют клиентскому компьютеру использовать серверные ресурсы для выполнения кода R на этом клиентском компьютере. Благодаря такому переключению контекста клиенты получают возможность использовать при выполнении кода память и ресурсы сервера, что во многих случаях позволяет резко повысить производительность.
Исключение возможности переключения контекста на серверах пользователями
При эксплуатации отдельных серверов возможны ситуации, когда управляющие этими серверами администраторы баз данных предпочли бы исключить возможность привлечения кем-либо из пользователей ресурсов SQL Server к выполнению кодов R. Данная функция легко деактивируется. Для этого нужно открыть окно диспетчера настроек SQL Server Configuration Manager и правой кнопкой мыши щелкнуть на значке SQL Server Launchpad. Откроется окно свойств, показанное на экране.
.jpg) |
| Экран. Окно свойств SQL Server Launchpad |
При выборе вкладки Advanced отображается параметр External Users Count (на экране он выделен). По умолчанию его значение принимается равным 20 пользователям. Это означает, что 20 пользователей могут изменять контекст кода, выполняемого ими на клиентских компьютерах, для запуска его на сервере. Чтобы лишить пользователей такой возможности, это значение нужно установить равным 0. Теперь следует нажать кнопку OK, после чего никто не сможет выполнять код R на SQL Server. Перезагружать систему не обязательно.
SQL Server и интеграция кода R
Приведенная на рисунке диаграмма показывает, каким образом код R реализуется в SQL Server. В случае создания средствами именованного конвейера запроса на запуск кода R с использованием хранимой процедуры или посредством переключения контекста SQL Server обращается к файлу Launchpad.exe. Всякий раз при вызове хранимой процедуры или при поступлении запроса на выполнение кода R запускается процесс rlauncher. Кроме того, если на данный момент не существует ни одного объекта «задание Windows», то формируется пять таких объектов для обработки R, но, если имеются неиспользованные объекты «задание Windows», инициированные в ходе предыдущего вызова и не задействованные в данный момент, эти объекты также будут использованы.
.jpg) |
| Рисунок. Схема выполнения кода R на SQL Server |
Контейнеры объектов «задание» будут выполнять код R с использованием файла rterm.exe и вызовом файла rxlink.dll. Этот dll-модуль обрабатывает сообщения, направляемые объекту BxlServer с целью обработки записанных внутри кода R функций ScaleR, направляет данные по мониторингу модулю SQLOS, создает события XEvents и вызывает модуль sqlsatellite.dll с целью передачи и считывания данных с SQL Server.
Распределение ресурсов SQL Server
Система SQL Server управляет всеми ресурсами на уровне приложений из модуля SQLOS. SQLOS представляет собой интерфейс между SQL Server и всеми базовыми аппаратными ресурсами, включая память. С помощью регулятора ресурсов в среде SQL Server мы можем распределять ресурсы, потребляемые теми или иными процессами, с тем чтобы исключить ситуацию, когда один процесс будет использовать, к примеру, всю память, обрекая на «голодную смерть» другие процессы, выполняющиеся на той же системе. Настройка и использование пулов ресурсов открывает более широкие возможности, такие как производственные приложения, обеспечивающие распределение большей части ресурсов SQL Server, потребляемых системой SQLOS. Таким образом исключаются ситуации, когда выполнение запроса на формирование нерегламентированного отчета может отразиться на производительности первичного приложения.
Поскольку R не выполняется в среде SQLOS, разработчики SQL Server 2016 реализовали в своем продукте новый пул внешних ресурсов External Resource Pool, ответственный за управление ресурсами, используемыми средствами для работы с языком R. Посредством настройки регулятора ресурсов и создания пулов внешних ресурсов администраторы могут осуществлять настройку и мониторинг ресурсов, потребляемых кодами R, с последующим определением текущей ресурсной нагрузки.
Распределение ресурсов R в среде SQL Server
Организовать распределение для регулятора ресурсов всех функций SQLOS можно с помощью следующей команды:
По умолчанию значения max cpu, memory и cpu cap установлены на уровне 100%. С целью распределения ресурсов для R необходимо исследовать пулы внешних ресурсов.
По умолчанию максимальный объем памяти, доступный для использования средствами R за границами памяти, выделенной для SQL Server, составляет 20% объема оставшейся памяти. Если в системе SQL Server требуется выполнять дополнительные объемы работ по обработке кодов R, может возникнуть необходимость соответствующим образом изменить значения объемов памяти SQLOS и ресурсов внешних пулов. Указанные в листинге 2 настройки позволят снизить общие параметры памяти для SQLOS и увеличить параметры памяти, выделяемые для внешних процессов.
Приведенные в листинге 2 значения даются на тот случай, чтобы выделять в системе SQL Server максимальный объем ресурсов для выполнения кодов R. Перед внесением соответствующих изменений рекомендуется создать пул внешних ресурсов для кодов R и наблюдать за его использованием на протяжении определенного времени. С помощью приведенного ниже кода вы можете создать пул внешних ресурсов для обслуживания процессов, использующих коды R. Имя нового пула — R_Resources. Для максимального объема памяти указано значение, принимаемое по умолчанию, поскольку в идеале этот шаг следует предпринимать до изменения значений для внешних ресурсов:
Следующий этап процесса состоит в создании группы рабочих приложений. Эта группа, которой в коде присвоено название RworkloadGroup, используется в качестве контейнера, содержащего процессы, идентифицированные как процессы R.
В ходе следующего этапа формируется функция, относящая процессы к категории R, с тем чтобы за ними можно было осуществлять наблюдение внутри группы рабочей нагрузки (см. листинг 3).
По завершении процесса создания функции регулятор ресурсов получает команду на ее использование таким образом, чтобы все функции R подвергались мониторингу внутри пула внешних ресурсов, и включается в работу с помощью команды на перенастройку, как показано в листинге 4.
Далее идентифицируются все процессы, в которых применяются коды R и используется указанная внешняя настройка регулятора.
Таковы этапы настройки, обеспечивающие оптимальное выполнение кодов R в среде SQL Server. В заключение хочу выразить благодарность Бобу Уорну из Microsoft, который помог мне разобраться в процессах, обеспечивающих выполнение кодов R.