使数据库在其他服务器上可用时管理元数据Manage Metadata When Making a Database Available on Another Server

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本文与下列情况有关:This article is relevant in the following situations:

  • 配置 AlwaysOn 可用性组Always On availability groups 可用性组的可用性副本。Configuring the availability replicas of an AlwaysOn 可用性组Always On availability groups availability group.

  • 设置数据库镜像。Setting up database mirroring for a database.

  • 准备在日志传送配置中交换主服务器和辅助服务器的角色。When preparing to change roles between primary and secondary servers in a log shipping configuration.

  • 将数据库还原到其他服务器实例。Restoring a database to another server instance.

  • 在其他服务器实例上附加数据库副本。Attaching a copy of a database on another server instance.

某些应用程序依赖于单个用户数据库范围之外的信息、实体和/或对象。Some applications depend on information, entities, and/or objects that are outside of the scope of a single user database. 通常,应用程序具有对 mastermsdb 数据库的依赖关系,并且还具有对用户数据库的依赖关系。Typically, an application has dependencies on the master and msdb databases, and also on the user database. 用户数据库正确运行所需的存储在该数据库外部的任何内容必须在目标服务器实例上可用。Anything stored outside of a user database that is required for the correct functioning of that database must be made available on the destination server instance. 例如,应用程序的登录名作为元数据存储在 master 数据库中,必须在目标服务器上重新创建这些登录名。For example, the logins for an application are stored as metadata in the master database, and they must be re-created on the destination server. 如果应用程序或数据库维护计划依赖于 SQL ServerSQL Server 代理作业(其元数据存储在 msdb 数据库中),则必须在目标服务器实例上重新创建这些作业。If an application or database maintenance plan depends on SQL ServerSQL Server Agent jobs, whose metadata is stored in the msdb database, you must re-create those jobs on the destination server instance. 同样,服务器级触发器的元数据存储在 master中。Similarly, the metadata for a server-level trigger is stored in master.

将应用程序的数据库移动到其他服务器实例时,必须在目标服务器实例的 mastermsdb 中重新创建依赖实体和依赖对象的所有元数据。When you move the database for an application to another server instance, you must re-create all the metadata of the dependant entities and objects in master and msdb on the destination server instance. 例如,如果数据库应用程序使用服务器级触发器,则仅在新系统上附加或还原数据库是不够的。For example, if a database application uses server-level triggers, just attaching or restoring the database on the new system is not enough. 如果不手动在 master 数据库中重新创建这些触发器的元数据,则数据库不能按预期方式工作。The database will not work as expected unless you manually re-create the metadata for those triggers in the master database.

存储在用户数据库外部的信息、实体和对象Information, Entities, and Objects That Are Stored Outside of User Databases

本文的其余部分概要说明了可能影响在其他服务器实例上可用的数据库的潜在问题。The remainder of this article summarizes the potential issues that might affect a database that is being made available on another server instance. 最好重新创建以下列表中列出的一种或多种信息、实体或对象。You might have to re-create one or more of the types of information, entities, or objects listed in the following list. 若要查看概要内容,请单击该项的链接。To see a summary, click the link for the item.

Server Configuration SettingsServer Configuration Settings

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 及更高版本会选择性地安装和启动密钥服务和功能。and later versions selectively install and starts key services and features. 这有助于减少系统可遭受攻击的外围应用。This helps reduce the attackable surface area of a system. 在新安装的默认配置中,许多功能并未启用。In the default configuration of new installations, many features are not enabled. 如果数据库依赖于默认处于禁用状态的服务或功能,则必须在目标服务器实例上启用此服务或功能。If the database relies on any service or feature that is off by default, this service or feature must be enabled on the destination server instance.

有关这些设置以及启用或禁用它们的详细信息,请参阅服务器配置选项 (SQL Server)For more information about these settings and enabling or disabling them, see Server Configuration Options (SQL Server).


凭据是包含连接到 SQL ServerSQL Server以外的资源时所需的身份验证信息的记录。A credential is a record that contains the authentication information that is required to connect to a resource outside SQL ServerSQL Server. 大多数凭据包含一个 Windows 登录名和密码。Most credentials consist of a Windows login and password.

有关此功能的详细信息,请参阅 凭据(数据库引擎)For more information about this feature, see Credentials (Database Engine).

注意SQL ServerSQL Server 代理的代理帐户使用凭据。NOTE: SQL ServerSQL Server Agent Proxy accounts use credentials. 若要了解代理帐户的凭据 ID,请使用 sysproxies 系统表。To learn the credential ID of a proxy account, use the sysproxies system table.

Cross-Database QueriesCross-Database Queries

