Guía para la sincronización de datos en Microsoft SQL Server

Compartir información relacionada entre sistemas aislados se ha vuelto cada vez más importante para las organizaciones, ya que les permite mejorar la calidad y disponibilidad de los datos. Hay muchas situaciones en las que es útil tener un conjunto de datos disponible y consistente en más de un servidor de directorio. Por eso es importante conocer los métodos comunes para realizar la sincronización de datos de SQL Server.

La disponibilidad y la consistencia de los datos se pueden lograr mediante procesos de replicación y sincronización de datos. La replicación de datos es el proceso de crear una o más copias redundantes de una base de datos con el fin de mejorar la tolerancia a fallas o la accesibilidad. La sincronización de datos es el proceso de establecer la consistencia de datos entre dos o más bases de datos, y las actualizaciones continuas posteriores para mantener dicha consistencia.

En muchas organizaciones, realizar la sincronización de datos en diversos sistemas es deseable y desafiante. Podemos encontrar muchos casos de uso donde necesitamos realizar sincronización de datos:

• Migración de base de datos
• Sincronización periódica entre sistemas de información
• Importación de datos de un sistema de información a otro
• Mover conjuntos de datos entre diferentes etapas o entornos
• Importación de datos desde una fuente que no es una base de datos

No existe una forma única o un método acordado por unanimidad para la sincronización de datos. Esta tarea difiere de un caso a otro, e incluso las sincronizaciones de datos que deberían ser simples a primera vista pueden resultar complicadas debido a la complejidad de las estructuras de datos. En escenarios reales, la sincronización de datos consta de muchas tareas complejas, que pueden tardar mucho tiempo en realizarse. Cuando surge un nuevo requisito, los especialistas en bases de datos generalmente tienen que volver a implementar todo el proceso de sincronización. Dado que no existen formas estándar de hacer esto, además de la replicación, las implementaciones de sincronización de datos rara vez son óptimas. Esto resulta en un mantenimiento difícil y mayores gastos. La implementación y el mantenimiento de la sincronización de datos es un proceso que consume tanto tiempo que puede ser un trabajo de tiempo completo en sí mismo.

Podemos implementar la arquitectura para tareas de sincronización de datos manualmente, posiblemente utilizando Microsoft Sync Framework, o podemos beneficiarnos de soluciones ya creadas dentro de las herramientas para administrar Microsoft SQL Server. Intentaremos describir los métodos y herramientas más comunes que se pueden usar para resolver la sincronización de datos en las bases de datos de Microsoft SQL Server e intentaremos dar algunas recomendaciones.

Con base en la estructura de la fuente y el destino (por ejemplo, bases de datos, tablas) podemos diferenciar los casos de uso cuando las estructuras son similares o diferentes.

El origen y el destino tienen estructuras muy similares

Este suele ser el caso cuando usamos datos en varias etapas del ciclo de vida del desarrollo de software. Por ejemplo, la estructura de datos en los entornos de prueba y producción es muy similar. El requisito común es comparar datos entre la base de datos de prueba y producción e importar datos de la producción a la base de datos de prueba.

El origen y el destino tienen estructuras diferentes

Si las estructuras son diferentes, la sincronización es más complicada. Esta es también una tarea que se repite con más frecuencia. Un caso común es importar de una base de datos a otra. El caso más común es cuando una pieza de software necesita importar datos de otra pieza de software que es mantenida por otra empresa. Por lo general, las importaciones deben ejecutarse automáticamente de forma programada.

El método utilizado depende de las preferencias personales y la complejidad del problema que necesita resolver.

Independientemente de cuán similares sean las estructuras, podemos elegir cuatro formas diferentes para resolver la sincronización de datos:

• Sincronización mediante scripts SQL creados manualmente
• Sincronización mediante el método de comparación de datos (solo se puede utilizar cuando el origen y el destino tienen una estructura similar)
• Sincronización mediante secuencias de comandos SQL generadas automáticamente: se necesita un producto comercial

El origen y el destino tienen estructuras iguales o muy similares

Uso de secuencias de comandos SQL creadas manualmente

La solución más sencilla y tediosa es escribir manualmente scripts SQL para la sincronización.

Ventajas

