Compartir a través de

Administrar metadatos al hacer que una base de datos esté disponible en otra instancia del servidor (SQL Server)

Este tema es relevante en las siguientes situaciones:

  • Configuración de las réplicas de disponibilidad de un grupo de disponibilidad de Always On Availability Groups.

  • Configuración del reflejo de la base de datos.

  • Al prepararse para cambiar los roles entre los servidores principal y secundario en una configuración de envío de registros.

  • Restaurar una base de datos en otra instancia del servidor.

  • Adjuntar una copia de una base de datos en otra instancia de servidor.

Algunas aplicaciones dependen de información, entidades o objetos que están fuera del ámbito de una base de datos de usuario única. Normalmente, una aplicación tiene dependencias en las bases de datos maestras y msdb , y también en la base de datos de usuario. Cualquier elemento almacenado fuera de la base de datos de usuario que sea necesario para el funcionamiento correcto de dicha base de datos debe estar disponible en la instancia de servidor de destino. Por ejemplo, los inicios de sesión de una aplicación se almacenan como metadatos en la base de datos maestra y se deben volver a crear en el servidor de destino. Si un plan de mantenimiento de aplicaciones o bases de datos depende de los trabajos del Agente SQL Server, cuyos metadatos se almacenan en la base de datos msdb , debe volver a crear esos trabajos en la instancia del servidor de destino. Del mismo modo, los metadatos de un desencadenador de nivel de servidor se almacenan en master.

Al mover la base de datos de una aplicación a otra instancia de servidor, debe volver a crear todos los metadatos de las entidades y objetos dependientes de master y msdb en la instancia del servidor de destino. Por ejemplo, si una aplicación de base de datos usa desencadenadores de nivel de servidor, simplemente adjuntar o restaurar la base de datos en el nuevo sistema no es suficiente. La base de datos no funcionará según lo previsto a menos que vuelva a crear manualmente los metadatos de esos desencadenadores en la base de datos maestra .

Información, entidades y objetos que se almacenan fuera de las bases de datos de usuario

El resto de este tema resume los posibles problemas que podrían afectar a una base de datos que está disponible en otra instancia del servidor. Es posible que tenga que volver a crear uno o varios de los tipos de información, entidades u objetos enumerados en la lista siguiente. Para ver un resumen, haga clic en el vínculo del elemento.

Opciones de configuración del servidor

SQL Server 2005 y versiones posteriores instalan de forma selectiva e inician características y servicios clave. Esto ayuda a reducir el área expuesta a ataques de un sistema. En la configuración predeterminada de las nuevas instalaciones, muchas características no están habilitadas. Si la base de datos se basa en cualquier servicio o característica que esté desactivada de forma predeterminada, este servicio o característica debe estar habilitado en la instancia del servidor de destino.

Para obtener más información sobre esta configuración y habilitarlas o deshabilitarlas, vea Opciones de configuración del servidor (SQL Server).

[Top]

Credenciales

Una credencial es un registro que contiene la información de autenticación necesaria para conectarse a un recurso fuera de SQL Server. La mayoría de las credenciales constan de un inicio de sesión y una contraseña de Windows.

Para obtener más información sobre esta característica, consulte Credenciales (motor de base de datos).

Nota:

Las cuentas de proxy del Agente SQL Server usan credenciales. Para obtener información sobre el identificador de credencial de una cuenta de proxy, use la tabla del sistema sysproxies .

[Top]

Consultas entre bases de datos

Las opciones de base de datos DB_CHAINING y TRUSTWORTHY están desactivadas de forma predeterminada. Si alguna de estas opciones está establecida en ON para la base de datos original, es posible que tenga que habilitarlas en la base de datos en la instancia del servidor de destino. Para obtener más información, consulte ALTER DATABASE (Transact-SQL).

Las operaciones de asociación y desasociación deshabilitan el encadenamiento de propiedad entre bases de datos. Para obtener información sobre cómo habilitar el encadenamiento, consulte Cross db ownership chaining Server Configuration Option (Opción de configuración del servidor de encadenamiento de propiedad entre bases de datos).

Para obtener más información, vea también Configurar una base de datos reflejada para utilizar la propiedad Trustworthy (Transact-SQL)