DB_CHAINING 和 TRUSTWORTHY 数据库选项默认设置为 OFF。The DB_CHAINING and TRUSTWORTHY database options are OFF by default. 如果针对原始数据库将这两个选项之一设置为 ON,则可能必须对目标服务器实例上的数据库启用这两个选项。If either of these are set to ON for the original database, you may have to enable them on the database on the destination server instance. 有关详细信息,请参阅 ALTER DATABASE (Transact-SQL)For more information, see ALTER DATABASE (Transact-SQL).

附加和分离操作都会禁用数据库的跨数据库所有权链接。Attach-and-detach operations disable cross-database ownership chaining for the database. 有关如何启用链接的详细信息,请参阅 cross db ownership chaining 服务器配置选项For information about how to enable chaining, see cross db ownership chaining Server Configuration Option.

有关详细信息,另请参阅设置镜像数据库以使用可信属性 (Transact-SQL)For more information, see also Set Up a Mirror Database to Use the Trustworthy Property (Transact-SQL)

Database OwnershipDatabase Ownership

在其他计算机上还原数据库时,启动还原操作的 SQL ServerSQL Server 登录用户或 Windows 用户将自动成为新数据库的所有者。When a database is restored on another computer, the SQL ServerSQL Server login or Windows user who initiated the restore operation becomes the owner of the new database automatically. 还原数据库时,系统管理员或新数据库所有者可以更改数据库所有权。When the database is restored, the system administrator or the new database owner can change database ownership.

分布式查询和链接服务器Distributed Queries and Linked Servers

OLE DB 应用程序支持分布式查询和链接服务器。Distributed queries and linked servers are supported for OLE DB applications. 分布式查询访问相同或不同计算机上多个异类数据源中的数据。Distributed queries access data from multiple heterogeneous data sources on either the same or different computers. 链接服务器配置使 SQL ServerSQL Server 可以对远程服务器上的 OLE DB 数据源执行命令。A linked server configuration enables SQL ServerSQL Server to execute commands against OLE DB data sources on remote servers. 有关这些功能的详细信息,请参阅链接服务器(数据库引擎)For more information about these features, see Linked Servers (Database Engine).

Encrypted DataEncrypted Data

如果在其他服务器实例上可用的数据库包含加密数据,并且数据库主密钥由原始服务器上的服务主密钥保护,则最好重新进行服务主密钥加密。If the database you are making available on another server instance contains encrypted data and if the database master key is protected by the service master key on the original server, it might be necessary to re-create the service master key encryption. “数据库主密钥 ”是一种对称密钥,用于在加密数据库中保护证书的私钥和非对称密钥的私钥。The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys in an encrypted database. 当创建数据库主密钥时,会使用 Triple DES 算法以及用户提供的密码对其进行加密。When created, the database master key is encrypted by using the Triple DES algorithm and a user-supplied password.

若要对服务器实例上的数据库主密钥启用自动解密,请使用服务主密钥对此密钥的副本进行加密。To enable the automatic decryption of the database master key on a server instance, a copy of this key is encrypted by using the service master key. 此加密副本存储在此数据库以及 master中。This encrypted copy is stored in both the database and in master. 通常,每当主密钥更改时,便会在不进行提示的情况下更新存储在 master 中的副本。Typically, the copy stored in master is silently updated whenever the master key is changed. SQL ServerSQL Server 最初尝试使用实例的服务主密钥解密数据库主密钥。first tries to decrypt the database master key with the service master key of the instance. 如果解密失败,则 SQL ServerSQL Server 将在凭据存储区中搜索与需要其主密钥的数据库具有相同系列 GUID 的主密钥凭据。If that decryption fails, SQL ServerSQL Server searches the credential store for master key credentials that have the same family GUID as the database for which it requires the master key. SQL ServerSQL Server 尝试使用每个匹配的凭据对数据库主密钥进行解密,直到成功解密或者没有更多的凭据为止。then tries to decrypt the database master key with each matching credential until the decryption succeeds or there are no more credentials. 必须使用 OPEN MASTER KEY 语句和密码打开未使用服务主密钥进行加密的主密钥。A master key that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.