• Se puede realizar con herramientas gratuitas y de código abierto (FOSS).
• Si la tabla tiene índices, es muy rápido.
• El script SQL puede guardarse en un procedimiento almacenado o ejecutarse periódicamente como un trabajo para SQL Server.
• Se puede utilizar como una importación automática, incluso en datos que cambian continuamente.

Desventajas

• Crear una secuencia de comandos SQL de este tipo es bastante tedioso, porque generalmente se necesitan tres secuencias de comandos para cada tabla: INSERT , UPDATE y DELETE .
• Solo puede sincronizar datos que están disponibles a través de consultas SQL, por lo que no puede importar desde fuentes como archivos CSV y XML.
• Es difícil de mantener: cuando se cambia la estructura de la base de datos, es necesario modificar dos o tres scripts ( INSERT , UPDATE y, a veces, también DELETE ).

Ejemplo

Haremos una sincronización entre la tabla Source , con las columnas ID y Value , y la tabla Target , con las mismas columnas.

Si las tablas tienen la misma clave principal y la tabla de destino no tiene una clave principal de incremento automático (identidad), puede ejecutar el siguiente script de sincronización.

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

Uso del método de comparación de datos

En este método, podemos usar una herramienta para comparar entre datos de origen y de destino. El proceso de comparación genera secuencias de comandos SQL que aplican las diferencias de la base de datos de origen a la base de datos de destino.

Hay una serie de programas para la comparación y sincronización de datos. Estos programas en su mayoría utilizan el mismo enfoque. El usuario selecciona la fuente y la base de datos de destino, pero otras alternativas podrían ser una copia de seguridad de la base de datos, una carpeta con scripts SQL o incluso una conexión a un sistema de control de fuente.

A continuación se encuentran las herramientas más populares que utilizan el enfoque de comparación de datos:

• Comparación de datos de dbForge para SQL Server
• Comparación de datos SQL de RedGate
• Diferencia de datos SQL de Apex

En el primer paso, se leen los datos, o simplemente se leen sumas de verificación de datos más grandes del origen y del destino. Luego se ejecuta el proceso de comparación.

Estas herramientas también ofrecen configuraciones adicionales para la sincronización.

Necesitamos configurar las siguientes opciones de configuración que son necesarias para la sincronización de datos:

Clave de sincronización

De forma predeterminada, se utiliza la clave principal o una restricción UNIQUE . Si no hay una clave principal, puede elegir una combinación de columnas. La tecla Sync se utiliza para emparejar filas del origen con filas del destino.

Maridaje de mesa

De forma predeterminada, las tablas se emparejan por nombre. Puede cambiar esto y emparejarlos según sus propias necesidades. En el software dbForge Data Compare, puede elegir que la consulta SQL sea el origen o el destino.

Proceso de sincronización

Después de confirmar, la herramienta compara los datos de origen y de destino. Todo el proceso consiste en descargar todos los datos de origen y de destino y compararlos según criterios específicos. De forma predeterminada, se comparan los valores de tablas y columnas con el mismo nombre. Todas las herramientas admiten la asignación de nombres de columnas y tablas. Además, existe la posibilidad de excluir columnas IDENTITY (autoincremento) o hacer algunas transformaciones antes de comparar valores (tipos flotantes redondos, ignorar mayúsculas y minúsculas, tratar NULL como una cadena vacía, etc.) La descarga de datos está optimizada. Si el volumen de datos es grande, solo se descargan las sumas de comprobación. Esta optimización es útil en la mayoría de los casos, pero los requisitos de tiempo para realizar operaciones aumentan con el volumen de datos.

En el siguiente paso, hay un script SQL con migraciones generadas. Este script se puede guardar o ejecutar directamente. Para estar seguros, incluso podemos hacer una copia de seguridad de la base de datos antes de ejecutar este script. La herramienta ApexSQL Data Diff puede crear un programa ejecutable que ejecuta el script en una base de datos seleccionada. Este script contiene datos que deben cambiarse, no la lógica de cómo cambiarlos. Esto significa que el script no se puede ejecutar automáticamente para proporcionar una importación recurrente. Esa es la mayor desventaja de este enfoque.

Ventajas

• No se requiere un conocimiento avanzado de SQL y se puede hacer a través de la GUI.
• Tiene la capacidad de verificar visualmente las diferencias entre las bases de datos antes de la sincronización.

Desventajas

