El Foso Del Sarlacc: SQL Mirroring con MS SQL Server 2005

Con esta configuración se pretende tener un sistema de base de datos resistente a fallos.
Se compone de dos servidores Microsoft SQL Server uno en producción y otro en modo escucha pasivo. De tal forma que si el servidor principal se produjese algún fallo el servidor secundario se activaría automáticamente sin perdida de datos.

Definición de los elementos
                             Servidor Principal                        -->       ServerProduccion
                             Servidor Reflejo                           -->       ServerReflejo
                             Servidor Testigo                          -->       No usado
                             Base de Datos a reflejar            -->      Inventario
                             Nombre EndPoint                       -->         MirroringEndPoint
                             Usuario de acceso                   -->      suja@DOMINIO

Los sistemas operativos de ServerProduccion y ServerReflejo son MS Windows 2003 Standard SP 2.

Los servidores SQL tanto en el servidor principal como el servidor reflejo tienen un Microsoft SQL Server 2005 Standard.

Identificar Puerto de conexión

Antes de asignar un puerto de conexión entre los dos servidores MS SQL se deberá comprobar los puertos disponibles en ambos servidores.

En la documentación de Microsoft indica como puerto de conexión el TCP 10111.
Se deberá comprobar la disponibilidad de este puerto ó cualquier otro en los servidores implicados, para ello ejecutar la instrucción siguiente en la línea de comandos:

netstat –nbt

Este comando muestra los puertos usados en el servidor donde se ha ejecutado.
Se comprobará que no se esta usando el puerto TCP 10111.

Puerto TCP conexión = 10111

Modificar el plan de mantenimiento de las Bases de Datos implicadas
El plan de mantenimiento de las bases de datos debe contener las siguientes tareas para evitar un crecimiento excesivo de los archivos de log (ldf):

· Copia de seguridad de carácter completa

· Copia de Registro de Transacciones

Preparación de servidores para reflejo

En Servidor Principal

Cambiar el modelo de recuperación de la base de datos de SIMPLE a FULL

ALTER DATABASE Inventario

SET RECOVERY FULL

Crear copia de seguridad de la base de datos a reflejar.

BACKUP DATABASE Inventario

TO DISK = 'C:\Inventario.bak'

WITH FORMAT

Crear una copia de seguridad de los registros (logs)

BACKUP LOG Inventario

TO DISK = 'C:\Inventario_log.bak'

Traspaso de copia de seguridad y log del ServerProduccion al ServerReflejo.

Servidor Reflejo

Restaurar la copia de seguridad con la opción WITH NORECOVERY

RESTORE DATABASE Inventario

FROM DISK = 'C:\Inventario.bak'

WITH NORECOVERY

Restaurar los log

RESTORE LOG Inventario

FROM DISK = 'C:\Inventario_log.bak'

WITH FILE=1, NORECOVERY

Mirroring - Reflejo de Base de Datos

Creación de endpoints y login de acceso.

Servidor Principal

-- Creacion de endpoint

CREATE ENDPOINT MirroringEndPoint

STATE=STARTED

AS TCP (LISTENER_PORT=10111)

FOR DATABASE_MIRRORING (ROLE=PARTNER)

-- Creación de login

USE master;

CREATE LOGIN [suja@DOMINIO] FROM WINDOWS ;

GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [suja@DOMINIO];

Servidor Reflejo

-- Creacion de endpoint

CREATE ENDPOINT MirroringEndPoint

STATE=STARTED

AS TCP (LISTENER_PORT=10111)

FOR DATABASE_MIRRORING (ROLE=ALL)

-- Creación de login

USE master;

CREATE LOGIN [suja@DOMINIO] FROM WINDOWS ;

GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [suja@DOMINIOs];

Estado de endpoint.

Para saber el estado de los endpoints ejecutar la siguiente sentencia:

SELECT * FROM sys.database_mirroring_endpoints

La columna state_desc indica el estado de inicialización del endpoint, en el caso de no estar inicializado ejecutar la siguiente sentencia.

ALTER ENDPOINT MirroringEndPoint STATE = STARTED

Establecer Mirroring

Servidor Reflejo

ALTER DATABASE Inventario

SET PARTNER ='TCP://ServerProduccion:10111'

Servidor Principal

