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

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

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

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

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

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

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

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

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

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

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

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

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

Дополнительные сведения см. в разделе "Настройка зеркальной базы данных для использования надежного свойства " (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 РАСШИФРОВКА С ПОМОЩЬЮ ПАРОЛЯ ='password'. Рекомендуется включить автоматическую расшифровку главного ключа базы данных, выполнив следующую инструкцию: ALTER MASTER KEY ADD ENCRYPTION BY SERVICESERVICE MASTER KEY. Эта ALTER 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 "Разрешения объектов" (Transact-SQL), DENY разрешения объектов (Transact-SQL), а также REVOKE разрешения объектов (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 "Разрешения системных объектов" (Transact-SQL). Дополнительные сведения см. в статьяхREVOKE "Разрешения системных объектов" (Transact-SQL) и DENY разрешения системных объектов (Transact-SQL).

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

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

Дополнительные сведения см. в статьяхGRANT "Разрешения сервера" (Transact-SQL), REVOKE разрешения сервера (Transact-SQL), а также DENY разрешения сервера (Transact-SQL).

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

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

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

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

• CREATE CERTIFICATE (Transact-SQL)

• CREATE ASYMMETRIC KEY (Transact-SQL)

Сопоставить сертификат или асимметричный ключ с именем входа

• CREATE LOGIN (Transact-SQL)

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

• GRANT Разрешения сервера (Transact-SQL)

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

Надёжное свойство

Свойство базы данных TRUSTY используется для указания того, доверяет ли этот экземпляр 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 при подключении базы данных с другого сервера. Сведения о том, как включить эти параметры, см. в разделе 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)
Переход к новой установкеОбзор миграции: с SQL Server на SQL Server на виртуальных машинах Azure