MS SQL Server i Linked Server MySQL

Czasami zdarza się, że klient ma kilka aplikacji, a każda z nich opiera się o inny silnik bazy danych. Sam spotkałem się z sytuacją, gdzie aplikacja desktopowa działa na MS SQL SERVER, strona internetowa korzysta standardowo z MySQL, a dodatkowa aplikacja webowa opiera się na spadkobiercy MySQL w postaci  MariaDB. Wszystko byłoby w porządku, gdyby nie to, że wszystkie te elementy są od siebie zależne, a dokładniej powinny uwzględniać zmiany zachodzące w bazie danych aplikacji desktopowej. Do tego dochodzi też fakt, że każda baza danych ma inną strukturę, więc replikacja byłaby tu fanaberią.

Z pomocą przychodzą nam tu LINKED SERVER’Y, o których postaram się pokrótce opowiedzieć w tym artykule.

A. Instalacja i konfiguracja PROVIDER’A

1. Pobierz MySQL ODBC driver ze strony mysql.com
2. Zainstaluj MySQL ODBC driver na serwerze, na krórym znajduje się SQL
-Kliknij dwa razy na pobrany plik instalatora i postępuj zgodnie z domyślnymi opcjami instalatora.

3. Stwórz DSN używając MySQL ODBC driver
Start -> Panel sterowania -> Narzędzia administracyjne -> Źródła danych (ODBC)

-Kliknij na kartę Systemowe DSN

-Kliknij Dodaj
-Wybierz MySQL ODBC Driver (druga opcja na poniższym printscreen’ie)

-Kliknij Zakończ
W formularzu parametrów połączenia:

-Wpisz jednoznaczną nazwę dla swojego DSN’a (Data Source Name).
-Wpisz nazwę serwera lub jego adres IP w polu tekstowym TCP/IP Server. W polu Port wpisz port na którym działa usługa SQL na zdalnym serwerze (standardowo jest to 3306).
-Wpisz nazwę użytkownika potrzebną do połączenia z bazą danych MySQL w pole tekstowe User.
-wpisz hasło potrzebne do połączenia z bazą danych MySQL w pole tekstowe Password.
-Wybierz bazę danych, którą chcesz połączyć korzystając z rozwijanej listy Database.

Przydatne może być zaznaczenie opcji podanych poniżej. Zalecam jednak używanie tej konfiguracji tylko dla prostych projektów. W przypadku bardziej zaawansowanych sami powinniście określić potrzebne właściwości.

Po kliknięciu przycisku Details:

W zakładce Connection:

-Zaznacz: Allow big result sets

-Zaznacz: Use compression

-Zaznacz: Enable automatic reconnect

-Zaznacz: Don’t prompt when connecting

W zakładce Metadata:

-Zaznacz: Treat BIGINT columns as INT columns

-Zaznacz: Include table name in SQLDescribeCol()

W zakładce Misc:

-Zaznacz: Enable safe options (see documentation)

-Zaznacz: Disable transaction suppport

 

Teraz przetestuj swój DSN klikając przycisk Test

 

B. Podłączenie zdalnej bazy jako LINKED SERVER

4. Stworzenie nowego Linked Server’a w SSMS (SQL Server Management Studio) dla bazy danych MySQL
SQL Server Management Studio -> Rozszerz drzewo Server Objects
-Kliknij prawym przyciskiem myszy na Linked Servers -> Wybierz opcję New Linked Server
Na stronie głównej formularza:
-Linked Server: Wpisz nazwę dla swojego Linked Server’a
-Server Type: wybierz Other Data Source
-Provider: Wybierz Microsoft OLE DB Provider for ODBC Drivers
-Product name: Wpisz MySQLDatabase
-Data Source: Wpisz nazwę DSN’a, który stworzyłeś
Na stronie Security (wybór stron po prawej stronie formularza)
-Map a login to the Remote User and provide the Remote Users Password
-Click Add under Local server login to remote server login mappings:
-Select a Local Login From the drop down box
-Wpisz nazwę zdalnego użytkownika
-Wpisz hasło dla zdalnego użytkownika

5. Zmień właściwości provider’a MSDASQL
Rozszerz drzewo Providers -> Kliknij prawym przyciskiem myszy na MSDASQL -> Wybierz opcję Properties
-Enable Nested queries
-Enable Level zero only (this one’s the kicker)
-Enable Allow inprocess
-Enable Supports 'Like’ operator