• Es una característica avanzada de los productos comerciales.
• El rendimiento disminuye cuando se transfieren enormes volúmenes de datos.
• El script SQL generado solo contiene diferencias y, por lo tanto, no se puede reutilizar para sincronizar automáticamente datos futuros.

A continuación puede ver la interfaz de usuario típica de estas herramientas.

Sincronizar con SQL generado automáticamente

Este método es muy similar al método de comparación de datos. La única diferencia con el método anterior es que no hay comparación de datos y el script SQL generado no contiene diferencias de datos, sino lógica de sincronización. El script generado se puede guardar fácilmente en un procedimiento almacenado y se puede ejecutar periódicamente (por ejemplo, cada noche). Este método es útil para importaciones automáticas entre bases de datos. El rendimiento de este método es mucho mejor que el método de comparación de datos.

La sincronización mediante SQL generado automáticamente solo la proporciona SQL Database Studio.

SQL Database Studio proporciona una interfaz similar al método de comparación de datos. Necesitamos seleccionar la fuente y el destino (bases de datos o tablas). Luego, debemos configurar las opciones (claves de sincronización, emparejamiento y mapeo). Hay una función de creación de consultas gráficas para configurar todos los parámetros.

Ventajas

• No se requieren conocimientos avanzados de SQL.
• Puede configurar todo en una GUI con bastante rapidez.
• El script SQL resultante se puede guardar en un procedimiento almacenado.
• Se puede usar como importación automática, como un trabajo para SQL Server.

Desventajas

• Es una característica avanzada de los productos comerciales.
• Las diferencias no se pueden verificar manualmente antes de la sincronización, porque todo el proceso se ejecuta en un solo paso.

Puntos de referencia de rendimiento

Caso de prueba

Dos bases de datos (A y B), cada una de las cuales contiene una tabla con 2 000 000 de filas. Las tablas están en dos bases de datos diferentes en el mismo SQL Server. Esta prueba cubre dos casos extremos: 1) La tabla de origen contiene las 2 000 000 filas y la tabla de destino está vacía. La sincronización necesita proporcionar muchos INSERTS . 2) Las tablas de origen y de destino contienen 2 000 000 de filas. La diferencia está solo en una fila. La sincronización debe proporcionar solo una UPDATE .

RedGate Data Compare necesita 3 pasos:

• Comparar
• Generar guión
• Ejecutar script en la base de datos de destino

ApexSQL Data Diff necesita 2 pasos:

• Comparar
• Generar script y ejecutar script en un solo paso

SQL Database Studio realiza toda la sincronización en un solo paso. A continuación se muestran los tiempos de sincronización, en segundos. En la columna etiquetada como "pasos individuales" se encuentran las duraciones de los pasos de sincronización enumerados anteriormente.

Más bajo es mejor.

La misma prueba, pero las bases de datos están en diferentes servidores SQL, que no están conectados a través de un servidor vinculado.

Más bajo es mejor.

Resumen

A partir de los resultados, es obvio que a RedGate y Apex no les importa si las bases de datos están en el mismo servidor SQL, porque el algoritmo de sincronización no depende de SQL Server. SQL Database Studio utiliza funciones nativas de SQL Server; por lo tanto, el resultado es mejor cuando las bases de datos están en el mismo servidor.

El origen y el destino tienen una estructura diferente.

También hay situaciones en las que una tabla ancha debe sincronizarse en muchas tablas pequeñas relacionadas.

Este ejemplo consta de una tabla ancha SourceData que debe sincronizarse en tablas pequeñas Continent , Country y City . El esquema se da a continuación.

Los datos en SourceData podrían ser como los de la imagen a continuación.

Uso de scripts SQL creados manualmente

Tabla de continentes de sincronización de secuencias de comandos

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

Tabla de ciudad de sincronización de secuencias de comandos

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

Este guión es más complicado. Es porque se deben encontrar los registros en las tablas Country y Continent . Este script inserta los registros que faltan en City y llena correctamente ContryId .

Los scripts UPDATE y DELETE también se pueden escribir de la misma manera si es necesario.

Ventajas

• No necesita ningún producto comercial.
• El script SQL puede guardarse en un procedimiento almacenado o ejecutarse periódicamente como un trabajo para SQL Server.

Desventajas

• La creación de una secuencia de comandos SQL de este tipo es difícil y complicada (para cada tabla, generalmente se necesitan tres secuencias de comandos: INSERT , UPDATE y DELETE ).
• Es muy difícil de mantener.