[Top]

Propiedad de la base de datos

Cuando se restaura una base de datos en otro equipo, el inicio de sesión de SQL Server o el usuario de Windows que inició la operación de restauración se convierte automáticamente en el propietario de la nueva base de datos. Cuando se restaura la base de datos, el administrador del sistema o el nuevo propietario de la base de datos pueden cambiar la propiedad de la base de datos.

Consultas distribuidas y servidores vinculados

Las consultas distribuidas y los servidores vinculados son compatibles con las aplicaciones OLE DB. Las consultas distribuidas acceden a los datos de varios orígenes de datos heterogéneos en los mismos equipos o en equipos diferentes. Una configuración de servidor vinculado permite a SQL Server ejecutar comandos en orígenes de datos OLE DB en servidores remotos. Para obtener más información sobre estas características, consulte Servidores vinculados (motor de base de datos).

[Top]

Datos cifrados

Si la base de datos que está poniendo a disposición en otra instancia del servidor contiene datos cifrados y si la clave maestra de base de datos está protegida por la clave maestra de servicio en el servidor original, es posible que sea necesario volver a crear el cifrado de la clave maestra de servicio. La clave maestra de base de datos es una clave simétrica que se usa para proteger las claves privadas de certificados y claves asimétricas en una base de datos cifrada. Cuando se crea, la clave maestra de base de datos se cifra mediante el algoritmo Triple DES y una contraseña proporcionada por el usuario.

Para habilitar el descifrado automático de la clave maestra de base de datos en una instancia de servidor, se cifra una copia de esta clave mediante la clave maestra de servicio. Esta copia cifrada se almacena en la base de datos y en la base de datos maestra. Normalmente, la copia almacenada en maestro se actualiza silenciosamente cada vez que se cambia la clave maestra. SQL Server intenta descifrar primero la clave maestra de base de datos con la clave maestra de servicio de la instancia. Si se produce un error en el descifrado, SQL Server busca en el almacén de credenciales las credenciales de clave maestra que tienen el mismo GUID de familia que la base de datos para la que requiere la clave maestra. A continuación, SQL Server intenta descifrar la clave maestra de base de datos con cada credencial coincidente hasta que el descifrado se realiza correctamente o no hay más credenciales. Una clave maestra que no está cifrada por la clave maestra de servicio debe abrirse mediante la instrucción OPEN MASTER KEY y una contraseña.

Cuando se copia, restaura o adjunta una base de datos cifrada a una nueva instancia de SQL Server, no se almacena una copia de la clave maestra de base de datos cifrada por la clave maestra de servicio en la instancia del servidor de destino. En la instancia del servidor de destino, debe abrir la clave maestra de la base de datos. Para abrir la clave maestra, ejecute la siguiente instrucción: OPEN MASTER KEY DECRYPTION BY PASSWORD ='password'. Recomendamos que habilite el descifrado automático de la clave maestra de la base de datos ejecutando la siguiente instrucción: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY. Esta instrucción ALTER MASTER KEY aprovisiona la instancia del servidor con una copia de la clave maestra de base de datos cifrada con la clave maestra de servicio. Para obtener más información, vea OPEN MASTER KEY (Transact-SQL) y ALTER MASTER KEY (Transact-SQL).

Para obtener información sobre cómo habilitar el descifrado automático de la clave maestra de base de datos de una base de datos reflejada, consulte Configuración de una base de datos reflejada cifrada.

Para obtener más información, consulte también:

[Top]

Mensajes de error definidos por el usuario

Los mensajes de error definidos por el usuario residen en la vista de catálogo sys.messages . Esta vista de catálogo se almacena en master. Si una aplicación de base de datos depende de los mensajes de error definidos por el usuario y la base de datos está disponible en otra instancia del servidor, use sp_addmessage para agregar esos mensajes definidos por el usuario en la instancia del servidor de destino.

[Top]

Notificaciones de eventos y eventos de Instrumentación de administración de Windows (WMI) (a nivel de servidor)

Notificaciones de eventos de Server-Level

Las notificaciones de eventos de nivel de servidor se almacenan en msdb. Por lo tanto, si una aplicación de base de datos se basa en notificaciones de eventos de nivel de servidor, esa notificación de eventos debe volver a crearse en la instancia del servidor de destino. Para ver las notificaciones de eventos en una instancia de servidor, use la vista de catálogo de sys.server_event_notifications . Para obtener más información, consulte Notificaciones de eventos.

Además, las notificaciones de eventos se entregan mediante Service Broker. Las rutas de los mensajes entrantes no se incluyen en la base de datos que contiene un servicio. En su lugar, las rutas explícitas se almacenan en msdb. Si el servicio usa una ruta explícita en la base de datos msdb para enrutar los mensajes entrantes al servicio, al adjuntar una base de datos en otra instancia, debe volver a crear esta ruta.

Eventos de Instrumentación de administración de Windows (WMI)

El proveedor WMI para eventos de servidor permite usar instrumental de administración de Windows (WMI) para supervisar eventos en SQL Server. Cualquier aplicación que se base en eventos de nivel de servidor expuestos a través del proveedor WMI en el que se basa una base de datos debe definirse el equipo de la instancia del servidor de destino. El proveedor de eventos WMI crea notificaciones de eventos con un servicio de destino definido en msdb.

Nota:

Para obtener más información, vea Conceptos del proveedor WMI para eventos de servidor.

Para crear una alerta WMI mediante SQL Server Management Studio

Cómo funcionan las notificaciones de eventos para una base de datos reflejada

La entrega entre bases de datos de notificaciones de eventos que implica una base de datos reflejada es remota, por definición, porque la base de datos reflejada puede conmutar por error. Service Broker proporciona soporte especial para las bases de datos reflejadas, en forma de rutas reflejadas. Una ruta reflejada tiene dos direcciones: una para la instancia de servidor principal y otra para la instancia del servidor reflejado.

Al configurar rutas reflejadas, el enrutamiento de Service Broker es consciente del espejamiento de la base de datos. Las rutas reflejadas permiten a Service Broker redirigir de forma transparente las conversaciones a la instancia actual del servidor principal. Por ejemplo, considere un servicio, Service_A, que está hospedado por una base de datos reflejada, Database_A. Supongamos que necesita otro servicio, Service_B, que está hospedado por Database_B, para tener un cuadro de diálogo con Service_A. Para que este cuadro de diálogo sea posible, Database_B debe contener una ruta reflejada para Service_A. Además, Database_A debe contener una ruta de transporte TCP no espejada a Service_B, que, a diferencia de una ruta local, permanece válida después de la conmutación por error. Estas rutas permiten que los ACK vuelvan después de una conmutación por error. Dado que el servicio del remitente siempre se denomina de la misma manera, la ruta debe especificar la instancia del agente.

El requisito de rutas reflejadas se aplica independientemente de si el servicio de la base de datos reflejada es el servicio iniciador o el servicio de destino:

  • Si el servicio de destino está en la base de datos reflejada, el servicio iniciador debe tener una ruta reflejada de vuelta al destino. Sin embargo, el destino puede tener una ruta regular al iniciador.

  • Si el servicio iniciador está en la base de datos reflejada, el servicio de destino debe mantener una ruta reflejada hacia el iniciador para entregar confirmaciones y respuestas. Sin embargo, el iniciador puede tener una ruta habitual al destino.

[Top]

Procedimientos almacenados extendidos

Importante

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan. Use clR Integration en su lugar.

Los procedimientos almacenados extendidos se programan mediante la API de procedimiento almacenado extendido de SQL Server. Un miembro del rol fijo de servidor sysadmin puede registrar un procedimiento almacenado extendido con una instancia de SQL Server y conceder permiso a los usuarios para ejecutar el procedimiento. Los procedimientos almacenados extendidos solo se pueden agregar a la base de datos maestra .

Los procedimientos almacenados extendidos se ejecutan directamente en el espacio de direcciones de una instancia de SQL Server y pueden producir pérdidas de memoria u otros problemas que reduzcan el rendimiento y la confiabilidad del servidor. Debe considerar la posibilidad de almacenar procedimientos almacenados extendidos en una instancia de SQL Server independiente de la instancia que contiene los datos a los que se hace referencia. También debe considerar el uso de consultas distribuidas para acceder a la base de datos.

Importante

Antes de agregar procedimientos almacenados extendidos al servidor y conceder permisos EXECUTE a otros usuarios, el administrador del sistema debe revisar exhaustivamente cada procedimiento almacenado extendido para asegurarse de que no contiene código dañino o malintencionado.

Para obtener más información, vea GRANT Object Permissions (Transact-SQL), DENY Object Permissions (Transact-SQL), and REVOKE Object Permissions (Transact-SQL).

[Top]

Propiedades del motor Full-Text para SQL Server

Las propiedades se establecen en el motor de Full-Text mediante sp_fulltext_service. Asegúrese de que la instancia del servidor de destino tenga la configuración necesaria para estas propiedades. Para obtener más información sobre estas propiedades, vea FULLTEXTSERVICEPROPERTY (Transact-SQL).

Además, si los separadores de palabras y el componente lematizador o el componente de filtros de búsqueda de texto completo tienen versiones diferentes en las instancias del servidor original y de destino, el índice de texto completo y las consultas pueden comportarse de forma diferente. Además, el diccionario de sinónimos se almacena en archivos específicos de la instancia. Debe transferir una copia de esos archivos a una ubicación equivalente en la instancia del servidor de destino o volver a crearlos en una nueva instancia.

Nota:

Al adjuntar una base de datos de SQL Server 2005 que contiene archivos de catálogo de texto completo en una instancia de servidor de SQL Server 2014, los archivos de catálogo se adjuntan desde su ubicación anterior junto con los demás archivos de base de datos, igual que en SQL Server 2005. Para obtener más información, vea Actualizar la búsqueda de texto completo.

Para obtener más información, consulte también:

[Top]

Trabajos

Si la base de datos se basa en trabajos del Agente SQL Server, tendrá que volver a crearlas en la instancia del servidor de destino. Los trabajos dependen de sus entornos. Si tiene previsto volver a crear un trabajo existente en la instancia del servidor de destino, es posible que la instancia del servidor de destino tenga que modificarse para que coincida con el entorno de ese trabajo en la instancia de servidor original. Los siguientes factores ambientales son significativos:

  • El inicio de sesión usado por la tarea

    Para crear o ejecutar trabajos del Agente SQL Server, primero debe agregar los inicios de sesión de SQL Server necesarios para el trabajo a la instancia del servidor de destino. Para obtener más información, vea Configurar un usuario para crear y administrar trabajos del Agente SQL Server.

  • Cuenta de inicio del servicio agente SQL Server

    La cuenta de inicio del servicio define la cuenta de Microsoft Windows en la que se ejecuta el Agente SQL Server y sus permisos de red. El Agente SQL Server se ejecuta como una cuenta de usuario especificada. El contexto del servicio de agente afecta la configuración del trabajo y su entorno de ejecución. La cuenta debe tener acceso a los recursos, como los recursos compartidos de red, requeridos por el trabajo. Para obtener información sobre cómo seleccionar y modificar la cuenta de inicio del servicio, vea Seleccionar una cuenta para el servicio agente SQL Server.

    Para funcionar correctamente, la cuenta de inicio del servicio debe configurarse para tener los permisos de dominio, sistema de archivos y registro correctos. Además, un trabajo puede requerir un recurso de red compartido que se debe configurar para la cuenta de servicio. Para obtener información, consulte Configurar cuentas y permisos de servicio de Windows.

  • El servicio Agente SQL Server, que está asociado a una instancia específica de SQL Server, tiene su propio subárbol del Registro y sus trabajos suelen tener dependencias en una o varias de las opciones de configuración de este subárbol del Registro. Para comportarse según lo previsto, un trabajo requiere esa configuración del Registro. Si usa un script para volver a crear un trabajo en otro servicio del Agente SQL Server, es posible que su registro no tenga la configuración correcta para ese trabajo. Para que los trabajos que se vuelvan a crear se comporten correctamente en una instancia del servidor de destino, los servicios del Agente SQL Server original y de destino deben tener la misma configuración del Registro.

    Precaución

    Cambiar la configuración del Registro en el servicio del Agente SQL Server de destino para controlar un trabajo creado de nuevo podría ser problemático si otros trabajos requieren la configuración actual. Además, la edición incorrecta del registro puede dañar gravemente el sistema. Antes de realizar cambios en el Registro, se recomienda realizar una copia de seguridad de los datos con valores en el equipo.

  • Proxies del Agente de SQL Server

    Un proxy del Agente SQL Server define el contexto de seguridad de un paso de trabajo especificado. Para que un trabajo se ejecute en la instancia del servidor de destino, todos los servidores proxy que requiere deben volver a crearse manualmente en esa instancia. Para obtener más información, vea Crear un proxy del Agente SQL Server y Solucionar problemas de trabajos multiservidor que usan servidores proxy.

Para obtener más información, consulte también:

Para ver los trabajos existentes y sus propiedades

Para crear un trabajo

Procedimientos recomendados para usar un script para volver a crear un trabajo

Se recomienda empezar por crear un trabajo sencillo, volver a crear el trabajo en el otro servicio del Agente SQL Server y ejecutar el trabajo para ver si funciona según lo previsto. Esto le permitirá identificar incompatibilidades e intentar resolverlas. Si un trabajo con script no funciona según lo previsto en su nuevo entorno, se recomienda crear un trabajo equivalente que funcione correctamente en ese entorno.

[Top]

Inicios de sesión

Iniciar sesión en una instancia de SQL Server requiere un inicio de sesión de SQL Server válido. Este inicio de sesión se usa en el proceso de autenticación que verifica si el principal puede conectarse a la instancia de SQL Server. Un usuario de base de datos para el que el inicio de sesión de SQL Server correspondiente no está definido o está definido incorrectamente en una instancia de servidor no puede iniciar sesión en la instancia. Es lo que se denomina un usuario huérfano de la base de datos en esa instancia de servidor. Un usuario de base de datos puede quedar aislado si, después de restaurar, adjuntar o copiar una base de datos a una instancia diferente de SQL Server.

Para generar un script para algunos o todos los objetos de la copia original de la base de datos, puede usar el Asistente para generación de scripts y, en el cuadro de diálogo Elegir opciones de script, establezca la opción de Inicios de sesión en Verdadero.

Nota:

Para obtener información sobre cómo configurar inicios de sesión para una base de datos reflejada, Vea Configurar Cuentas de Inicio de Sesión para Reflejo de la Base de Datos o Grupos de Disponibilidad AlwaysOn (SQL Server) y Administración de Inicios de Sesión y Trabajos Después de la Conmutación de Roles (SQL Server).

[Top]

Permisos

Los siguientes tipos de permisos pueden verse afectados cuando una base de datos está disponible en otra instancia del servidor.

  • Otorgar, revocar o denegar permisos en objetos del sistema

  • Conceder, revocar o denegar permisos en la instancia de servidor (permisos de nivel de servidor)

Otorgar, Revocar y Denegar permisos en objetos del sistema

Los permisos en objetos del sistema, como procedimientos almacenados, procedimientos almacenados extendidos, funciones y vistas, se almacenan en la base de datos maestra y deben configurarse en la instancia del servidor de destino.

Para generar un script para algunos o todos los objetos de la copia original de la base de datos, puede usar el Asistente para generar scripts y, en el cuadro de diálogo Elegir opciones de script , establezca la opción Script Object-Level Permissions en True.

Importante

Si crea scripts de inicios de sesión, las contraseñas no tienen script. Si tiene inicios de sesión que usan la autenticación de SQL Server, debe modificar el script en el destino.

Los objetos del sistema son visibles en la vista de catálogo sys.system_objects. Los permisos de los objetos del sistema están visibles en la vista de catálogo sys.database_permissions de la base de datos maestra . Para obtener información sobre cómo consultar estas vistas de catálogo y conceder permisos de objeto del sistema, vea GRANT System Object Permissions (Transact-SQL). Para obtener más información, vea REVOKE System Object Permissions (Transact-SQL) y DENY System Object Permissions (Transact-SQL).

Otorgar, revocar y denegar permisos en una instancia de servidor

Los permisos en el ámbito del servidor se almacenan en la base de datos maestra y deben configurarse en la instancia del servidor de destino. Para obtener información sobre los permisos de servidor de una instancia de servidor, consulte la vista de catálogo sys.server_permissions; para obtener información sobre las entidades de seguridad del servidor, consulte la vista de catálogo sys.server_principals; y para obtener información sobre la pertenencia a roles de servidor, consulte la vista de catálogo sys.server_role_members.

