Configurando Log Shipping

SQL Server

https://msdn.microsoft.com/es-es/sqlserver/hh291511

Introducción

El hecho de que Log Shipping sea una de las tecnologías que antes vinieron de la mano con las primeras versiones de SQL Server, no quita que sea siendo muy válido en una gran cantidad de soluciones a problemas reales de la actualidad. Pero log Shipping tiene un requerimiento que lo limita de entrada a funcionar en un entorno gestionado bajo un único Active Directory (o al menos entre varios AD, pero con confiabilidad). En este artículo vamos a ver cómo podemos personalizar un entorno en el que sin disponer de confiabilidad entre varios AD, podamos hacer funcionar Log Shipping.

Log Shippingconsiste en automatizar el proceso de restauración de una copia de seguridad del registro de transacciones en otra base de datos en otra máquina.

Ejemplos en los que Log Shipping encaja como tecnología a implementar serían los siguientes:

• Centralización de todas las BBDD de las "instancias sql satélite" de nuestra empresa, bajo un único servidor, posibilitando que se realice una explotación posterior de los datos mediante herramientas de inteligencia de negocio.
• Disponibilidad redundante de la información físicamente en diferentes ubicaciones
• Necesitamos un entorno con múltiples nodos secundarios de solo lectura
• Necesitamos un sistema de configuración sencilla y mínimo coste de mantenimiento.
• Disponemos de un entorno de conectividad intermitente

Algunas de las características más interesantes de Log Shipping se podrían resumir en las siguientes:

• Permite que el envío de información sea programado fácilmente a intervalos definidos y regulares, de forma que no haga falta una conexión abierta constante, minimizando el riesgo de problemas ante cortes de conexión.
• • Si ocurre algún error en el envío (por ejemplo no hay conexión) se seguirá intentando en los siguientes envíos programados
• Permite la monitorización automática del estado de la configuración, minimizando la labor de los administradores de base de datos.
• Permite múltiples bases de datos secundarias
• Permite utilizar cualquier BBDD secundaria como respaldo de forma que en caso de caída del servidor principal, se pueda levantar como servidor principal
• Se permite la configuración automática a través de despliegues de scripts T-SQL minimizando errores de intervención manual.

En definitiva, podemos apoyarnos en log shipping, para realizar arquitecturas de comunicaciones que faciliten la consolidación de información, siguiendo esquemas como estos:

Resumen de funcionamiento de Log Shipping

A grandes rasgos, los pasos que realiza Log Shipping de forma automática son los siguientes:

1. Crear copia del registro de transacciones de la base de datos BD en el servidor secundario X con periodicidad configurable.
2. Copiar dicho backup en el servidor de copia
3. Restaurar dicho backup en la base de datos BD del servidor secundario con la periodicidad que se requiera, dejándola en modo NORECOVERY o STANDBY (sin o con acceso de solo lectura respectivamente)

Junto con estos pasos, existen otros como el de eliminar copias antiguas a un periodo establecido (por ejemplo cada 4 días).

Nota: En este artículo vamos a referirnos como instancia principal a la instancia que tiene el rol principal e instancia secundaria a la instancia que asume el rol de instancia secundaria (la que albergará la copia)

Configuración de Log Shipping

La configuración estándar de Log Shipping está pensada para un entorno de Active Directory por lo que la situación se complica si la instancia secundaria posee su propio Active Directory totalmente independiente del de la instancia principal y sin confiabilidad.

El problema principal que existe en una configuración Log Shipping entre servidores que no forman parte de un mismo dominio de Active Directory es la imposibilidad de conceder acceso a las carpetas donde se encuentran los backups del log de transacciones a restaurar en el servidor destino. Por poner un ejemplo práctico, el usuario que levanta el servicio del agente de SQL Server en el servidor secundario, es quien debe disponer de acceso de lectura sobre la carpeta de red donde el servidor principal se encuentra realizando los backups. Esto que es sencillo en una topología gestionada bajo el amparo de un dominio active directory se complica cuando no es así, siendo necesario configurar "pass-throw security" o configurar confianza entre dominios.

La configuración de "pass-throw security" no suele ser buena idea plantearla puesto  que al margen de los potenciales problemas de seguridad que produce, se requiere el mantenimiento extra de un nuevo servidor para minimizar impactos y además deja de ser una solución transparente, puesto que se debería realizar algún tipo de configuración de sistemas entre ambos servidores. Esto, cuando hablamos del orden de centenas de instancias, se convierte en inviable.

