• Article

Managing Metadata When Making a Database Available on Another Server Instance

This topic is relevant for using Microsoft SQL Server 2005 and later versions in the following situations:

  • 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. 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. 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. If an application or database maintenance plan depends on SQL Server Agent jobs, whose metadata is stored in the msdb database, you must re-create those jobs on the destination server instance. Similarly, the metadata for a server-level trigger is stored in master.

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. 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 topic 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 settings

  • Credentials

  • Cross-database queries

  • Database ownership

  • Distributed queries/linked servers

  • Encrypted data

  • User-defined error messages

  • Event notifications and Windows Management Instrumentation (WMI) events (at server level)

  • Extended stored procedures

  • Full-text engine for SQL Server properties

  • Jobs

  • Logins

  • Permissions

  • Replication settings

  • Service Broker applications

  • Startup procedures

  • Triggers (at server level)

Server Configuration Settings

SQL Server 2005 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.

For more information about these settings and enabling or disabling them, see Understanding Surface Area Configuration and Setting Server Configuration Options.

[Top]

Credentials

A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials consist of a Windows login and password.

For more information about this feature, see Credentials (Database Engine).

Note

SQL Server Agent Proxy accounts use credentials. To learn the credential ID of a proxy account, use the sysproxies system table.

[Top]

Cross-Database Queries

The DB_CHAINING and TRUSTWORTHY database options are OFF by default. If either of these is set to ON for the original database, you may have to enable them on the database on the destination server instance. For more information, see ALTER DATABASE (Transact-SQL).

In SQL Server 2000 Service Pack 3 (SP3) and later versions of SQL Server, attach-and-detach operations disable cross-database ownership chaining for the database. For information about how to enable chaining, see cross db ownership chaining Option.

For more information, see also:

[Top]

Database Ownership

When a database is restored on another computer, the SQL Server login or Windows user who initiates 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

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. A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. For more information about these features, see Distributed Queries, Linking Servers, and Obtaining Metadata from Linked Servers.

[Top]

Encrypted 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. 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. This encrypted copy is stored in both the database and in master. Typically, the copy stored in master is silently updated whenever the master key is changed. SQL Server first tries to decrypt the database master key with the service master key of the instance. If that decryption fails, SQL 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 Server then tries to decrypt the database master key with each matching credential until the decryption succeeds or there are no more credentials. 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.

When an encrypted database is copied, restored, or attached to a new instance of SQL 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. To open the master key, execute the following statement: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'. 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. 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. 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 Setting Up an Encrypted Mirror Database.

For more information, see also:

[Top]

User-defined Error Messages

User-defined error messages reside in the sys.messages catalog view. This catalog view is stored in master. 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.

[Top]

Event Notifications and Windows Management Instrumentation (WMI) Events (at Server Level)

Server-Level Event Notifications

Server-level event notifications are stored in msdb. Therefore, if a database application relies on a server-level event notifications, that event notification must be re-created on the destination server instance. To view the event notifications on a server instance, use the sys.server_event_notifications catalog view. For more information, see Event Notifications (Database Engine).

Additionally, event notifications are delivered by using Service Broker. Routes for incoming messages are not included in the database that contains a service. Instead, explicit routes are stored in 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. For more information, see Service Broker Routing.

To set up a database for remote message delivery

Windows Management Instrumentation (WMI) Events

The WMI Provider for Server Events lets you use the Windows Management Instrumentation (WMI) to monitor events in SQL Server. 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 Event provider creates event notifications with a target service that is defined in msdb.

Note

For more information, see WMI Provider for Server Events Concepts.

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 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.

By setting up mirrored routes, you make Service Broker routing aware of database mirroring. The mirrored routes enable Service Broker to transparently redirect conversations to the current principal server instance. For example, consider a service, Service_A, which is hosted by a mirrored database, Database_A. Assume that you need another service, Service_B, which is hosted by Database_B, to have a dialog with Service_A. For this dialog to be possible, Database_B must contain a mirrored route for Service_A. In addition, Database_A must contain a nonmirrored TCP transport route to Service_B, which, unlike a local route, remains valid after failover. These routes enable ACKs to come back after a failover. 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.

For more information, see also:

[Top]

Extended Stored Procedures

Important

This feature will 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. Use CLR Integration instead.

Extended stored procedures are programmed by using the SQL Server Extended Stored Procedure API. A member of the sysadmin fixed server role can register an extended stored procedure with an instance of SQL Server and grant permission to users to execute the procedure. Extended stored procedures can be added only to the master database.