对加密数据库执行复制、还原或附加到新的 SQL ServerSQL Server实例等操作时,由服务主密钥加密的数据库主密钥的副本不存储在目标服务器实例上的 master 中。When an encrypted database is copied, restored, or attached to a new instance of SQL ServerSQL Server, a copy of the database master key encrypted by the service master key is not stored in master on the destination server instance. 在目标服务器实例上,必须打开数据库的主密钥。On the destination server instance, you must open the master key of the database. 若要打开主密钥,请执行以下语句:OPEN MASTER KEY DECRYPTION BY PASSWORD =' password ' .To open the master key, execute the following statement: OPEN MASTER KEY DECRYPTION BY PASSWORD ='password'. 建议通过执行下面的语句对数据库主密钥启用自动解密:ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY。We recommend that you then enable automatic decryption of the database master key by executing the following statement: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY. 此 ALTER MASTER KEY 语句使用数据库主密钥(使用服务主密钥加密)的副本来设置服务器实例。This ALTER MASTER KEY statement provisions the server instance with a copy of the database master key that is encrypted with the service master key. 有关详细信息,请参阅 OPEN MASTER KEY (Transact-SQL)ALTER MASTER KEY (Transact-SQL)For more information, see OPEN MASTER KEY (Transact-SQL) and ALTER MASTER KEY (Transact-SQL).

有关如何启用镜像数据库主秘钥自动加密的详细信息,请参阅设置加密的镜像数据库For information about how to enable automatic decryption of the database master key of a mirror database, see Set Up an Encrypted Mirror Database.

有关详细信息,请参阅:For more information, see also:

User-defined Error MessagesUser-defined Error Messages

用户定义的错误消息位于 sys.messages 目录视图中。User-defined error messages reside in the sys.messages catalog view. 此目录视图存储在 master中。This catalog view is stored in master. 如果数据库应用程序依赖于用户定义的错误消息并且此数据库在其他服务器实例上可用,则请使用 sp_addmessage 在目标服务器实例上添加这些用户定义的消息。If a database application depends on user-defined error messages and the database is made available on another server instance, use sp_addmessage to add those user-defined messages on the destination server instance.

事件通知和 Windows Management Instrumentation (WMI) 事件 (在服务器级别)Event Notifications and Windows Management Instrumentation (WMI) Events (at Server Level)

服务器级事件通知Server-Level Event Notifications

服务器级事件通知存储在 msdb中。Server-level event notifications are stored in msdb. 因此,如果数据库应用程序依赖于服务器级事件通知,则必须在目标服务器实例上重新创建该事件通知。Therefore, if a database application relies on a server-level event notification, that event notification must be re-created on the destination server instance. 若要查看服务器实例上的事件通知,请使用 sys.server_event_notifications 目录视图。To view the event notifications on a server instance, use the sys.server_event_notifications catalog view. 有关详细信息,请参阅 Event NotificationsFor more information, see Event Notifications.

此外,使用 Service BrokerService Broker传递事件通知。Additionally, event notifications are delivered by using Service BrokerService Broker. 传入消息的路由不包括在包含服务的数据库中。Routes for incoming messages are not included in the database that contains a service. 相反,显式路由存储在 msdb中。Instead, explicit routes are stored in msdb. 如果服务使用 msdb 数据库中的显式路由将传入的消息路由到该服务,则在将数据库附加到其他实例时,必须重新创建此路由。If your service uses an explicit route in the msdb database to route incoming messages to the service, when you attach a database in a different instance, you must re-create this route.

Windows Management Instrumentation (WMI) 事件Windows Management Instrumentation (WMI) Events

使用服务器事件的 WMI 提供程序,可以使用 Windows Management Instrumentation (WMI) 监视 SQL ServerSQL Server中的事件。The WMI Provider for Server Events lets you use the Windows Management Instrumentation (WMI) to monitor events in SQL ServerSQL Server. 必须在目标服务器实例所在的计算机上定义任何依赖于服务器级事件(此事件通过数据库所依赖的 WMI 提供程序显示)的应用程序。Any application that relies on server-level events exposed through the WMI provider on which a database relies must be defined the computer of the destination server instance. WMI 事件提供程序使用在 msdb中定义的目标服务创建事件通知。WMI Event provider creates event notifications with a target service that is defined in msdb.

注意:有关详细信息,请参阅 WMI Provider for Server Events 的概念NOTE: For more information, see WMI Provider for Server Events Concepts.

使用 SQL Server Management Studio 创建 WMI 警报To create a WMI alert using SQL Server Management Studio

镜像数据库事件通知工作原理How Event Notifications Work for a Mirrored Database

因为镜像数据库可以进行故障转移,所以涉及镜像数据库的事件通知的跨数据库传递是按照定义以远程方式进行的。Cross-database delivery of event notifications that involves a mirrored database is remote, by definition, because the mirrored database can fail over. Service BrokerService Broker 镜像路由的形式为镜像数据库提供特殊支持。provides special support for mirrored databases, in the form of mirrored routes. 镜像路由有两个地址:一个针对主体服务器实例,另一个针对镜像服务器实例。A mirrored route has two addresses: one for the principal server instance and one for the mirror server instance.

