Administrar los metadatos cuando una base de datos pasa a estar disponible en otra instancia de servidor (SQL Server)
Este tema es pertinente en las siguientes situaciones:
Configuración de las réplicas de disponibilidad de un grupo de disponibilidad Always On grupos de disponibilidad.
Configurar la creación de reflejo de una base de datos.
Preparar la conmutación de roles entre los servidores primario y secundario de una configuración de trasvase de registros.
Restaurar una base de datos en otra instancia de servidor.
Adjuntar una copia de una base de datos en otra instancia de servidor.
Algunas aplicaciones dependen de información, entidades u objetos que se encuentran fuera del ámbito de una base de datos de usuario único. Normalmente, una aplicación depende de las bases de datos maestras y msdb , así como de la base de datos del 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 una aplicación o un plan de mantenimiento de base de datos depende de Agente SQL Server trabajos, cuyos metadatos se almacenan en la base de datos msdb, debe volver a crear esos trabajos en la instancia del servidor de destino. De forma similar, los metadatos de un desencadenador de servidor se almacenan en la base de datos maestra.
Si mueve la base de datos de una aplicación a otra instancia de servidor, debe volver a crear todos los metadatos de las entidades y los objetos dependientes de las bases de datos maestra y msdb en la instancia de servidor de destino. Por ejemplo, si una aplicación de la base de datos usa desencadenadores de servidor, no basta con adjuntar o restaurar la base de datos en el nuevo sistema. La base de datos no funcionará según lo previsto a menos que se vuelvan a crear manualmente los metadatos para dichos desencadenadores en la base de datos maestra .
Información, entidades y objetos almacenados fuera de las bases de datos de usuario
En lo que queda de este tema se resumen los posibles problemas que podrían afectar a una base de datos que se pone a disposición de otra instancia de servidor. Podría tener que volver a crear uno o varios de los tipos de información, entidades u objetos de la lista siguiente. Para ver un resumen, haga clic en el vínculo del elemento.
Valores de configuración del servidor
SQL Server 2005 y versiones posteriores instalan e inician de forma selectiva servicios y características clave. Esto ayuda a reducir el área de un sistema susceptible de recibir ataques. Con la configuración predeterminada de nuevas instalaciones, no se habilitan muchas de las características. Si la base de datos se basa en un servicio o característica desactivada de forma predeterminada, este servicio o característica debe habilitarse en la instancia de 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).
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 consta de un inicio de sesión de Windows y una contraseña.
Para obtener más información sobre esta característica, vea Credenciales (motor de base de datos).
Nota:
Agente SQL Server las cuentas de proxy usan credenciales. Para conocer el identificador de la credencial de una cuenta proxy, use la tabla del sistema sysproxies .
Consultas entre bases de datos
Las opciones de base de datos DB_CHAINING y TRUSTWORTHY se establecen, de forma predeterminada, en OFF. Si alguna de estas opciones se establece en ON para la base de datos original, es posible que deba habilitarlas en la base de datos de la instancia de servidor de destino. Para obtener más información, consulte ALTER DATABASE (Transact-SQL).
Las operaciones de adjuntar y separar deshabilitan el encadenamiento de propiedad entre bases de datos. Para obtener más información sobre cómo habilitar el encadenamiento, vea cross db ownership chaining (opción de configuración del servidor).
Para obtener más información, vea también Configurar una base de datos reflejada para usar la propiedad Trustworthy (Transact-SQL).
Propiedad de la base de datos
Cuando se restaura una base de datos en otro equipo, el SQL Server inicio de sesión o usuario de Windows que inició la operación de restauración se convierte automáticamente en el propietario de la nueva base de datos. Una vez restaurada 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 aplicaciones OLE DB admiten las consultas distribuidas y los servidores vinculados. Las consultas distribuidas obtienen acceso a datos desde varios orígenes de datos heterogéneos del mismo equipo o diferentes equipos. Una configuración de servidor vinculado permite SQL Server ejecutar comandos en orígenes de datos OLE DB en servidores remotos. Para obtener más información sobre estas características, vea Servidores vinculados (motor de base de datos).
Datos cifrados
Si la base de datos que pasa a estar disponible en otra instancia de servidor contiene datos cifrados y la clave maestra de la base de datos está protegida por la clave maestra de servicio del servidor original, es posible que deba volver a crear el cifrado de la clave maestra de servicio. La clave maestra de la base de datos es una clave simétrica que se utiliza para proteger las claves privadas de certificados y las claves asimétricas de una base de datos cifrada. Al crearla, la clave maestra de la base de datos se cifra mediante el algoritmo Triple DES y una contraseña proporcionada por el usuario.
Para habilitar el cifrado automático de la clave maestra de 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 maestra. Por lo general, la copia almacenada en la base de datos maestra se actualiza automáticamente al cambiar la clave maestra. SQL Server primero intenta descifrar 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. SQL Server luego 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. Para abrir una clave maestra que no se haya cifrado con la clave maestra de servicio, debe utilizarse 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, una copia de la clave maestra de base de datos cifrada por la clave maestra de servicio no se almacena en master en la instancia del servidor de destino. Se debe abrir la clave maestra de la base de datos en esta instancia. Para abrir la clave maestra, ejecute la siguiente instrucción: OPEN MASTER KEY DECRYPTION BY PASSWORD ='password'. Se recomienda habilitar 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. La instrucción ALTER MASTER KEY proporciona a la instancia de servidor una copia de la clave maestra de la base de datos que se ha cifrado 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, vea Establecer una base de datos reflejada cifrada.
Para obtener más información, vea también:
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 se almacena en la base de datos maestra. Si una aplicación de la base de datos depende de los mensajes de error definidos por el usuario y la base de datos pasa a estar disponible en otra instancia del servidor, use sp_addmessage para agregar esos mensajes de error en la instancia de servidor de destino.
Notificaciones de eventos y eventos del Instrumental de administración de Microsoft Windows (WMI) (en el nivel de servidor)
Notificaciones de eventos del servidor
Las notificaciones de eventos del servidor se almacenan en la base de datos msdb. Por lo tanto, si una aplicación de la base de datos depende de las notificaciones de eventos del servidor, la notificación de un evento debe volver a crearse en la instancia de servidor de destino. Para ver las notificaciones de eventos de una instancia del servidor, use la vista de catálogo sys.server_event_notifications . Para más información, consulte Event Notifications.
Además, las notificaciones de eventos se entregan mediante Service Broker. Las rutas de los mensajes entrantes no están incluidas en la base de datos que contiene un servicio. En lugar de ello, las rutas explícitas se almacenan en la base de datos msdb. Si el servicio usa una ruta explicita de la base de datos msdb para enrutar los mensajes entrantes al servicio, cuando adjunte una base de datos en una instancia diferente debe volver a crear esta ruta.
Eventos de Instrumental de administración de Windows (WMI)
El proveedor WMI para eventos de servidor le permite usar instrumental de administración de Windows (WMI) para supervisar eventos en SQL Server. Toda aplicación que dependa de eventos de servidor expuestos a través del proveedor WMI del que depende la base de datos se debe definir en el equipo de la instancia de servidor de destino. El proveedor de eventos WMI crea notificaciones de evento 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
Funcionamiento de 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 compatibilidad especial con las bases de datos reflejadas, en forma de rutas reflejadas. En una ruta reflejada hay 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 de la creación de reflejo 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 es hospedado por una base de datos reflejada, Database_A. Suponga que necesita que otro servicio, Service_B, que es hospedado por Database_B, tenga un diálogo con Service_A. Para que este 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 reflejada a Service_B que, a diferencia de una ruta local, sigue siendo válida después de una conmutación por error. Estas rutas permiten que los ACK regresen después de la conmutación por error. Puesto que al servicio del remitente siempre se le asigna un nombre de la misma forma, la ruta debe especificar la instancia del agente.
El requisito de las rutas reflejadas es válido independientemente de que el servicio de la base de datos reflejada sea el servicio iniciador o el 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. No obstante, el destino puede tener una ruta normal de vuelta al iniciador.
Si el servicio iniciador está en la base de datos reflejada, el servicio de destino debe tener una ruta reflejada de vuelta al iniciador para entregar reconocimientos y respuestas. No obstante, el iniciador puede tener una ruta normal al destino.
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 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 se debe considerar la posibilidad de utilizar consultas distribuidas para tener acceso a la base de datos.
Importante
Antes de agregar procedimientos almacenados extendidos al servidor y otorgar permisos EXECUTE a otros usuarios, el administrador del sistema debe revisar por completo cada procedimiento almacenado extendido para asegurarse de que no contiene código perjudicial o malintencionado.
Para obtener más información, vea GRANT Object Permissions (Transact-SQL),DENY Object Permissions (Transact-SQL) y REVOKE Object Permissions (Transact-SQL).
Propiedades del motor de texto completo para SQL Server
Las propiedades se establecen en el motor de texto completo a través de sp_fulltext_service. Asegúrese de que la instancia de servidor de destino tiene la configuración necesaria para estas propiedades. Para obtener más información sobre estas propiedades, vea FULLTEXTSERVICEPROPERTY (Transact-SQL).
Además, si el componente de separadores de palabras y lematizadores o el componente de filtros de búsqueda de texto completo tiene versiones diferentes en las instancias del servidor original y de destino, los índices de texto completo y las consultas pueden tener un comportamiento diferente. Además, el diccionario de sinónimos se almacena en archivos específicos de la instancia. Se debe transferir una copia de esos archivos a una ubicación equivalente de la instancia de servidor de destino o volver a crearlos en la 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, vea también:
Realizar copias de seguridad de los catálogos e índices de texto completo y restaurarlos
Creación de reflejo de la base de datos y catálogos de texto completo (SQL Server)
Trabajos
Si la base de datos se basa en Agente SQL Server trabajos, tendrá que volver a crearlos 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 de servidor de destino, es posible que deba modificar esta instancia para que el entorno de ese trabajo coincida con la instancia de servidor original. Los siguientes factores del entorno son importantes:
El inicio de sesión que utiliza el trabajo
Para crear o ejecutar trabajos Agente SQL Server, primero debe agregar los inicios de sesión de SQL Server requeridos por 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.
Agente SQL Server cuenta de inicio del servicio
La cuenta de inicio del servicio define la cuenta de Microsoft Windows en la que se ejecuta Agente SQL Server y sus permisos de red. Agente SQL Server se ejecuta como una cuenta de usuario especificada. El contexto del servicio del Agente afecta a la configuración del trabajo y a su entorno de ejecución. La cuenta debe tener acceso a los recursos (como por ejemplo, los recursos compartidos de red) que requiere 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 del Agente SQL Server.
Para que funcione correctamente, la cuenta de inicio del servicio se debe configurar para que disponga del dominio, el sistema de archivos y los permisos del Registro correctos. Además, es posible que un trabajo requiera un recurso de red compartido que debe configurarse para la cuenta de servicio. Para obtener información, vea Configurar los permisos y las cuentas de servicio de Windows.
Agente SQL Server servicio, 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. Un trabajo requiere estos parámetro del Registro para que funcione como se espera. Si usa un script para volver a crear un trabajo en otro servicio de 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 de servidor de destino, los servicios de Agente SQL Server originales y de destino deben tener la misma configuración del Registro.
Precaución
Cambiar la configuración del Registro en el servicio de destino Agente SQL Server para controlar un trabajo creado de nuevo podría ser problemático si otros trabajos requieren la configuración actual. Además, si el Registro se modifica incorrectamente, pueden provocarse daños graves en el sistema. Antes de efectuar cambios en el Registro, es recomendable realizar una copia de seguridad de los datos importantes del equipo.
servidores proxy de Agente SQL Server
Un proxy Agente SQL Server define el contexto de seguridad de un paso de trabajo especificado. Para ejecutar un trabajo en la instancia de servidor de destino, todos los servidores proxy que requiere se deben volver a crear 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, vea también:
Administración de inicios de sesión y trabajos después de la conmutación de roles (SQL Server) (para la creación de reflejo de la base de datos)
Configurar cuentas y permisos de servicio de Windows (al instalar una instancia de SQL Server)
Configurar Agente SQL Server (al instalar una instancia de SQL Server)
Para ver los trabajos existentes y sus propiedades
Para crear un trabajo
Prácticas recomendadas 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 Agente SQL Server y ejecutar el trabajo para ver si funciona según lo previsto. Esto permitirá identificar las incompatibilidades e intentar resolverlas. Si el trabajo generado con el script no funciona como se espera en este nuevo entorno, se recomienda crear un trabajo equivalente que funcione de forma correcta en ese entorno.
Inicios de sesión
El inicio de 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 comprueba si la entidad de seguridad 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 huérfano si después de restaurar, adjuntar o copiar una base de datos en otra instancia de SQL Server.
Para generar un script de algunos o todos los objetos de la copia original de la base de datos, se puede usar el asistente Generar scripts y, en el cuadro de diálogo Elegir opciones de script , configurar la opción Incluir inicios de sesión en el script en True.
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 la creación de 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).
Permisos
Los siguientes tipos de permisos se podrían ver afectados cuando una base de datos se pone a disposición de otra instancia de servidor.
Permisos GRANT, REVOKE o DENY sobre los objetos del sistema
Permisos GRANT, REVOKE o DENY sobre la instancia del servidor (permisos de servidor)
Permisos GRANT, REVOKE o DENY sobre los objetos del sistema
Los permisos de los objetos del sistema como procedimientos almacenados, procedimientos almacenados extendidos, funciones y vistas, se almacenan en la base de datos maestra y se deben configurar en la instancia de servidor de destino.
Para generar un script de algunos o todos los objetos de la copia original de la base de datos, se puede usar el asistente Generar scripts y, en el cuadro de diálogo Elegir opciones de script, configurar la opción Incluir permisos de objeto en el script en True.
Importante
Si incluye inicios de sesión en el script, no se incluirán las contraseñas. Si tiene inicios de sesión que usan SQL Server Autenticación, debe modificar el script en el destino.
Puede ver los objetos del sistema en la vista de catálogo sys.system_objects . Puede ver los permisos de objetos del sistema 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 Permisos de objeto del sistema (Transact-SQL). Para obtener más información, vea REVOKE System Object Permissions (Transact-SQL) y DENY System Object Permissions (Transact-SQL) (Permisos de objeto del sistema REVOKE [Transact-SQL]).
Permisos GRANT, REVOKE o DENY sobre la instancia de servidor
Los permisos en el ámbito del servidor se almacenan en la base de datos maestra y se deben configurar en la instancia de 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 de servidor, consulte la vista de catálogo sys.server_principalsy, para obtener información sobre la pertenencia a los 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)).
Permisos de nivel de servidor para un certificado o clave asimétrica
Los permisos de nivel de servidor para un certificado o clave asimétrica no se pueden conceder directamente. En su lugar, los permisos de nivel de servidor se conceden a un inicio de sesión asignado que se crea exclusivamente para un certificado o clave asimétrica. Por lo tanto, cada certificado o clave asimétrica que requiere permisos de nivel de servidor, necesita su propio inicio de sesión asignado a un certificado o inicio de sesión asignado a una clave asimétrica. Para conceder permisos de nivel de servidor para un certificado o clave asimétrica, conceda los permisos a su inicio de sesión asignado.
Nota
Un inicio de sesión asignado solo se utiliza para la autorización de código firmado con el certificado o clave asimétrica correspondiente. Los inicios de sesión asignados no se pueden utilizar para la autenticación.
El inicio de sesión asignado y sus permisos residen en la base de datos maestra. Si un certificado o clave asimétrica reside en una base de datos que no sea maestra, se debe volver a crear en maestra y asignarlo a un inicio de sesión. Si la base de datos se mueve, copia o restaura en otra instancia del servidor, se deben volver a crear sus certificados o claves asimétricas en la base de datos maestra de la instancia del servidor de destino, asignarlos a un inicio de sesión y conceder a este los permisos necesarios de nivel de servidor.
Para crear un certificado o clave asimétrica
Para asignar un certificado o clave asimétrica a un inicio de sesión
Para asignar permisos a un inicio de sesión asignado
Para obtener más información acerca de los certificados y las claves asimétricas, vea Encryption Hierarchy.
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 conservará la configuración de la replicación. En este caso, deberá 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 actual de la replicación y también para habilitar y deshabilitar la replicación. Para volver a crear los parámetros de la replicación, copie estos scripts y cambie las referencias del nombre de servidor para que funcionen con la instancia de servidor de destino.
Para obtener más información, consulte Copia de seguridad y restauración de bases de datos replicadas, Creación de reflejo de la base de datos y replicación (SQL Server) y Trasvase de registros y replicación (SQL Server).
Aplicaciones de Service Broker
Muchos aspectos de una aplicación de Service Broker se mueven con la base de datos. No obstante, algunos aspectos deben volver a crearse o configurarse en la nueva ubicación.
Procedimientos de inicio
Un procedimiento de inicio es un procedimiento almacenado marcado para la ejecución automática y que se ejecuta cada vez que se inicia SQL Server. Si la base de datos depende de algún procedimiento de inicio, se deben definir en la instancia de servidor de destino y configurarse para su ejecución automática durante el inicio.
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 ejecutan operaciones de tipo DDL también pueden activar desencadenadores DDL.
Para obtener más información acerca de esta característica, vea DDL Triggers.
Consulte 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)