Каталог служб SSIS

Область применения: SQL Server (все поддерживаемые версии) SSIS Integration Runtime в Фабрике данных Azure

Каталог SSISDB служит центральным пунктом для работы с проектами служб Службы Integration Services (SSIS), развернутыми на сервере служб Службы Integration Services. Например, можно задавать параметры проектов и пакетов, настраивать среды для указания значений времени выполнения для пакетов, выполнять пакеты и проводить устранение неполадок, а также управлять операциями на сервере служб Службы Integration Services .

Примечание

В этой статье содержатся общие сведения о каталоге служб SSIS и о каталоге служб SSIS, который запущен в локальной среде. Можно также создать каталог служб SSIS в базе данных SQL Azure и развертывать и выполнять пакеты служб SSIS в Azure. Дополнительные сведения см. в разделе Перенос рабочих нагрузок SQL Server Integration Services в облако.

Несмотря на то что пакеты служб SSIS можно выполнять в Linux, каталог служб SSIS не поддерживается на платформе Linux. Дополнительные сведения см. в разделе Извлечение, преобразование и загрузка данных в Linux с помощью служб SSIS.

Объекты, которые хранятся в каталоге SSISDB , включают проекты, пакеты, параметры, среды и журнал операций.

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

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

Каталог базы данных SSISDB и база данных SSISDB поддерживают Windows PowerShell. Дополнительные сведения об использовании SQL Server с Windows PowerShell см. в разделе SQL Server PowerShell. Примеры использования Windows PowerShell для выполнения задач, например таких как развертывание проекта, см. в записи блога SSIS и Powershell в SQL Server 2012на сайте blogs.msdn.com.

Дополнительные сведения о просмотре данных операций см. в разделе Наблюдение за выполнением пакетов и других операций.

Для доступа к каталогу SSISDB в среде SQL Server Management Studio соединитесь с ядром СУБД SQL Server , а затем разверните узел Каталоги служб Integration Services в обозревателе объектов. Для доступа к базе данных SSISDB в среде SQL Server Management Studio разверните узел «Базы данных» в обозревателе объектов.

Примечание

Невозможно переименовать базу данных SSISDB .

Примечание

Если экземпляр SQL Server , к которому присоединена база данных SSISDB , остановлен или не отвечает, процесс ISServerExec.exe завершается. Сообщение записывается в журнал событий Windows.

Если ресурсы SQL Server переходят на другой ресурс в процессе отработки отказа кластера, выполняемые пакеты не перезапускаются. Перезапуск пакетов вы можете выполнять с помощью контрольных точек. Дополнительные сведения см. в разделе Restart Packages by Using Checkpoints.

Функции и возможности

Идентификаторы объектов каталога

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

  • Папка

  • Проект

  • Среда

  • Параметр

  • Переменная среды

Папка, проект, среда

