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


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

Я никогда не слышал, чтобы кто-нибудь в детстве мечтал стать администратором базы данных, когда вырастет. Жизнь просто приводит нас к этой профессии, хотя многим она очень даже нравится. Погружение в администрирование SQL Server редко происходит при наличии руководства по продукту, чаще всего нам приходится самостоятельно постигать секреты ремесла. Именно так начинал я сам, и в процессе учебы делал очень много ошибок. Поэтому я подготовил серию статей, в которой уже рассматривались темы резервного копирования и восстановления. Теперь пришло время рассказать об основном инструменте для настройки параметров SQL Server - диспетчере настроек SQL Server.

Диспетчер настроек SQL Server - интерактивное приложение для управления всеми службами на основе SQL Server, сетевыми протоколами, портами средства прослушивания и создания псевдонимов серверов. Диспетчер настроек SQL Server (SSCM) доступен в иерархии пунктов меню Start\Microsoft SQL Server 20xx\Configuration Tools\SQL Server Configuration Manager в версиях Microsoft Windows, предшествующих выпуску Windows 8 и Windows Server 2012. В последних указанных версиях операционной системы просто выполните поиск по словам SQL Server и выберите «Диспетчер настроек SQL Server» из списка приложений. При первом обращении к SSCM программа выглядит примерно так, как показано на экране 1 (в качестве примера в данной статье используется SQL Server 2014).

Экран 1. Диспетчер настроек SQL Server

Настройка

Рассмотрим более подробно возможные действия, разрешенные в диспетчере SSCM. Каждый элемент на левой панели представляет одну или несколько задач, которые можно выполнить в диспетчере SSCM. В некоторых случаях есть как 64-разрядный, так и 32-разрядный вариант. В данной статье мы остановимся на 32-разрядном варианте. Сегодня Microsoft SQL Server размещается на 32-разрядных серверах только в том случае, если:

а) вы обладатель старой версии SQL Server;

б) скорее всего, лишаете экземпляр SQL Server ценных ресурсов оперативной памяти.

Перечислим действия, доступные в диспетчере SSCM (см. экран 2).
Экран 2. Действия, доступные в SSCM
  • Службы SQL Server. Данное действие позволяет запускать, останавливать и перезапускать все службы, связанные с Microsoft SQL Server. Кроме того, вы можете изменять учетные записи службы, поведение при запуске и дополнительные функции и параметры запуска в зависимости от службы.
  • Сетевые настройки SQL Server. Это действие позволяет включать и отключать конкретные сетевые протоколы: Shared Memory, Named Pipes и TCP/IP, а также настраивать дополнительные параметры для каждого из них.
  • Настройки собственного клиента SQL Server (на сегодня версия 11.0). Это действие позволяет установить порядок, в котором клиенты будут использовать специально включенные протоколы для подключения к настраиваемому экземпляру SQL Server. С его помощью можно создавать псевдонимы для экземпляра SQL Server, чтобы различные приложения конечных пользователей могли подключаться к серверам с именами, отличными от действительного имени сервера. Это делается на случай, если вы не можете изменить строки подключения при переносе баз данных приложения, но все же хотите обеспечить преемственность или скрыть настоящее имя сервера от конечных пользователей. Рассмотрим каждое из этих действий подробнее.

Службы SQL Server

Связанные службы SQL Server могут (и по идее должны) управляться и настраиваться из диспетчера SSCM, а не API-интерфейса services.msc. Как отмечалось выше, мы можем управлять не только поведением при запуске и учетной записью службы, но и дополнительными параметрами каждой службы (см. экран 3).

  • SQL Server Integration Services. Дополнительные настраиваемые параметры отсутствуют.
  • SQL Server Analysis Services. Дополнительные настраиваемые параметры отсутствуют.
  • SQL Server Service:

1. FILESTREAM. Эта настройка позволяет включить или отключить доступ T-SQL, доступ файлового ввода-вывода, доступ к удаленному клиенту и установить имя общего ресурса FILESTREAM.

