Interrogare MySQL da SQL Server utilizzando linked server

SQL Server ha una caratteristica interessante chiama i server collegati. Si tratta di collegare altri database a SQL Server e utilizzare i loro dati come se fossero locali. Esistono molti potenti sistemi open source scritti su PHP e utilizzano principalmente MySQL come database. Questo post del blog mostra come collegare il database MySQL a SQL Server e come utilizzare il server collegato nelle query SQL.

Che cos’è il server collegato?,

Il server collegato in MSSQL è un altro server di database collegato a uno dato, rendendo possibile interrogare e manipolare i dati in altri database. Ad esempio, possiamo collegare alcuni database MySQL a MSSQL e usarlo quasi come qualsiasi altro database su MSSQL.

L’immagine è presa dalla pagina di documentazione MSSQL 2019
Server collegati (motore di database)

Sebbene la comunicazione con i server collegati passi attraverso i provider OLE DB, esiste anche un provider OLE DB per ODBC e possiamo usarlo se il nostro database esterno non ha un provider OLE DB.

NB!, Il server collegato è disponibile per l’intera istanza di SQL Server. Significa che tutti i database SQL Server possono utilizzare il server collegato per recuperare i dati.

Collegare MySQL a SQL Server

Aggiungere un server collegato e configurare le impostazioni di connessione non è sempre facile e diretto.

Per ottenere MySQL collegato a SQL Server avevo bisogno di creare ODBC DSN per MySQL (l’ho chiamato MySQLCrm). Prima di passare ai passaggi successivi, assicurati che l’origine dati ODBC funzioni.,

Attenersi alla seguente procedura per collegare MySQL a SQL Server:

1. Esegui SQL Server Management Studio (SSMS)
2. Connetti al server
3. Espandere Oggetti Server nodo dall’albero a sinistra
4. fare clic su Server Collegati
5. Selezionare Nuovo Server Collegato…

Si dovrebbe vedere la seguente finestra di dialogo (o un po ‘ diverso, ma l’idea rimane la stessa).

NB! Prestare particolare attenzione a ciò che si inserisce in questa finestra di dialogo. Con questo insieme di dati ho fatto funzionare il collegamento. Ho provato valori diversi e se qualcosa è sbagliato di un millimetro, la connessione fallisce., E ‘ un dialogo dannatamente sensibile.

La stringa di connessione al database MySQL dovrebbe essere come mostrata qui:

Presta attenzione anche all’OPZIONE=3 – senza questo ho ricevuto solo errori durante la connessione al server collegato.

Provare a salvare facendo clic su OK e vedere se è possibile passare al server collegato. Se ricevi errori, fai clic con il pulsante destro del mouse su server e seleziona Proprietà. Mantenendo aperta la finestra di dialogo, passare alla pagina Opzioni server. Impostare le impostazioni RPC e RPC Out su True.,

Non sono ancora molto sicuro di cosa stiano facendo queste opzioni, ma alcuni di coloro che hanno avuto problemi con il collegamento a MySQL hanno funzionato dopo aver impostato RPC-s su true.

Per far funzionare effettivamente l’interrogazione, abbiamo bisogno di un altro piccolo cambiamento che riguarda l’intero provider OLE DB e quindi tutte le connessioni che lo utilizzano. Aprire il nodo Provider in Server collegati, fare clic destro su MSDASQL (questo è OLE DB provider per le origini dati ODBC) e selezionare proprietà.

Seleziona la casella solo prima del livello Zero e fai clic su OK per salvare le modifiche.,

Interrogare i dati dal server collegato

Interrogare i database collegati è in realtà facile. Ecco la tabella dei clienti dal database crmlinked in MySQL. Questo database è collegato al mio SQL Server.

La sintassi per interrogare il server collegato è un po ‘ diversa da quella che di solito scriviamo su SQL Server. Dobbiamo usare nomi di quattro parti: server.database.Schema.tavolo. Poiché non ci sono schemi su MySQL e la stringa di connessione specifica il nome del database, possiamo lasciarli fuori come mostrato qui.

select * from MYSQLCRM...customers

L’esecuzione di questa query da SSMS fornisce il seguente output., Sono gli stessi dati presenti nella tabella MySQL customers.

Naturalmente, possiamo anche scrivere query più complesse. Tutto ciò che ODBC può gestire va bene.

Mescolare i dati dal server locale e collegato

Le tabelle dal server collegato non sono totalmente isolate dalle tabelle e dalle viste del database locale. Possiamo anche mescolare i dati dal server locale e collegato.

Per dimostrare query miste su tabelle locali e collegate scriviamo query semplici per ottenere tutti i clienti dalla tabella locale e i loro rating di credito dalla tabella collegata.

L’esecuzione di questa query ci dà il seguente output.,

Poiché Mark non è presente nel database MySQL (supponiamo che sia un nuovo cliente nell’e-shop e che il reparto vendite non lo abbia ancora nel proprio sistema CRM), allora non ha un rating di credito disponibile. I rating di credito per John e Mary provengono da MySQL nel caso attuale.

Utilizzo di OPENQUERY () per eseguire query nel server collegato

Gli esempi precedenti rendono l’elaborazione di tutti i dati su SQL Server. Può essere molto poco ottimale se ci sono molti dati nelle tabelle del server collegato. Potremmo voler – o di solito vogliamo-elaborare alcuni dati nel server collegato prima che SQL Server inizi l’elaborazione locale., Per questo abbiamo OPENQUERY ().

Ecco l’esempio di utilizzo della funzione OPENQUERY() nella query mista. Dobbiamo specificare il nome del server collegato e la query SQL da eseguire nel server collegato quando si chiama OPENQUERY (). La query in rosso viene eseguita in MySQL server e i risultati vengono letti su SQL Server per ulteriori elaborazioni.

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 () è un ottimo modo per ottimizzare e velocizzare le query miste eseguendo query più complesse sui dati del server collegato in server collegato.,

Avvolgendo

Server collegati è potente caratteristica di SQL Server che lo rende facile per noi di utilizzare i dati da server esterni. Esistono due modi per scrivere query utilizzando i dati dai server collegati: query dirette che eseguono tutte le elaborazioni su SQL Server e OPENQUERY() che ci consente di eseguire alcune elaborazioni nel server remoto. Server collegato è l’integrazione e quindi l’utilizzo ha bisogno di cure extra. La pianificazione e la misurazione delle prestazioni sono attività indispensabili quando si pianifica di utilizzare il server collegato.