Управление метаданными при предоставлении доступа к базе данных на другом сервере

Применимо к: SQL Server (все поддерживаемые версии)

Информация в этой статье применима в следующих ситуациях:

  • Настройка реплик доступности группы доступности Always On групп доступности.

  • Настройка зеркального отображения базы данных.

  • Подготовка смены ролей между сервером-источником и сервером-получателем в конфигурации доставки журналов.

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

  • Присоединение копии базы данных к другому экземпляру сервера.

Некоторые приложения зависят от информации, сущностей или объектов, которые находятся вне области однопользовательской базы данных. Как правило, приложение зависит от баз данных master и msdb пользовательской базы данных. Что-либо сохраненное вне пользовательской базы данных, которая требуется для правильного функционирования другой базы данных, должно быть доступно на экземпляре целевого сервера. Например, имена входа для приложений сохраняются как метаданные в базе данных master и должны быть созданы заново на целевом сервере. Если приложение или план обслуживания базы данных зависит от заданий агента SQL Server, чьи метаданные сохранены в базе данных msdb, необходимо заново создать эти задания в экземпляре целевого сервера. Точно так же метаданные для триггера уровня сервера сохраняются в базе данных master.

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

Информация, сущности и объекты, сохраненные вне пользовательской базы данных

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

Параметры конфигурации сервера

SQL Server 2005 (9.x) и более поздних версий выборочно устанавливаются и запускают ключевые службы и компоненты. Это помогает сократить уязвимую контактную зону системы. В конфигурации новых установок по умолчанию многие компоненты не включены. Если в базе данных имеется зависимость от какой-либо отключенной по умолчанию службы или свойства, то их необходимо включить на целевом экземпляре сервера.

Дополнительные сведения об этих параметрах и их включении или отключении см. в разделе "Параметры конфигурации сервера" (SQL Server).

Учетные данные

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

Дополнительные сведения об этой функции см. в разделе "Учетные данные" (ядро СУБД).

Примечание

агент SQL Server учетные записи-посредники используют учетные данные. Получить идентификационный номер участника-посредника можно в системной таблице sysproxies .

Межбазовые запросы

Параметры базы данных DB_CHAINING и TRUSTWORTHY по умолчанию принимают значение OFF. Если в исходной базе данных какой-либо из этих параметров имеет значение ON, то может потребоваться его включение в целевом экземпляре сервера. Дополнительные сведения см. в разделе ALTER DATABASE (Transact-SQL).

Операции присоединения и отсоединения приводят к отмене межбазовых цепочек владения для базы данных. Сведения о том, как включить цепочки владения, см. в разделе Параметр конфигурации сервера "cross db ownership chaining".

Дополнительные сведения см. в разделе "Настройка зеркальной базы данных для использования надежного свойства " (Transact-SQL)

Владелец базы данных

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

Распределенные запросы и связанные серверы

Распределенные запросы и связанные серверы поддерживаются приложениями OLE DB. Распределенные запросы получают доступ к данным из нескольких разнородных источников, расположенных на одних и тех же или разных компьютерах. Конфигурация связанного сервера позволяет SQL Server выполнять команды для источников данных OLE DB на удаленных серверах. Дополнительные сведения об этих функциях см. в разделе "Связанные серверы (ядро СУБД)".

Зашифрованные данные

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

Чтобы разрешить автоматическое шифрование главного ключа базы данных на экземпляре сервера, копия этого ключа зашифровывается с использованием главного ключа службы. Эта зашифрованная копия хранится как в базе данных, так и в master. Как правило, копия, которая хранится в master, обновляется без взаимодействия с пользователем при каждом изменении главного ключа. SQL Server сначала пытается расшифровать главный ключ базы данных с помощью главного ключа службы экземпляра. Если эта расшифровка завершается сбоем, SQL Server выполняет поиск учетных данных главного ключа с тем же идентификатором GUID семейства, что и база данных, для которой требуется главный ключ. SQL Server затем пытается расшифровать главный ключ базы данных с каждым соответствующими учетными данными до тех пор, пока расшифровка не будет выполнена или больше нет учетных данных. Главный ключ, который не зашифрован главным ключом службы, должен быть открыт с помощью инструкции OPEN MASTER KEY и пароля.

При копировании, восстановлении или присоединении зашифрованной базы данных к новому экземпляру SQL Server копия главного ключа базы данных, зашифрованная главным ключом службы, не хранится в master экземпляре целевого сервера. На целевом экземпляре сервера необходимо открыть главный ключ базы данных. Сделать это можно, выполнив следующую инструкцию: OPEN MASTER KEY DECRYPTION BY PASSWORD ='password' . После этого рекомендуется включить автоматическую расшифровку главного ключа базы данных, выполнив следующую инструкцию: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY. Эта инструкция передает экземпляру сервера копию главного ключа базы данных, зашифрованного с использованием главного ключа службы. Дополнительные сведения см. в разделе OPEN MASTER KEY (Transact-SQL) и ALTER MASTER KEY (Transact-SQL).