通过设置镜像路由,您可以使 Service BrokerService Broker 路由支持数据库镜像。By setting up mirrored routes, you make Service BrokerService Broker routing aware of database mirroring. 使用镜像路由, Service BrokerService Broker 能够透明地将会话重定向到当前的主体服务器实例。The mirrored routes enable Service BrokerService Broker to transparently redirect conversations to the current principal server instance. 例如,有一项由镜像数据库 Database_A 承载的服务 Service_A。For example, consider a service, Service_A, which is hosted by a mirrored database, Database_A. 假定您需要由 Database_B 承载的另一项服务 Service_B 与 Service_A 进行对话。Assume that you need another service, Service_B, which is hosted by Database_B, to have a dialog with Service_A. 为了实现此对话,Database_B 必须包含 Service_A 的镜像路由。For this dialog to be possible, Database_B must contain a mirrored route for Service_A. 此外,Database_A 必须包含 Service_B 的非镜像 TCP 传输路由,与本地路由不同的是,该路由在故障转移后保持有效。In addition, Database_A must contain a nonmirrored TCP transport route to Service_B, which, unlike a local route, remains valid after failover. 这些路由使 ACK 能够在故障转移后恢复。These routes enable ACKs to come back after a failover. 由于发送方的服务始终以相同方式命名,因此路由必须指定 Broker 实例。Because the service of the sender is always named in the same manner, the route must specify the broker instance.

不管镜像数据库中的服务是发起方服务还是目标服务,下列情况均要求使用镜像路由:The requirement for mirrored routes applies for regardless of whether the service in the mirrored database is the initiator service or the target service:

  • 如果目标服务位于镜像数据库中,则发起方服务必须具有返回目标的镜像路由。If target service is in the mirrored database, the initiator service must have a mirrored route back to the target. 但是,目标可以具有返回发起方的常规路由。However, the target can have a regular route back to initiator.

  • 如果发起方服务位于镜像数据库中,则目标服务必须具有返回发起方的镜像路由,以传递确认和应答。If initiator service is in the mirrored database, the target service must have a mirrored route back to initiator to deliver acknowledgements and replies. 但是,发起方可能拥有指向目标的常规路由。However, the initiator can have a regular route to the target.

Extended Stored ProceduresExtended Stored Procedures

重要说明!IMPORTANT! 此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 请改用 CLR 集成Use CLR Integration instead.

扩展存储过程使用 SQL ServerSQL Server 扩展存储过程 API 进行编程。Extended stored procedures are programmed by using the SQL ServerSQL Server Extended Stored Procedure API. sysadmin 固定服务器角色的成员可以使用 SQL ServerSQL Server 实例注册扩展存储过程,并授予用户执行此过程的权限。A member of the sysadmin fixed server role can register an extended stored procedure with an instance of SQL ServerSQL Server and grant permission to users to execute the procedure. 扩展存储过程只能添加到 master 数据库。Extended stored procedures can be added only to the master database.

扩展存储过程直接在 SQL ServerSQL Server实例的地址空间中运行,它们可能会引起内存泄漏或其他问题,从而降低服务器的性能和可靠性。Extended stored procedures run directly in the address space of an instance of SQL ServerSQL Server, and they may produce memory leaks or other problems that reduce the performance and reliability of the server. 您应考虑将扩展存储过程存储在独立于包含被引用数据的实例的 SQL ServerSQL Server 实例中。You should consider storing extended stored procedures in an instance of SQL ServerSQL Server that is separate from the instance that contains the referenced data. 还应考虑使用分布式查询访问数据库。You should also consider using distributed queries to access the database.


将扩展存储过程添加到服务器并向其他用户授予 EXECUTE 权限之前,系统管理员应全面查看每个扩展存储过程,以确保它不包含有害代码或恶意代码。Before adding extended stored procedures to the server and granting EXECUTE permissions to other users, the system administrator should thoroughly review each extended stored procedure to make sure that it does not contain harmful or malicious code.

有关更多详细信息,请参阅 GRANT 对象权限 (Transact-SQL)DENY 对象权限 (Transact-SQL)REVOKE 对象权限 (Transact-SQL)For more information, see GRANT Object Permissions (Transact-SQL), DENY Object Permissions (Transact-SQL), and REVOKE Object Permissions (Transact-SQL).

Full-Text Engine for SQL Server PropertiesFull-Text Engine for SQL Server Properties

