Abfragen von MySQL von SQL Server mit linked Server

SQL Server hat interessante Funktion calles Linked Server. Es geht darum, andere Datenbanken mit SQL Server zu verknüpfen und ihre Daten wie lokal zu verwenden. Es gibt viele leistungsstarke Open-Source-Systeme, die auf PHP geschrieben sind und meistens MySQL als Datenbank verwenden. Dieser Blogbeitrag zeigt, wie MySQL-Datenbank zu SQL Server zu verknüpfen und wie Linked Server in SQL-Abfragen zu verwenden.

Was ist linked server?,

Verknüpfte server in MSSQL ist einige andere Datenbank-server verbunden zu eine gegeben, das es möglich macht, Abfrage und Manipulation von Daten in andere Datenbanken. Zum Beispiel können wir eine MySQL-Datenbank mit MSSQL verknüpfen und sie fast wie jede andere Datenbank in MSSQL verwenden.

Das Bild stammt von der Dokumentationsseite MSSQL 2019
Verknüpfte Server (Datenbank-Engine)

Obwohl die Kommunikation mit verknüpften Servern über OLE DB-Anbieter erfolgt, gibt es auch OLE DB-Anbieter für ODBC und wir können es verwenden, wenn unsere externe Datenbank keinen OLE DB-Anbieter hat.

NB!, Verknüpfter Server ist für die gesamte SQL Server-Instanz verfügbar. Dies bedeutet, dass alle SQL Server-Datenbanken verknüpfte Server zum Abrufen von Daten verwenden können.

Verknüpfen von MySQL mit SQL Server

Hinzufügen von Linked Server und Konfigurieren der Verbindungseinstellung ist nicht immer einfach und unkompliziert.

Um MySQL mit SQL Server zu verknüpfen, musste ich ODBC DSN für MySQL erstellen (ich habe es als MySQLCrm bezeichnet). Stellen Sie vor den nächsten Schritten sicher, dass die ODBC-Datenquelle funktioniert.,

Führen Sie diese Schritte aus, um MySQL mit SQL Server zu verknüpfen:

1. Führen Sie SQL Server Management Studio (SSMS) aus
2. Stellen Sie eine Verbindung zu Ihrem Server her
3. Erweitern Sie den Serverobjektknoten vom Baum links
4. Klicken Sie mit der rechten Maustaste auf verknüpfte Server
5. Wählen Sie neuen verknüpften Server aus…

Sie sollten den folgenden Dialog sehen (oder etwas anders, aber die Idee bleibt gleich).

NB! Achten Sie besonders darauf, was Sie in diesen Dialog einfügen. Mit diesem Datensatz habe ich Link zum Laufen gebracht. Ich habe verschiedene Werte ausprobiert und wenn etwas einen Millimeter falsch ist, schlägt die Verbindung fehl., Es ist ein verdammt sensibler Dialog.

Connection-string zum MySQL-Datenbank sollte wie hier dargestellt:

beachten Sie Auch OPTION=3–, ohne dies bekam ich nur Fehler zurück, wenn die Verbindung zum Verbindungsserver.

Versuchen Sie zu speichern, indem Sie auf OK klicken, und prüfen Sie, ob Sie zum verknüpften Server navigieren können. Wenn Sie Fehler erhalten, klicken Sie mit der rechten Maustaste auf Server und wählen Sie Eigenschaften aus. Wenn der Dialog geöffnet bleibt, wechseln Sie zur Seite Serveroptionen. Setzen Sie die Einstellungen RPC und RPC Out auf True.,

Ich bin mir immer noch nicht sehr sicher, was diese Optionen tun, aber einige von denen, die Probleme mit dem Link zu MySQL hatten, haben es funktioniert, nachdem sie RPC-s auf true gesetzt haben.

Damit die Abfrage tatsächlich funktioniert, benötigen wir eine weitere kleine Änderung, die sich auf den gesamten OLE DB-Anbieter und damit auf alle Verbindungen auswirkt, die ihn verwenden. Öffnen Sie den Knoten Provider unter Verknüpfte Server, klicken Sie mit der rechten Maustaste auf MSDASQL (dies ist OLE DB Provider für ODBC-Datenquellen) und wählen Sie Eigenschaften aus.

Aktivieren Sie das Kontrollkästchen nur vor Stufe Null und klicken Sie auf OK, um Änderungen zu speichern.,

Das Abfragen von Daten vom verknüpften Server

Das Abfragen verknüpfter Datenbanken ist tatsächlich einfach. Hier ist die Kundentabelle aus der Crmlinked-Datenbank in MySQL. Diese Datenbank ist mit meinem SQL-Server.

Die Syntax zum Abfragen verknüpfter Server unterscheidet sich ein wenig von dem, was wir normalerweise auf SQL Server schreiben. Wir müssen vierteilige Namen verwenden: Server.Datenbank.Schema.Tabelle. Da es in MySQL keine Schemas gibt und die Verbindungszeichenfolge den Datenbanknamen angibt, können wir diese wie hier gezeigt weglassen.

select * from MYSQLCRM...customers

Wenn Sie diese Abfrage über SSMS ausführen, wird die folgende Ausgabe ausgegeben., Es sind die gleichen Daten, die sich in der MySQL-Kundentabelle befinden.

natürlich können wir auch komplexere Abfragen schreiben. Alles, was ODBC verarbeiten kann, ist in Ordnung.

Mischen von Daten vom lokalen und verknüpften Server

Tabellen vom verknüpften Server sind nicht vollständig von lokalen Datenbanktabellen und-ansichten isoliert. Wir können auch Daten vom lokalen und verknüpften Server mischen.

Um gemischte Abfragen über lokale und verknüpfte Tabellen zu demonstrieren, schreiben wir eine einfache Abfrage, um alle Kunden aus der lokalen Tabelle und ihre Ratings aus der verknüpften Tabelle abzurufen.

Wenn Sie diese Abfrage ausführen, erhalten Sie die folgende Ausgabe.,

Da Mark nicht in der MySQL-Datenbank vorhanden ist (angenommen, er ist Neukunde im E-Shop und die Verkaufsabteilung hat ihn noch nicht in ihrem CRM-System), steht ihm keine Bonität zur Verfügung. Die Bonitätsnoten für John und Mary kommen im aktuellen Fall von MySQL.

Mit OPENQUERY () Abfrage in verknüpften Server ausführen

Die obigen Beispiele machen alle Datenverarbeitung auf SQL Server. Es kann sehr unoptimal sein, wenn sich in den Tabellen des Servers viele Daten befinden. Möglicherweise möchten oder möchten wir einige Daten auf dem verknüpften Server verarbeiten, bevor SQL Server mit der lokalen Verarbeitung beginnt., Dafür haben wir OPENQUERY ().

Hier ist das Beispiel für die Verwendung der OPENQUERY () – Funktion in gemischten Abfragen. Wir müssen den Namen des verknüpften Servers und die SQL-Abfrage angeben, die auf dem verknüpften Server ausgeführt werden sollen, wenn OPENQUERY () aufgerufen wird. Die Abfrage in Rot wird in MySQL Server ausgeführt und die Ergebnisse werden zur weiteren Verarbeitung in SQL Server gelesen.

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() ist eine großartige Möglichkeit, gemischte Abfragen zu optimieren und zu beschleunigen, indem komplexere Abfragen über verknüpfte Serverdaten in verknüpften Servern ausgeführt werden.,

Zusammenfassung

Verknüpfte Server ist eine leistungsstarke Funktion von SQL Server macht es einfach für uns zu verwenden Sie Daten von externen Servern. Es gibt zwei Möglichkeiten, Abfragen mit Daten von verknüpften Servern zu schreiben – direkte Abfragen, die alle Verarbeitung auf SQL Server und OPENQUERY (), die uns einige Verarbeitung in Remote-Server tun können. Linked Server ist Integration und daher mit ihm braucht zusätzliche Sorgfalt. Planung und Leistungsmessung sind unverzichtbare Aktivitäten bei der Planung der Verwendung von Linked Server.