Lo mismo ocurre con la confiabilidad entre AD. Cuando hablamos de centenares de instancias, la configuración de confiabilidad puede tornarse muy problemática.

Una vez veamos cómo funciona Log Shipping, veremos que se puede realizar una pequeña modificación de Log Shipping para adaptarla a este tipo de entornos. Como veremos con esta solución, conseguiremos un menor coste de implementación, mantenimiento y un reaprovechamiento de la tecnología existente.

El concepto principal de la configuración personalizada a implementar, consiste en que en lugar de que sean los servidores secundarios los que traigan los ficheros de backup del log de transacciones (comportamiento estándar), sea la instancia principal, la que copie a un repositorio FTP en la instancia secundaria. Esto además se implementa dejando intacta la parte de gestión que realiza el propio ejecutable sqllogship.exe, consiguiendo que la gestión de copias de seguridad y restauraciones sea llevada a cabo con normalidad, así como que funcionen los reportes de estado de Log Shipping y las alertas de servidor.´

NOTA: En este artículo utilizaremos FTP por tratarse de un sistema de sencilla configuración. En cualquier caso, note el lector que podría utilizarse cualquier sistema-servicio para gestionar las copias de ficheros entre entornos no confiables

Recordemos, que en la mayoría de entornos, existen múltiples servidores principales y un único servidor secundario, de forma que en la mayoría de escenarios, solo existirá un único servidor FTP.

Por tanto, la solución que se plantea en este artículo posibilita que con unos cambios e implementación mínimos, toda la arquitectura y gestión de Log Shipping de serie funcione con normalidad.

Para que los pasos de creación de Log Shipping personalizado que se explican más adelante puedan ser llevados a cabo, hay que cumplir los siguientes prerrequisitos:

1. Tener un servidor ftp dentro de nuestro dominio de instancia secundaria (por ejemplo DominioContoso). Dicho servidor a partir de ahora y para ilustrar un ejemplo lo más real posible, le daremos ip 172.16.2.249. Por seguridad, se recomienda que solo sea accesible a través de la VPN y sin acceso a extranet. Además se recomienda que se encuentre en un servidor diferente al que albergue al SQL Server.
2. Tener la cuenta que levanta el servicio SQL server Agent con un usuario del dominio DominioContoso en lugar de un usuario local.
3. Compartir la carpeta del ftp para que tenga acceso de lectura/escritura el usuario que levanta el agente de SQL Server desde dentro del dominio DominioContoso. En el ejemplo, dicha carpeta será \\172.16.2.249\backups_ftp  (solo accesible desde dentro del dominio DominioContoso)
4. Disponer de una herramienta de copia de ficheros (a la que por ejemplo llamaremos en este artículo FileManager)

NOTA: Crear un fichero .bat que utilice xcopy podría ser suficiente. No vamos a realizar mención a ninguna herramienta comercial o similar puesto que se entiende que es algo fácil de implementar.

Los pasos a seguir para realizar la configuración personalizada de Log Shipping son prácticamente igual que si no se hubiera necesitado realizar la configuración personalizada y consisten en lo siguiente:

1. Asegurarnos que el agente de SQL Server tanto en la instancia principal, como en la secundaria se encuentra activo.
2. Asegurarnos que durante el proceso de configuración de Log Shipping, no existe ningún Job en el agente de la instancia principal que pueda realizar ningún backup. Una vez finalizada la configuración de Log Shipping, procederemos a activarlo de nuevo.
3. Realizar un backup completo de la BBDD que queremos configurar en Log Shipping
   
   Paso 1:
   
   
   
   Paso 2:
   
   
   
   
4. Copiar dicho archivo de backup al servidor que va a tener el rol secundario (se puede copiar por ftp, por carpeta compartida, desde terminal services,…) y restaurarlo en dicho servidor asegurándonos de marcar la casilla "RESTORE WITH STANDBY" si queremos tener acceso de lectura a la BBDD y que las celdas de "Restaurar como" apuntan al directorio elegido como carpeta para ficheros de datos y carpeta para ficheros de log de transacciones.
   Para la ruta de archivo en espera, se puede utilizar también la ruta de ficheros de datos.
   
   Paso 1:
   
   
   
   Paso 2
   
   
   
   