全文引擎的属性是通过 sp_fulltext_service设置的。Properties are set on the Full-Text Engine by sp_fulltext_service. 请确保目标服务器实例具有这些属性的必需设置。Make sure that the destination server instance has the required settings for these properties. 有关这些属性的详细信息,请参阅 FULLTEXTSERVICEPROPERTY (Transact SQL)For more information about these properties, see FULLTEXTSERVICEPROPERTY (Transact-SQL).

此外,如果原始服务器实例和目标服务器示例具有不同版本的 断字符和词干分析器组件或全文搜索筛选器组件,则全文索引和查询的行为可能有所不同。Additionally, if the word breakers and stemmers component or full-text search filters component have different versions on the original and destination server instances, full-text index and queries may behave differently. 此外, 同义词库 存储在特定于实例的文件中。Also, the thesaurus is stored in instance-specific files. 您必须将这些文件的副本传输到目标服务器实例上的相同位置,或者在新的实例上重新创建这些文件。You must either transfer a copy of those files to an equivalent location on the destination server instance or re-create them on new instance.

注意:当将包含全文目录文件的 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 数据库附加到 SQL Server 2017SQL Server 2017 服务器实例上时,会将目录文件从其以前的位置与其他数据库文件一起附加,这与 SQL Server 2005 (9.x)SQL Server 2005 (9.x)中的情况相同。NOTE: When you attach a SQL Server 2005 (9.x)SQL Server 2005 (9.x) database that contains full-text catalog files onto a SQL Server 2017SQL Server 2017 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005 (9.x)SQL Server 2005 (9.x). 有关详细信息,请参阅 全文搜索升级For more information, see Upgrade Full-Text Search.

有关详细信息,请参阅:For more information, see also:


如果数据库依赖于 SQL ServerSQL Server 代理作业,则必须在目标服务器实例上重新创建这些作业。If the database relies on SQL ServerSQL Server Agent jobs, you will have to re-create them on the destination server instance. 作业取决于其环境。Jobs depend on their environments. 如果计划在目标服务器实例上重新创建现有作业,则可能必须修改目标服务器实例,以便与原始服务器实例上此作业的环境相匹配。If you plan to re-create an existing job on the destination server instance, the destination server instance might have to be modified to match the environment of that job on the original server instance. 下面是重要的环境因素:The following environmental factors are significant:

  • 作业使用的登录名The login used by the job

    若要创建或执行 SQL ServerSQL Server 代理作业,首先必须将作业所需的所有 SQL ServerSQL Server 登录名添加到目标服务器实例。To create or execute SQL ServerSQL Server Agent jobs, you must first add any SQL ServerSQL Server logins required by the job to the destination server instance. 有关详细信息,请参阅 配置帐户以创建和管理 SQL Server 代理作业For more information, see Configure a User to Create and Manage SQL Server Agent Jobs.

  • SQL ServerSQL Server 代理服务启动帐户Agent service startup account

    服务启动帐户可以定义运行 MicrosoftMicrosoft 代理的 SQL ServerSQL Server Windows 帐户及其网络权限。The service startup account defines the MicrosoftMicrosoft Windows account in which SQL ServerSQL Server Agent runs and its network permissions. SQL ServerSQL Server 代理在指定的用户帐户下运行。Agent runs as a specified user account. 代理服务的上下文会影响作业及其运行环境的设置。The context of the Agent service affects the settings for the job and its run environment. 帐户必须有权访问作业所需的资源(如网络共享)。The account must have access to the resources, such as network shares, required by the job. 有关如何选择和修改服务启动帐户的信息,请参阅 选择 SQL Server 代理服务帐户For information about how to select and modify the service startup account, see Select an Account for the SQL Server Agent Service.

    为了正常操作,必须对服务启动帐户进行配置,使其具有正确的域、文件系统和注册表权限。To operate correctly, the service startup account must be configured to have the correct domain, file system, and registry permissions. 此外,作业可能还需要必须针对服务帐户配置的共享网络资源。Also, a job might require a shared network resource that must be configured for the service account. 有关详细信息,请参阅 配置 Windows 服务帐户和权限For information, see Configure Windows Service Accounts and Permissions.

  • SQL ServerSQL Server 代理服务与特定的 SQL ServerSQL Server实例关联,具有自己的注册表配置单元,并且其作业通常与此注册表配置单元中的一个或多个设置具有依赖关系。Agent service, which is associated with a specific instance of SQL ServerSQL Server, has its own registry hive, and its jobs typically have dependencies on one or more of the settings in this registry hive. 若要按预期方式运行,作业需要这些注册表设置。To behave as intended, a job requires those registry settings. 如果使用脚本在其他 SQL ServerSQL Server 代理服务中重新创建一个作业,则此服务的注册表中可能没有用于该作业的正确设置。If you use a script to re-create a job in another SQL ServerSQL Server Agent service, its registry might not have the correct settings for that job. 为使重新创建的作业在目标服务器实例上正常运行,原始和目标 SQL ServerSQL Server 代理服务应具有相同的注册表设置。For re-created jobs to behave correctly on a destination server instance, the original and destination SQL ServerSQL Server Agent services should have the same registry settings.


    如果其他作业需要当前设置,则通过更改目标 SQL ServerSQL Server 代理服务上的注册表设置来处理重新创建的作业可能会出现问题。Changing registry settings on the destination SQL ServerSQL Server Agent service to handle a re-created job could be problematic if the current settings are required by other jobs. 此外,错误编辑注册表可能会严重损坏您的系统。Furthermore, incorrectly editing the registry can severely damage your system. 更改注册表项之前,建议您备份计算机中的所有重要数据。Before you make changes to the registry, we recommend that you back up any valued data on the computer.

  • SQL ServerSQL Server 代理的代理帐户Agent Proxies

    SQL ServerSQL Server 代理的代理帐户定义指定作业步骤的安全上下文。A SQL ServerSQL Server Agent proxy defines the security context for a specified job step. 对于要在目标服务器实例上运行的作业,必须在此实例上手动重新创建此作业所需的所有代理。For a job to run on the destination server instance, all the proxies it requires must be manually re-created on that instance. 有关详细信息,请参阅 创建 SQL Server 代理程序代理对使用代理的多服务器作业进行故障排除For more information, see Create a SQL Server Agent Proxy and Troubleshoot Multiserver Jobs That Use Proxies.

有关详细信息,请参阅:For more information, see also:

查看现有作业及其属性To view existing jobs and their properties

创建作业To create a job

使用脚本重新创建作业的最佳实践Best Practices for Using a Script to Re-create a Job

建议您首先编写简单作业的脚本,接下来在其他 SQL ServerSQL Server 代理服务上重新创建此作业,然后运行此作业以查看它是否按预期方式工作。We recommend that you start by scripting a simple job, re-creating the job on the other SQL ServerSQL Server Agent service, and running the job to see whether it works as intended. 这样便可确定不兼容性并尝试进行解决。This will let you identify incompatibilities and try to resolve them. 如果已编写脚本的作业在新环境中未按预期方式工作,则建议您创建可在此环境中正常工作的等价作业。If a scripted job does not work as intended in its new environment, we recommend that you create an equivalent job that works correctly in that environment.


登录到 SQL ServerSQL Server 实例需要有效的 SQL ServerSQL Server 登录名。Logging into an instance of SQL ServerSQL Server requires a valid SQL ServerSQL Server login. 在身份验证过程中会使用此登录名,以验证主体是否可以连接到 SQL ServerSQL Server实例。This login is used in the authentication process that verifies whether the principal can connect to the instance of SQL ServerSQL Server. 在服务器实例上未定义或错误定义了其相应 SQL ServerSQL Server 登录名的数据库用户无法登录到实例。A database user for which the corresponding SQL ServerSQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. 这样的用户被称为此服务器实例上的数据库的“孤立用户” 。Such a user is said to be an orphaned user of the database on that server instance. 当数据库还原、附加或复制到 SQL ServerSQL Server的其他实例之后,数据库用户便可变为孤立用户。A database user can become orphaned if after a database is restored, attached, or copied to a different instance of SQL ServerSQL Server.

若要为数据库原始副本中的部分或全部对象生成脚本,可以使用生成脚本向导,并在 “选择脚本选项” 对话框中将 “编写登录脚本” 选项设置为 TrueTo generate a script for some or all the objects in the original copy of the database, you can use the Generate Scripts Wizard, and in the Choose Script Options dialog box, set the Script Logins option to True.

注意:有关如何为镜像数据库设置登录名的信息,请参阅设置数据库镜像或 AlwaysOn 可用性组的登录帐户 (SQL Server)角色切换后登录名和作业的管理 (SQL Server)NOTE: For information about how to set up logins for a mirrored database, see Set Up Login Accounts for Database Mirroring or Always On Availability Groups (SQL Server) and Management of Logins and Jobs After Role Switching (SQL Server).


当数据库在其他服务器实例上可用时,下列类型的权限可能受到影响。The following types of permissions might be affected when a database is made available on another server instance.

  • 对系统对象的 GRANT、REVOKE 或 DENY 权限GRANT, REVOKE, or DENY permissions on system objects

  • 对服务器实例的 GRANT、REVOKE 或 DENY 权限(服务器级权限GRANT, REVOKE, or DENY permissions on server instance (server-level permissions)

对系统对象的 GRANT、REVOKE 和 DENY 权限GRANT, REVOKE, and DENY Permissions on System Objects

对系统对象(例如存储过程、扩展存储过程、函数和视图)的权限存储在 master 数据库中,并且必须在目标服务器实例上进行配置。Permissions on system objects such as stored procedures, extended stored procedures, functions, and views, are stored in the master database and must be configured on the destination server instance.

若要为数据库原始副本中的部分或全部对象生成脚本,可以使用生成脚本向导,并在 “选择脚本选项” 对话框中将 “编写对象级权限脚本” 选项设置为 TrueTo generate a script for some or all the objects in the original copy of the database, you can use the Generate Scripts Wizard, and in the Choose Script Options dialog box, set the Script Object-Level Permissions option to True.


如果编写登录脚本,则不编写密码的脚本。If you script logins, the passwords are not scripted. 如果登录名使用 SQL ServerSQL Server 身份验证,则必须在目标上修改脚本。If you have logins that use SQL ServerSQL Server Authentication, you have to modify the script on the destination.

sys.system_objects 目录视图中可以查看系统对象。System objects are visible in the sys.system_objects catalog view. master 数据库中的 sys.database_permissions 目录视图中可以查看对系统对象的权限。The permissions on system objects are visible in the sys.database_permissions catalog view in the master database. 有关查询这些目录视图并授予系统对象权限的信息,请参阅 GRANT 系统对象权限 (Transact-SQL)For information about querying these catalog views and granting system-object permissions, see GRANT System Object Permissions (Transact-SQL). 有关更多详细信息,请参阅 REVOKE 系统对象权限 (Transact-SQL)DENY 系统权限 (Transact-SQL)For more information, see REVOKE System Object Permissions (Transact-SQL) and DENY System Object Permissions (Transact-SQL).

对服务器实例的 GRANT、REVOKE 和 DENY 权限GRANT, REVOKE, and DENY Permissions on a Server Instance

服务器范围的权限存储在 master 数据库中,并且必须在目标服务器实例上进行配置。Permissions at the server scope are stored in the master database and must be configured on the destination server instance. 有关服务器实例的服务器权限的信息,请查询 sys.server_permissions目录视图;有关服务器主体的信息,请查询 sys.server_principals 目录视图;有关服务器角色成员身份的信息,请查询 sys.server_role_members 目录视图。For information about the server permissions of a server instance, query the sys.server_permissions catalog view, for information about server principals query the sys.server_principalss catalog view, and for information about membership of server roles query the sys.server_role_members catalog view.

有关更多详细信息,请参阅 GRANT 服务器权限 (Transact-SQL)REVOKE 服务器权限 (Transact-SQL)DENY 服务器权限 (Transact-SQL)For more information, see GRANT Server Permissions (Transact-SQL), REVOKE Server Permissions (Transact-SQL), and DENY Server Permissions (Transact-SQL).

证书或非对称密钥的服务器级权限Server-Level Permissions for a Certificate or Asymmetric Key

不能向证书或非对称密钥直接授予服务器级权限。Server-level permissions cannot be granted directly to a certificate or asymmetric key. 相反,可以向专门针对特定证书或非对称密钥创建的映射登录名授予服务器级权限。Instead, server-level permissions are granted to a mapped login that is created exclusively for a specific certificate or asymmetric key. 因此,每个需要服务器级权限的证书或非对称密钥都需要自己的“证书映射登录名 ”或“非对称密钥映射登录名 ”。Therefore, each certificate or asymmetric key that requires server-level permissions, requires its own certificate-mapped login or asymmetric key-mapped login. 若要为证书或非对称密钥授予服务器级权限,请向其映射登录名授予相应权限。To grant server-level permissions for a certificate or asymmetric key, grant the permissions to its mapped login.

注意:映射登录名仅用于对使用相应证书或非对称密钥签名的代码进行授权。NOTE: A mapped login is used only for authorization of code signed with the corresponding certificate or asymmetric key. 映射登录名不能用于身份验证。Mapped logins cannot be used for authentication.

映射登录名及其权限都位于 master中。The mapped login and its permissions both reside in master. 如果证书或非对称密钥位于 master之外的数据库中,则必须在 master 中重新创建证书或非对称密钥并将其映射到登录名。If a certificate or asymmetric key resides in a database other than master, you must re-create it in master and map it to a login. 如果将数据库移动、复制或还原到其他服务器实例,则必须在目标服务器实例的 master 数据库中重新创建其证书或非对称密钥,将证书或非对称密钥映射到登录名,并向此登录名授予必需的服务器级权限。If you move, copy, or restore the database to another server instance, you must re-create its certificate or asymmetric key in the master database of the destination server instance, map to a login, and grant the required server-level permissions to the login.

创建证书或非对称密钥To create a certificate or asymmetric key

将证书或非对称密钥映射到登录名To map a certificate or asymmetric key to a login

为映射登录名分配权限To assign permissions to the mapped login

有关证书和非对称密钥的详细信息,请参阅 Encryption HierarchyFor more information about certificates and asymmetric keys, see Encryption Hierarchy.

Trustworthy 属性Trustworthy Property

TRUSTWORTHY 数据库属性用于指明此 SQL Server 实例是否信任该数据库以及其中的内容。The TRUSTWORHTY database property is used to indicate whether this instance of SQL Server trusts the database and the contents within it. 为安全起见,默认情况下,附加数据库后此选项需设置为 OFF,即使源服务器上此选项被设置为 ON。When a database is attached, by default and for security, this option is set to OFF, even if this option was set to ON on the original server. 有关此属性的详细信息,请参阅 TRUSTWORTHY 数据库属性,有关如何启用此选项的信息,请参阅 ALTER DATABASE (Transact-SQL)For more information about this property, see TRUSTWORTHY database property and for information on turning this option ON, see ALTER DATABASE (Transact-SQL).

Replication SettingsReplication Settings

如果将复制数据库的备份还原到其他服务器或数据库,则无法保留复制设置。If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. 在这种情况下,您必须在还原备份后重新创建所有发布和订阅。In this case, you must re-create all publications and subscriptions after backups are restored. 为使此过程更加简单,请创建用于当前复制设置以及启用和禁用复制的脚本。To make this process easier, create scripts for your current replication settings and, also, for the enabling and disabling of replication. 为了帮助重新创建复制设置,请复制这些脚本,并更改服务器名称引用以用于目标服务器实例。To help re-create your replication settings, copy these scripts and change the server name references to work for the destination server instance.

有关详细信息,请参阅备份和还原复制的数据库数据库镜像和复制 (SQL Server) 以及日志传送和复制 (SQL Server)For more information, see Back Up and Restore Replicated Databases, Database Mirroring and Replication (SQL Server), and Log Shipping and Replication (SQL Server).

Service Broker ApplicationsService Broker Applications

Service BrokerService Broker 应用程序的许多相关内容都将随数据库一起移动。Many aspects of a Service BrokerService Broker application move with the database. 但是,应用程序的某些相关内容必须在新位置重新创建或重新配置。However, some aspects of the application must be re-created or reconfigured in the new location. 为安全起见,默认情况下,从其他服务器附加数据库后,is_broker_enabled 和 is_honoor_broker_priority_on 的选项设置为 OFF 。By default and for security, when a database is attached from another server, the options for is_broker_enabled and is_honoor_broker_priority_on are set to OFF. 有关如何将这些选项设置为 ON 的详细信息,请参阅 ALTER DATABASE (Transact-SQL)For information about how to set these options ON, see ALTER DATABASE (Transact-SQL).

Startup ProceduresStartup Procedures

启动过程是指标记为自动执行并在每次启动 SQL ServerSQL Server 时执行的存储过程。A startup procedure is a stored procedure that is marked for automatic execution and is executed every time SQL ServerSQL Server starts. 如果数据库依赖于启动过程,则必须在目标服务器实例上定义这些启动过程并将其配置为启动时自动执行。If the database depends on any startup procedures, they must be defined on the destination server instance and be configured to be automatically executed at startup.

Triggers (at Server Level)Triggers (at Server Level)

DDL 触发器激发存储过程以响应各种数据定义语言 (DDL) 事件。DDL triggers fire stored procedures in response to a variety of Data Definition Language (DDL) events. 这些事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQLTransact-SQL 语句对应。These events primarily correspond to Transact-SQLTransact-SQL statements that start with the keywords CREATE, ALTER, and DROP. 执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

有关此功能的详细信息,请参阅 DDL TriggersFor more information about this feature, see DDL Triggers.

另请参阅See Also

包含的数据库 Contained Databases
将数据库复制到其他服务器 Copy Databases to Other Servers
数据库分离和附加 (SQL Server) Database Detach and Attach (SQL Server)
故障转移到日志传送辅助服务器 (SQL Server) Fail Over to a Log Shipping Secondary (SQL Server)
数据库镜像会话期间的角色切换 (SQL Server) Role Switching During a Database Mirroring Session (SQL Server)
设置加密的镜像数据库 Set Up an Encrypted Mirror Database
SQL Server 配置管理器 SQL Server Configuration Manager
孤立用户故障排除 (SQL Server)Troubleshoot Orphaned Users (SQL Server)