6. zmień ustawienia in SQL Server Surface Area Configuration for Features
-Enable OPENROWSET and OPENDATASOURCE support.
7. Zmień ustawienia SQL Server Surface Area Configuration for Services and Connections
-Enable Local and Remote connections via TCP/IP and named pipes

8. Zastopuj SQL Server i SQL Server Agent
9. Wystartuj SQL Server i SQL Server Agent

W celu ustawienia autentykacji zdalnego użytkownika, Na stronie Security (wybór stron po prawej stronie formularza New Linked Server):

Mamy tu do wyboru kilka sposobów autentykacji.

Poniżej etykiety Local server login to remote server login mappings, może być ustawione dwa sposoby zmapowania lokalnego logowania na logowanie zdalne. Jednym jest powiązanie lokalnego loginu z kontem zdalnym, a drugim opcja impersonate, czyli jakkolwiek to brzmi „podszywanie się” pod zdalnego użytkownika.

Local Login

W polu Local Login, będą wymienione wszystkie lokalne loginy. Local Login może być lokalnym loginem SQL Server Authentication (Object Explorer -> Security -> Logins):

Lub loginem autentykacji systemu Windows:

 

Jeśli dodamy login i nie zaznaczymy opcji impersonate,  albo nie dodamy loginu w polu Remote User i klikniemy przycisk OK pojawi się komunikat błędu.

Jak na obrazku poniżej:

Impersonate

Zaznaczenie check box’a dla opcji Impersonate powoduje przekazanie poświadczeń lokalnych wybranego konta do serwera zdalnego. Dla SQL Server Authentication, ten sam login z dokładnie tymi samymi poświadczeniami musi istnieć dla linked server’a, w innym przypadku połączenie z serwerem zdalnym przy użyciu SQL Server Authentication, lista baz danych w folderze Catalogs linked server’a może wyglądać tak:

 

Dla poświadczeń Windows, login musi być zgodny z tym na linked serwerze. w przypadku użycia impersonation, musi być ustawiona delegacja pomiędzy serwerem lokalnym i linked server’em.

stwórzmy linked server używając lokalnych poświadczeń Windows. Z listy wyboru Local Login, wybierz lokalny login Windows i zaznacz pole wyboru Impersonate, a potem kliknij przycisk OK:

 

Poniżej folderu Catalogs, zostaną wymienione wszystkie bazy danych linked server’a:

 

Remote User

Właściwość remote user pozwala użytkownikom lokalnego SQL server’a połączyć się z linked SQL server’em nawet jeśli ich poświadczenia nie są obecne na zdalnym serverze przy użyciu poświadczeń użytkownika, który tam istnieje. Ogólnie pozwala to użytkownikowi na wybranie i użycie innego loginu użytkownika istniejącego na zdalnym serwerze.

Remote Password

W polu Remote Password wpisz odpowiadające hasło dla podanego wcześniej Remote User (użytkownika bazy danych na linked serwerze). Następnie wciśnij przycisk OK:

 

Teraz gdy połączymy się z lokalnym serwerem przy użyciu SQL Server Authentication, poświadczeniami, dla których wykonaliśmy wcześniejsze mapowanie, w drzewie katalogowym pod folderem Catalogs będą dostępne wszystkie bazy danych linked server’a, do których ma dostęp podany zdalny użytkownik lub nasz lokalny użytkownik przy zaznaczeniu opcji Impersonate, jeśli występuje analogiczny użytkownik z naszymi poświadczeniami na linked serwerze:

 

Dodatkowo/zamiennie możemy użyć czterech opcji poniżej listy Local server login to remote server login mappings znajdujących się w sekcji For a login not defined in the list above, connections will:

 

Not be made

Po zaznaczeniu tej opcji, żaden użytkownik, który nie jest zdefiniowany na liście Local server login to remote server login mappings nie może uzyskać połączenia z linked server’em.

Jeśli zalogujemy się lokalnie za pomocą poświadczeń, których nie dodaliśmy do wcześniej wspomnianej listy, otrzymamy widok, taki jak w przypadku braku poświadczeń na serwerze zdalnym:

 

