Het opvragen van MySQL vanuit SQL Server met behulp van gekoppelde server

SQL Server heeft interessante feature Calles gekoppelde Servers. Het gaat over het koppelen van andere databases aan SQL Server en het gebruik van hun gegevens alsof het lokaal is. Er zijn veel krachtige open-source systemen geschreven op PHP en ze zijn meestal met behulp van MySQL als database. Deze blog post laat zien hoe MySQL database te koppelen aan SQL Server en hoe gekoppelde server te gebruiken in SQL queries.

Wat is gekoppelde server?,

gelinkte server in MSSQL is een andere databaseserver die verbonden is met een gegeven server, waardoor het mogelijk is om gegevens in andere databases op te vragen en te manipuleren. Door voorbeeld, kunnen we sommige MySQL database koppelen aan MSSQL en gebruiken het bijna als elke andere database op MSSQL.

Image is afkomstig van MSSQL 2019 documentatie pagina
gekoppelde Servers (Database Engine)

hoewel communicatie met gekoppelde servers via OLE DB providers verloopt, is er ook OLE DB provider voor ODBC en we kunnen het gebruiken als onze externe database geen OLE DB provider heeft.

NB!, Gekoppelde server is beschikbaar voor de hele SQL Server-instantie. Dit betekent dat alle SQL Server databases gekoppelde server kunnen gebruiken om gegevens op te halen.

MySQL koppelen aan SQL Server

gekoppelde server toevoegen en verbindingsinstellingen configureren is niet altijd eenvoudig en eenvoudig.

om MySQL te koppelen aan SQL Server moest ik ODBC DSN maken voor MySQL (ik noemde het mysqlcrm). Voordat u naar de volgende stappen gaat, moet u ervoor zorgen dat ODBC-gegevensbron werkt.,

volg deze stappen om MySQL te koppelen aan SQL Server:

1. Run SQL Server Management Studio (SSMS)
2. verbinding maken met uw server
3. Expand Server Objects node from tree at left
4. Klik met de rechtermuisknop op gekoppelde Servers
5. Selecteer Nieuwe gekoppelde Server…

u zou het volgende dialoogvenster moeten zien (of een Bit anders maar het idee blijft hetzelfde).

NB! Besteed extra aandacht aan wat u invoegt in dit dialoogvenster. Met deze set gegevens heb ik link laten werken. Ik probeerde verschillende waarden en als er iets is een millimeter verkeerd dan verbinding mislukt., Het is een gevoelig dialoogvenster.

Verbindingsstring naar MySQL – database moet zoals hier getoond worden:

let ook op OPTION=3-zonder dit kreeg ik alleen fouten terug bij het verbinden met gekoppelde server.

probeer op te slaan door op OK te klikken en kijk of u naar gekoppelde server kunt bladeren. Als je fouten krijgt klik dan met de rechtermuisknop op server en selecteer Eigenschappen. Als u het dialoogvenster open houdt, gaat u naar de pagina Serveropties. Stel RPC en RPC Out instellingen in op True.,

Ik weet nog steeds niet zeker wat deze opties doen, maar sommige van degenen die problemen hadden met een link naar MySQL hebben het laten werken nadat RPC-s op true was ingesteld.

om querying daadwerkelijk te laten werken, hebben we nog een kleine verandering nodig die de hele OLE DB provider beïnvloedt en dus alle verbindingen die het gebruiken. Open Providers knooppunt onder gekoppelde Servers, klik met de rechtermuisknop op MSDASQL (dit is OLE DB provider voor ODBC-gegevensbronnen) en selecteer Eigenschappen.

Vink alleen het vakje voor niveau nul aan en klik op OK om de wijzigingen op te slaan.,

gegevens opvragen van gekoppelde server

gekoppelde databases opvragen is eigenlijk eenvoudig. Hier is de klantentabel uit crmlinked database in MySQL. Deze database is gekoppeld aan mijn SQL Server.

syntaxis voor het opvragen van gekoppelde server is een beetje anders dan wat we gewoonlijk schrijven op SQL Server. We moeten vierdelige namen gebruiken: server.database.schema.tabel. Aangezien er geen schema ‘ s zijn op MySQL en de verbindingsreeks de databasenaam specificeert, kunnen we deze weglaten zoals hier getoond.

select * from MYSQLCRM...customers

het uitvoeren van deze query vanuit SSMS geeft de volgende uitvoer., Het is dezelfde gegevens die in MySQL klanten tabel.

natuurlijk kunnen we ook complexere queries schrijven. Alles wat ODBC aankan is oké.

het mengen van gegevens van lokale en gekoppelde server

tabellen van gekoppelde server zijn niet volledig geïsoleerd van lokale databasetabellen en-weergaven. We kunnen ook gegevens van lokale en gekoppelde server mengen.

om gemengde query over lokale en gekoppelde tabellen aan te tonen, schrijven we een eenvoudige query om alle klanten uit lokale tabel en hun credit ratings uit gekoppelde tabel te krijgen.

het uitvoeren van deze query geeft ons de volgende uitvoer.,

aangezien Mark niet aanwezig is in de MySQL-database (stel dat hij een nieuwe klant is in de e-shop en de verkoopafdeling hem nog niet in hun CRM-systeem heeft), heeft hij geen credit rating beschikbaar. Credit ratings voor John en Mary komen van MySQL in dit geval.

OPENQUERY () gebruiken om query uit te voeren in gekoppelde server

De voorbeelden hierboven maken alle gegevensverwerking op SQL Server. Het kan erg onoptimaal zijn als er veel gegevens in de tabellen van de gekoppelde server staan. We willen – of willen meestal-sommige gegevens in gekoppelde server verwerken voordat SQL Server lokale verwerking start., Hiervoor hebben we OPENQUERY().

Hier is het voorbeeld van het gebruik van OPENQUERY () functie in gemengde query. We moeten de naam van de gekoppelde server en de SQL-query opgeven om in de gekoppelde server uit te voeren bij het aanroepen van OPENQUERY(). De query in het rood wordt uitgevoerd in MySQL server en de resultaten worden gelezen naar SQL Server voor verdere verwerking.

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() is een geweldige manier om gemengde query ’s te optimaliseren en te versnellen door complexere query’ s uit te voeren over gekoppelde servergegevens in gekoppelde server.,

het afbreken van

gekoppelde servers is een krachtige functie van SQL Server, waardoor het voor ons gemakkelijk is om gegevens van externe servers te gebruiken. Er zijn twee manieren om query ’s te schrijven met behulp van gegevens van gekoppelde servers – directe query’ s die alle verwerking doen op SQL Server en OPENQUERY() waarmee we enige verwerking kunnen doen in remote server. Linked server is integratie en daarom moet het gebruik ervan extra zorg. Planning en prestatiemeting zijn must-be activiteiten bij het plannen van het gebruik van linked server.