Alta disponibilidad con un espejo en SQL Server

SQL Server tiene varias formas para que dispongamos de alta disponibilidad en nuestras bases de datos, en este post veremos una de ellas: la creación de un espejo. Para mi la creación de un espejo vs un clúster es una preferencia personal, creo que un clúster es más complejo de mantener y gestionar, es más caro porque ambas máquinas comparten el almacenamiento y por lo menos mi experiencia con SQL Server 2008 R2 no fue muy buena y sin un soporte premiun por parte de Microsoft los fallos son difícilmente corregibles. En cambio un mirror o espejo es sencillo de configurar y pueden ser dos máquinas totalmente independientes.

¿Por qué crear un espejo en SQL Server?

Porque este método permite hacer una réplica exacta de una base de datos de un servidor a otro. La gran ventaja de este método es que permite el failover automático sin intervención humana (siempre que se instale un tercer servidor witness). La desventaja del mirror es que sólo podemos tener una máquina secundaria o mirror y que esta no es accesible y no podemos tenerla en modo lectura.

Para la creación de un espejo en SQL Server me he guiado por este manual, pero está incompleto y con erratas de tal modo que reproduciré aquí los pasos realizados, pero para consultar los requisitos previos si recomiendo leerlo.

Certificados para el inicio de sesión

Lo primero que hay que hacer es crear tanto en el servidor principal como en el secundario los certificados para que cada máquina se pueda autentificar en la otra, existen otros métodos como por dominio pero en este caso el más sencillo es este.

Las contraseñas siguientes deben ser todas distintas y seguras, podéis utilizar esta web para generarlas.

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';
GO

y luego

CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
    LISTENER_PORT=7020
    , 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';

NOTA: Es importante separar estas queries porque la instrucción CREATE ENDPOINT debe ser la primera línea de una consulta siempre.

Esto nos creará el archivo C:\HOST_Principal_cert.cer que copiaremos al servidor secundario e importaremos de la siguiente forma:

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 

Luego en este servidor secundario generaremos su certificado

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=7020
    , 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';

Y copiaremos el archivo C:\HOST_Secundario_cert.cer al servidor principal y lo importamos

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

Backups

Ahora procederemos a crear las relaciones, pero antes la base de datos principal debe estar en modo de recuperación completo o tendremos el siguiente error:

Msg 1465, Level 16, State 1, Line 1
Database mirroring cannot be enabled because the "xxx" database is not in full recovery mode on both partners.

Para esto podemos ejecutar esta consulta en el servidor principal

ALTER DATABASE dbname SET RECOVERY FULL

Luego tenemos que ejecutar un backup completo de esta base de datos

Y restaurarlo en modo de no recuperación en el espejo.

NOTA: Por favor ten en cuenta que el nombre de  ambas bases debe ser igual.

Creación del espejo en la base de datos

Importante: Por comodidad que en ambas máquinas el puerto sea el mismo y este abierto en el Firewall tanto en entrante como saliente.

Una vez restaurado ejecutamos en el servidor secundario lo siguiente:

ALTER DATABASE name_database
SET PARTNER = 'TCP://hostprincipal:7020';
GO

Y en el principal

ALTER DATABASE name_database
SET PARTNER = 'TCP://hostsecundario:7020';
GO

En mi caso hostprincipal y hostsecundario son IPs pero pueden ser nombres de maquinas o dominios dns.

NOTA: el puerto (7020 en mi caso pero puede ser cualquiera libre) para TCP y conexiones entrantes debe estar habilitado en ambos servidores.

Con esto ya estaría creado el espejo, crear un testigo para el failover lo dejo como tarea pendiente.

NOTA: si al probar el monitor de conexiones espejo os da este error:

SQL Server replication requires the actual server name to make a connection to the server.

Podéis intentar solucionarlo aquí.

Otro error común suele ser Database mirroring error: status 1460, severity 16, state 1, en este caso comprobad los puertos y ejecutar esto en el servidor espejo (no el principal)

Use master;
GO
ALTER DATABASE name_database SET PARTNER OFF;
GO
ALTER DATABASE name_database SET PARTNER = 'TCP://hostprincipal:7020';
GO

Luego volved a ejecutar la consulta del servidor principal.