Consultar MySQL desde SQL Server usando linked server

SQL Server tiene interesantes características de calles Linked Servers. Se trata de vincular otras bases de datos a SQL Server y usar sus datos como si fueran locales. Hay muchos potentes sistemas de código abierto escritos en PHP y en su mayoría utilizan MySQL como base de datos. Esta entrada de blog muestra cómo vincular la base de datos MySQL al servidor SQL y cómo usar el servidor vinculado en consultas SQL.

¿qué es el servidor vinculado?,

Linked server en MSSQL es algún otro servidor de base de datos conectado a uno dado, lo que permite consultar y manipular datos en otras bases de datos. Por ejemplo, podemos vincular alguna base de datos MySQL a MSSQL y usarla casi como cualquier otra base de datos en MSSQL.

La imagen se toma de la página de documentación de MSSQL 2019
servidores vinculados (motor de base de datos)

aunque la comunicación con los servidores vinculados pasa por proveedores OLE DB, también existe OLE DB provider para ODBC y podemos usarlo si nuestra base de datos externa no tiene proveedor OLE DB.

NB!, El servidor vinculado está disponible para toda la instancia de SQL Server. Esto significa que todas las bases de datos de SQL Server pueden utilizar el servidor vinculado para recuperar datos.

vincular MySQL a SQL Server

Agregar servidor vinculado y configurar la configuración de conexión no siempre es fácil y directo.

para vincular MySQL a SQL Server necesitaba crear ODBC DSN para MySQL (lo nombré MySQLCrm). Antes de ir a los siguientes pasos, asegúrese de que el origen de datos ODBC funcione.,

siga estos pasos para vincular MySQL a SQL Server:

1. ejecute SQL Server Management Studio (SSMS)
2. conéctese a su servidor
3. expanda el nodo Objetos del servidor desde el árbol a la izquierda
4. haga clic con el botón derecho en servidores vinculados
5. Seleccione Nuevo servidor vinculado

debería ver el siguiente cuadro de diálogo (o un poco diferente, pero la idea sigue siendo la misma).

NB! Preste especial atención a lo que inserta en este cuadro de diálogo. Con este conjunto de datos hice que link funcionara. Probé diferentes valores y si algo está un milímetro equivocado, entonces la conexión falla., Es un diálogo muy sensible.

la cadena de conexión a la base de datos MySQL debe ser como se muestra aquí:

también preste atención a OPTION=3 – sin esto solo tengo errores cuando se conecta al servidor vinculado.

intente guardar haciendo clic en Aceptar y vea si puede navegar al servidor vinculado. Si obtiene errores, haga clic con el botón derecho en servidor y seleccione Propiedades. Manteniendo el diálogo abierto, vaya a la página de opciones del servidor. Establezca la configuración de RPC y RPC Out en True.,

todavía no estoy muy seguro de lo que están haciendo estas opciones, pero algunos de los que tenían problemas con el enlace a MySQL lo hicieron funcionar después de configurar RPC-s a true.

para hacer que la consulta realmente funcione, necesitamos un pequeño cambio más que afecte a todo el proveedor OLE DB y, por lo tanto, a todas las conexiones que lo usan. Abra el nodo Proveedores en servidores vinculados, haga clic con el botón derecho en MSDASQL (este es OLE DB provider para fuentes de datos ODBC) y seleccione Propiedades.

marque la casilla antes del nivel cero solamente y haga clic en Aceptar para guardar los cambios.,

consultar datos del servidor vinculado

consultar bases de datos vinculadas Es realmente fácil. Aquí está la tabla de clientes de la base de datos crmlinked en MySQL. Esta base de datos está vinculada a mi servidor SQL.

la sintaxis para consultar el servidor vinculado es un poco diferente de lo que solemos escribir en SQL Server. Necesitamos usar nombres de cuatro partes: servidor.base.esquema.tabla. Como no hay esquemas en MySQL y la cadena de conexión especifica el nombre de la base de datos, podemos omitirlos como se muestra aquí.

select * from MYSQLCRM...customers

ejecutar esta consulta desde SSMS da la siguiente salida., Son los mismos datos que están en la tabla de clientes de MySQL.

por supuesto, también podemos escribir consultas más complejas. Todo lo que ODBC puede manejar está bien.

mezclar datos del servidor local y del servidor vinculado

Las Tablas del servidor vinculado no están totalmente aisladas de las tablas y vistas de la base de datos local. También podemos mezclar datos del servidor local y vinculado.

para demostrar una consulta mixta sobre tablas locales y vinculadas, escribamos una consulta simple para obtener a todos los clientes de la tabla local y sus calificaciones crediticias de la tabla vinculada.

ejecutar esta consulta nos da la siguiente salida.,

como Mark no está presente en la base de datos MySQL (supongamos que es un nuevo cliente en la tienda electrónica y el Departamento de ventas aún no lo tiene en su sistema CRM), entonces no tiene calificación crediticia disponible. Las calificaciones crediticias de John y Mary provienen de MySQL en el caso actual.

usando OPENQUERY () para ejecutar consultas en el servidor vinculado

Los ejemplos anteriores hacen que todo el procesamiento de datos en SQL Server. Puede ser muy poco óptimo si hay una gran cantidad de datos en las tablas del servidor vinculado. Es posible que queramos, o normalmente queramos, procesar algunos datos en el servidor vinculado antes de que SQL Server inicie el procesamiento local., Para esto tenemos OPENQUERY ().

Este es el ejemplo de usar la función OPENQUERY () en una consulta mixta. Tenemos que especificar el nombre del servidor vinculado y la consulta SQL para que se ejecute en el servidor vinculado al llamar a OPENQUERY(). La consulta en rojo se ejecuta en MySQL server y los resultados se leen en SQL Server para su posterior procesamiento.

SELECT
c.FirstName,
c.LastName,
crm_c.credit_rating as CreditRating
FROM 
Customers c
LEFT JOIN OPENQUERY(MYSQLCRM, '
SELECT 
c.credit_rating
FROM 
customers p
left join loyalty_points lp on
c.customer_id = lp.customer_id
WHERE
lp.points > 1000
') crm_c ON
c.ssn = crm_c.ssn
ORDER BY
crm_c.credit_rating,
c.LastName,
c.FirstName 

OPENQUERY () es una excelente manera de optimizar y acelerar las consultas mixtas ejecutando consultas más complejas sobre datos del servidor vinculado en el servidor vinculado.,

envolver

servidores vinculados es una poderosa característica de SQL Server que nos facilita el uso de datos de servidores externos. Hay dos formas de escribir consultas usando datos de servidores vinculados: consultas directas que hacen todo el procesamiento en SQL Server y OPENQUERY() que nos permite hacer algo de procesamiento en un servidor remoto. El servidor vinculado es la integración y, por lo tanto, su uso necesita un cuidado adicional. La planificación y la medición del rendimiento son actividades imprescindibles al planificar el uso del servidor vinculado.