przez: Eric Blinn | aktualizacja: 2019-06-28 | Komentarze (4) | Powiązane: więcej > serwery połączone
Problem
widzę, że w SQL Server Management Studio (SSMS) istnieje opcja dla serwerów połączonych i chcę dowiedzieć się więcej o tym, co robią i jak działają.
rozwiązanie
połączone serwery są metodą, za pomocą której serwer SQL może rozmawiać z inną bazą danych zgodną z ODBC, taką jak inna instancja SQL Server lub baza danych Oracle, z zapytaniem directT-SQL.,
istnieje kilka ważnych ustawień, które należy znać podczas konfigurowania połączonego serwera.Ta wskazówka obejmie tworzenie połączonego serwera z inną instancją serwera SQL przy użyciu GUI SSMS wraz z odpowiednimi opcjami zabezpieczeń. Pokaże również, jak używać Linked Server w instrukcji T-SQL. Po opanowaniu podstaw dostępna jest biblioteka bardziej zaawansowanych tematów.,
Tworzenie SQL Server Linked Server
W Sql Server Management Studio, w okienku Object Explorer rozwiń sekcję „ServerObjects”, kliknij prawym przyciskiem myszy na „Linked Servers” i wybierz „NewLinked Server…” z menu.
New Linked Server – ogólna strona
która wyświetli ekran, który wygląda jak ekran poniżej. Wybierz servertype jako SQL Server i wpisz nazwę drugiej instancji SQL Server. W tym przykładzie połączenie zostanie nawiązane do nazwanej instancji serwera SQL na tej samej maszynie., Jest to z pewnością dozwolone, ale częściej połączenie jest wykonywanew całości na innej maszynie. Nazwa połączonego serwera jest określana jako theServerName\InstanceName. W poniższym przykładzie podłączam się do serwera localserver i nazwa instancji to „SECURITY_TEST”.
nowa strona Linked Server – Security
następnie przejdź ze strony ogólnej do strony Security za pomocą menu po lewej stronie.Ta strona pozwala administratorowi powiedzieć temu serwerowi SQL, jak uwierzytelnić drugą instancję., Górna połowa ekranu pozwala na indywidualne logowanie się do korzystania z połączonego serwera.
istnieją 2 metody uwierzytelniania użytkowników. Pierwszym z nich jest wybranie loginu do użycia podczas korzystania z połączonego serwera. Na zrzucie ekranu poniżej użytkownik Eric automatycznie loguje się do serwera wtórnego, ponieważ login łączy się z drugą instancją. Poświadczenie to jest przechowywane w silniku SQL Server i jest szyfrowane. Jeden drugi wiersz, login LinkedServerTest (Logowanie SQL) jest sprawdzany Impersonatebox., Spowoduje to próbę użycia tej samej nazwy użytkownika i hasła na serwerze LinkedServer, co na oryginalnym serwerze. Podszywanie się pod Login Windows może być również wykonywane, ale wymaga odpowiedniego skonfigurowania delegacji.
teraz skup się na dolnej połowie ekranu. Dostępne są 4 opcje. Który zostanie wybrany tutaj, będzie miał zastosowanie do każdego użytkownika niewymienionego w górnej sekcji.,
| opcja | opis |
|---|---|
| nie zostanie wykonana | spowoduje to błąd każdego użytkownika próbującego użyć połączonego serwera, którego nie ma na powyższej liście. Aby wybrać tę opcję, musi być co najmniej 1 login w górnej połowie ekranu. To jest błąd, który otrzymają wszyscy inni użytkownicy. Msg 7416, Level 16, State 1,Line 1 dostęp do zdalnego serwera jest zabroniony, ponieważ nie ma login-mappingexists., |
| być wykonane bez użycia kontekstu bezpieczeństwa | spowoduje to wysłanie anonimowego połączenia do połączonego serwera i prawdopodobnie nie zapewni żadnego dostępu do serwera wtórnego. |
| być wykonane przy użyciu bieżącego kontekstu bezpieczeństwa logowania | jest to to samo, co umieszczenie każdego pojedynczego użytkownika w górnej sekcji z zaznaczonym polem podszywania się., |
| być wykonane przy użyciu tego kontekstu bezpieczeństwa: | ta opcja będzie wymagała wprowadzenia loginu i hasła i będzie działać tak samo, jak gdyby każdy użytkownik został dodany do górnej sekcji i podał dokładnie tę samą nazwę użytkownika i hasło. Jest to najmniej bezpieczna metoda konfiguracji serwera połączonego, ponieważ każdy, kto ma dostęp do pierwszego serwera SQL, będzie mógł korzystać z serwera połączonego przy użyciu zapisanych poświadczeń., |
nowa strona Linked Server – Opcje serwera
na stronie opcje są również ustawienia, ale dla prostych zapytań te ustawienia mogą być pozostawione jako wartości domyślne, jak pokazano poniżej.
ustawienia nie muszą być zmieniane w większości przypadków użycia, a ta tabela wyjaśnia, co robią ustawienia.
| opcja | opis |
|---|---|
| kompatybilny z kolacją | domyślnie jest false., Można to ustawić na true, jeśli serwer docelowy używa dokładnie tej samej klasyfikacji co serwer główny. Zmiana może poprawić wydajność niektórych zapytań, umożliwiając zdalne porównywanie kolumn z kolumnami typu tekstowego. |
| dostęp do danych | domyślnie jest True. Gdy false wszystkie poszczególne zdalne zapytania wykonywanewyniki nie powiodą się. To ustawienie jest niezależne od wyjścia RPC. W scenariuszu, gdzie tylko zdalne wywołania procedury muszą być wykonane to ustawienie może zostać zmienione na false, podczas gdy RPC Out pozostaje prawdziwe., |
| RPC / RPC Out | to oznacza zdalne wywołanie procedury i pozwala na uruchomienie storedprocedures na połączonym serwerze. RPC włącza zdalne rozmowy z podanego serwera, a RPC OUT włącza zdalne rozmowy z podanym serwerem. |
| Użyj nazwy zdalnego zestawiania / zestawiania | Wartość domyślna to False. Gdy wartość false, Kolacja serwera lokalnego będzie używana we wszystkich zapytaniach zdalnych. Gdy true, zestawianie serwera zdalnego będzie używane w zapytaniach., Jeżeli true i zdalny serwer nie jest serwerem SQLServer, to lokalny serwer nie może automatycznie wyszukać właściwej wartości collationvalue. W tym przypadku użyj ustawienia Collation Name, aby wpisać w theremote server collation. |
| limit czasu połączenia | domyślnie wynosi 0. Gdy jest niezerowa, jest to liczba sekund przed przerwą czasową podczas łączenia się z połączonym serwerem. Gdy 0 to wartość jest rzeczywiście odczytywana z domyślnego serwera, który można znaleźć przy użyciu polecenia runningthis.
exec sp_configure 'remote login timeout (s)' |
| limit czasu oczekiwania na zapytanie | domyślnie wynosi 0., Gdy jest niezerowa, jest to liczba sekund przed zakończeniem zapytania uruchamianego na połączonym serwerze. Gdy 0 wtedy wartość jest rzeczywiście odczytywana z domyślnego serwera, który można znaleźć przez uruchomienie tego polecenia.
exec sp_configure 'remote query timeout (s)' |
| Dystrybutor / Wydawca / Abonent | ustawienia te są używane tylko wtedy, gdy połączony serwer jest używany do wspierania replikacji. |
| Walidacja leniwego schematu | domyślnie jest False. W przypadku false sprawdzany jest schemat zdalnego zapytania przed każdym wykonaniem zapytania., Wszelkie zmiany w zdalnym schemacie są wykrywane z wyprzedzeniem i można odtworzyć odpowiedni plan zapytań, aby uwzględnić zmianę. Gdy prawda schemat nie jest sprawdzany do momentu wyjęcia. Jeśli zmiana w zdalnym schemacie spowoduje unieważnienie planu zapytań, spowoduje to błąd w uruchomieniu. |
| Włącz promocję transakcji rozproszonych | domyślnie jest True. Po włączeniu dowolne zdalne zapytanie, które zmienia dane, wywoła DTC (Distributed Transaction Coordinator)., Proces ten chroni takie zapytania, pozwalając obu serwerom na współdzielenie transakcji, która kończy się w 100% lub 0% – tak jak zwykła transakcja z lokalnym zapytaniem. Po wyłączeniu kod DTC nie jest wywoływany, a nieudane remotequery może się nie udać i nie zostać całkowicie wycofane. |
w celu odczytu danych z połączonego serwera należy odwoływać się do wszelkich tabel lub widoków za pomocą 4-częściowego identyfikatora składającego się z nazwy połączonego serwera, nazwy bazy danych, nazwy schematu i nazwy obiektu – w tej kolejności.,
na przykład:
SELECT * FROM .master.sys.databasesSELECT * FROM .WideWorldImporters.Sales.Orders
- najpierw podana jest nazwa połączonego serwera, która jest dla naszego przykładu. W tym przykładzie musi być w nawiasach ze względu na swój format. Nie wszystkie nazwy serwerów LinkedServer muszą być dołączone.
- dalej jest odpowiednio nazwa bazy danych, master i WideWorldImporters.
- trzecia to odpowiednio nazwa schematu, sys i Sales.
- na koniec wyświetlana jest nazwa obiektu. W tych przykładach obiektami są bazy danych i rozkazy.
Ten przykład jest dość brzydki ze względu na charakter nazwy połączonego serwera., Na szczęście ta świetna wskazówka pokazuje, jak ułatwić czytanie nazw w takich scenariuszach.
po wypisaniu w klauzuli FROM te tabele i widoki mogą być traktowane w sposób exactsame jako inne tabele lokalne. Mogą być odpytywane, aliasowane, łączone, a jeśli ustawione są odpowiednie uprawnienia, mogą być modyfikowane za pomocą insert, update i deletestatements.
wywołanie procedur przechowywanych na połączonym serwerze
na potrzeby tego demo, ta mała procedura została utworzona w bazie masterdatabase na docelowym połączonym serwerze.,
USE masterGOCREATE PROCEDURE dbo.SQLTips1 AS SELECT 'I was able to be called';
procedura jest wykonywana na instancji głównej przy użyciu tego polecenia, które obejmuje ten sam schemat identyfikatora 4 części, Co instrukcje w poprzedniej sekcji.
EXEC .master.dbo.SQLTips1
jednak zamiast oczekiwanego wyniku pojawia się błąd.
RPC oznacza zdalne wywołanie procedury. Domyślnie serwery połączone są godne uwagi do wykonywania wywołań procedur składowanych. Można temu łatwo zaradzić, włączając pojedyncze ustawienie na połączonym serwerze., Przejdź do Właściwości połączonych serwerów, wybierz Opcje serwera i 4thoption na liście będzie RPC Out. Po prostu zmień tę wartość na True i clickOK.
ustawienie o nazwie RPC nie jest wartością, którą należy zmienić. To może wydawać się mylące, ponieważ komunikat o błędzie wydaje się nazywać go konkretnie, ale w rzeczywistości jest to starsze ustawienie, które nie włącza wywołań RPC.
ponowne uruchomienie procedury daje teraz znacznie lepszy wynik.,
SQL Server Linked Server ograniczenia
Linked server mogą być bardzo przydatne, ale istnieje kilka ograniczeń, z których należy zdawać sobie sprawę przed ich wdrożeniem. Serwery połączone nie mogą być używane do wywoływania funkcji tablevalued lub scalar. Ponadto polecenie truncate nie może być wywołane przy użyciu połączonego serwera.
przypadki użycia SQL Server Linked Server
wydajność serwerów Linked nie zawsze jest duża, zwłaszcza przy dużych zestawach danych i wielu połączeniach między tabelami local i linked. Ale mogą być bardzo przydatne do odpytywania mniejszych zbiorów danych.,
czasami wymóg ETL dla mniejszego zbioru danych może być spełniony za pomocą LinkedServer, a nie narzędzia ETL, takiego jak SSIS. Gdy liczba wierszy w tysięcznych lub niższych dziesiątkach tysięcy, ETL oparty na serwerze połączonym może być w stanie ukończyć w czasie krótszym niż potrzeba, aby uruchomić silnik SSIS.
Następne kroki
- na MSSQLTips znajduje się cała rodzina zaawansowanych podpowiedzi dotyczących połączonych serwerów.,com
- ułatwienie odczytu nazw połączonych serwerów
Ostatnia aktualizacja: 2019-06-28
w 2009 roku, po raz pierwszy w historii firmy, w 2010 roku, firma została wyróżniona tytułem Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Data Architect w kategorii Master Jest również autorem SQL i liderem grupy PASS Local.
Zobacz wszystkie moje porady
- Więcej porad SQL Server DBA…