Сведения о включении автоматической расшифровки главного ключа базы данных в зеркальной базе данных см. в разделе Настройка зашифрованной зеркальной базы данных.

Дополнительные сведения см. также в следующих разделах:

Определяемые пользователем сообщения об ошибках

Определяемые пользователем сообщения об ошибках настраиваются в представлении каталога sys.messages , Это представление каталога хранится в master. Если приложение базы данных зависит от определяемых пользователем сообщений об ошибках и если эта база данных доступна на другом экземпляре сервера, то для добавления на целевой экземпляр сервера уже имеющихся определяемых пользователем сообщений об ошибках следует пользоваться хранимой процедурой sp_addmessage .

Уведомления о событиях и события инструментария управления Windows (WMI) на уровне сервера

Уведомления о событии на уровне сервера

Уведомления о событиях на уровне сервера хранятся в msdb. Поэтому, если приложение базы данных находится в зависимости от уведомления о событии уровня сервера, это уведомление необходимо создать и в целевом экземпляре сервера. Для просмотра уведомлений о событиях в экземпляре сервера используется представление каталога sys.server_event_notifications . Дополнительные сведения см. в разделе Event Notifications.

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

События инструментария управления Windows (WMI)

Поставщик WMI для событий сервера позволяет использовать инструментарий управления Windows (WMI) для мониторинга событий в SQL Server. Все приложения, основанные на событиях уровня сервера, обработанных поставщиком инструментария WMI, который необходим базе данных, должны быть определены на компьютере экземпляра целевого сервера. Поставщик событий WMI создает уведомления о событиях с целевой службой, определенной в msdb.

Примечание

Дополнительные сведения см. в разделе Основные понятия о поставщике WMI для событий сервера.

Создание оповещения WMI в среде SQL Server Management Studio

Принцип работы уведомлений о событиях зеркальной базы данных

Межбазовая доставка уведомлений о событиях, в которой участвует зеркально отображенная база данных, по определению является удаленной, потому что зеркально отображенная база данных может выполнить переход на другой ресурс. Компонент Service Broker обеспечивает специальную поддержку зеркальных баз данных в виде зеркальных маршрутов. Маршрут зеркального отображения имеет два адреса: один для экземпляра основного сервера и другой — для экземпляра зеркального сервера.

Настроив зеркальные маршруты, вы узнаете, как маршрутизация Service Broker учитывает зеркальное отображение базы данных. Зеркальные маршруты позволяют Компоненту Service Broker прозрачно перенаправлять беседы на текущий экземпляр основного сервера. Например, рассмотрим службу Service_A, которая расположена на зеркальной базе данных Database_A. Предположим, что необходима другая служба Service_B, расположенная в базе данных Database_B, чтобы вести диалог со службой Service_A. Для этого диалога база данных Database_B должна содержать зеркально отображенный маршрут для службы Service_A. Кроме того, база данных Database_A должна содержать незеркальный маршрут протокола TCP к службе Service_B, который в отличие от локального остается допустимым после отработки отказа. Эти маршруты включают ACK для возврата после отработки отказа. Поскольку службу отправителя всегда называют тем же способом, маршрут должен указывать экземпляр брокера.

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

  • Если целевая служба находится в зеркально отображенной базе данных, вызывающая служба должна иметь обратный, зеркально отображенный маршрут к целевой службе. Однако целевая служба может иметь постоянный маршрут к вызывающей службе.

  • Если служба инициатора находится в зеркальной базе данных, целевая служба должна иметь зеркальный маршрут обратно в инициатор для доставки подтверждений и ответов. Однако вызывающая служба может иметь постоянный обратный маршрут к целевой службе.

Расширенные хранимые процедуры

Важно!

В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Вместо этого используйте интеграцию со средой CLR .

Расширенные хранимые процедуры программируются с помощью API расширенных хранимых процедур SQL Server. Член предопределенных ролей сервера sysadmin может зарегистрировать расширенную хранимую процедуру в экземпляре SQL Server и предоставить пользователям разрешение на выполнение процедуры. Расширенные хранимые процедуры можно добавлять только в master базу данных.

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

Важно!

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

Дополнительные сведения см. в разделе GRANT Object Permissions (Transact-SQL), DENY Object Permissions (Transact-SQL) и REVOKE Object Permissions (Transact-SQL).

Свойства средства полнотекстового поиска для SQL Server

Свойства средства полнотекстового поиска устанавливаются процедурой sp_fulltext_service. Убедитесь, что на целевом экземпляре сервера настроены необходимые для этих свойств параметры. Дополнительные сведения об этих свойствах см. в разделе FULLTEXTSERVICEPROPERTY (Transact-SQL).

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

Примечание

При присоединении базы данных SQL Server 2005 (9.x), содержащей файлы полнотекстового каталога, на экземпляр сервера SQL Server файлы каталога присоединяются из предыдущего расположения вместе с другими файлами базы данных, как в SQL Server 2005 (9.x). Дополнительные сведения см. в разделе Обновление полнотекстового поиска.

Дополнительные сведения см. также в следующих разделах:

