SQL Server Merge Replication FTP: la mejor guía para la sincronización remota de bases de datos

Microsoft SQL Server es una base de datos con muchas funciones relacionadas con la replicación, la duplicación y los clústeres de conmutación por error. Son todas estrategias y configuraciones que tienen el propósito, de diferentes maneras, de hacer que los datos estén siempre disponibles para las diversas aplicaciones en caso de fallos o interrupciones, o simplemente para replicar datos en varias ubicaciones geográficas para aumentar el rendimiento en accesibilidad y disponibilidad.
En este tutorial no hablaremos sobre Failover Clustering o Availability Groups Always On, que son procedimientos de duplicación de bases de datos con el objetivo principal de asegurar la disponibilidad continua de todos los servicios de manera inmediata en caso de fallos del servicio. Por el contrario, nos centraremos en los procedimientos de replicación de la base de datos, es decir, todas aquellas configuraciones que permiten a los usuarios tener todos los datos replicados en más Servidores SQL, de forma automática y en tiempo real.

SQL Server proporciona 4 tipos de procedimientos de replicación:
• Transaccional
• Merge
• Snapshot (instantánea)
• Peer to Peer (punto a punto)

La replicación Peer to Peer es una solución orientada a la escalabilidad horizontal con alta disponibilidad, ya que permite administrar muchas copias de datos en más instancias del servidor, llamadas “nodos”. En función de la replicación transaccional, la replicación punto a punto propaga los cambios de datos a todos los nodos y casi en tiempo real. Gracias a esta redundancia, las aplicaciones que solicitan la escalabilidad horizontal de las operaciones de lectura pueden dividir a los clientes en procesos de lectura en nodos múltiples, y acceder a los datos de una manera mucho más rápida. Este tipo de replicación es uno de los procedimientos de replicación más efectivos y “a prueba de choques”, ya que la falta de disponibilidad de uno o más nodos no afecta el funcionamiento y la disponibilidad de todo el sistema.

Sin embargo, la replicación Peer to Peer solo está disponible en la versión Enterprise de SQL Server, con costos de licencia que obviamente son muy caros en relación con otras versiones. Una buena opción alternativa, al alcance de las pequeñas y medianas empresas, estable y flexible de configurar, es la réplica de Merge, de la que hablaremos ahora.

Comencemos de inmediato con la práctica, empezando con lo que necesitamos para configurar una réplica de Merge y luego cómo configurarla en unos sencillos pasos.

Consideremos 3 SQL Server 2014, geográficamente distantes y ejecutándose en diferentes redes. El esquema final que obtendremos es el que se muestra en la imagen siguiente:

Debe haber un servidor primario principal llamado Publisher (publicador) y servidores secundarios llamados Subscribers (suscriptores). Los suscriptores sincronizarán sus propios datos con el publicador (envío y recepción) y, como consecuencia de esto, también los datos de cada suscriptor estarán sincronizados con los demás. Por lo tanto, lo primero que debe hacer es decidir cuál de los tres servidores actuará como Publisher y luego proceda con su configuración, como se explica a continuación.

En primer lugar, debemos asegurarnos que el servicio del Agente SQL Server se inicie y configure en modo automático, sino es así lo iniciaremos.

PASO 1: cree la publicación en el servidor del Publisher

Comenzaremos a configurar todas las diferentes opciones dentro de la ventana “Nuevo asistente de publicación”. Ahora vamos directamente a la primera ventana:

Ahora seleccionemos la base de datos que queremos sincronizar:

Elijamos entonces el tipo de replicación que queremos configurar, es decir, “Merge publiaction“:

En la siguiente ventana, mantendremos las opciones de compatibilidad predeterminadas y pulsaremos en “Siguiente”:

Ahora hay un paso muy importante en nuestra instalación: elegir qué tablas de la base de datos queremos sincronizar. Seleccionarlas es muy simple:

Pasando a la siguiente ventana, el asistente nos advertirá que se agregará un nuevo campo de ID a las tablas seleccionadas para identificar de forma única a los registros.

Pasemos ahora a la siguiente ventana, donde también se nos preguntará si queremos agregar filtros a los datos que se sincronizarán. En este tutorial no utilizaremos ningún filtro, así que simplemente haga clic en “Siguiente”:

En la siguiente ventana, debe tener marcadas ambas opciones, como se establece de forma predeterminada, y luego procedamos:

Ahora hay otro paso importante de nuestra instalación, que es la configuración de las cuentas para ejecutar el servicio de replicación (agente) y conectarse al publicador (Publisher), por lo tanto, en este caso, a sí mismo.

En la ventana de la derecha, vamos a escribir el nombre de usuario del administrador del servidor, debe ir precedido del nombre del servidor o el nombre de dominio. En este tutorial, consideremos que el nombre del servidor es “PUBLISHER”.

