Обслуживание sql server. Диспетчер настроек SQL Server

31.03.2019

В 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 от вашего имени.

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

Если вы установили 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 (в базе данных).

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

В MS SQL Server есть несколько системных баз данных:

master — В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server.

Model — Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения.
Msdb — Используется агентом SQL Server для планирования предупреждений и задач, так же является хранилищем пакетов SSIS, хранилищем информации по резервному копированию.
tempdb — База данных для временных объектов или для промежуточных результирующих наборов.
Resource — База данных только для чтения. Содержит системные объекты, которые входят в состав SQL Server. Системные объекты физически хранятся в базе данных Resource, но логически отображаются в схеме sys любой базы данных.

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

Типичные задачи обслуживания для системных баз данных (за исключением БД TempDb и resource):

— Создание резервной копии баз данных (с глубиной хранения минимум 7 дней)

— Проверка целостности баз данных инструкцией DBCC CHECKDB

Все эти операции можно завернуть в задание sql agent-а и выполнять ежедневно, благо объем данных баз данных обычно небольшой, то и операции проходят довольно быстро, а спокойствия прибавляет).

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

Как известно, в базе данных msdb хранится история резервных копий по базам данных. Теперь представим сервер, у которого баз данных более 50, каждые 10-15 минут проходит создание резервное копирование файла транзакций, какой объем будет таблиц с данной информацией?

На одном месте работы, когда я только туда пришел, на сервере было более 70 баз данных, серверу было более 2,5 лет и информация по резервному копированию никогда не чистилась, в итоге объем базы данных msdb был более 20 Гб!! А это уже совсем другое время и для создания резервной копии баз данных и для проверки целостности самой базы данных, и лишняя дисковая активность, плюс и время восстановления при аварии, в итоге имеем полно минусов, которые мы можем спокойно решить.

Очистка истории резервного копирования осуществляется через процедуру:

sp_delete_backuphistory [ @oldest_date = ] ‘oldest_date’

[ @oldest_date = ] ‘oldest_date’
Самая ранняя дата, сохраненная в таблицах журнала резервного копирования и восстановления. Аргумент oldest_date имеет тип datetime и не имеет значения по умолчанию
Одну информацию почистили, что еще там хранится?!

Почта. Настроен ли у вас Database Mail и происходит ли отсылка писем, а если еще с вложениями письма?

Вся история по нему так же хранится в базе данных msdb. Для очистки данной истории тоже есть системные процедуры:

sysmail_delete_mailitems_sp [ [ @sent_before = ] ‘sent_before’ ] [ , [ @sent_status = ] ‘sent_status’ ]

где
[ @sent_before = ] ‘sent_before’
Удаляет сообщения электронной почты до даты и времени, указанных аргументом sent_before. Аргумент sent_before имеет тип datetime и не имеет значения по умолчанию. Значение NULL соответствует всем датам.

[ @sent_status = ] ‘sent_status’

Удаляет сообщения электронной почты, тип которых указан аргументом sent_status. Аргумент sent_status имеет тип varchar(8) и не имеет значения по умолчанию. Допустимые значения: sent, unsent, retrying и failed. Значение NULL соответствует всем состояниям.

sysmail_delete_log_sp [ [ @logged_before = ] ‘logged_before’ ] [, [ @event_type = ] ‘event_type’ ]

[ @logged_before = ] ‘logged_before’

Удаляет записи вплоть до даты и времени, указанных в аргументе logged_before. Аргумент logged_before имеет тип datetime и значение по умолчанию NULL. Значение NULL соответствует всем датам.

[ @event_type = ] ‘event_type’

Удаляет журнальные записи определенного типа, заданного аргументом event_type. Аргумент event_type имеет тип varchar(15) и не имеет значения по умолчанию. Допустимые записи: success, warning, error и informational. NULL соответствует всем типам событий.

С почтой мы решили, удалил старую информацию, что еще может быть там?

А есть ли у вас SSIS пакеты и как часто они запускаются? История по их выполнению хранится в таблице ...

Для очистки ее настроена простая инструкция:

FROM .. where starttime<@dt

Где @dt – дата, записи до которой следует удалить.

После этого, выше указанные операции:

— удаление истории резервного копирования
— очистка журнала Database Mail
— очистка таблицы истории ..

Мы заворачиваем в ms sql agent задание и запускаем пару раз в месяц, и в итоге имеем наши компактные системные базы данных:).

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

Возможно что-то пропустил, так что буду рад комментариям.

Будь аккуратны, держите рабочее место в чистоте!:).

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

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

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

PaaS: SQL Azure + SQL Reporting;

SQL Reporting будет использован как сервис.
Гибридное решение: 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 не несет ответственности за проблемы в работе аппаратного или программного обеспечения, которые могли возникнуть после использования материалов данной статьи.