Blog Archive

SQL-Server

 
Rubén Garrigós

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

2011-08-02 23:14:00 por Rubén Garrigós

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:

 

SQL Server transactional replication configuration: Configurar distribución 1

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

SQL Server transactional replication configuration: Configurar distribución 2

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.

SQL Server transactional replication configuration: Configurar distribución 3

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:

SQL Server transactional replication configuration: Configurar distribución 4

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:

 

SQL Server transactional replication configuration: Configurar distribución 5

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:

SQL Server transactional replication configuration: Configurar una publicación

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:

SQL Server transactional replication configuration: Configurar una publicación 2

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.

SQL Server transactional replication configuration: Tipo de publicación

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

SQL Server transactional replication configuration: Artículos publicados

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:

SQL Server transactional replication configuration: Propiedades de artículos

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:

SQL Server transactional replication configuration: Filtrado de artículos

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:

SQL Server transactional replication configuration: Filtrado de artículos 2

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:

SQL Server transactional replication configuration: Generación de instantánea

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:

SQL Server transactional replication configuration: Seguridad del agente de instantánea

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.

SQL Server transactional replication configuration: Script de la publicación

SQL Server transactional replication configuration: Creación de la publicación

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:

SQL Server transactional replication configuration: Configuración de la subscripción

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

 SQL Server transactional replication configuration: Seleccionar el publicador

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:

SQL Server transactional replication configuration: Seleccionar el modelo push o pull

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

SQL Server transactional replication configuration: Seleccionar base de datos suscrita

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:

SQL Server transactional replication configuration: Seguridad del agente de distribució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:

SQL Server transactional replication configuration: Ejecución del agente de distribución

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:

SQL Server transactional replication configuration: Ejecución del agente de distribución 2

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:

SQL Server transactional replication configuration: Script de la subscripción

Finalmente tenemos nuestra suscripción creada:

SQL Server transactional replication configuration: Creación de la subscripción

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”:

SQL Server transactional replication configuration: Monitor de replicación

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

SQL Server transactional replication configuration: Monitor de replicación 2

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

SQL Server transactional replication configuration: Monitor de replicación 3

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

SQL Server transactional replication configuration: Monitor de replicación 4

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.

Comments