Учитывайте следующие правила при переименовании папки, проекта или среды.

  • Недопустимы символы ASCII и Юникода с кодами от 1 до 31, символ двойных кавычек ("), символ "меньше" (<), символ "больше" (>), символ вертикальной черты (|), знак возврата на один символ (\b), символ NULL (\0) и знак табуляции (\t).

  • Имя не должно содержать начальных и конечных пробелов.

  • Символ «@» не допускается в качестве первого символа, но в последующих символах может использоваться.

  • Длина имени должна быть больше 0 и меньше или равна 128.

Параметр

Принимайте во внимание следующие правила при именовании параметра.

  • Первым символом имени должна быть буква, по определению стандарта Юникод 2.0, или символ подчеркивания (_).

  • Далее могут следовать буквы или цифры, по определению стандарта Юникод 2.0, или символы подчеркивания (_).

Переменная среды

Учитывайте следующие правила при наименовании переменной среды

  • Недопустимы символы ASCII и Юникода с кодами от 1 до 31, символ двойных кавычек ("), символ "меньше" (<), символ "больше" (>), символ вертикальной черты (|), знак возврата на один символ (\b), символ NULL (\0) и знак табуляции (\t).

  • Имя не должно содержать начальных и конечных пробелов.

  • Символ «@» не допускается в качестве первого символа, но в последующих символах может использоваться.

  • Длина имени должна быть больше 0 и меньше или равна 128.

  • Первым символом имени должна быть буква, по определению стандарта Юникод 2.0, или символ подчеркивания (_).

  • Далее могут следовать буквы или цифры, по определению стандарта Юникод 2.0, или символы подчеркивания (_).

Конфигурация каталога

Для точной настройки поведения каталога измените свойства каталога. Свойства каталога определяют методы шифрования конфиденциальных данных и способы хранения данных об управлении версиями операций и проектов. Задать свойства каталога можно в диалоговом окне Свойства каталога или с помощью хранимой процедуры catalog.configure_catalog (база данных SSISDB). Просмотреть свойства можно в диалоговом окне или с помощью запроса catalog.catalog_properties (база данных SSISDB). Диалоговое окно можно открыть, щелкнув SSISDB правой кнопкой мыши в обозревателе объектов.

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

Данные о состоянии для многих из этих операций в каталоге хранятся во внутренних таблицах базы данных. Например, каталог отслеживает состояние выполнения пакета и развертывания проекта. Чтобы поддерживался размер данных операций, для удаления старых данных используется задание по обслуживанию служб SSIS в среде SQL Server Management Studio . Это задание агента SQL Server создается при установке служб Службы Integration Services .

Вы можете обновить или повторно развернуть проект Службы Integration Services с тем же именем в той же папке в каталоге. По умолчанию при каждом повторном развертывании проекта в каталоге SSISDB сохраняется предыдущая версия проекта. Чтобы поддерживался размер данных операций, для удаления старых версий проектов используется задание обслуживания сервера служб SSIS .

Для запуска задания обслуживания сервера служб SSISслужбы SSIS создают имя для входа SQL Server ##MS_SSISServerCleanupJobLogin## . Это имя входа предназначено только для внутреннего использования службами SSIS.

Следующие два свойства каталога SSISDB определяют поведение этого задания агента SQL Server . Просмотреть и изменить свойства вы можете в диалоговом окне Свойства каталога или с помощью процедур catalog.catalog_properties (база данных SSISDB) и catalog.configure_catalog (база данных SSISDB).

Периодическая очистка журналов
Шаг задания для очистки операций запускается в том случае, если это свойство имеет значение True.

Срок хранения (в днях)
Определяет максимальный срок хранения данных о допустимых операциях (в днях). Более старые данные удаляются.

Минимальное значение срока хранения — 1 день. Максимальное значение ограничено только максимальным значением данных SQL Server int. Сведения об этом типе данных см. в разделе int, bigint, smallint, and tinyint (Transact-SQL).

Периодическое удаление старых версий
Шаг задания для очистки версий проекта запускается в том случае, если это свойство имеет значение True.

Максимальное количество версий в проекте
Определяет, сколько версий проекта будет храниться в каталоге. Более старые версии проектов удаляются.

Алгоритм шифрования

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

  • AES_256 (по умолчанию)

  • AES_192

  • AES_128

  • DESX

  • TRIPLE_DES_3KEY

  • TRIPLE_DES

  • DES

При развертывании проекта Службы Integration Services на сервере Службы Integration Services каталог автоматически шифрует данные пакета и конфиденциальные значения. Каталог также автоматически расшифровывает данные после их получения. Каталог SSISDB использует уровень защиты ServerStorage . Дополнительные сведения см. в разделе Access Control for Sensitive Data in Packages.

Изменение алгоритма шифрования занимает длительное время. Сначала сервер использует указанный ранее алгоритм для расшифровки всех значений конфигурации. Затем сервер использует новый алгоритм для повторного шифрования значений. При выполнении этого процесса на сервере не могут выполняться другие операции служб Службы Integration Services . Таким образом, чтобы обеспечить непрерывное выполнение операций служб Службы Integration Services, для алгоритма шифрования задается значение только для чтения в диалоговом окне в Среда Management Studio.

Чтобы изменить настройку свойства алгоритма шифрования , переведите базу данных SSISDB в однопользовательский режим и вызовите хранимую процедуру catalog.configure_catalog. Используйте ENCRYPTION_ALGORITHM для аргумента property_name. Список поддерживаемых значений свойств см. в разделе catalog.catalog_properties (база данных SSISDB). Дополнительные сведения о хранимой процедуре см. в разделе catalog.configure_catalog (база данных SSISDB).

Дополнительные сведения об однопользовательском режиме см. в разделе Установка однопользовательского режима базы данных. Дополнительные сведения о шифровании и алгоритмах шифрования в SQL Serverсм. в подразделах раздела Шифрование SQL Server.

Для шифрования используется главный ключ базы данных. Ключ создается при создании каталога.

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

Имя свойства (диалоговое окноСвойства каталога ) Имя свойства (представление базы данных)
Имя алгоритма шифрования ENCRYPTION_ALGORITHM
Периодическая очистка журналов OPERATION_CLEANUP_ENABLED
Срок хранения (в днях) RETENTION_WINDOW
Периодическое удаление старых версий VERSION_CLEANUP_ENABLED
Максимальное количество версий в проекте MAX_PROJECT_VERSIONS
Серверное значение уровня ведения журнала по умолчанию SERVER_LOGGING_LEVEL

Разрешения

Проекты, среды и пакеты содержатся в папках, которые являются защищаемыми объектами. Вы можете предоставить разрешения для папки, включая разрешение MANAGE_OBJECT_PERMISSIONS. Разрешение MANAGE_OBJECT_PERMISSIONS позволяет делегировать пользователю разрешения на администрирование содержимого папки, не предоставляя ему членства в роли ssis_admin. Вы можете также предоставлять разрешения проектам, средам и операциям. К операциям относятся инициализация Службы Integration Services, развертывание проектов, создание и запуск выполнений, проверка проектов и пакетов, а также настройка каталога SSISDB .

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

В каталоге SSISDB используется триггер DDL ddl_cleanup_object_permissions для принудительного обеспечения целостности сведений о разрешениях для защищаемых объектов служб SSIS. Триггер срабатывает, когда участник базы данных, например пользователь базы данных, роль базы данных или роль приложения базы данных, удаляется из базы данных SSISDB.

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

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

Управление разрешениями

Вы можете управлять разрешениями на основе пользовательского интерфейса SQL Server Management Studio , хранимых процедур и пространства имен Microsoft.SqlServer.Management.IntegrationServices .

Для управления разрешениями с помощью пользовательского интерфейса SQL Server Management Studio используются следующие диалоговые окна:

  • Для папки пользуйтесь страницей Разрешения в диалоговом окне Folder Properties Dialog Box

  • Для проекта пользуйтесь страницей Разрешения в диалоговом окне Project Properties Dialog Box.

Для управления разрешениями с помощью Transact-SQL вызовите процедуру catalog.grant_permission (база данных SSISDB), catalog.deny_permission (база данных SSISDB) и catalog.revoke_permission (база данных SSISDB). Чтобы просмотреть действующие разрешения текущего участника для всех объектов, выполните запрос catalog.effective_object_permissions (база данных SSISDB). В этом разделе содержатся описания различных типов разрешений. Для просмотра разрешений, явным образом назначенных пользователю, выполните запрос catalog.explicit_object_permissions (база данных SSISDB).

Папки

Папка содержит один или несколько проектов и сред в каталоге SSISDB . Вы можете использовать представление catalog.folders (база данных SSISDB) для получения доступа к сведениям о папках в каталоге. Для управления папками вы можете использовать следующие хранимые процедуры:

Проекты и пакеты

Каждый проект может содержать несколько пакетов. Как проекты, так и пакеты могут содержать параметры и ссылки на среды. Доступ к параметрам и ссылкам на среды возможен с использованием Configure Dialog Box.

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

Эти представления содержат сведения о пакетах, проектах и версиях проектов.

Параметры

Параметры используются для присвоения значений свойствам пакета во время выполнения пакета. Для задания значения параметра проекта или пакета и очистки этого значения следует вызвать процедуру catalog.set_object_parameter_value (база данных SSISDB) или catalog.clear_object_parameter_value (база данных SSISDB). Чтобы задать значение параметра для экземпляра выполнения, следует вызвать catalog.set_execution_parameter_value (база данных SSISDB). Значения параметров по умолчанию можно получить, вызвав процедуру catalog.get_parameter_values (база данных SSISDB).

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

Серверные среды, переменные сервера и ссылки на серверные среды

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

Следующие хранимые процедуры позволяют выполнять многие другие задачи управления для сред и переменных.

Вызов хранимой процедуры catalog.set_environment_variable_protection (база данных SSISDB) позволит установить бит конфиденциальности для переменной.

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

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

Выполнения и проверки

Выполнение — это экземпляр выполнения пакета. Процедуры catalog.create_execution (база данных SSISDB) и catalog.start_execution (база данных SSISDB) позволяют настроить и запустить выполнение пакета. Чтобы остановить выполнение или проверку пакета или проекта, вызовите catalog.stop_operation (база данных SSISDB).

Для приостановки выполняемого пакета и создания файла дампа вызовите хранимую процедуру catalog.create_execution_dump. Файл дампа предоставляет сведения о выполнении пакета, которые могут быть полезны при диагностике неполадок в ходе выполнения. Дополнительные сведения о создании и настройке файлов дампа см. в разделе Generating Dump Files for Package Execution.

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

Для проверки проектов и пакетов можно вызвать хранимые процедуры catalog.validate_project (база данных SSISDB) и catalog.validate_package (база данных SSISDB). Представление catalog.validations (база данных SSISDB) содержит сведения о таких проверках, как ссылки серверной среды, учитываемые при проверке, имеет ли место проверка зависимостей или полная проверка и используется ли при запуске пакета 32-разрядная или 64-разрядная среда выполнения.

Создание каталога служб SSIS

После разработки и тестирования пакетов в SQL Server Data Toolsможно выполнить развертывание проектов, содержащих пакеты, на сервере Службы Integration Services . Прежде чем развертывать проекты на сервере служб Службы Integration Services , необходимо создать каталог SSISDB на этом сервере. Программа установки SQL Server 2012 (11.x) не создает этот каталог автоматически. Его необходимо создать вручную, следуя приведенным ниже инструкциям.

Вы можете создать каталог SSISDB в среде SQL Server Management Studio. Можно также создать каталог программным способом с помощью Windows PowerShell.

Создание каталога SSISDB в SQL Server Management Studio

  1. Откройте среду SQL Server Management Studio.

  2. Соединитесь с ядром СУБД SQL Server .

  3. В обозревателе объектов разверните узел сервера, щелкните правой кнопкой мыши узел Каталоги служб Integration Services и выберите пункт Создать каталог.

  4. Установите флажок Включить интеграцию со средой CLR.

    Каталог использует хранимые процедуры CLR.

  5. Щелкните Включить автоматическое выполнение хранимой процедуры служб Integration Services при запуске SQL Server , чтобы хранимая процедура catalog.startup выполнялась каждый раз при перезапуске экземпляра сервера служб Integration Services .

    Хранимая процедура осуществляет обслуживание состояния операций для каталога SSISDB. Она исправляет состояние любых пакетов, выполнявшихся в момент отключения экземпляра сервера служб Integration Services.

  6. Введите пароль и нажмите кнопку ОК.

    Этот пароль защищает главный ключ базы данных, используемый для шифрования данных каталога. Сохраните пароль в надежном месте. Рекомендуется также создать резервную копию главного ключа базы данных. Дополнительные сведения см. в статье Back Up a Database Master Key.

Создание каталога SSISDB программным способом

  1. Выполните следующий скрипт PowerShell.

    # Load the IntegrationServices Assembly  
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")  
    
    # Store the IntegrationServices Assembly namespace to avoid typing it every time  
    $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"  
    
    Write-Host "Connecting to server ..."  
    
    # Create a connection to the server  
    $sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"  
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString  
    
    # Create the Integration Services object  
    $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection  
    
    # Provision a new SSIS Catalog  
    $catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "P@assword1")  
    $catalog.Create()  
    
    

    Дополнительные примеры использования Windows PowerShell и пространства имен Microsoft.SqlServer.Management.IntegrationServices см. в записи блога SSIS and PowerShell in SQL Server 2012 (Службы SSIS и PowerShell в SQL Server 2012) на сайте blogs.msdn.com. Общие сведения о пространстве имен и примеры кода см. в записи блога Обзор модели управляемых объектов каталога служб SSISна сайте blogs.msdn.com.

Диалоговое окно свойств каталога

Диалоговое окно свойств каталога служит для настройки каталога SSISDB. Свойства каталога определяют методы шифрования конфиденциальных данных, параметры хранения данных о версиях операций и проектов, а также время ожидания операций проверки. Каталог служб SSISDB представляет собой центральную точку хранения и администрирования проектов, пакетов, параметров и сред служб Службы Integration Services .

Свойства каталога можно также просмотреть в представлении catalog.catalog_properties и задать эти свойства с помощью хранимой процедуры catalog.configure_catalog. Дополнительные сведения см. в разделах catalog.catalog_properties (база данных SSISDB) и catalog.configure_catalog (база данных SSISDB).

Выбор действия

Открытие диалогового окна свойств каталога

  1. Откройте среду SQL ServerСреда Management Studio.

  2. Создайте соединение с компонентом Microsoft SQL Server Database Engine.

  3. В обозревателе объектов разверните узел Службы Integration Services , щелкните правой кнопкой мыши элемент SSISDBи выберите пункт Свойства.

Настройка параметров

Параметры

В приведенной ниже таблице описаны некоторые свойства, определенные в диалоговом окне, а также соответствующие свойства в представлении catalog.catalog_properties.

Имя свойства (диалоговое окно свойств каталога) Имя свойства (представление catalog.catalog_properties) Описание
Имя алгоритма шифрования ENCRYPTION_ALGORITHM Указывает тип шифрования, который используется при шифровании значений конфиденциальных параметров каталога. Допустимы следующие значения:

DES

TRIPLE_DES

TRIPLE_DES_3KEY

DESPX

AES_128

AES_192

AES_256 (по умолчанию)
Максимальное количество версий в проекте MAX_PROJECT_VERSIONS Определяет, сколько версий проекта будет храниться в каталоге. Когда общее количество версий превышает максимальное значение, более ранние версии проектов удаляются при выполнении задания по очистке версий проекта.
Периодическая очистка журналов OPERATION_CLEANUP_ENABLED Установите это свойство в значение True, чтобы указать, что задание агента SQL Server по очистке операций выполняется. В противном случае установите свойство в значение False.
Срок хранения (в днях) RETENTION_WINDOW Задайте максимальный срок хранения данных о допустимых операциях (в днях). Данные, которые хранятся дольше указанного числа дней, удаляются заданием агента SQL по очистке операций.

Резервное копирование, восстановление и перемещение каталога служб SSIS

Область применения: SQL Server 2016 и более поздних версий Не поддерживается База данных SQL Azure Не поддерживается Azure Synapse Analytics Не поддерживается Analytics Platform System (PDW)

Службы SQL Server 2019 Integration Services (SSIS) включена база данных SSISDB. Создайте запрос представления в базе данных SSISDB для просмотра объектов, настроек и рабочих данных, которые хранятся в каталоге SSISDB . Этот раздел содержит инструкции для выполнения резервного копирования и восстановления базы данных.

В каталоге SSISDB хранятся пакеты, которые развернуты на сервере Службы Integration Services. Дополнительные сведения о каталоге см. в разделе Каталог служб SSIS.

Создание резервной копии базы данных служб SSIS

  1. Откройте среду SQL Server Management Studio и установите соединение с экземпляром SQL Server.

  2. Создайте резервную копию главного ключа для базы данных SSISDB с помощью инструкции BACKUP MASTER KEY Transact-SQL. Ключ хранится в указанном файле. Используйте пароль для шифрования главного ключа базы данных в файле.

    Дополнительные сведения об инструкции см. в разделе BACKUP MASTER KEY (Transact-SQL).

    В следующем примере главный ключ экспортируется в файл c:\temp directory\RCTestInstKey . Пароль LS2Setup! используется для шифрования главного ключа.

    backup master key to file = 'c:\temp\RCTestInstKey'  
           encryption by password = 'LS2Setup!'  
    
    
  3. Выполните резервное копирование базы данных SSISDB с помощью диалогового окна Создание резервной копии базы данных в SQL Server Management Studio. Дополнительные сведения см. в разделе Как создать резервную копию базы данных (среда SQL Server Management Studio).

  4. Создайте скрипт CREATE LOGIN для ## MS_SSISServerCleanupJobLogin ##, выполнив следующие действия. Дополнительные сведения см. в статье CREATE LOGIN (Transact-SQL).

    1. В обозревателе объектов среды SQL Server Management Studioразверните узел Безопасность , а затем узел Имена входа .

    2. Щелкните правой кнопкой мыши ##MS_SSISServerCleanupJobLogin## и выберите Внести в скрипт имена входа как>СОЗДАТЬ в>В новом окне редактора запросов.

  5. Если планируется восстановление базы данных SSISDB из копии в экземпляре SQL Server, где каталог SSISDB еще не создан, создайте скрипт CREATE PROCEDURE для sp_ssis_startup следующим образом. Дополнительные сведения см. в статье CREATE PROCEDURE (Transact-SQL).

    1. В обозревателе объектов разверните узел Базы данных , а затем узел master>Программирование>Хранимые процедуры .

    2. Щелкните правой кнопкой мыши dbo.sp_ssis_startup и выберите Внести в скрипт хранимые процедуры как>СОЗДАТЬ в>В новом окне редактора запросов.

  6. Убедитесь, что агент SQL Server запущен.

  7. Если планируется восстановление базы данных SSISDB из копии в экземпляре SQL Server, где каталог SSISDB еще не создан, создайте скрипт для задания обслуживания сервера SSIS следующим образом. Скрипт создается в агенте SQL Server автоматически при создании каталога SSISDB. Задание помогает очистить журналы операции с вышедшим сроком сохранения и удалить старые версии проектов.

    1. В обозревателе объектов разверните узел Агент SQL Server , а затем узел Задания .

    2. Щелкните правой кнопкой мыши задание по обслуживанию служб SSIS и выберите Внести в скрипт задание как>СОЗДАТЬ в>В новом окне редактора запросов.

Восстановление базы данных служб SSIS

  1. Если база данных SSISDB восстанавливается из копии в экземпляре SQL Server, где каталог SSISDB никогда не создавался, включите среду CLR с помощью хранимой процедуры sp_configure. Дополнительные сведения см. в разделах sp_configure (Transact-SQL) и Параметр clr enabled.

    use master   
           sp_configure 'clr enabled', 1  
           reconfigure  
    
    
  2. Если база данных SSISDB восстанавливается из копии на экземпляре SQL Server , где каталог SSISDB никогда не создавался, создайте асимметричный ключ и имя входа из асимметричного ключа и предоставьте разрешение UNSAFE для имени входа.

    Create Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey  
           FROM Executable File = 'C:\Program Files\Microsoft SQL Server\YourSQLServerDefaultCompatibilityLevel\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'  
    

    Для значения YourSQLServerDefaultCompatibilityLevel см. список уровней совместимости SQL Server по умолчанию.

    Службы Integration Services требуют предоставления разрешения UNSAFE для имени входа, поскольку имени входа необходим дополнительный доступ к ресурсам, на которые существуют ограничения, например API-интерфейс Microsoft Win32. Дополнительные сведения о коде разрешения UNSAFE см. в разделе Creating an Assembly.

    Create Login ##MS_SQLEnableSystemAssemblyLoadingUser## FROM Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey   
    Grant Unsafe Assembly to ##MS_SQLEnableSystemAssemblyLoadingUser##    
    
  3. Восстановите базу данных SSISDB из резервной копии с помощью диалогового окна Восстановление базы данных в SQL Server Management Studio. Дополнительные сведения см. в следующих разделах:

  4. Выполните скрипт, созданный в разделе Создание резервной копии базы данных служб SSIS для ##MS_SSISServerCleanupJobLogin##, sp_ssis_startup и заданий по обслуживанию служб SSIS. Убедитесь, что агент SQL Server запущен.

  5. Выполните следующую инструкцию для установки автоматического выполнения процедуры sp_ssis_startup. Дополнительные сведения см. в разделе sp_procoption (Transact-SQL).

    EXEC sp_procoption N'sp_ssis_startup','startup','on'  
    
  6. Сопоставьте пользователя SSISDB ##MS_SSISServerCleanupJobUser## (база данных SSISDB) с ##MS_SSISServerCleanupJobLogin## с помощью диалогового окна Свойства имени входа в среде SQL Server Management Studio.

  7. Восстановите главный ключ с помощью одного из следующих методов. Дополнительные сведения о шифровании см. в разделе Encryption Hierarchy.

    • Метод 1

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

             Restore master key from file = 'c:\temp\RCTestInstKey'  
             Decryption by password = 'LS2Setup!' -- 'Password used to encrypt the master key during SSISDB backup'  
             Encryption by password = 'LS3Setup!' -- 'New Password'  
             Force  
      
      

      Примечание

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

      Примечание

      Если главный ключ базы данных еще не зашифрован главным ключом сервера, то в среде SQL Server Management Studio отображается следующее предупреждающее сообщение. Пропустите это предупреждающее сообщение.

      Не удается расшифровать текущий главный ключ. Ошибка пропущена, поскольку задан параметр FORCE.

      Аргумент FORCE указывает, что следует продолжить процесс восстановления, даже если текущий главный ключ базы данных закрыт. Для каталога SSISDB это сообщение отображается, так как главный ключ базы данных не открыт в экземпляре, где осуществляется восстановление базы данных.

    • Метод 2.

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

      open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'  
             Alter Master Key Add encryption by Service Master Key  
      
  8. Определите, совместимы ли схема каталога SSISDB и двоичные файлы Службы Integration Services (сборка ISServerExec и SQLCLR), запустив catalog.check_schema_version.

  9. Для подтверждения того, что база данных SSISDB успешно восстановлена, выполните такие операции с каталогом SSISDB, как запуск пакетов, развернутых на сервере Службы Integration Services . Дополнительные сведения см. в разделе Запуск пакетов служб Integration Services (SSIS).

Перемещение базы данных служб SSIS

  • Следуйте инструкциям по перемещению пользовательских баз данных. Дополнительные сведения см. в статье Move User Databases.

    Обязательно создайте резервную копию главного ключа базы данных SSISDB и защитите файл резервной копии. Дополнительные сведения см. в статье Создание резервной копии каталога SSISDB.

    Убедитесь, что соответствующие объекты служб Integration Services (SSIS) созданы в новом экземпляре SQL Server , где каталог SSISDB еще не был создан.

Обновление каталога служб SSIS (SSISDB)

Мастер обновления SSISDB можно использовать для обновления базы данных каталога служб SSIS (SSISDB), когда эта база данных старше текущей версии экземпляра SQL Server. База данных может быть старше, если верно любое из следующих условий.

  • База данных восстановлена из более старой версии SQL Server.

  • База данных не была удалена из группы доступности AlwaysOn перед обновлением экземпляра SQL Server. Это состояние препятствует автоматическому обновлению базы данных. Дополнительные сведения: Обновление SSISDB в группе доступности.

Мастер может обновить только базу данных на экземпляре локального сервера.

Обновление каталога служб SSIS (SSISDB) посредством запуска мастера обновления SSISDB

  1. Выполните архивацию базы данных каталога служб SSIS (SSISDB).

  2. В SQL Server Management Studioразверните локальный сервер, а затем — Каталоги служб Integration Services.

  3. Щелкните правой кнопкой мыши SSISDB, а затем выберите Обновление базы данных , чтобы запустить мастер обновления SSISDB. Либо запустите мастер обновления SSISDB, запустив файл C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDBUpgradeWizard.exe с повышенными привилегиями на локальном сервере.

    Запуск мастера обновления SSISDB

  4. На странице Выбор экземпляра выберите экземпляр SQL Server на локальном сервере.

    Важно!

    Мастер может обновить только базу данных на экземпляре локального сервера.

    Установите флажок, чтобы указать, что вы выполнили архивацию базы данных SSISDB перед запуском мастера.

    Выбор сервера в мастере обновления SSISDB

  5. Выберите Обновить , чтобы обновить базу данных каталога служб SSIS.

  6. Просмотрите результаты на странице Результаты .

    Просмотр результатов в мастере обновления SSISDB

AlwaysOn для каталога служб SSIS (SSISDB)

Группы доступности AlwaysOn — это решение для высокой доступности и аварийного восстановления, являющееся альтернативой зеркальному отображению баз данных на уровне предприятия. Группа доступности поддерживает среду отработки отказа для дискретного набора пользовательских баз данных, известных как базы данных доступности, которые совместно выполняют переход на другой ресурс. Дополнительные сведения см. в статье Группы доступности AlwaysOn.

Для обеспечения высокого уровня доступности для каталога служб SSIS (SSISDB) и его содержимого (проектов, пакетов, журналов выполнения и т. д.) можно добавить базу данных SSISDB (практически так же, как и любую другую базу данных) в группу доступности AlwaysOn. В случае сбоя один из вторичных узлов автоматически становится новым основным узлом.

Важно!

В случае сбоя выполнявшиеся пакеты не перезапускаются и не возобновляются.

В этом разделе:

  1. Предварительные требования

  2. Настройка поддержки служб SSIS для AlwaysOn

  3. Обновление SSISDB в группе доступности

Предварительные требования

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

  1. Настроить отказоустойчивый кластер Windows. Инструкции см. в записи блога Установка компонентов и средств отказоустойчивого кластера для Windows Server 2012) (Installing the Failover Cluster Feature and Tools for Windows Server 2012). Установите компоненты и средства на всех узлах кластера.

  2. Установить SQL Server 2016 с компонентом Integration Services (SSIS) на каждом узле кластера.

  3. Включите функцию "Группы доступности AlwaysOn" для каждого экземпляра SQL Server. Более подробные сведения см. в разделе Включение групп доступности AlwaysOn .

Настройка поддержки служб SSIS для AlwaysOn

Важно!

  • Эти действия необходимо выполнить на основном узле группы доступности.
  • После добавления SSISDB в группу доступности AlwaysOn необходимо включить поддержку SSIS для AlwaysOn.

Шаг 1. Создание каталога служб Integration Services

  1. Запустите SQL Server Management Studio и подключитесь к экземпляру SQL Server в кластере, который нужно задать в качестве основного узла группы высокой доступности AlwaysOn для SSISDB.

  2. В обозревателе объектов разверните узел сервера, щелкните правой кнопкой мыши узел Каталоги служб Integration Services и выберите пункт Создать каталог.

  3. Установите флажок Включить интеграцию со средой CLR. Каталог использует хранимые процедуры CLR.

  4. Щелкните Включить автоматическое выполнение хранимой процедуры служб Integration Services при запуске SQL Server , чтобы хранимая процедура catalog.startup выполнялась каждый раз при перезапуске экземпляра сервера служб Integration Services . Хранимая процедура осуществляет обслуживание состояния операций для каталога SSISDB. Она исправляет состояние любых пакетов, выполнявшихся в момент отключения экземпляра сервера служб SSIS.

  5. Введите парольи нажмите кнопку ОК. Этот пароль защищает главный ключ базы данных, используемый для шифрования данных каталога. Сохраните пароль в надежном месте. Рекомендуется также создать резервную копию главного ключа базы данных. Дополнительные сведения см. в статье Back Up a Database Master Key.

Шаг 2. Добавление SSISDB в группу доступности AlwaysOn

Процедура добавления базы данных SSISDB в группу доступности AlwaysOn практически не отличается от добавления другой базы данных пользователей в группу доступности. См. раздел Использование мастера групп доступности.

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

Создание группы доступности

Шаг 3. Включение поддержки служб SSIS для AlwaysOn

После создания каталога службы интеграции щелкните правой кнопкой мыши узел Каталоги службы интеграции и выберите пункт Включить поддержку Always On. Откроется следующее диалоговое окно Включение поддержки Always On. Если этот пункт меню неактивен, убедитесь, что установлены все необходимые компоненты, и нажмите кнопку Обновить.

Включение поддержки AlwaysOn

Предупреждение

Автоматическая отработка отказа базы данных SSISDB поддерживается только после включения поддержки служб SSIS для AlwaysOn.

Вторичные реплики, добавленные из группы доступности AlwaysOn, отображаются в таблице. Щелкните кнопку Подключить... для каждой реплики в списке и введите учетные данные для подключения к реплике. Для включения поддержки служб SSIS для AlwaysOn учетная запись пользователя должна входить в группу системных администраторов в каждой реплике. После успешного подключения к каждой реплике нажмите кнопку ОК , чтобы включить поддержку служб SSIS для AlwaysOn.

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

  1. Обновите контекстное меню с помощью команды Обновить.
  2. Убедитесь, что вы устанавливаете подключение к основному узлу. Необходимо включить поддержку AlwaysOn на основном узле.
  3. Убедитесь, что используется SQL Server версии 13.0 или более поздней. Службы SSIS поддерживают AlwaysOn только в версиях SQL Server 2016 и более поздних.