W tej sytuacji, jeśli spróbujesz wykonać zapytanie odnoszące się do bazy danych na linked serverze np.:

Rezultatem będzie komunikat błędu:

Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.

Be made without using a security context

Opcja Be made without using a security context jest używana do łączenia ze źródłem danych, które nie wymaga autentykacji, np. pliku Excel. W tym wypadku próba wykonania zapytania na linked serverze, który wymaga poświadczeń zakończy się praktycznie tak samo jak przy wyborze opcji “Not be made”.

Próba wykonania zapytania:

Poskutkuje następującym komunikatem błędu:

OLE DB provider “SQLNCLI11” for linked server “WINSERVER\SQLSERVER” returned message “Invalid authorization specification”.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “SQLNCLI11” for linked server “WINSERVER\SQLSERVER” reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “WINSERVER\SQLSERVER”.

Be made using the login’s current security context

Jeśli wybierzemy tą opcję, na serwerze zdalnym zostanie użyty obowiązujący lokalnie kontekst poświadczeń. W przypadku używania Windows Authentication, do próby połączenia ze zdalnym serwerem SQL zostaną użyte obecne poświadczenia systemu Windows. Jeśli używamy SQL Server Authentication, zostaną użyte lokalne poświadczenia SQL Server. Pamiętaj, że w tym przypadku na serwerze zdalnym muszą być używane dokładnie takie same poświadczenia. Jeśli spróbujesz wykonać poniższe zapytanie, będąc zalogowany jako użytkownik, którego nie ma w liście poświadczeń linked server’a np. 'NieMaUsera’:

Pojawi się następujący komunikat błędu:

Msg 18456, Level 14, State 1, Line 1
Login failed for user ‘NieMaUsera’.

Be made using this security context

Ostatnią opcją listy For a login not defined in the list above, connections will jest Be made using this security context. w polach Remote login i With password, wprowadź istniejące dla zdalnego SQL’a login i hasło, w innym wypadku wystąpi błąd:

 

Ostatnią rzeczą w menu Select a page są opcje serwera – Server Options. Jeśli wybierzesz tą opcję, wyświetli się następujące okno:

 

Możesz tu przejrzeć i ustawić dodatkowe opcje linked server’a.

Collation Compatible

Pierwszą opcją jest opcja kompatybilności Collation. Ta opcja jest używana do identyfikacji, czy linked server ma to samo collation co serwer lokalny. Ta opcja powinna być ustawiona na True tylko wtedy, jeśli linked server ma identyczne collation jak nasz server lokalny, w innym przypadku powinno być wybrane False (opcja domyślna).

Data Access

Ta opcja jest używana do zezwolenia/odmówienia dostępu do danych na linked serverze. Jeśli opcja ustawiona jest na False, dostęp zdalny będzie odrzucony. Opcja ta jest przydatna, żeby czasowo odmówić dostępu do danych zdalnych. Jeśli wykonamy zapytanie do linked server’a z zaznaczoną opcją False, otrzymamy następujący komunikat błędu:

Msg 7411, Level 16, State 1, Line 1
Server ‘WINSERVER\SQLSERVER’ is not configured for DATA ACCESS.

Domyślnie opcja jest ustawiona na True.

RPC i RCP Out

Opcja RCP (Remote Procedure Call) jest używana w celu umożliwienia dostępu do zdalnych procedur wywoływanych z linked server’a lub go dotyczących.

Jeśli te opcje ustawione są na False, próba wywołania procedury z linked server’a zakończy się następującym komunikatem błędu:

Msg 7411, Level 16, State 1, Line 4
Server ‘WINSERVER\SQLSERVER’ is not configured for RPC.

Domyślnie obie opcje mają ustawioną wartość False.

Use Remote Collation

Jeśli ta opcja ustawiona jest na True, kolekcja będzie używana kolekcja określona w polu Collation Name dla źródła, które nie jest źródłem danych SQL Server, jednak jeśli opcja będzie ustawiona na False wtedy będzie używane collation lokalnego serwera. Domyślnie opcja ta ustawiona jest na False.

Collation Name

Jeśli Use Remote Collation ustawione jest na True, opcja ta używana jest do sprecyzowania nazwy collation linked server’a dla źródła danych, które nie jest źródłem danych SQL Server. Jeśli wybierzemy collation name, musi to byś collation wspierane przez SQL Server.