En la ventana más a la izquierda, pongamos la cuenta de SQL Server, con la cual es posible conectarse a la base de datos con privilegios máximos. En este tutorial utilizamos el usuario SQL predeterminado “sa”.

Nota: lo mejor que se puede hacer es tener el mismo nombre de usuario de SQL Server y la misma contraseña en todo el servidor de la base de datos (tanto en el publicador como en los suscriptores).

Confirmaremos todos los datos haciendo clic en Aceptar y vayamos a la siguiente ventana.

En la siguiente ventana, mantendremos marcada la opción “Crear la publicación” y haga clic en “Siguiente”:

Finalmente, se mostrará el resumen de todas las configuraciones y ahora podemos crear la publicación haciendo clic en “Finalizar”:

Se abrirá una ventana de progreso de la instantánea inicial y las dos operaciones se completarán correctamente.

PASO 2: Configure cómo el servidor de Publisher permite a los suscriptores acceder a los datos publicados: Servidor FTP

Una de las configuraciones de replicación más fundamentales es cómo los suscriptores pueden acceder a los datos “publicados” por el servidor publicador. Físicamente hablando, el publicador crea periódicamente una instantánea de sus datos en una carpeta local y luego la actualiza con cambios en los datos. Por lo tanto, debemos asegurarnos de que los suscriptores puedan tener acceso completo a esta instantánea, de modo que puedan sincronizar sus datos con el publicador. Como en nuestro tutorial hablamos de servidores remotos (por lo tanto, no dentro de la misma LAN), la forma en que elegimos hacer que los datos estén disponibles para los suscriptores es el FTP (ya que sería imposible usar una carpeta simple compartida en la red).

Luego, debemos configurar en el editor un servidor FTP que haga disponible de forma remota, para los suscriptores, la carpeta donde se encuentra la instantánea de datos. Podemos usar tanto el servidor FTP de IIS, que es el predeterminado de Windows, como uno de terceros, como el Servidor FileZilla. Para la configuración de FTP dentro de IIS, consulte la guía oficial de Microsoft https://technet.microsoft.com/it-it/library/hh831655(v=ws.11).aspx.

En primer lugar, tenemos que hacer un cambio en la publicación que acabamos de crear, para habilitar FTP como método de acceso a los datos publicados:

Establezcamos la configuración de conexión (la configuración debe reflejar la configuración del servidor FTP). Escribamos el nombre del servidor FTP (el mismo que el nombre del servidor), el puerto (21 el predeterminado, pero debido a razones de seguridad también podríamos especificar uno diferente), el nombre de usuario y la contraseña del FTP (si estamos usando IIS), elegiremos un nombre de usuario de Windows, asegurándonos de que sea un administrador, por el contrario, si estamos usando otro servidor FTP, usemos el usuario que configuramos dentro de él). Finalmente, especifiquemos la ruta relativa de la carpeta que contiene la instantánea, considerando que comienza desde la raíz del servidor FTP. Al configurar este procedimiento, SQL Server crea (de forma predeterminada) una carpeta llamada “ftp” (con la instantánea de datos dentro) en la ruta “C:\Archivos de programa\ Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\repldata”. Esta ruta debe configurarse como raíz del servidor FTP y, por lo tanto, la ruta relativa a establecer dentro de esta ventana será “/ftp”, como se muestra en la siguiente imagen.

Nota: será necesario abrir el puerto FTP utilizando una regla específica en el Firewall de Windows del servidor publicador. Además, si se producen errores de conexión a pesar de la correcta configuración del firewall en el publicador, esto también podría deberse al firewall del suscriptor. Para verificar esto, intente desactivar completamente el firewall del suscriptor y luego vuelva a intentar la sincronización. Posteriormente, puede volver a activar el firewall y eventualmente agregar las reglas apropiadas.

Nota 2: un error en la sincronización (en el suscriptor) podría significar que esta configuración de FTP no se realizó correctamente. El suscriptor, al acceder a los datos, podría encontrar un error genérico, lo que provocaría una interrupción anormal del agente / trabajo de sincronización (errores como “el agente no se está ejecutando” o fallos junto con volcados de memoria). En estas situaciones, no nos deje engañar por mensajes de error, que podrían referirse a problemas completamente diferentes, pero revisemos nuevamente esta configuración. Una primera prueba podría ser conectarse desde el servidor remoto (un suscriptor) con cualquier otro software de cliente FTP, como FileZilla, para asegurarse de que la conexión esté bien y de que las carpetas estén accesibles.

Nota 3: la carpeta “C:\Archivos de programa\Microsoft SQL Server\MSSQL12.MSSQLSERVER\ MSSQL\repldata” y su subcarpeta relativa “/ftp” pueden no ser accesibles debido a problemas de permisos. En caso de problemas que parezcan no fácilmente reconocibles, hagamos una prueba inmediatamente configurando los permisos de esta carpeta en “Todos” -> “Control total”.