Обновление SSISDB в группе доступности

Если вы обновляете SQL Server с предыдущей версии и SSISDB находится в группе доступности AlwaysOn, обновление может блокироваться правилом "Проверка SSISDB в группе доступности AlwaysOn". Эта блокировка связана с тем, что обновление выполняется в однопользовательском режиме, а база данных доступности должна быть многопользовательской. Таким образом, во время обновления или применения исправлений все базы данных доступности, включая SSISDB, переводятся в автономный режим и не обновляются или исправляются. Чтобы продолжить обновление, сначала удалите SSISDB из группы доступности, затем обновите каждый узел или примените к нему исправление, а после этого снова добавьте SSISDB в группу доступности.

Если обновление заблокировано правилом "Проверка SSISDB в группе доступности AlwaysOn", выполните следующие действия, чтобы обновить SQL Server.

  1. Удалите базу данных SSISDB из группы доступности. Дополнительные сведения см. в разделах Удаление базы данных-получателя из группы доступности (SQL Server) и Удаление базы данных-источника из группы доступности (SQL Server).

  2. В мастере обновления щелкните Выполнить снова. Правило "Проверка SSISDB в группе доступности AlwaysOn" соблюдено.

  3. Нажмите кнопку Далее , чтобы продолжить обновление.

  4. После обновления всех узлов добавьте базу данных SSISDB обратно в группу доступности AlwaysOn. Дополнительные сведения см. в разделе Добавление базы данных в группу доступности (SQL Server).