5. Una vez tenemos la BBDD restaurada en la instancia secundaria en modo solo lectura, podemos comenzar la configuración de Log Shipping. Para ello entramos en la zona de configuración de Log Shipping de la BBDD, dentro del apartado "propiedades" de dicha BBDD (en la instancia principal)
   
   
   
   
6. Ahora marcamos que se habilite para configuración en Log Shipping
   
   
   
   
7. Pulsamos sobre el botón "Configuración de copia de seguridad…" y procedemos a configurar dónde se realizará la copia de seguridad.
   
   En este apartado es donde entra en juego la primera de las acciones para permitir Log Shipping en nuestra topología. Para ello, vamos a rellenar tanto la ruta de red donde se va a realizar la copia de seguridad (recuadro superior en rojo), como la ruta local donde lo va a hacer en la instancia principal.

   o   En la ruta de red, vamos a poner una ruta COMPARTIDA DENTRO DE LA RED DE la instancia secundaria (dominio DominioContoso) y a la que obviamente no va a tener permisos de lectura ni escritura el equipo de la instancia principal. Dicha ruta compartida, será además, la que albergue el directorio de ftp donde se realizará el copiado de ficheros (más adelante se explica cómo).

   o   En la ruta local, pondremos por seguir un estándar, una carpeta en c:\log_Shipping (que previamente tendremos que haber creado en la instancia principal, ya que debe existir)

   
   
   
   
   
   La dirección "\\172.16.2.249\Backups_ftp" es la ruta donde se encuentra tanto la carpeta ftp, como la carpeta compartida de Windows (en nuestro caso, recordemos que se trata de la ruta \\172.16.2.249\Backups_ftp\ ). Dicha IP se corresponde con una máquina del dominio DominioContoso (en instancia secundaria) a la que no puede acceder la máquina de la instancia principal.
   Esto es así porque internamente, en los metadatos de configuración de Log Shipping, quedará marcado que los backups del log se encuentran en\\172.16.2.249\Backups_ftp\ , que solo será utilizado por el Job de copia (LSCopy_%) cuando se ejecute en el servidor secundario. Pero realmente, el proceso de backup no tocará para nada la ruta de red \\172.16.2.249\Backups_ftp (a la cual el servidor no tiene acceso realmente) y realizará el backup contra la ruta local c:\log_Shipping. Más adelante, se configurará el paso para copiar por ftp el contenido de c:\log_Shipping a la instancia secundaria.
8. El siguiente paso será configurar la eliminación de archivos, alertas y cuándo queremos que se lance el proceso de backup: Como ejemplo, podemos configurar la eliminación de archivos cada 4 días y las alertas cada 48 horas. El proceso de backup se configurará para lanzarse cada 1h. Esto obviamente dependerá del requerimiento de cada caso y nunca debe tomarse al pié de la letra.
9. Una vez configurado el proceso de backup, procederemos a configurar la copia y restauración. Para ello nos conectaremos al servidor que realizará el rol de secundario pinchando sobre el botón "Agregar…"
   
   
   
   
10. Una vez aparece el formulario de configuración de la base de datos secundaria, pulsamos sobre "Conectar.." y nos conectamos a la instancia que va a tener el rol de base de datos secundaria.
11. Cuando conectamos, automáticamente nos saldrá la BBDD secundaria que estamos configurando en Log Shipping (en este caso "prueba_ls") y la opción marcada "No, la base de datos secundaria está inicializada"
    
    
    
    
    La razón de esto es que en el paso 4, hemos restaurado ya la BBDD en modo standby sobre el servidor secundario.
12. Ahora nos vamos a la pestaña "Copiar archivos" y ahí indicamos, la ruta local al servidor secundario, donde queremos que sean copiados los ficheros .trn que se supone se encuentran en \\172.16.2.249\Backups_ftp. Es decir, que internamente Log Shipping va a ir a \\172.16.2.249\Backups_ftp, va a copiar lo que allí se encuentre a la ubicación que se especifique aquí, y será de aquí donde se realice la restauración (siguiente pestaña)
    
    
    
    
    
