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

Область применения: SQL Server

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

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

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

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

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

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

• Обновление ядра СУБД с помощью метода — миграция на новую установку.

• Перенос баз данных в SQL Azure (виртуальная машина или Управляемый экземпляр).

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

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

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

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

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

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

• Запросы баз данных

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

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

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

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

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

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

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

• Работы

• Имена входа

• Разрешения

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

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

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

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

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

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

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

Подтверждение компетенции

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

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

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

Параметры базы данных 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 ='пароль'. После этого рекомендуется включить автоматическую расшифровку главного ключа базы данных, выполнив следующую инструкцию: 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 в среде SQL Server Management Studio

• Создание предупреждения о событии WMI

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

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

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

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

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

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

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

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

Дополнительные сведения см. в разделе 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)

Работы

Если база данных использует задания агент 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) (для зеркального отображения базы данных)

• Настройка учетных записей служб Windows и разрешений (при установке экземпляра SQL Server)

• Настройка агент SQL Server (при установке экземпляра SQL Server)

• Обеспечение безопасности агента SQL Server

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

• Наблюдение за активностью заданий

• sp_help_job (Transact-SQL)

• Просмотр сведений о шаге задания

• dbo.sysjobs (Transact-SQL)

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

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

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

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

Имена входа

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

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

Разрешения

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

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

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

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

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

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

Системные объекты отображаются в представлении каталога 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) (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).

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

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

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

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

• CREATE CERTIFICATE (Transact-SQL)

• CREATE ASYMMETRIC KEY (Transact-SQL)

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

• CREATE LOGIN (Transact-SQL)

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

• GRANT, предоставление разрешений на сервер (Transact-SQL)

Дополнительные сведения о сертификатах и асимметричных ключах см. в разделе 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).<a1/>

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

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

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

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

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

См. также

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