20 października 2011

Linked Server w SQL Server dla początkujących

Linked Server jako dobry wujek


Linked Server w SQL Serwerze oferuje prosty mechanizm udostępnienia danych ze zdalnego serwera (MS SQL Server, Oracle, DB2, itd). Z mojego doświadczenia wyniki, że jest to jeden z bardziej nadużywanych komponentów MS SQL Server'a. Korzystanie z Linked Server'ów jest na tyle proste, że większość systemów opartych na silniku baz danych MSSQL używa ich do przesyłania danych pomiędzy systemami.

Łatwość używania Linked Server'ów spowodowała, że stały się one jakby "standardem" wykorzystywanym do "integracji" systemów. W przypadku jednorazowego przesłania pewnej ilości danych użycie Linked Server'a jest jak najbardziej zrozumiałe. Jednak tworzenie w ten sposób rozwiązania integrującego np. dwa kluczowe systemy w firmie jest dla mnie pewnym nieporozumieniem.

Często połączenie dwóch systemów zaczyna się jako niewinne udostępnienie jednego widoku, tak na próbę. Po jakims czasie zaczynamy zaciągać kolejne tabele, nawet wykonywać złożone zapytania z JOIN'ami pomiędzy widokami/tableami zdalnymi i lokalnymi. Apogeum takiej sytuacji jest udostępnianie procedur składowanych wywoływanych przez zewnętrzne systemy.



Primo, żeby działało czyli architektura gwoździa i młotka


Użycie Linked Server jest często drogą na skróty, zdarza się, że integracja z innymi systemami to ostatni etap projektu. Wtedy nie ma czasu na implementację zestawu pakietów SQL Server Integration Services(SSIS), czy zaprojektowanie usług do pobierania danych, już nie mówię o SOA (przede wszystkim dlatego, że się na tym nie znam:)).SSIS zapewnia duży worek kompnentów np. do kowersji typów z którą mogą pojawić się problemy jeżeli wyciągamy dane z np. Oracle'a. SSIS jest też bardziej elastyczny, pozwala skalować te rozwiązania, które z czasem mogą zrobić się bardzo skomplikowane.

Secundo, dane są przesyłane po sieci


Inny problemem jest to, że z jakiegoś magicznego powodu, programiści MSSQL'a zapominają o jednej ważnej kwestii. Podczas wykonywania zapytań na Linked Server dane muszą zostać przesłane przez sieć zanim dotrą do docelowej instancji MSSQL'a. Ostatnio widziałem rozwiązanie oparte na Linked Server'ach, które wywoływały zdalne zapytania z poziomu aplikacji klienta. Okazało się, że aplikacja pomimo, że była używana na razie tylko przez kilku użytkowników już przeżywała problemy wydajnościowe. Ciężko, też było zrozumieć architektom systemu, że przesyłanie 1 miliona wierszy pomiędzy serwerami dla każdego wywołania zapytania nie jest dobrym pomysłem.

Mechanizm Linked Server'a umożliwia wywoływanie zdalnych procedur (jeżeli źródło danych to instancja MSSQL'a). Nie znaczy to, że koniecznie trzeba go używać. Niech przestrogą jak to może być nadużywane będzie następująca historia. W pewnym systemie z którym pracowałem ten mechanizm został "użyty" w następujący sposób. Poniżej jak wyglądała implementacja z użyciem Linked Server'a i wywołań procedur na zdalnych serwerach.

  1. Aplikacja intranetowa wywoływała procedurę ProcA na serwerze ServerA
  2. Procedura ProcA na ServerA wywoływała procedurę ProcB na serwerze ServerB (via Linked Server)
  3. Procedura ProcB na ServerB wywoływała procedurę ProcC na serwerze ServerC  (via Linked Server)
  4. Procedure ProcC na ServerzeC wywoływała (korzystając z Linked Servera wskazującego na lokalną instancje) wywoływała ProcC2 na ServerC
Rozwiązania to w pewnym momencie działało (chyba było to nawet kilka lat), aczkolwiek ilość danych i użytkowników w systemach A, B, C systematycznie rosła, i zaczęły pojawiać się timeout'y, blokowanie itd.  Rozwizanie tego problemu, bez przepisania połowy systemu A i B, okazało się praktycznie niemożliwe, nawet hardware upgrade nie pomógł.

Przykład użycia