Uso de herramientas externas

Este tipo de sincronización (tabla amplia en muchas tablas relacionadas) no se puede realizar con el método de comparación de datos, porque se centra en diferentes casos de uso. Dado que el método de comparación de datos produce una secuencia de comandos SQL con datos para insertar, no tiene la capacidad directa de buscar referencias en tablas relacionadas. Por ese motivo, las aplicaciones que utilizan este método no se pueden utilizar (dbForge Data Compare for SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).

Sin embargo, SQL Database Studio puede ayudarlo a crear secuencias de comandos de sincronización automáticamente. En la imagen a continuación, hay un elemento llamado Editor para sincronización de datos en SQL Database Studio.

El editor se parece al conocido generador de consultas y funciona de manera muy similar. Cada tabla necesita tener una clave de sincronización definida, pero también hay relaciones definidas entre tablas. En la imagen de arriba también hay mapeo para sincronización. En la lista de columnas (parte inferior de la imagen) están las columnas de la tabla City (para otras tablas es similar).

columnas

• Id : esta columna no está asignada porque es la clave principal (generada automáticamente).
• CountryId : esta columna se define como una referencia para la tabla.
• Nombre : esta columna se completa a partir de la columna Ciudad en la tabla de origen (tabla amplia).

Las columnas CountryId y Name se eligen como claves de sincronización. La clave de sincronización es un conjunto de columnas que identifican de forma única una fila en la tabla de origen y de destino. No puede utilizar el Id . de clave principal como clave de sincronización porque no se encuentra en la tabla de origen.

Después de la sincronización, así es como se ven las tablas:

En el ejemplo anterior, había una tabla ancha como fuente. También hay un escenario común cuando los datos de origen se almacenan en varias tablas relacionadas. Las relaciones en SQL Database Studio no se definen mediante claves externas, sino nombres de columna. De esta forma, también es posible importar desde archivos CSV o Excel (el archivo se carga en una tabla temporal y la sincronización se ejecuta desde esa tabla). Es una buena práctica tener nombres de columna únicos. Si esto no es posible, puede definir alias para esas columnas.

Ventajas

• Fácil y rápido de crear.
• Facil de mantener
• Se puede guardar en un procedimiento almacenado (el procedimiento almacenado se guarda con los datos necesarios para abrir la sincronización en un editor más adelante)

Desventajas

• Solución comercial

Comparando las soluciones

La sincronización de datos consiste en una secuencia de comandos INSERT , UPDATE o DELETE . Hay varias formas de crear secuencias de estos comandos. En este artículo, analizamos tres opciones para crear secuencias de comandos SQL de sincronización. La primera opción es crear todo manualmente. Es factible (pero lleva demasiado tiempo), requiere una comprensión compleja de SQL y es difícil de crear y mantener. La segunda opción es utilizar herramientas comerciales. Analizamos las siguientes herramientas:

• Comparación de datos de dbForge para SQL Server
• Comparación de datos SQL de RedGate
• Diferencia de datos SQL de Apex
• Estudio de base de datos SQL

Las primeras tres herramientas funcionan de manera muy similar. Comparan datos, permiten que el usuario analice las diferencias y pueden sincronizar las diferencias seleccionadas (incluso automáticamente o desde la línea de comandos). Son beneficiosos para estos escenarios de uso:

• Las bases de datos no están sincronizadas debido a varios errores.
• Debe evitar la replicación al transferir datos entre entornos.
• Se necesitan informes de comparación de datos en Excel o HTML.

Cada herramienta es amada por una razón u otra: dbForge tiene una excelente interfaz de usuario y muchas opciones, ApexSQL funciona mejor que el resto y RedGate es la más popular.

La cuarta herramienta, SQL Database Studio, funciona de manera un poco diferente. Genera scripts SQL que contienen lógica de sincronización, no cambios. El rendimiento también es excelente, porque todo el trabajo se realiza directamente en el servidor de la base de datos, por lo que no es necesario transferir datos entre el servidor de la base de datos y la herramienta de sincronización. Esta herramienta es útil para los siguientes casos de uso:

• Migraciones automáticas de bases de datos donde las bases de datos tienen una estructura diferente
• Importar en varias tablas relacionadas
• Importar desde fuentes externas XML, CSV, MS Excel