ALTER DATABASE Inventario

SET PARTNER ='TCP://ServerReflejo:10111'

En este momento el estado de la base de datos inventario reflejada será Synchronized y tendrá el siguiente aspecto en Server Management Studio:

Probar Mirroring

Se puede producir un fallo en la base de datos principal para observar que la base de datos reflejo pasa del estado Synchronized a un estado normal que nos permita acceder a tablas y registros.

En la base de datos principal ejecutar:

USE master

ALTER DATABASE Inventario SET PARTNER FAILOVER

Con esta línea se produce un fallo en la base de datos principal y se podrá observar que la base de datos reflejo pasa a un estado normal, dejando accede a las tablas y registros.

Para volver a levantar la base de datos principal y que la de reflejo pase a estado Synchronized, se deberá ejecutar en la principal:

USE master

ALTER DATABASE Inventario SET PARTNER RESUME

Ajustar el tiempo de sincronismo

El valor por defecto de sincronización entre la base de dato principal y reflejo es 20 segundos, este valor se puede modificar. Para ello debemos conectarnos al servidor principal y ejecutar la siguiente consulta

ALTER DATABASE Inventario SET PARTNER TIMEOUT 10

Acciones con Mirroring

1. Parar Mirroring

En el servidor de reflejo se deberá ejecutar la consulta

ALTER DATABASE Inventario SET partner suspend

2. Reanudar Mirroring

En el servidor de reflejo se deberá ejecutar la consulta

ALTER DATABASE Inventario SET partner resume

3. Cancelar Mirroring

En el servidor de reflejo se deberá ejecutar la consulta

ALTER DATABASE Inventario SET partner off

4. Reactivar Mirroring

Realizar el paso 4.3 de este mismo apartado.

Obtener información del mirroring
Para ver la descripción básica del estado del mirroring ejecutar la sentencia:

SELECT

DB_NAME(database_id) AS 'inventario'

, mirroring_role_desc

, mirroring_safety_level_desc

, mirroring_state_desc

, mirroring_safety_sequence

, mirroring_role_sequence

, mirroring_partner_instance

, mirroring_witness_name

, mirroring_witness_state_desc

, mirroring_failover_lsn

FROM sys.database_mirroring

WHERE mirroring_guid IS NOT NULL;

El resultado será:

Reiniciar archivo de log (ldf)
Opción A)

Seleccionar la Base de datos con la que se va ha operar.

USE inventario

Para que se graben en disco todas las páginas desfasadas (las que están en caché y todavía no están en el disco)

CHECKPOINT

Se ha creado una copia de la información del archivo de log con lo que se ha liberado alguno de los VLF(Virtual Log File)

BACKUP LOG inventario WITH truncate_only

Este comando libera espacio físico si hay algún VLF vacío.

El primer argumento es el nombre lógico del archivo que queremos reducir y el 100 es el tamaño objetivo en Mb.

DBCC SHRINKFILE (inventario, 100)

Opción B)

Parar el reflejo de la base de datos.

Poner la base de datos en modo "single user".

ALTER DATABASE inventario SET SINGLE_USER

Ejecuta el comando CHECKPOINT varias veces para asegurar que todas las páginas de memoria se han escrito en el fichero de datos.

Desconectar todas las sesiones de la base de datos, ponerla fuera de conexión.

Ejecutar la línea siguiente para desconectar la base de datos:

sp_detach_db inventario

Renombrar archivo ldf.

Ejecutar la siguiente línea para reconectar la base de datos.

EXEC sp_attach_db @dbname = N'inventario',

@filename1 = N'X:\...\Inventario.mdf'

Microsoft SQL Server creará un nuevo fichero de log.

Poner la BB. DD. en estado de conexión.

            ALTER DATABASE inventario SET MULTI_USER

Eliminar EndPoints.
Verificar el estado de los endpoints mediante ejecutando:
            USE master
            GO
            SELECT * FROM sys.database_mirroring_endpoints

El resultado nos indica entre otras cosas, el nombre del endpoint y el estado.

Se deben eliminar los endPoint en ambos servidores
DROP EndPoint [MirroringEndPoint]

El Foso Del Sarlacc

SQL Mirroring con MS SQL Server 2005

No hay comentarios:

Publicar un comentario