Connection Timeout

Ta opcja jest używana do ustawienia maksymalnego czasu oczekiwania na połączenie z linked server’em. Jeśli jest ustawionana 0 (zero), to używana jest opcja serwera remote login timeout. Domyślnie dla tej opcji ustawiona jest wartość 10 sekund. Dla SQL Server 2008 wartość domyślna to 20 sekund.

Query Timeout

Ta opcja określa ile czasu w sekundach może trwać zdalny proces. Domyślną wartością jest 600 sekund (10 minut). Żeby wyłączyć query timeout wstaw wartość 0 (zero) w odpowiadającym polu, a SQL zawsze będzie czekał, aż zapytanie zostanie ukończone.

Distributor

W tej opcji może być określone, czy linked server uczestniczy w replikacji jako distribution Publisher.

Distributor to instancja bazy danych, która pełni rolę magazynu dla specyficznych danych replikacji powiązanych z jednym lub wieloma Publisher’ami.

Publisher

W tej opcji może być ustawione, czy linked server będzie replication publisher czy też nie. Jeśli wartość to True, linked server jest publisher’em. W odwrotnej sytuacji nim nie jest.

Publisher jest instancją bazy danych, która udostępnia dane innym lokalizacjom przez replikację.

Subscriber

W tej opcji możemy określić, czy linked server jest subscriber’em replikacji, czy nie.

Subscriber jest instancją bazy danych, która otrzymuje zreplikowane dane.

Więcej o funkcjach Distributor, Publisher, Subscriber można znaleźć na stronie Replication Publishing Model Overview.

Lazy schema validation

Ta opcja umożliwia sprawdzenie zmian jakie zaszły w schemacie od kompilacji tabel zdalnych. Jeśli jest ustawiona na False (ustawienie domyślne), SQL Server sprawdza zmiany przed wykonaniem zapytania i jeśli są jakieś zmiany, rekompiluje zapytanie. Jeśli Lazy schema validation ustawione jest na True, SQL Server opóźnia sprawdzenie schematu tabel zdalnych do czasu wykonania zapytania.

Enable Promotion of Distributed Transactions

Ta opcja jest używana do ochrony akcji procedury transakcji server-to-server przez Microsoft Distributed Transaction Coordinator (MS DTC). Jeśli jest ustawiona na True, wywołanie zdalnej procedury rozpoczyna distributed transaction i rejestruje transakcje w MS DTC.

Kiedy już wszystko ustawiłeś wciśnij przycisk OK w oknie dialogowym New Linked Server. Nowo utworzony linked server pojawi się pod folderem Linked Servers.

Żeby sprawdzić, czy linked server działa poprawnie, kliknij prawym przyciskiem myszy na jego nazwiei wybierz polecenie Test Connection:

 

Jeśli połączenie z linked server’em zostało ustanowione poprawnie zobaczysz wyskakujące okno informacyjne:

W innym przypadku pojawi się okno z komunikatem błędu, przez który połączenie nie może zostać ustanowione:

 

Wykonywanie zapytań odwołujących się do linked server’a

Wykonywanie zapytań odwołujących się do linked server’a jest nieco odmienne od zapytan lokalnych w SQL Server. W normalnych zapytaniach zazwyczaj używamy notacji dwuczęściowej [Schemat].[NazwaObiektu], dla przykładu MySchema.MyTable:

Kiedy wykonujemy zapytanie z linked server’a, używamy czteroczęściowej notacji LinkedServer.BazaDanych.Schemat.NazwaObiektu. Żeby otrzymać w wyniku dane zawarte w tabeli MyTable, wykonujemy zapytanie wyglądające tak:

Usuwanie linked server’a

Żeby usunąć linked server, pod folderem Linked Servers, kliknij prawym przyciskiem myszy na linked server, który chcesz usunąć i z menu kontekstowego wybierz polecenie Delete:

 

otworzy to okno dialogowe Delete Object:

 

kliknij przycisk OK i potwierdź swój wybór w wyskakującym oknie dialogowym przez kliknięcie przycisku Yes:

 

Jeśli wszystko poszło dobrze linked server, który chcemy usunąć zniknie z listy pod folderem Linked Servers.