13. Una vez especificada dicha ruta, se realizará la programación de horas a las cuales querremos que se realice el proceso de copia y cada cuanto tiempo queremos que se eliminen los archivos copiados de la carpeta de destino (no nos borrará lo que exista en el ftp).
14. Ahora, en la pestaña de "Restaurar registro de transacciones", procederemos a indicar que el modo de restauración va a ser "Modo de espera" y que queremos "desconectar los usuarios en la base de datos al restaurar las copias de seguridad"
    
    
    
    
    El modo de espera nos dejará la base de datos en modo solo lectura (necesario para la consolidación) y el check de desconexión es para que la restauración se pueda realizar con éxito, sino puede fallar si existe alguien conectado. Podemos elegir Modo sin recuperación, si lo que queremos es únicamente un Log Shipping como respaldo de alta disponibilidad, pero este no es nuestro caso de ejemplo.
    Hay que recalcar que si existe alguna conexión abierta en el momento de la restauración, al haber marcado el check, dicha conexión será abortada en el momento de comenzar la restauración. Por lo tanto habrá que tenerlo en cuenta para que los posibles procesos de Business Intelligence que puedan existir en un futuro lo tengan en cuenta.
15. Por último solo queda planificar las horas en las que queremos realizar la restauración y aceptar la configuración.
    El proceso de restauración solo restaura aquellos ficheros que han sido copiados al 100%, por lo que si se intenta restaurar antes de finalizar la copia, no se restaurará (se restaurará la siguiente vez). Se puede configurar sin ningún problema varias restauraciones, puesto que como se ha indicado, si se intenta restaurar y todavía no se puede, a la siguiente vez que se pueda se hará sin problemas.
16. Dado que en el paso 1 puede que hayamos deshabilitado algún Job que hiciera backups de la BBDD en la instancia principal, procederemos a volver a habilitarlo puesto que ya no interferirá con nuestra configuración de Log Shipping.

Personalización de Log Shipping en instancia secundaria

Llegados a este punto, la configuración de Log Shipping ya ha sido realizada, pero no se están realizando restauraciones en el servidor secundario, puesto que en la carpeta \\172.16.2.249\Backups_ftp, no existen los archivos de backup del log de transacciones, dado que se encuentran en c:\log_Shipping

Lo que hemos de hacer es modificar el Job de backup que se genera automáticamente en la instancia de SQL Server principal, para que se ejecute el paso de copia por ftp de c:\log_Shipping, a ftp://172.16.2.34/  (que recordemos que internamente al dominio DominioContoso, es una carpeta compartida como\\172.16.2.249\Backups_ftp.

Para ello hemos de seguir los siguientes pasos:

1. Editar el Job LSCopy_% (donde % será el nombre de la BBDD) que se encuentra en la instancia SQL Server de la instancia principal pulsando botón derecho -> propiedades
   
   
   
   
2. Ahora entramos en el apartado "pasos" y pulsamos sobre "nuevo…"
   
   
   
   
3. En este momento, le damos un nombre al paso "ftp" por ejemplo, y seleccionamos que lo que vamos a lanzar va a ser un comando CMD
   
   
   
   
4. Como comando cmd, introducimos el código de copia de ficheros que utilizaremos. Recordemos que es fácil mediante xcopy generarlo
   Dicho comando, será el encargado ni más ni menos que de copiar el contenido de la carpeta c:\log_Shipping a ftp://172.16.2.34/ (la carpeta compartida en el dominio DominioContoso como \\172.16.2.249\Backups_ftp
5. El siguiente paso es indicar que después de ejecutar el paso 1, queremos ir al paso 2
   Para ello lo que haremos será pinchar sobre el paso 1 y darle a "editar"
   
   
   
   
6. Ahora nos iremos al nodo "avanzado" y elegiremos que en caso de éxito, continúe la ejecución con el paso 2
   
   
   
   
7. Aceptaremos cambios y ya tendremos listo el sistema de Log Shipping a falta de únicamente programar el borrado de los archivos generados en el ftp.

 Para borrar los archivos del ftp (ya en la instancia secundaria), crearemos un job de SQL Server que elimine dichos ficheros, donde especificaremos el periodo de retención de archivos. De esta forma podremos mantener en el ftp aquellos archivos con fecha menor de X días.  Para ello:

1. Nos conectaremos a la instancia secundaria
2. Crearemos un nuevo Job
3. Incluiremos el código del ejecutable que sea capaz de borrar ficheros. De nuevo una alternativa viable podría ser utilizar un paso CMD con un comando "del" estándar
4. Programaremos para que sea ejecutado una vez al día.

NOTA: Solo es necesario crear un único Job de borrado de ficheros del ftp. Planificar el borrado de la carpeta de  \\172.16.2.249\Backups_ftp para que se borren los archivos en función de la disponibilidad de espacio en disco.