Задания

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

  • Имя входа, используемое заданием

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

  • агент SQL Server учетной записи запуска службы

    Учетная запись запуска службы определяет учетную запись Microsoft Windows, с которой запускается агент SQL Server, а также его сетевые разрешения. Агент SQL Server выполняется как заданная учетная запись пользователя. Контекст службы агента влияет на настройки задания и его среды выполнения. Учетной записи должен быть предоставлен доступ к необходимым для задания сетевым и другим ресурсам. Сведения о выборе и изменении стартовой учетной записи службы см. в разделе Выбор учетной записи для службы агента SQL Server.

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

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

    Внимание!

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

  • прокси-серверы агент SQL Server

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

Дополнительные сведения см. также в следующих разделах:

Просмотр существующих заданий и их свойств

Создание задания

Рекомендуемые методы использования скриптов для повторного создания заданий

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

Имена входа

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

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

Разрешения

При открытии доступа к базе данных на другом экземпляре сервера могут быть применены следующие типы разрешений:

  • Разрешения GRANT, REVOKE или DENY на системные объекты.

  • Разрешения GRANT, REVOKE или DENY на экземпляр сервера (разрешения уровня сервера).

Разрешения GRANT, REVOKE или DENY на системные объекты

Разрешения на системные объекты, такие как хранимые процедуры, расширенные хранимые процедуры, функции и представления, хранятся в master базе данных и должны быть настроены на экземпляре целевого сервера.

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

Важно!

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

Системные объекты отображаются в представлении каталога sys.system_objects. Разрешения на системные объекты отображаются в представлении каталога sys.database_permissions в master базе данных. Сведения о запросе этих представлений каталога и предоставлении разрешений системного объекта см. в разделе GRANT System Object Permissions (Transact-SQL). Дополнительные сведения см. в разделе REVOKE System Object Permissions (Transact-SQL) и DENY System Object Permissions (Transact-SQL).

Разрешения GRANT, REVOKE или DENY на экземпляр сервера

Разрешения в области сервера хранятся в master базе данных и должны быть настроены на экземпляре целевого сервера. Чтобы получить сведения о разрешениях сервера в экземпляре сервера, запросите представление каталога sys.server_permissions . Сведения об участниках сервера можно получить из представления каталога sys.server_principals, а сведения о членстве ролей сервера содержатся в представлении каталога sys.server_role_members .

Дополнительные сведения см. в разделе GRANT Server Permissions (Transact-SQL),REVOKE Server Permissions (Transact-SQL) и DENY Server Permissions (Transact-SQL) (Transact-SQL).

Разрешения уровня сервера на сертификат или асимметричный ключ

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

Примечание

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

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

Создание сертификата или асимметричного ключа

Сопоставление сертификата или асимметричного ключа

Выдача разрешений сопоставленному имени входа

Дополнительные сведения о сертификатах и асимметричных ключах см. в разделе Encryption Hierarchy.

Свойство TRUSTWORTHY

Свойство TRUSTWORTHY базы данных используется для указания того, доверяет ли этот экземпляр SQL Server базе данных и ее содержимому. При подключении базы данных в целях безопасности это свойство по умолчанию устанавливается в значение OFF, даже если на исходном сервере оно имело значение ON. Дополнительные сведения об этом свойстве см. в описании свойства базы данных TRUSTWORTHY и сведения о включении этого параметра см. в разделе ALTER DATABASE (Transact-SQL).

Параметры репликации

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

Дополнительные сведения см. в статьях резервное копирование и восстановление реплицированных баз данных, зеркального отображения баз данных и репликации (SQL Server), а также доставка журналов и репликация (SQL Server).

Приложения компонента Service Broker

Многие аспекты приложения Компонента Service Broker перемещаются вместе с базой данных. Однако некоторые из них в новом местоположении необходимо создать или настроить повторно. По умолчанию в целях безопасности параметры is_broker_enabled и is_honor_broker_priority_on устанавливаются в значение OFF при подключении базы данных с другого сервера. Сведения о настройке этих параметров on см. в разделе ALTER DATABASE (Transact-SQL).

Стартовые процедуры

Процедура запуска — это хранимая процедура, помеченная для автоматического выполнения и выполняемая при каждом запуске SQL Server. Если база данных зависит от любых процедур запуска, они должны быть определены в экземпляре целевого сервера и настроены для автоматического выполнения при запуске.

Триггеры уровня сервера

DDL запускает хранимые процедуры в ответ на несколько событий языка определения данных (DDL). Эти события в основном соответствуют инструкциям Transact-SQL, которые начинаются с ключевых слов CREATE, ALTER и DROP. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.

Дополнительные сведения об этой возможности см. в разделе DDL Triggers.

См. также:

Автономные базы данных
Копирование баз данных на другие серверы
Присоединение и отсоединение базы данных (SQL Server)
Переход на вторичный сервер доставки журналов (SQL Server)
Переключение ролей во время сеанса зеркального отображения базы данных (SQL Server)
Настройка зашифрованной зеркальной базы данных
Диспетчер конфигурации SQL Server
Диагностика пользователей, утративших связь с учетной записью (SQL Server)