Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes.

SQL Server dispone de múltiples tipos de replicación de datos que nos ayudarán a automatizar la duplicación de datos. Habitualmente estos datos son duplicados para sacar un beneficio de ello. Un ejemplo típico es el desacoplamiento de cargas de lectura y de escritura, donde desde una instancia de lectura/escritura distribuimos datos a N servidores de solo lectura. Otro escenario típico de replicación implica la duplicación de datos entre una oficina central y un conjunto de oficinas distribuidas geográficamente.En este post vamos a mostrar paso a paso cómo configurar una replicación transaccional unidireccional explicando durante el proceso de configuración los roles implicados así como los distintos agentes. El primer paso que debemos realizar es configurar la distribución de datos en nuestro publicador. El servidor publicador será aquel servidor (central, principal, etc.) que contendrá los datos que deseamos replicar a los servidores suscriptores (secundarios, oficinas, etc.). Para configurar la distribución utilizaremos la opción “Configure Distribution…” directamente desde Management Studio:

Indicaremos que el distribuidor de nuestra instancia será la misma instancia que estamos utilizando para publicar los datos:

Esta configuración es válida cuando la carga de nuestra réplica no es elevada, el número de suscriptores no es muy alto y no tenemos funcionalidades de alta disponibilidad no compatibles con el rol de distribución. Por ejemplo podemos tener esta configuración sin problemas montada en un cluster pero si utilizamos Database Mirroring como mecanismo de alta disponibilidad deberemos utilizar otra instancia independiente como distribuidor. También es habitual que cuando tenemos un entorno con múltiples publicadores se centralice la configuración en un único distribuidor.

A continuación indicaremos que el agente de SQL Server se configure para arrancar automáticamente (si no lo teníamos ya configurado de esta forma). La relación entre el agente de SQL Server y la replicación es muy íntima ya que el agente es el responsable de la ejecución de los agentes de replicación (meros ejecutables con parámetros) mediante el uso de jobs de SQL Server.

Para inicializar una réplica habitualmente utilizamos lo que se llama una instantánea. Una instantánea contiene todos los datos que vamos a replicar que están presentes en un instante en el tiempo. Una vez que se ha generado una instantánea se utilizará para inicializar los suscriptores y poder continuar la replicación de datos desde ese punto en el tiempo. Como símil podemos pensar en que una instantánea corresponde con un backup completo de los datos a replicar sobre los que posteriormente aplicaremos los “log de transacciones” pendientes hasta dejar los suscriptores sincronizados. Indicaremos la ruta donde queremos almacenar las instantáneas, siendo recomendable que la ruta sea una ruta de red para facilitar el acceso de la instantánea desde los suscriptores:

Elegiremos la ubicación para los ficheros de datos y del log de transacciones de nuestra base de datos de distribución e indicaremos que deseamos que nuestra instancia sea un publicador autorizado:

Una vez configurada la distribución procederemos a crear nuestra publicación. Para ello desplegaremos el nodo “Replication” del Object Explorer de la instancia e indicaremos con el menú contextual del nodo “Local Publications” que queremos crear una nueva:

Elegiremos la base de datos que contiene los artículos (tablas, vistas, procedimientos, etc.) que vamos a replicar. Debemos tener en cuenta que si deseamos replicar datos de distintas bases de datos lo deberemos realizar en distintas publicaciones ya que una publicación puede contener únicamente objetos de una base de datos. Seleccionaremos por ejemplo AdventureWorks:

Indicaremos que vamos a utilizar replicación transaccional. En la replicación transaccional se garantiza el orden de las transacciones así como la consistencia de dichas transacciones. Para ellos se marcarán en el log de transacciones los cambios que afecten a los artículos publicados de forma que el agente logreader pueda llevar dichos cambios de forma consistente a la base de datos de distribución.

A continuación seleccionaremos los artículos a replicar. En nuestro caso seleccionaremos únicamente una tabla, la tabla de contactos, para ser replicada:

Es interesante que revisemos si las propiedades de replicación del artículo por defecto se ajustan a nuestras necesidades. Para ello utilizaremos el botón “Article Properties” para desplegar la ventana de propiedades:

Una modificación habitual a estas propiedades por defecto es activar la copia automática de los índices no-clustered. Por defecto el artículo contara únicamente con el índice cluster si éste existiera. También puede ser interesante habilitar la opción de copiar las restricciones check (“copy check constraints”) para facilitar al optimizador los planes de ejecución de algunas consultas.

Una vez configuradas las propiedades, continuaremos con los filtros de datos:

En ocasiones no deseamos replicar todo el conjunto de datos de la tabla. Por ejemplo podemos desear mantener únicamente los datos a partir de una fecha o bien los correspondientes a un país concreto. Por ejemplo si quisiéramos filtrar únicamente aquellos contactos cuyo título sea “Mr.” podríamos crear un filtro de este tipo:

A continuación configuraremos el agente de instantáneas (snapshot) para que nos genere una instantánea al finalizar el asistente. De esta forma podremos inicializar un suscriptor tan pronto como la instantánea finalice:

A continuación indicaremos las cuentas de seguridad que utilizarán los agentes. Como buena práctica deberemos generar usuarios específicos para cada uno de los agentes y dar a cada uno de ellos los permisos necesarios. Aunque no lo recomendamos para un sistema en producción, para simplificar en este ejemplo, utilizaremos la misma cuenta que utilizamos para el servicio del agente de SQL Server:

Finalmente, crearemos la publicación que hemos configurado. En esta pantalla tenemos una opción muy útil de generar un script con todos los pasos necesarios para recrear la replicación. Como hemos comprobado, son muchos los pasos a seguir y es fácil, cuando tenemos unas cuantas decenas de artículos replicados con propiedades distintas, equivocarnos al configurar una réplica con el interfaz de usuario. Además recomendamos guardar dicho script para si tenemos un desastre y necesitamos por ejemplo restaurar nuestra base de datos en otro servidor poder regenerar nuestra réplica rápidamente.

Una vez creada la publicación, procederemos a crear una suscripción nueva. Para ello sobre la nueva réplica utilizaremos la opción contextual de crear nueva suscripción:

Para comenzar seleccionaremos el publicador, nuestra instancia donde configuramos la publicación, y la base de datos publicada:

A continuación elegimos la modalidad de la suscripción, push o pull. Las suscripciones push son aquellas en las que el agente de distribución, responsable de entregar las transacciones del distribuidor al suscriptor, se ejecuta en el distribuidor. Las suscripciones pull son aquellas en las que el agente de distribución se ejecuta en el suscriptor. La elección de una modalidad u otra dependerá de diversos factores. Por ejemplo si tenemos un alto número de suscriptores, 100 por ejemplo, puede ser recomendable utilizar suscripciones pull para evitar saturar al distribuidor con la ejecución de 100 agentes, uno por cada suscriptor. Sin embargo si el número es bajo puede interesarnos centralizar los agentes en el distribuidor y de esa forma tener más control sobre la ejecución de los agentes. En nuestro ejemplo elegiremos crear una suscripción push:

Elegiremos la instancia y la base de datos donde se replicarán nuestros datos:

De nuevo utilizaremos la seguridad del proceso del agente para simplificar la conectividad. Recordemos que lo recomendable es impersonar una cuenta específica para los agentes de replicación:

Aunque lo habitual es que los agentes de distribución se ejecuten de forma continua en el caso de la replicación transaccional, también podemos definir una planificación específica de forma que por ejemplo únicamente distribuyan cambios por la noche de 8 PM a 6 AM. Elegiremos que ejecute el agente continuamente y continuamos:

Finalmente indicaremos cuando deseamos que se inicialice la suscripción a partir del snapshot. Es posible que creemos la suscripción en un momento del día distinto al que queremos que inicialice. En este ejemplo indicaremos que se inicialice de forma inmediata:

Al igual que ocurría al crear la publicación, con la suscripción tenemos también la opción de realizar la operación directamente y de generar un script con la configuración utilizada. Es útil disponer de dichos scripts para casos de desastre o si deseamos configurar N suscriptores con la misma configuración utilizar dicho script editando los parámetros correspondientes para facilitar la labor:

Finalmente tenemos nuestra suscripción creada:

Por último nos quedará comprobar con el monitor de replicación que todo está funcionando correctamente. Lanzaremos el monitor de replicación desde el menu contextual del nodo “Replication”:

Podemos ver como se ha generado correctamente el snapshot y el agente Log Reader está en marcha:

Si pasamos a la pestaña de las suscripciones podemos ver que está funcionando correctamente el agente de distribución:

Si hacemos doble-click sobre la publicación veremos los detalles de la inicialización mediante el uso de la instantánea:

Como hemos podido ver la configuración de una replicación transaccional no es compleja aunque sí requiere de un conjunto de pasos bastante extenso. Existen además multitud de parámetros y configuraciones que modifican su comportamiento por lo que debemos ser cautos con el uso de la replicación por las implicaciones que pueda tener.

Con la práctica y el uso de scripts es viable generar publicaciones y suscripciones en pocos segundos bajo demanda o incluso utilizar una modalidad especial de suscripciones anónimas donde exponemos una publicación y son los suscriptores los que se encargan de suscribirse/desuscribirse a nuestra publicación.