2. Высокий уровень доступности AlwaysOn. Эта настройка дает возможность включить или отключить группы доступности AlwaysOn, а также настроить отказоустойчивый кластер Windows (WFCS), на котором построена группа доступности.

3. Параметры запуска. Диспетчер SSCM позволяет назначить специальные параметры запуска, вступающие в силу для экземпляра. В вашем распоряжении всегда будет по крайней мере три параметра запуска для любого экземпляра SQL Server, чтобы обеспечить корректный запуск Microsoft SQL Server:

  • -d. Указывает местонахождение файла данных (.mdf) базы данных master.
  • -l. Указывает местонахождение файла журнала транзакций (.ldf) базы данных master.
  • -e. Задает местонахождение файла журнала ошибок экземпляра.

Почему именно эти три параметра? Потому что master функционирует как обращенный вовне «мозг» экземпляра SQL, работающий в сочетании со скрытой базой данных Resource, предоставляя все необходимые метаданные, необходимые для запуска экземпляра SQL Server. Требуется использовать журнал ошибок для записи каждого шага процесса запуска.

Дополнительные параметры могут быть настроены, в частности, для запуска сервера в однопользовательском режиме с целью поиска неисправностей (-m); запуска экземпляра с минимальным набором настроек (-f) в случаях, когда необходимо обойти неудачный параметр, способный привести к ухудшению отклика экземпляра; установки флагов трассировки, изменяющих базовое поведение Microsoft SQL Server (-T). Мне приходилось использовать -f для обхода неудачных настроек, когда я пытался продемонстрировать проблемы ограничения памяти и устанавливал максимальное значение памяти сервера столь малым, что не мог запустить SQL Server. Конечно, большинство администраторов баз данных устанавливают несколько флагов трассировки за время своей профессиональной деятельности на каждом из серверов SQL Server через параметр -T, но подробнее об этом я расскажу в другой статье.

