Guía para crear réplica de mezcla con filtros dinámicos parametrizados en SQL SERVER
Introducción
Una réplica de mezcla es usada cuando el intercambio de datos entre servidores es bidireccional, o sea, tanto el publicador (servidor donde están los datos a replicar) como el suscriptor (servidor que recibe los datos) pueden actualizar los datos publicados y los mismos son cambiados periódicamente.
Las tablas o artículos con filtros parametrizados permiten realizar particiones de los datos a replicar, lo cual puede eliminar la posibilidad de cualquier conflicto entre los servidores de suscripción y el publicador, siendo visibles las actualizaciones de los datos para el suscriptor en cuestión.
Las aplicaciones para puntos de venta son un ejemplo de uso de réplica de mezcla con filtros parametrizados. Para este tipo de problema es importante lograr un dinamismo en la configuración de la réplica ya que de lo contrario por cada punto de venta habría que crear una suscripción. Esto se logra a través de la función HOST_NAME().
En la presente guía solo se tendrán en cuenta algunas opciones que propone la configuración de la réplica de mezcla dado el objetivo de la aplicación donde fue utilizada la misma. Para realizar la réplica fue utilizado SQL Server 2008 R2.
La base de datos escogida SalesDB es un ejemplo ilustrativo. La misma contiene la tabla SalesPoints la cual será replicada ya que contiene los datos para cada punto de venta.
Guía de la práctica
PASOS PARA CREAR LA PUBLICACIÓN:
1. Si el publicador tiene activado algún antivirus debe deshabilitarlo mientras se configura la réplica, esto en algunos casos evita conflictos en la sincronización.
2. En este caso el publicador es el mismo que el distribuidor (servidor que hace de puente para las suscripciones), el distribuidor puede ser configurado, pero debe tener cuidado con las publicaciones creadas anteriormente en el mismo servidor.
3. En el entorno del SQL Server Management Studio (SSMS) del publicador, hacer clic derecho en "Local Publications", y seleccionar "New Publication".
4. Marcar la base de datos que se va a replicar.
5. Escoger el tipo de publicación: "Merge publication"
6. Escoger las versiones de compatibilidad para la réplica según versiones de SQL de los servidores de suscripción.
7. Escoger la(s) tabla(s) ("articles") que se quieran publicar.
8. Después de seleccionar la tabla, clic en "Article Properties" y escoger "Set Properties of Highlighted Table Article" o la segunda opción si quiere poner la misma propiedad para todas las tablas. Más adelante se verá cómo utilizando la cláusula WHERE se puede seleccionar los datos a publicar dentro de una tabla, lo cual crea particiones de la misma que la réplica maneja según si una partición va dirigida a múltiples suscriptores ("overlapping") o si va dirigida a un solo suscriptor ("nonoverlapping"). En la opción "Partition Options" puede seleccionar esta opción, para nuestro caso es "Nonoverlapping, single subscription".
Ver otras propiedades como "Action if name is in use" y "Synchronization direction", así como los permisos para insertar ("Insert"), borrar ("Delete") y actualizar ("Update") para cambiar el estado de la mezcla. Esto es porque el Agente de Mezcla ("Merge Agent") tiene acceso a los datos publicados usando el Publication Access List (PAL), si quisiera que otro usuario de servidor SQL tuviera estos permisos basta con agregarlos a esta lista.
9. Clic en la pestaña "Resolver" y marcar la opción que aparece para resolver los conflictos interactivamente durante la sincronización entre el publicador y el suscriptor.
10. Dado que la réplica de mezcla utiliza un UNIQUEIDENTIFIER para manejar las filas, se agregará una nueva columna a las tablas a replicar llamada "rowguid" de tipo "uniqueidentifier".
11. En este paso se agregarán los filtros dinámicos para replicar las filas según el suscriptor de destino. Seleccionar "Add Filter" y lo siguiente será escoger, en la sección 1. La tabla a la cual aplicarle el filtro; sección 2. Construir el filtro especificando la columna de la tabla y haciendo uso de la cláusula WHERE (se puede usar las mismas cláusulas definidas en SQL); sección 3. Se chequeará la opción "A row will only go to one subscription", la misma debe estar marcada por defecto debido a que se escogió la propiedad como "Nonoverlapping, single subscription".
Para construir el filtro puede utilizar cláusulas definidas en la sintaxis de SQL como JOIN así como funciones predefinidas. En este caso es utilizada la función HOST_NAME() que devuelve un nvarchar con el nombre del servidor de suscripción. Para utilizar la misma en filtros de construcción más compleja puede utilizarse una conversión de esta función. Esta función permite el dinamismo del filtro, ya que en lugar de crear una publicación con filtro estático para cada suscriptor, o sea para cada punto de venta, se crea una publicación en la cual se puede identificar al suscriptor usando esta función y comparando con un id dentro de la tabla de interés.
12. Seleccionar la planificación del agente de instantánea ("Snapshot Agent"). El "Snapshot Agent" se ejecuta en el publicador y es el encargado de captar instantáneas o "snapshot" de cada tabla o partición a replicar. El mismo se puede cambiar cliqueando en el botón "Change…" si aparece chequeada la segunda opción. La frecuencia de ejecución se puede decir cada cierto día, hora, minuto o segundo.
En esta misma ventana puede generar los "snapshots" inmediatamente o no. Como aconseja el mensaje que aparece, si piensa cambiar las propiedades del "snapshot" no la cree inmediatamente. En este caso no la generaremos porque más adelante cambiaremos la dirección de la carpeta donde se guardarán los "snapshots".
13. Especifique la configuración relacionada con la seguridad para el "Snapshot Agent". En esta parte se especificará la cuenta de usuario a usar para que sean creados los "snapshots" y la cuenta de SQL necesaria para acceder a la base de datos. Otra opción es utilizar una cuenta del SQL Agent que debe estar activo en todo momento (chequear si es preciso).
14. Seleccionar "Create the publication".
15. Identificar la publicación con un nombre y seleccionar finalizar.
16. Cerciorarse que la creación de la publicación no tuvo problema alguno
Modificaremos algunas propiedades de la publicación:
Al hacer clic derecho sobre la publicación en el SSMS, seleccione "Properties" y en el panel de la izquierda vaya a la opción "Subscription Options". Cerciorase que ambas opciones poseen los valores indicados como se indica en la siguiente imagen.
En la opción "Data Partitions" es donde se predefinen los identificadores de las particiones. El valor otorgado a una partición en el "HOST_NAME value", será el identificador que tendrá el suscriptor, devolviendo el mismo en cada llamada a "HOST_NAME()" mediante los filtros. Esto permite la creación dinámica de los "snapshots" para cada una de las particiones.
En la siguiente imagen se predefine un suscriptor cuyo valor es 22. (Recordar que este valor es de tipo nvarchar). Además puede editar el horario de inicialización de la sincronización del suscriptor con el publicador usando el botón "Edit".
En la opción "Snapshot" aparecerá para cambiar la dirección de la carpeta donde son guardados los archivos "snapshots". En la sección "Location of snapshot files", la carpeta por defecto que aparecerá es la especificada por el distribuidor, donde se almacenan todos los "snapshots" de las publicaciones creadas siendo el distribuidor el mismo que el publicador. Esta carpeta puede ser cambiada seleccionando "Put files in the following folder". Mediante esta opción los "snapshots" de esta publicación serán guardadas en esa carpeta sin modificar las carpetas de almacenamiento de las demás publicaciones. Para cambiar esta última vea "Distributor Properties".
Una vez realizados todas las modificaciones a las propiedades de la publicación, puede iniciar el "Snapshot Agent" para que cree los "snapshots" siguiendo los cambios. Para ello al hacer clic derecho sobre la publicación en el SSMS, seleccione "View Snapshot Agent Status". Utilizando el botón "Start" comienza la creación de los "snapshots" y el botón "Monitor" es utilizado para visualizar el comportamiento del proceso de la réplica de los datos.
Pasos para crear la suscripción
Corresponde crear las suscripciones a la publicación realizada para que los datos puedan ser replicados correctamente a cada suscriptor, en este caso cada suscriptor es un punto de venta.
1. En "Local Publication", hacer clic derecho sobre el nombre de la publicación ("SalesDataPublication") y seleccione "New Subscription".
2. A continuación escoger la publicación a la cual se va a suscribir. Si el publicador no aparece por defecto, lo puede escoger, así como también la publicación a la cual se quiere suscribir.
3. Escoger el tipo de suscripción, o sea, si es "push subscriptions" o "pull subscriptions". La diferencia radica en que para el primer caso todos los agentes de mezcla se ejecutarán en el distribuidor (que es el mismo publicador para esta configuración) y en el segundo caso el suscriptor ejecutará su agente de mezcla. Cada uno tiene sus objetivos y su selección varía dependiendo del problema.
4. Escoger el servidor de suscripción y la base de datos de suscripción, si no aparece listado seleccione "Add SQL Server Subscriber…", debe suministrar una cuenta de SQL . Para este ejemplo la suscripción se realizó desde otro servidor y la base de datos[1]se nombra SalesDBPoint22.
5. Especificar las opciones de seguridad tanto de la conexión al publicador como al suscriptor. En este caso se utilizó el "SQL Server Agent service account".
6. Lo siguiente es especificar el horario de sincronización al publicador. Escogiendo la opción "", puede definir la frecuencia, en días, horas o minutos de la misma.
7. Escoger el modo de inicializar la suscripción según el "snapshot" de los datos publicados. Si no quiere inicializarlo hasta no tener completada la configuración del snapshot, puede desmarcar la opción "Initialize".
8. Seleccionar el tipo de suscripción para la resolución de conflictos. En la réplica de mezcla la suscripciones pueden ser "Server" o "Client". La suscripción de tipo "Server" se utiliza para establecer una prioridad a cada suscriptor, asignándole un valor comprendido entre 0.00 y 99.99. La suscripción de tipo "Client" se establce cuando se quiere que todos los suscriptores tengan la misma prioridad, el valor establecido por defecto es 0.00. Para ver más sobre el tema consulte http://technet.microsoft.com/en-us/library/ms151749%28v=sql.105%29.aspx.
9. En la siguiente ventana aparecerá para sobreescribir el valor de HOST_NAME(), que por defecto es el nombre del servidor de suscripción. Se escribirá 22, que ya fue establecido en nuestro caso para el punto de venta.
10. Escoger la opción "Create the subscription" y puedes finalizar la suscripción para su creación.
Ahora puede chequear insertando datos en la base de datos de réplica y los mismos son replicados según el valor del HOST_NAME().
Conclusiones
La réplica de mezcla con filtro es utilizada en ambientes cliente-servidor donde es importante tener una correcta distribución de los datos tratando que no existan conflictos entre las particiones de datos a replicar y que la comunicación sea continua en ambos sentidos. Esto depende en gran medida del problema en cuestión y de la configuración que se le dé a la réplica.
Al finalizar la práctica pudimos comprobar que, utilizando datos reales, la configuración cumplió los objetivos para el cual fue creada esta réplica y que tanto el cliente como el servidor podían actualizar sus datos de forma periódica y la visualización de los mismos era efectiva en ambas terminales. Igualmente se probó su buen funcionamiento para más de un cliente.
Autor:
Lic. Dahiana Torres Pablos[2]
Lic. Marcel Domínguez Domínguez1
Empresa de Tecnologías de la Información y la Automática. Departamento de Aplicaciones.
Cuba, 2013
[1] La base de datos de la suscripci?n debe tener el mismo dise?o de la base de datos de la publicaci?n.
[2]