Extended stored procedures run directly in the address space of an instance of SQL Server, and they may produce memory leaks or other problems that reduce the performance and reliability of the server. You should consider storing extended stored procedures in an instance of SQL Server that is separate from the instance that contains the referenced data. You should also consider using distributed queries to access the database. For more information, see Distributed Queries.

Important

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. For more information, see Extended Stored Procedures.

For more information, see GRANT Object Permissions (Transact-SQL), DENY Object Permissions (Transact-SQL), and REVOKE Object Permissions (Transact-SQL).

[Top]

Full-Text Engine for SQL Server Properties

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. 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.

Note

When you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2008 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005. For more information, see Full-Text Search Upgrade.

For more information, see also:

[Top]

Jobs

If the database relies on SQL 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

    To create or execute SQL Server Agent jobs, you must first add any SQL Server logins required by the job to the destination server instance. For more information, see How to: Configure a User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio).

  • SQL Server Agent service startup account

    The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL 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. For information about how to select and modify the service startup account, see Selecting 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. For information, see Setting Up Windows Service Accounts.

  • SQL Server Agent service, which is associated with a specific instance of SQL 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. If you use a script to re-create a job in another SQL Server Agent service, its registry might not have the correct settings for that job. For re-created jobs to behave correctly on a destination server instance, the original and destination SQL Server Agent services should have the same registry settings.

    Warning

    Changing registry settings on the destination SQL 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 Server Agent Proxies

    A SQL 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. For more information, see Creating SQL Server Agent Proxies and Troubleshooting Multiserver Jobs That Use Proxies.

For more information, see also:

To view existing jobs and their properties

To create a job

To script an existing job

Best Practices for Using a Script to Re-create a Job

We recommend that you start by scripting a simple job, re-creating the job on the other SQL Server Agent service, and running the job to see whether it works as intended. This will let you to 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. 

[Top]

Logins

Logging into an instance of SQL Server requires a valid SQL Server login. This login is used in the authentication process that verifies whether the principal can connect to the instance of SQL Server. A database user for which the corresponding SQL 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. A database user can become orphaned if after a database is restored, attached, or copied to a different instance of SQL Server.

To 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. For more information, see How to: Generate a Script (SQL Server Management Studio).

For information about how to view the SQL Server logins and to detect and resolve orphaned users on a server instance, see Troubleshooting Orphaned Users.

Note

For information about how to set up logins for a mirrored database, see Setting Up Login Accounts for Database Mirroring and Managing Logins and Jobs After Role Switching.

[Top]

Permissions

The following types of permissions might be affected when a database is made available on another server instance.

  • GRANT, REVOKE, or DENY permissions on system objects

  • GRANT, REVOKE, or DENY permissions on server instance (server-level permissions)

GRANT, REVOKE, and DENY Permissions on System Objects

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.

To 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. For more information, see How to: Generate a Script (SQL Server Management Studio).

Important

If you script logins, the passwords are not scripted. If you have logins that use SQL Server Authentication, you have to modify the script on the destination.

System objects are visible in the sys.system_objects catalog view. The permissions on system objects are visible in the sys.database_permissions catalog view in the master database. For information about querying these catalog views and granting system-object permissions, see GRANT System Object Permissions (Transact-SQL). For more information, see REVOKE System Object Permissions (Transact-SQL) and DENY System Object Permissions (Transact-SQL).

GRANT, REVOKE, and DENY Permissions on a Server Instance

Permissions at the server scope are stored in the master database and must be configured on the destination server instance. 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.

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.

The mapped login and its permissions both reside in 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. 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

For more information about certificates and asymmetric keys, see Encryption Hierarchy.

[Top]

Replication 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. For more information, see How to: Script Replication Objects (SQL Server Management Studio). To help re-create your replication settings, copy these scripts and change the server name references to work for the destination server instance.

For more information, see Backing Up and Restoring Replicated Databases, Replication and Database Mirroring, and Replication and Log Shipping.

[Top]

Service Broker Applications

Many aspects of a Service Broker application move with the database. However, some aspects of the application must be re-created or reconfigured in the new location. For more information, see Migration (Service Broker).

[Top]

Startup Procedures

A startup procedure is a stored procedure that is marked for automatic execution and is executed every time SQL 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.

For more information, see Automatic Execution of Stored Procedures.

[Top]

Triggers (at Server Level)

DDL triggers fire stored procedures in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

For more information about this feature, see DDL Triggers.

[Top]