Полный список параметров запуска можно найти в официальной документации Microsoft (https://msdn.

microsoft.com/en-us/library/ms190

4. Advanced («Дополнительно»). Вкладка Advanced для службы SQL Server (см. экран 4) обеспечивает возможность изменить каталог дампа для экземпляра, а также настроить механизм передачи отзывов в компанию Microsoft для дальнейшей работы над продуктом. Кроме того, вы получаете возможность читать (но не изменять) дополнительные параметры, перечисленные ниже.

  • SQL Server Reporting Services. Мы можем управлять некоторыми базовыми параметрами служб для SQL Server Reporting Services (SSRS), но для этой службы существует отдельный интерфейс. Я не рекомендую использовать SSCM для всех аспектов настройки SSRS, хотя некоторые из них доступны.
  • SQL Server Browser («Обозреватель SQL Server»). Помимо поведения при запуске и учетной записи службы, существует несколько дополнительных настроек этой службы, которыми можно управлять, кроме каталога дампа и журнала ошибок. Рекомендуется отключить эту службу, если только вы не располагаете несколькими экземплярами SQL Server на одном узле.
  • SQL Server Agent Service («Служба агента SQL Server»). Аналогично многим другим службам, вы можете настраивать только каталог дампа, ведение журнала ошибок и передачу отзывов, наряду с поведением при запуске и учетной записью службы.

Сетевые настройки SQL Server

Действия по сетевой настройке SQL Server позволяют включить любой или все три сетевых протокола, доступные в Microsoft SQL Server: Shared Memory, Named Pipes и TCP/IP.

Shared Memory и Named Pipes обеспечивают доступ сетевого компьютера к SQL Server, а TCP/IP определяет способы связи сетевых устройств с экземпляром SQL Server. Да, Named Pipes можно применять в среде Windows, но вы теряете все преимущества обхода сетевого стека при использовании Named Pipes между удаленными серверами. Параметры настройки Shared Memory начинаются и кончаются статусом включения. Named Pipes, в дополнение к статусу «включен-отключен», позволяет задать имя канала для SQL Server. Наконец, TCP/IP позволяет включать и отключать этот протокол, наряду с изменением порта, по которому SQL Server прослушивает запросы.

Настройка собственного клиента SQL Server

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

Интерфейс диспетчера настроек SQL Server - важнейший компонент настройки функционирования экземпляров и взаимодействия SQL Server с компьютерами и клиентами, которые отправляют запросы в базы данных, размещенные на экземпляре. Он определяет способ запуска и безопасность экземпляра на самом базовом уровне через управление учетными записями службы, владеющими различными компонентами Microsoft SQL Server. Это первая линия обороны SQL Server, и как начинающему, так и опытному администратору базы данных необходимо правильно использовать диспетчер SSCM и знать открывающиеся благодаря ему возможности настройки.

Зачастую база работает в «нормальных» условиях. Что под этим подразумевается:

  • Сервер SQL хорошо «питается», т.е. объем ОЗУ предоставляемой для работы SQL сервера выбирать из расчёта 70% от размера всех mdf файлов баз данных.
  • Процессор не загружен более чем на 50% в течении 90% времени.
  • Имеется достаточное место на дисках (в частности для сортировки используется база temp.db, 1С ее использует вообще для всей своей жизнедеятельности, потому стоит заранее озаботиться местом на диске с этой базой).
  • Режим восстановления базы данных - «Простой». (Эмпирически выяснено, что большой ldf файл тормозит 1с-ку, а возможность восстановления по лог-файлу весьма сомнительна).

Так же стоит учитывать несколько нюансов:

  • При использовании Standard редакции SQL, при полном перестроении индекса, все пользователи будут отключены от базы, потому стоит это учитывать при решении проведения Weekly плана обслуживания (план будет описан ниже).
  • Стоит учитывать, что сервер 1С тоже потребляет память, особенно если используются тонкие клиенты или веб-службы.
  • Самому SQL лучше ограничить в параметрах сервера максимальный объем ОЗУ, дабы по достижению критической массы, он заранее начинал очищать ненужные данные из ОЗУ. Да и чтоб разрастаясь не вгонять весь сервер в ступор.

Рационально при нормальных условиях использовать 2 плана обслуживания Weekly (раз в неделю) и Daily (в остальные 6 дней недели).

Weekly

Общий вид

По пунктам плана обслуживания:

  1. Перестроение индекса. Смысл задачи в удалении всех имеющихся индексов и установки новых. (грубо говоря инвентаризация и расстановка всего по порядку).
    В качестве параметров:
    • Выбор целевой базы (это будет почти во всех задачах, потому далее на этот параметр я не буду обращать внимание в пределах этой статьи).
    • Объект, в котором мы выбираем «Таблицы и представления».
    • Параметры свободного места – при малом объеме жесткого диска можно выбирать пункт «по умолчанию», однако я рекомендую использовать «Изменить долю свободного места на странице», рекомендуемое значение 20%. Это позволит оставить запас свободных страниц, и позволит дольше держать индексы в актуальном состоянии. ВНИМАНИЕ: Увеличивает размер базы данных.
    • Отсортировать результаты в tempdb. Думаю пояснять не требуется, однако предупредить хочу, в это время tempdb, будет очень сильно разрастаться, хоть и сортировка в ней и призвана ускорить процесс, будьте осторожны, имейте запас пространства.
    • Сохранять индекс в режиме «в сети» - фишка доступная для enterprise версии SQL. Позволяет делать переиндексацию без отключения клиентов.

    !!! ВНИМАНИЕ!!! В Standard версии при переиндексации происходит отключение клиентов от базы данных на время работы данного шага.

    Пример настроек


  2. Обновление статистики. Задача сбора информации о состоянии индексов в базе. (В общем-то мало актуальная после переиндексации, но все же я делаю).
    Параметры:
    • Объект. Все те же таблицы и представления, что и для перестроения индекса.
    • Обновить. Тут обновляем всю статистику.
    • Тип просмотра – Полный просмотр.

    Таким образом, мы обновляем статистику по всей базе данных.

    Пример настроек


  3. Выполнение инструкции T-SQL. Это выполнение произвольной команды на языке SQL, в частности нас интересует dbcc proccache

    Как следует из название – чистка кэша.

    Пример


  4. Проверка целостности базы данных. Тут кажется излишни пояснения – убеждаемся, что ничего не сломалось. В параметрах «включаем индексы» в проверку, не зря же перестраивали.

    Пример настроек


  5. Резервное копирование базы данных. Тут поговорить надо побольше, ввиду многих особенностей. Лучше изучить данный пункт отдельно самостоятельно в других руководствах, формат данной статьи не предусматривает углубленного изучения резервного копирования.
    Но о паре нюансов хочу предупредить:
    • SQL не умеет чистить контейнер свой, потому если добавлять резервные копии в файл (оно же обзывается «Устройство резервного копирования»), в итоге забьете все свободное место.
    • SQL помнит о своих резервных копиях, потому сделав ручками бэкап, единоразовый (например, отнести базу в другое место, или чтоб развернуть для теста в еще одну базу из бэкапа), следующий «разностный» будет отсчитываться от него. Дабы предотвратить это, требуется ставить галочку «Только резервное копирование». В задаче резервного копирования такого пункта нет. Вообще в недельном плане рекомендую все же использовать полный тип резервной копии.
    • И хорошо бы проверять копию, пусть спиться спокойнее.
    • Сжатие, в общем-то, использовать можно, но будьте аккуратны, разностные тогда надо тоже сжимать.

    Пример настроек

  6. Очистка журнала.
    • Журнал резервного копирования и восстановления.
    • Журнал заданий агента SQL Server.
    • Журнал плана обслуживания.

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

    Пример настроек


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

Daily

Общий вид

Говорить отдельно не имеет смысла. Почти все аналогично Weekly.
Различие в первой задаче – «Реорганизации индексов». Задачи отличаются тем, что реорганизация пытается выправить имеющиеся индексы, а не делает все с чистого листа. Чем больше фрагментация – тем чаще стоить запускать. Но в нормальных условиях раз в день достаточно, чтобы поддерживать индекс в актуальном состоянии до следующего перестроения.

Параметры


Так же можно использовать разностное резервное копирование.

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

Желаю Вам быстрой и надежной работы.
P.S. В силу того что не являюсь полноценным DBA, возможно мои комментарии весьма поверхностны, с удовольствием прочту замечания в комментариях и личке.

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

Более точно сказать было даже несколько проблем, которые, как обычно, наслоились друг на друга (или их “наслоили” администраторы пытавшиеся решить проблему).
Статья не ставит перед собой целью всестороннее и подробнейшее описание всей системы Service Broker, со всеми ее возможностями.
Здесь лишь описана среда, с которой я сталкивался наиболее часто в ходе решения проблем.
Наверное все, кто читают данный блог знают, что такое Service Broker в SQL Server, но, для того чтобы начать с одной исходной точки, я скажу пару слов об этой штуковине.

Впервые эта полезная вещь появилась в SQL Server 2005, и с тех пор не сильно изменилась. Точнее сказать она приросла некоторыми новыми возможностями, но принципы, заложенные в те годы, так и остались неизменными.
Итак.
Как следует из https://technet.microsoft.com/ru-ru/library/ms166049(v=sql.105). aspx, компонент Service Broker помогает создавать асинхронные слабосвязанные приложения, в которых независимые компоненты совместно выполняют ту или иную задачу. Эти компоненты обмениваются сообщениями, которые содержат данные, необходимые для выполнения задачи. В этом разделе описываются следующие аспекты компонента Service Broker:
диалоги;

  • упорядочение и координация сообщений;
  • асинхронное программирование на основе транзакций;
  • поддержка слабосвязанных приложений;
  • составные части компонента Service Broker.

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

  • Сообщения. Блоки информации, которыми обмениваются участники.
  • Контракт. Сообщения собираются в контракт для того, что бы обмен сообщениями был более формализован.
  • Очереди. Сообщения для отправки и при получении помещаются в специальные очереди, которые есть как у отправителя, так и у получателя.
  • Сервис. Все вышеперечисленные компоненты соединяются сервисом, который и является единицей взаимодействия в системе.
  • Маршруты. Для доставки сообщений на сервисы создаются маршруты доставки.
  • Диалог. Программируемый способ отправки сообщения от инициатора к получателю и обратно.
  • Транзакции. Вся обработка данных при передаче и получении производится по транзактному принципу, исключающему утрату данных.

Моя тестовая среда включает в себя:

  • Два экземпляра SQL Server установленных на разных виртуальных машинах
  • Контроллер домена.
  • Точки доступа (Endpoints) настроены для использования Windows аутентификации.
  • На обоих серверах созданы пользователи без логинов, использующие сертификаты для взаимной аутентификации.
  • Сертификаты скопированы на оба сервера с использованием backup.

Как это все работает вместе. Ниже приведена упрощенная схема обмена сообщениями и ее описание.

1. При отправке с инициатора сообщение попадает во внутреннюю очередь передачи представляющую собой внутреннюю системную таблицу sys.sysxmitqueue , выполнив запрос к ней мы получим, то, что ожидали.

Для просмотра сообщений, находящихся в очереди, во вне “выставлено” динамическое представление sys.transmission_queue, выполнив запрос к которому вы получите почти тот же результат. Однако в этом представлении есть очень полезный элемент, это столбец transmission_status, содержащий информацию об ошибке, возникшей при передаче и обработке сообщения.
Например: “Connection attempt failed with error: ‘10061(No connection could be made because the target machine actively refused it.)‘ .”
Также сообщение логируется в журнале транзакций, что обеспечивает его транзактную обработку.

Все сообщения от всех создаваемых сервисов проходят через эту внутреннюю таблицу и соответственно через просмотр, и при отсутствии ошибок передаются далее. Перед передачей сообщения могут шифроваться с использованием сертификатов. Будут сообщения шифроваться или нет зависит от настроек диалога инициирующего соединение.
2. После помещения сообщения в очередь передачи (sys.transmission_queue) выполняется его классифицирование.
Суть классификации состоит в том, чтобы определить, где размещен сервис, которому это сообщение адресуется. Для определения направления передачи используются маршруты доставки,созданные на этапе развертывания сервиса. В данном случае настроено два маршрута. Один указывает на удаленный сервис-получатель, другой указывает на локальный экземпляр SQL Server для доставки локальных оповещений.

Мы можем увидеть выполнение этапа классификации с помощью трасс SQL Profiler, если выберем события относящиеся к Service Broker. Обращаю внимание, что часть событий относящихся к Service Broker размещены в разделе Security Audit.

3. После выполнения классификации сообщения, устанавливается соединение с удаленной (как в данном случае) точкой соединения (Endpoint). В данном случае – это сервер использующий протокол TCP, с именем SQL2014-I1 и номером порта (Endpoint) 4022. Если при выполнении соединения возникнет ошибка, то она появится в трассе (как показано ниже) и в столбце transmission_status просмотра sys.transmission_queue.

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

В трассе на инициаторе и отправителе мы увидим подтверждение передачи.

5. Поскольку сообщение доставлено до получателя, то оно должно появиться во входной очереди получателя.

Далее, целевой сервис должен прочитать сообщения из очереди (желательно по одному) и отправить подтверждение о получении инициатору диалога. Делается эта процедура с использованием специального синтаксиса, который есть в прилагаемых скриптах https://technet.microsoft.com/en-us/library/bb839483(v=sql.105).aspx .
По мере чтения данных из очереди она пустеет, а отправителю посылается подтверждающие сообщения (или не посылаются) форма, состав которых, а также посылаются они, или нет, зависит от разработчика сервиса.
Если по мере чтения очереди программируется отправка подтверждающих сообщений, то они проходят тот же путь, что и от получателя, только в обратном порядке.
В данном блоге мы достаточно подробно рассмотрели весь путь от отправителя до получателя и в следующем начнем рассмотрение вопросов связанных с решением проблем с Service Broker на каждом из этапов обработки и передачи..

Александр Каленик, Senior Premier Field Engineer (PFE), MSFT (Russia)

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, а также на сохранение индекса доступным во время перестроения - в зависимости от редакции вашей СУБД последняя функция может быть недоступна.

Уведомления

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

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


2024 © gtavrl.ru.