En este documento vamos a explicar como reflejar una base de datos de SQL Server para aumentar la disponibilidad de este servicio.
Está arquitectura es idonea para disponer un reflejo de nuestra base de datos en centros alojados remotamente, donde podemos disponer de una conmutación por error rápida sin que se pierdan datos de las transacciones confirmadas.
Requisitos previos
- Tanto el servidor SQL Server principal o reflejado, como el secundario o reflejo, deben tener la misma versión y edición de SQL Server.
- Si va a usar un testigo, este debe tener la misma versión de SQL Server que los asociados, aunque si pueden ser de diferente edición, ya sea Standard, Enterprise, Workgroup o Express.
- Verifique que el servidor donde vamos a reflejar la base de datos, tenga suficiente espacio para alojar la misma.
- La base de datos a reflejar, debe tener el modelo de recuperación completa.
- Al crear la base de datos reflejada en el servidor reflejado, asegúrese de restaurar la copia de seguridad de la base de datos principal especificando la misma base de datos con la opción WITH NORECOVERY. Además, todas las copias de seguridad de registros creadas después de realizar esa copia de seguridad, deben restaurarse con WITH NORECOVERY.
Configurar cuentas de inicio de sesión para la creación de reflejo de la base de datos
Para que dos instancias del servidor se comuniquen en una sesión de creación de reflejo de la base de datos, la cuenta de inicio de sesión de cada instancia debe tener acceso a la otra. Además, cada cuenta de inicio de sesión requiere permiso de conexión al extremo de creación de reflejo de la base de datos de la otra instancia.
En nuestro caso, vamos a usar certificados para la creación de usuarios y autentificación entre las instancias, por ser el método más versatil en cada situación.
Crear los certificados de autentificación en ambos extremos
Ejecutaremos las siguiente instrucciones en el servidor principal:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Poner Aqui una contraseña segura A *‘;
CREATE CERTIFICATE HOST_Principal_cert WITH SUBJECT = ‘HOST_Principal certificate’;
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_Principal_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
BACKUP CERTIFICATE HOST_Principal_cert TO FILE = ‘C:\HOST_Principal_cert.cer’;
GO
* Reemplace este texto por una contraseña segura.
Con estas sentencias, habremos creado un fichero, C:\HOST_Principal_cert.cer, que debemos importar en el servidor secundario para que autentifique con dicho certificado. Copie de forma segura este certificado en el servidor secudario.
Esta misma operación, la vamos a realizar en el servidor secundario:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Poner Aqui una contraseña segura B‘;
CREATE CERTIFICATE HOST_Secundario_cert WITH SUBJECT = ‘HOST_Secundario certificate’;
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_Secundario_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
BACKUP CERTIFICATE HOST_Secundario_cert TO FILE = ‘C:\HOST_Secundario_cert.cer’;
GO
Ahora, copiaremos el fichero C:\Host_Secundario_cert.cer, al sevidor principal, para su ponterior importación.
Importar certificados en ambos extremos
Vamos a importar los certificados de autentificación que hemos creado anteriormente, y que hemos copiado al otros servidor.
En el servidor principal, ejecutaremos estás sentencias:
USE master;
CREATE LOGIN HOST_Secundario_login WITH PASSWORD = ‘Poner Aqui una contraseña segura C‘;
CREATE USER HOST_Secundario_user FOR LOGIN HOST_Secundario_login;
CREATE CERTIFICATE HOST_Secundario_cert AUTHORIZATION HOST_Secundario_user FROM FILE = ‘C:\HOST_Secundario_cert.cer’;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_Secundario_login];
GO
Con estas sentencias habremos dado acceso al servidor Secundario en el principal, tambien realizaremos está operación en el servidor secundario.
Ejecutar estas sentencias en el servidor secundario:
USE master;
CREATE LOGIN HOST_Principal_login WITH PASSWORD = ‘Poner Aqui una contraseña segura D‘;
CREATE USER HOST_Principal_user FOR LOGIN HOST_Principal_login;
CREATE CERTIFICATE HOST_Principal_cert AUTHORIZATION HOST_Principal_user FROM FILE = ‘C:\HOST_Principal_cert.cer’;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_Principal_login];
GO
Apartir de este momento ambos servidores tiene acceso entre ellos para hacer un reflejo de una o varias bases de datos, a través de los puertos 7024, según los ejemplos anteriores.
Configurar el reflejo de la base de datos.
Vamos a configurar el reflejo de una base de datos, pero antes es imprescindible que recuperemos la última copia backup de la base de datos y transacciones si las hubierá en el servidor secundario y marcando la opción WITH NORECOVERY.
Ahora estamos en disposición de reflejar la base de datos. Primeramente en el servidor secundario, ejecutaremos esta sentencia SQL:
ALTER DATABASE name_database
SET PARTNER = ‘TCP://hostprincipal:7024′;
GO
En el servidor principal, ejecutaremos la siguiente sentencia SQL:
ALTER DATABASE name_database
SET PARTNER = ‘TCP://hostprincipal:7024′;
GO
Si no hemos tenido ningún problema, nuestra base de datos comenzará a sincronizarse y tendremos el reflejo configurado y funcionando.
Supervisar el reflejo de la base de datos
Podemos iniciar el monitor de reflejo mediante SQL Server Management Studio, para ello desde la instancia del servidor principal, seleccione la base de datos que desea monitorizar y haga clic con el botón secundario para seleccionar dentro de Tareas, la opción Iniciar Monitor de creación de reflejo de la base de datos.
Lo que nos abrirá el monitor de reflejo y podremos comprobar el estado del mismo, tal como nos muestra la siguiente imagen:
El estado del reflejo, también podemos obtenerlo consultando el procedimiento sp_dbmmonitorresults. Este prodecimiento admite 3 argumentos:
- Nombre de la base de datos a monitorizar
- Número de filas a retornar (0; última fila, 1; los registros de las últimas 2 horas, …)
- Actualizar el estado; (0; si no queremos actualizar el estado, 1; para que se actualice el estado del reflejo antes de obtener los resultados)
Ej:
use msdb;
EXEC sp_dbmmonitorresults basededato 0,1
Estos son los estados que nos podemos encontrar:
- 0 = Suspendida; el reflejo se encuentra suspendido por algún motivo, revise cual es el motivo para reactivarlo.
- 1 = Desconectado; las instancias de SQL Server, entre reflejado y reflejo, se encuentran desconectadas
- 2 = En proceso de sincronización; sincronizando transacciones de registros
- 3 = En proceso de conmutación por error; Es el estado, mientras conmutamos el reflejo de una instancia a otra.
- 4 = Sincronizada; la base de datos, se encuentra reflejada.