Если при обновлении SQL Server блокировка применена не была и SSISDB находится в группе доступности AlwaysOn, отдельно обновите SSISDB после обновления компонента SQL Server Database Engine. Используйте мастер обновления служб SSIS для обновления SSISDB, как описано в следующей процедуре.

  1. Переместите базу данных SSISDB из группы доступности или удалите группу доступности, если SSISDB является единственной базой данных в группе доступности. Для выполнения этой задачи необходимо запустить SQL Server Management Studio на основном узле группы доступности.

  2. Удалите базу данных SSISDB со всех узлов реплики.

  3. Обновите базу данных SSISDB на основном узле. Вобозревателе объектов в SQL Server Management Studio разверните Каталоги служб Integration Services, щелкните правой кнопкой мыши SSISDB, а затем выберите команду Обновить базу данных. Следуйте инструкциям в мастере обновления SSISDB по обновлению базы данных. Мастер обновления SSIDB необходимо запустить локально на основном узле.

  4. Следуйте инструкциям по добавлению SSISDB обратно в группу доступности в разделе Шаг 2. Добавление SSISDB в группу доступности Always On.

  5. Следуйте инструкциям в разделе Шаг 3. Включение поддержки служб SSIS для Always On.

Каталог SSISDB и делегирование в сценариях двойного прыжка

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