Ahora, todas las configuraciones se han realizado. Comprobemos con “Ver el estado del agente de instantáneas” que las instantáneas están hechas correctamente y con “Iniciar el monitor de replicación” para ver si los suscriptores se sincronizan correctamente (hasta que no configuremos un suscriptor, permanecerá vacío).

Si en Publisher todo está configurado correctamente, configuremos un suscriptor y luego realicemos la primera sincronización de datos.

PASO 3: Crear la suscripción en los suscriptores

Requisito previo: asegurémonos de que el servicio del Agente SQL Server se esté ejecutando en los suscriptores.

Pasemos ahora al SQL Server que se conectará al servidor principal como suscriptor y luego sincronizará los datos. Dentro del menú “Replicación”, haga clic con el botón derecho en “Suscripciones locales” y luego en “Nuevas suscripciones”.

En cuanto a la publicación, aparecerá una página del asistente:

En la siguiente ventana, tenemos que conectarnos al servidor de Publisher para ver y seleccionar su publicación (la que hicimos antes):

Aquí hay algunos pasos fundamentales de configuración para garantizar una conexión correcta. De hecho, cuando buscamos un publicador, podemos conectarnos solo especificando el nombre del servidor, pero no la dirección IP (con o sin un puerto específico). Esto significa que, en este sistema, tenemos que “mapear” de algún modo la dirección IP del servidor publicador utilizando su nombre. Podemos hacer esto creando un Alias dentro de los servicios de la ventana de configuración SQL Server, como se muestra en la siguiente imagen:

Agreguemos un alias con el nombre del servidor remoto de Publisher, su dirección IP y el puerto del servidor SQL, en las dos ramas “Alias”.

Esto permitirá conectar servidores SQL remotos solo con nombres y no con direcciones IP. Es fundamental abrir el puerto de SQL Server (aquí podemos ver el predeterminado) en los servidores. Al hacer clic en “Buscar editor de SQL Server”, se nos pedirá que nos conectemos al servidor editor de SQL, y luego se le solicitará la ventana de autenticación estándar de SQL Server:

Una vez autenticado, podremos ver la publicación que hemos creado en el editor, luego seleccionarla y continuar:

Avancemos al siguiente paso y conservemos las opciones predeterminadas:

En la siguiente ventana, seleccionemos la base de datos para sincronizar. Podemos crear esta base de datos en el suscriptor simplemente restaurando una copia de la base de datos principal que tenemos en el publicador.

Como se muestra en la imagen, hay que indicar tanto el nombre del servidor del suscriptor (lo hemos llamado “SUBSCRIBER” en este ejemplo) como el nombre de la base de datos.

Posteriormente, establezcamos las credenciales necesarias para la autenticación. Usaremos la cuenta de administrador de Windows en el primer panel y la cuenta de SQL Server en el segundo (como lo hicimos anteriormente con el publicador).

Vayamos al próximo paso, que consiste en configurar CUÁNDO para ejecutar la sincronización de datos. Es posible configurarlo en tiempo real (sincronización continua) o programarlo definiendo un intervalo de tiempo. Es solo una programación estándar de Windows. Si necesitamos que los datos se sincronicen continuamente, también podemos elegir un intervalo de 10 segundos. De lo contrario, un intervalo de minutos / horas / días también será correcto.

En la siguiente ventana, establezcamos que la suscripción se inicialice de inmediato:

Finalmente, configuremos al suscriptor para que se ejecute también en el modo de servidor: esto significa que el suscriptor no solo recuperará los datos del publicador, sino que también le enviará sus propios datos, lo que implica una sincronización bidireccional. Mantengamos la opción predeterminada para la prioridad de resolución de conflictos:

Vayamos a la última ventana y haga clic en “Finalizar” para crear la suscripción e iniciar la sincronización:

Comprobemos si la sincronización funciona correctamente solo abriendo la ventana de estado de sincronización (ver imagen a continuación):

¡La sincronización se está ejecutando correctamente!

Siguiendo exactamente todos los pasos de este tutorial, hemos configurado correctamente una sincronización de datos entre dos (o más) servidores SQL remotos sin ningún problema.

Pueden surgir problemas debido a la compleja configuración, pero con algunos pequeños trucos es posible hacer que todo sea muy estable y eficiente.

En el sitio web de Microsoft, podemos encontrar mucha documentación sobre cómo optimizar y configurar la replicación de la base de datos:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/enhance-general-replication-performance?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/enhance-merge-replication-performance?view=sql-server-2017

 

(Inglés, Italiano, Francés, Alemán, Portugués, Brasil)