Chciałbym nie pozostać gołosłownym i zademonstrować dwa przykłady użycia Linked Server'a. Na dwóch serwerach HOME i HOME\SQL2008R2 mam po jednej instancji bazy danych AdventureWorks.W pierszym przykładzie prezentuję zapytanie skonstruowane z dwóch tabel znajdujących się na zdalnym serwerze HOME.

Tworzymy login linkedaccount na serwerze HOME (zdalny) i użytkownika dla bazy AdventureWorks z uprawnieniami do odczytu danych.

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'linkedaccount')
BEGIN
 DROP LOGIN [linkedaccount]
END
GO
CREATE LOGIN [linkedaccount] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[AdventureWorks]
GO
USE [AdventureWorks]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'linkedaccount')
BEGIN
 DROP USER [linkedaccount]
END
GO
CREATE USER [linkedaccount] FOR LOGIN [linkedaccount] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember db_datareader, [linkedaccount] 

Na serwerze HOME\SQL2008R2 tworzymy Linked Server o nazwie HOME i konfigurujemy login linkedaccount jako konto z dostępem do danych zdalnego serwera HOME.

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'.\WE2005')
BEGIN
 EXEC master.dbo.sp_dropserver @server=N'HOME', @droplogins='droplogins'
END
GO
EXEC master.dbo.sp_addlinkedserver @server = N'HOME', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HOME',@useself=N'False',@locallogin=NULL,@rmtuser=N'linkedaccount',@rmtpassword='P@ssw0rd'


Uruchamiamy pierwsze zapytanie na serwerze HOME\SQL2008R2.

use AdventureWorks;

SELECT empRemote.LoginID
     , empHistoryRemote.StartDate
     , empHistoryRemote.DepartmentID
  FROM HOME.AdventureWorks.HumanResources.Employee empRemote
  JOIN HOME.AdventureWorks.HumanResources.EmployeeDepartmentHistory empHistoryRemote
    ON empRemote.EmployeeID = empHistoryRemote.EmployeeID
 WHERE empHistoryRemote.StartDate < '1998-12-12'

Powyższe zapytanie do dwóch zdalnych tabel znajdujących się na serwerze HOME jest uruchamiane z poniższym planem wykonania. Widać na nim, że JOIN został wykonany na serwerze zdalnym i tylko 19 wierszy zostało zwróconych do serwera HOME\SQL2008R2.


Drugi przykład prezentuje plan zapytania przy użyciu trzech tabel "zdalnych" i jednej lokalnej znajdującej się na serwerze HOME\SQL2008R2. Po dodaniu do zapytania pierszego jednej tabeli zdalnej HumanResources.EmployeePayHistory i jednej lokalnej HumanResources.Employee otrzymujemy całkiem inny plan zapytania. Zdalne zapytanie (Remote Query na planie) jest wykonywane dwukrotnie, ponieważ SQL Server w ten sposób radzi sobie z optymalizacją (mniej lub bardziej udaną). 

use AdventureWorks;

SELECT empRemote.LoginID
     , empHistoryRemote.StartDate
     , empHistoryRemote.DepartmentID
     , empPayRemote.Rate
     , empLocal.Gender
  FROM HOME.AdventureWorks.HumanResources.Employee empRemote
  JOIN HOME.AdventureWorks.HumanResources.EmployeePayHistory empPayRemote
    ON empRemote.EmployeeID = empPayRemote.EmployeeID 
  JOIN HumanResources.Employee empLocal
    ON empRemote.EmployeeID = empLocal.EmployeeID 
  JOIN HOME.AdventureWorks.HumanResources.EmployeeDepartmentHistory empHistoryRemote
    ON empLocal.EmployeeID = empHistoryRemote.EmployeeID
 WHERE empHistoryRemote.StartDate < '1998-12-12'

Nie doszukiwałbym się jakiejś szczególnej logiki w powyższym zapytaniu. Chodziło mi tylko o pokazanie jak plan zapytania z użyciem Linked Server może ulec zmianie. W pewnym momencie może się okazać, że pobieramy ze zdalnego serwera całą dużą tabelę, żeby tylko ją lokalnie odfiltrować.

Podsumowanie




Nie jestem w stanie udowodnić w tak krótkim poście, że rozwiązania na Linked Serverach są trudne do utrzymania i mogą doprowadzić do problemów wydajnościowych. Mogę tylko zachęcić do wnikliwej ewaluacji potencjalnych pomysłów, które używają mechanizmu Linked Server. Pewnie, jest on wygodny, prosty w użyciu i w administracji, aczkolwiek jak ze wszystkim trzeba zachować pewną dozę rozsądku.

Brak komentarzy:

Prześlij komentarz

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.