Представьте себе сценарий, в котором пользователь входит в систему на клиентском компьютере A и запускает SQL Server Management Studio (SSMS). В среде SSMS пользователь подключается к серверу SQL Server, размещенному на компьютере B, на котором находится каталог SSISDB. Пакет служб SSIS хранится в этом каталоге SSISDB, и пакет в свою очередь подключается к службе SQL Server, работающей на компьютере C (пакет также может обращаться к любым другим службам). Когда пользователь запускает пакет служб SSIS на компьютере A, среда SSMS сначала успешно передает учетные данные пользователя с компьютера A на компьютер B (на котором среда выполнения служб SSIS выполняет пакет). Теперь процессу среды выполнения служб SSIS (ISServerExec.exe) необходимо делегировать учетные данные пользователя с компьютера B на компьютер C, чтобы успешно завершить выполнение. Однако делегирование учетных данных по умолчанию отключено.

Пользователь может включить делегирование учетных данных, предоставив право Доверять этому пользователю делегирование служб (только Kerberos) учетной записи службы SQL Server (на компьютере B), которая запускает ISServerExec.exe в качестве дочернего процесса. Этот процесс называется настройкой неограниченного делегирования или открытием делегирования для учетной записи службы SQL Server. Прежде чем предоставить это право, определите, соответствует ли это требованиям безопасности организации.

SSISDB не поддерживает ограниченное делегирование. В среде с двойным прыжком, если учетная запись службы сервера SQL Server, на котором размещается каталог SSISDB (компьютер B в нашем примере), настроена для ограниченного делегирования, ISServerExec.exe не сможет делегировать учетные данные третьему компьютеру (компьютеру C). Это применимо к сценариям, в которых включен Credential Guard в Защитнике Windows (для работы которого должно быть включено ограниченное делегирование).

См. также