#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes
Wednesday, August 3, 2011 - 03:16 PM by JQ
Muy buen post Rubén, Gracias!
#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes.
Tuesday, November 8, 2011 - 09:53 PM by Andris Marte
Hola Rubén, estoy leyendo tu post para fines de edificacion en el tema de replicacion, y tengo una pregunta: porque tu mencionas que si tengo el Database Mirror instalado en mi Servidor de Produccion ese server no lo puedo tomar como Distribuidor tambien al momento de yo configurar replicacion Transaction - Standard - ? -------------- Hola Andris El problema que existe es que el distribuidor requiere que se mantenga el nombre de la instancia sin cambios cuando se realiza el failover. Esto no es posible con mirroring ya que no existe un nombre virtual que nos ofrezca esta transparencia. Por tanto si queremos disponer de un mecanismo de alta disponibilidad y que nos funcione con la distribución, éste deberá ser el que proporciona una instancia clusterizada.
#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes.
Wednesday, November 9, 2011 - 07:51 PM by Andris Marte
okkk Gracias Ruben por la repuesta de manera muy rapida, lo que sucede es que estoy evaluando la necesidad de montar Replicacion Transaccional para fines de reporteria. y yo en mi ambiente de produccion tengo mi server en cluster con una instancia virtual y mis base de datos actualmente la tengo en mirror, pero ahora tengo la necesidad de configurar replicacion con este esenario que tengo me es posible? Quizas estoy repitiendo lo que dices pero me gustaria que me responda. gracias!! ---------------- Hola de nuevo, Si es posible. Si el fin es únicamente configurar la réplica para el testeo de la solución para reportes no veo problema. Es decir, configurarías la réplica sobre la instancia que actua como principal en el mirror, de la misma forma que lo harías sin tener mirroring. Si por alguna razón tuvieras que balancear al secundario el mirroring entonces la replicación dejaría de funcionarte temporalmente hasta que o bien la reconfiguraras en el otro servidor o bien la volvieras a balancear de vuelta al servidor donde originalmente se configuró.
#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes.
Wednesday, November 9, 2011 - 09:22 PM by Andris Marte
Mil Gracias nuevamente Rubén, mira soy nuevo y de verdad me ayuda bastantes con tus repuestas. Ahora me gustaría en la medida de lo posible hacerte otra pregunta. yo necesito iniciar una replicacion transacional standard. pero la base de datos es muy grande 250gb y la verdad que por la red no puedo llevarla. yo eh leido que puedo hacerlo sin la necesidad de crear un snapshot y cambiando la opcion "allow Initialization from backup files setting to - True".. ok todo bien hasta ahora. creo un full backup y lo transporto hacia donde está el "Suscriptor Server", restauro el full backup en Recovery Mode y entonces a partir de ahi puedo syncronizar con la ayuda de un storeprocedure "sp_addsubscription".. ok todo bien hasta ahora... lo que pasa es que yo solo quiero tener replicada 8 tablas y el full backup tiene toda mi base de datos que incluye 55 tablas. La pregunta es: Yo del lado del suscriptor server puedo eliminar las tablas que no necesito replicar del y entonces cuando replico solo replico las actualizaciones de las 8 que si necesito? ----------- Efectivamente, no habría problema en que borraras todas aquellas tablas que no se repliquen ya que la réplica no las necesita. Ten en cuenta sin embargo posibles dependencias derivadas del uso de claves ajenas/foráneas, triggers, etc.
#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes.
Thursday, November 10, 2011 - 01:47 PM by Andris Marte
Waooo muchas gracias Rubén agradecido de tí. de verdad gracias! en la semana proxima te escribo nuevamente por si tengo alguna pregunta. que voy a montar un ambiente de prueba el cual tiene DB Mirror & Replicacion .. Gracias!!
#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes.
Monday, November 14, 2011 - 02:41 PM by Andris Marte
Hola Rubén, aquí otra vez... Fijate inicie las pruebas de inicializar una replicacion desde un backup utilizando la opcion de "allow Initialization from backup files setting" y con el storeprocedure "sp_addsubscription"; pero ahora mismo no se como actualizar mi suscriptor desde el distribuidor. - Como actualizo periodicamente mi suscriptor si en el storeprocedure que utilice para configurar el sync_type="allow Initialization from backup files setting" y nó en sync_type="automatic"? - Tambien tengo la interrogante de si tengo que utilizar un snapshot nuevo..?? -------- Las actualizaciones del subscriptor no vienen determinadas por el método de inicialización, son cosas independientes. Respecto a la utilización de un snapshot no tiene sentido ya que es precisamente lo que estás tratando de evitar con la inicialización mediante backup (crear un snapshot y transmitirlo por la red al subscriptor) Creo que tienes muchas dudas como para plantearte poner en producción el sistema que planteas. La replicación debe siempre tenerse muy en cuenta ya que no se trata de algo tan transparente como un DBmirroring por ejemplo. Te recomendaría que comenzaras leyendo bien la documentación del producto respecto a la réplica transaccional (http://technet.microsoft.com/en-us/library/ms151706.aspx) y que siguieras con algun libro/tutorial sobre replicación para asentar los conocimientos básicos. Una vez tengas ya cierta soltura con la replicación transaccional en tu entorno de pruebas configura y prueba bien la replicación en un entorno que simule tu entorno de producción antes de desplegarla.
#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes.
Tuesday, November 15, 2011 - 07:30 PM by Andris Marte
Muchas Gracias Rubén por el link.. de verdad gracias!!
#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes.
Wednesday, November 16, 2011 - 08:21 PM by Andris Marte
Hola Rubén Otra inquietud pero antes dejame decirte que estuve en la difución del webcats de Alta Disponibilidad y me gustó bastante.... Pude realizar la replicación transaccional sin inconvenientes, Bueno la inquietud es que realmente tengo una duda sobre el siguiente esenario. Yo eliminé una linea en la base de datos que tengo en el suscriptor Server y pensaba que cuando sincronizaba desde el distribuidor hacia el suscriptor esa linea se agregaba sin problemas. que me puedes decir en cuanto a este caso!. ----------------- La replicación transaccional típica es unidireccional y trabaja sobre el log de transacciones. Es decir únicamente los cambios que realices en el publicador (inserts, deletes y updates) serán replicados en el subscriptor (a través del distribuidor) pero no viceversa. Tampoco hay razón alguna para que una fila que no se ha modificado, borrado o insertado en el publicador tenga que replicarse. El cambio que has hecho en el subscriptor (que deberías tratarlo como "solo lectura" en este escenario y no modificarlo) te puede traer problemas. Por ejemplo si ahora hicieras un update sobre dicha fila que únicamente existe en el publicador tendrías un problema con la réplica ya que ella presupone que esa fila aún existe. Te recomiendo por tanto que "compenses" el delete que has hecho con un insert en el subscriptor de forma manual para volver a dejar la información sincronizada cuanto antes para evitar futuros problemas. Una vez hecho esto asegúrate validando que el subsrciptor está 100% correcto. Más información:http://technet.microsoft.com/en-us/library/ms152758.aspx
#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes.
Wednesday, November 16, 2011 - 09:45 PM by Andris Marte
Hola Rubén, realmente lo que tu dices tiene bastante sentido. gracias otra vez!!
#re: Replicación transaccional en SQL Server: Una historia de publicadores, suscriptores y agentes
Tuesday, December 13, 2011 - 09:51 AM by Jose
Hola. Tengo un problema con la replicación transaccional. He seguido paso a paso todo el proceso y obtengo un error: El proceso no pudo hacer inserciones masivas en la tabla... El caso es que en la base de datos de replica se crea la tabla (artículo) pero no se rellena con los datos. Gracias. ----------------- Hola Jose, Tiene toda la pinta de ser un problema de permisos. Asegúrate que la cuenta que utiliza el distribuidor para conectar con el subscriptor dispone de los permisos necesarios. Prueba a añadir el usuario al rol db_owner de la base de datos subscrita. Rubén
#re: replicación transaccional en sql server: una historia de publicadores, suscriptores y agentes.
Wednesday, August 20, 2014 - 12:41 AM by Carlos
Hola Ruben Junto con saludarte tengo el siguiente problema con la configuracion de la replica tengo 2 escenario de configuracion de replica : 1.- Configuracion de replica local BD1 a Bd2 con actualizacion masiva cada 15 dias y este opera bien pero con lentitud en la transferencia de la informacion. 2.- Configuracion de replica BD1 a suscriptor fuera del pais, el cual al momento de una actualizacion de informacion este no replica la informacion en los tiempos adecuados ya que para 5000 comando se de mora 10 hrs en replicas, por lo cual me genera un problema importante en la recepcion de la informacion en destino, hemos probado la configuracion Scall en update, comportamiento lento, xcall comportamiento replica mas rapido pero no lleva toda la informacion. por lo cual cada vez que tenemos un proceso que actualiza masivamente se nos genera un cuello botella en donde necesitamos realizar cuadratura manual de la informacion. Si tienes algun tip que nos pueda ayudar a realizar un seguimiento al problema te lo agradezco. -------------------------------------------------- Hola Carlos, Lo primero sería tener claro ese proceso masivo de actualización que comentas en que consiste exactamente. Qué comandos y sobre que artículos se actúa. La cantidad de 5000 comandos (inserts, updates o deletes) parecen poca cosa pero igual hay sorpresas. Por ejemplo se me ocurre que las tablas a su vez pueden tener triggers en destino que realizan acciones pesadas. O las tablas pueden tener una gran cantidad de índices que ralenticen la operación. También podría ser que esos 5000 comandos realmente lleven mucha información asociada, por ejemplo inserts en tablas con columnas de tipo binario y sean muy pesados de transmitir por la red. En ese caso quizás simplemente sea un problema de ancho de banda. También podría ser que la máquina destino sea mucho más lenta (disco, memoria, CPU, etc.) que la máquina origen. Mi recomendación es que, por un lado, configures los agentes para que guarden información extra en el log (http://support.microsoft.com/kb/312292) ya que ahi puede que aparezcan pistas. Por otro, cuando ocurra ese proceso masivo conectar SQL Profiler en el suscriptor para analizar exactamente la ejecución de los comandos de la réplica para ver más en detalle lo que está ocurriendo. También podrías configurar un perfil para el agente más agresivo en lo que respecta al número de comandos por batch (CommitBatchSize) De todas formas 5000 comandos me siguen pareciéndome muy pocos para causar problemas de ese tipo (este tipo de ajustes normalmente son necesarios cuando hablamos de cientos de miles o millones de comandos). Por último si no encontráis la solución o no veis claro por donde seguir podéis contactar con SolidQ para una colaboración puntual y hacer un troubleshooting más a fondo sobre vuestro entorno. Un saludo, Rubén
#re: replicación transaccional en sql server: una historia de publicadores, suscriptores y agentes.
Wednesday, August 20, 2014 - 07:14 PM by German Rodriguez
Hola Ruben muchas gracias por el post, muy bueno
#re: replicación transaccional en sql server: una historia de publicadores, suscriptores y agentes.
Thursday, September 25, 2014 - 11:42 PM by German Rodriguez
Excelente Ruben, muchas gracias
Leave a Comment
(*) Title:
(*) Name:
Your URL:
(*) Comments:
Follow us on: