Odpytywanie MySQL z SQL Server za pomocą linked server

SQL Server posiada ciekawe funkcje wywoływania Linked Servers. Chodzi o łączenie innych baz danych z serwerem SQL i używanie ich danych tak, jakby były lokalne. Istnieje wiele potężnych systemów open-source napisanych w PHP i głównie używają MySQL jako bazy danych. Ten wpis na blogu pokazuje, jak połączyć bazę danych MySQL z serwerem SQL i jak używać linked server w zapytaniach SQL.

Co to jest linked server?,

Linked server w MSSQL jest innym serwerem bazodanowym podłączonym do danego serwera, umożliwiającym odpytywanie i manipulowanie danymi w innych bazach danych. Na przykład, możemy połączyć niektóre bazy danych MySQL do MSSQL i używać go prawie jak każda inna baza danych w MSSQL.

obraz pochodzi ze strony dokumentacji MSSQL 2019
Linked Servers (Database Engine)

chociaż komunikacja z linked servers odbywa się za pośrednictwem dostawców OLE DB, istnieje również OLE DB provider dla ODBC i możemy go używać, jeśli nasza zewnętrzna baza danych nie ma OLE DB provider.

NB!, Linked server jest dostępny dla całej instancji SQL Server. Oznacza to, że wszystkie bazy danych SQL Server mogą korzystać z linked server do pobierania danych.

łączenie MySQL z SQL Server

dodawanie linked server i konfigurowanie ustawienia połączenia nie zawsze jest łatwe i proste.

aby połączyć MySQL z SQL serverem musiałem utworzyć ODBC DSN dla MySQL (nazwałem go MySQLCrm). Przed przejściem do kolejnych kroków upewnij się, że źródło danych ODBC działa.,

wykonaj następujące kroki, aby połączyć MySQL z serwerem SQL:

1. Uruchom SQL Server Management Studio (SSMS)
2. Połącz się z serwerem
3. rozwiń węzeł obiektów serwera z drzewa po lewej stronie
4. kliknij prawym przyciskiem myszy na połączonych serwerach
5. wybierz nowy połączony Serwer…

powinieneś zobaczyć następujące okno dialogowe (lub nieco inne, ale pomysł pozostaje ten sam).

NB! Zwróć szczególną uwagę na to, co wstawiasz do tego okna dialogowego. Z tym zestawem danych sprawiłem, że link działał. Próbowałem różnych wartości i jeśli coś jest o jeden milimetr źle to połączenie nie działa., To cholernie delikatny dialog.

ciąg połączenia do bazy danych MySQL powinien być taki jak pokazano tutaj:

zwróć również uwagę na OPTION=3 – bez tego mam tylko błędy z powrotem podczas łączenia się z połączonym serwerem.

spróbuj zapisać klikając OK i sprawdź czy możesz przejść do linked server. Jeśli pojawią się błędy, kliknij prawym przyciskiem myszy na serwerze i wybierz Właściwości. Zachowując otwarte okno dialogowe, przejdź do strony Opcje serwera. Ustaw ustawienia RPC i RPC Out Na True.,

nadal nie jestem pewien, co robią te opcje, ale niektórzy z tych, którzy mieli problemy z linkiem do MySQL, sprawili, że działało po ustawieniu RPC-s na true.

aby zapytania faktycznie działały, potrzebujemy jeszcze jednej małej zmiany, która wpływa na całego dostawcę OLE DB, a zatem wszystkie połączenia z nim korzystające. Otwórz węzeł Providers pod Linked Servers, kliknij prawym przyciskiem myszy na MSDASQL (to jest OLE DB provider dla źródeł danych ODBC) i wybierz Właściwości.

zaznacz pole tylko przed poziomem Zero i kliknij OK, aby zapisać zmiany.,

odpytywanie danych z połączonych serwerów

odpytywanie połączonych baz danych jest w rzeczywistości łatwe. Oto tabela klientów z bazy danych CRM w MySQL. Ta baza danych jest połączona z moim serwerem SQL.

składnia zapytań linked server różni się nieco od tego, co Zwykle piszemy na serwerze SQL. Musimy użyć czteroczęściowych nazw: server.baza danych.schemat.stolik. Ponieważ nie ma schematów w MySQL i łańcuch połączeń określa nazwę bazy danych, możemy je pominąć, jak pokazano tutaj.

select * from MYSQLCRM...customers

uruchomienie tego zapytania z SSMS daje następujące wyjście., To te same dane, które znajdują się w tabeli klientów MySQL.

oczywiście możemy również pisać bardziej złożone zapytania. Wszystko, co może obsłużyć ODBC, jest w porządku.

mieszanie danych z serwera lokalnego i połączonego

tabele z serwera połączonego nie są całkowicie odizolowane od tabel i widoków lokalnej bazy danych. Możemy również mieszać dane z serwera lokalnego i połączonego.

aby zademonstrować mieszane zapytania nad tabelami lokalnymi i połączonymi, napiszmy proste zapytanie, aby uzyskać wszystkich klientów z tabeli lokalnej i ich ratingi kredytowe z tabeli połączonej.

uruchomienie tego zapytania daje nam następujące wyjście.,

ponieważ Mark nie jest obecny w bazie MySQL (Załóżmy, że jest nowym klientem w e-sklepie, a dział sprzedaży nie ma go jeszcze w systemie CRM) , to nie ma zdolności kredytowej. W bieżącym przypadku ratingi dla Johna i Mary pochodzą z MySQL.

użycie openquery() do wykonania zapytania w linked server

powyższe przykłady sprawiają, że wszystkie dane są przetwarzane na serwerze SQL. Może to być bardzo nieoptymalne, jeśli w tabelach linked server jest dużo danych. Możemy chcieć – lub zazwyczaj chcieć-przetworzyć niektóre dane w linked server przed uruchomieniem lokalnego przetwarzania SQL Server., Do tego mamy openquery ().

oto przykład użycia funkcji OPENQUERY() w zapytaniu mieszanym. Musimy podać nazwę linked server i zapytanie SQL, które ma zostać uruchomione w linked server podczas wywoływania OPENQUERY(). Zapytanie w kolorze czerwonym jest wykonywane na serwerze MySQL, a wyniki są odczytywane do serwera SQL w celu dalszego przetwarzania.

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() to świetny sposób na optymalizację i przyspieszenie mieszanych zapytań poprzez uruchamianie bardziej złożonych zapytań na danych połączonych serwerów w linked server.,

Linked servers to potężna funkcja SQL Server, która ułatwia nam korzystanie z danych z zewnętrznych serwerów. Istnieją dwa sposoby zapisu zapytań przy użyciu danych z połączonych serwerów – zapytania bezpośrednie, które przetwarzają wszystkie dane na serwerze SQL oraz OPENQUERY (), które pozwala nam przetwarzać niektóre dane na serwerze zdalnym. Linked server jest integracją i dlatego korzystanie z niego wymaga dodatkowej opieki. Planowanie i pomiar wydajności to niezbędne czynności podczas planowania korzystania z serwera połączonego.