Para obtener más información, vea GRANT Server Permissions (Transact-SQL), REVOKE Server Permissions (Transact-SQL)y DENY Server Permissions (Transact-SQL)).

Server-Level Permisos de un certificado o una clave asimétrica

Los permisos de nivel de servidor no se pueden conceder directamente a un certificado o una clave asimétrica. En su lugar, se conceden permisos de nivel de servidor a un inicio de sesión asignado que se crea exclusivamente para un certificado específico o una clave asimétrica. Por lo tanto, cada certificado o clave asimétrica que requiera permisos de nivel de servidor necesita su propio inicio de sesión con asignación a certificado o inicio de sesión con asignación a clave asimétrica. Para conceder permisos de nivel de servidor para un certificado o una clave asimétrica, conceda los permisos a su inicio de sesión mapeado.

Nota:

Un inicio de sesión mapeado solo se utiliza para la autorización del código firmado con el certificado correspondiente o la clave asimétrica. Los inicios de sesión asignados no pueden ser usados para la autenticación.

El inicio de sesión mapeado y sus permisos residen ambos en master. Si un certificado o una clave asimétrica residen en una base de datos distinta de master, debe volver a crearlo en master y asociarlo a un inicio de sesión. Si mueve, copia o restaura la base de datos a otra instancia del servidor, debe volver a crear su certificado o clave asimétrica en la base de datos maestra de la instancia del servidor de destino, asignarla a un inicio de sesión y conceder al inicio de sesión los permisos necesarios de nivel de servidor.

Para crear un certificado o una clave asimétrica

Para asignar un certificado o una clave asimétrica a un inicio de sesión

Para asignar permisos al inicio de sesión mapeado

Para obtener más información sobre los certificados y las claves asimétricas, consulte Jerarquía de cifrado.

[Top]

Configuración de replicación

Si restaura una copia de seguridad de una base de datos replicada en otro servidor o base de datos, no se puede conservar la configuración de replicación. En este caso, debe volver a crear todas las publicaciones y suscripciones después de restaurar las copias de seguridad. Para facilitar este proceso, cree scripts para la configuración de replicación actual y, además, para habilitar y deshabilitar la replicación. Para ayudar a volver a crear la configuración de replicación, copie estos scripts y cambie las referencias de nombre del servidor para que funcionen para la instancia del servidor de destino.

Para obtener más información, vea Respaldo y restauración de bases de datos replicadas, Reflejo de la base de datos y replicación (SQL Server) y Envio de registros y replicación (SQL Server).

[Top]

Aplicaciones de Service Broker

Muchos aspectos de una aplicación de Service Broker se mueven con la base de datos. Sin embargo, algunos aspectos de la aplicación deben volver a crearse o volver a configurarse en la nueva ubicación.

[Top]

Procedimientos de inicio

Un procedimiento de inicio es un procedimiento almacenado marcado para la ejecución automática y se ejecuta cada vez que se inicia SQL Server. Si la base de datos depende de cualquier procedimiento de inicio, deben definirse en la instancia del servidor de destino y configurarse para que se ejecuten automáticamente en el inicio.

[Top]

Desencadenadores (en el nivel de servidor)

Los desencadenadores DDL activan procedimientos almacenados en respuesta a una variedad de eventos del lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones Transact-SQL que comienzan con las palabras clave CREATE, ALTER y DROP. Algunos procedimientos almacenados del sistema que realizan operaciones de tipo DDL también pueden desencadenar desencadenadores DDL.

Para obtener más información sobre esta característica, consulte Desencadenadores DDL.

[Top]

Véase también

Bases de datos independientes
Copiar bases de datos en otros servidores
Adjuntar y separar bases de datos (SQL Server)
Conmutar por error a una base de datos secundaria de trasvase de registros (SQL Server)
Conmutación de roles durante una sesión de creación de reflejo de la base de datos (SQL Server)
Establecer una base de datos reflejada cifrada
Administrador de configuración de SQL Server
Solución de problemas de usuarios huérfanos (SQL Server)