SQLShack (Polski)

powszechną czynnością podczas pisania zapytań T-SQL jest łączenie się z lokalnymi bazami danych i bezpośrednie przetwarzanie danych. Będą jednak sytuacje, w których będziesz musiał połączyć się ze zdalną bazą danych, która znajduje się w innej instancji na tym samym serwerze lub na innym serwerze fizycznym, i przetwarzać jej dane równolegle z lokalnym przetwarzaniem danych.,

SQL Server zapewnia nam cztery przydatne metody do łączenia się ze zdalnymi serwerami baz danych, nawet z innymi typami serwerów baz danych, i odpytywania swoich danych w instrukcji T-SQL. W tym artykule omówimy te cztery metody i sposób korzystania z nich do odpytywania zdalnych baz danych SQL Server.

OPENDATASOURCE

pierwszą metodą odpytywania zdalnej bazy danych SQL Server jest funkcja T-SQL OPENDATASOURCE poniżej:

OPENDATASOURCE ( provider_name as char, init_string)

Gdzie provider_name jest dostawcą OLE DB używanym do uzyskania dostępu do źródła danych., Init_string jest ciągiem połączeń zdalnego serwera.

aby móc korzystać z instrukcji OPENDATASOURCE, musisz upewnić się, że klucz rejestru DisallowAdhocAccess jest ustawiony na 0 dla dostawcy, z którym chcesz się połączyć, innego niż serwer SQL, który znajduje się w poniższej ścieżce kluczy rejestru :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\<ProviderName>

musisz również włączyć opcję Advanced Configuration Ad Hoc distributed queries, która jest domyślnie wyłączona w SQL Server., Jeśli spróbujesz uruchomić poniższe proste zapytanie, które używa instrukcji T-SQL OPENDATASOURCE, otrzymasz błąd:

Msg 15281, Level 16, State 1, Line 1
SQL Server zablokował dostęp do instrukcji 'OpenRowset/OpenDatasource' komponentu 'Ad Hoc Distributed Queries', ponieważ ten komponent jest wyłączony jako część konfiguracji zabezpieczeń dla tego serwera. Administrator systemu może włączyć „rozproszone zapytania ad Hoc” za pomocą sp_configure., Aby uzyskać więcej informacji na temat włączania „rozproszonych zapytań ad Hoc”, wyszukaj „rozproszone zapytania Ad Hoc” w książkach SQL Server Online.

jak widać z komunikatu o błędzie, opcja advanced configuration Ad Hoc Distributed Queries powinna być włączona, aby otworzyć połączenie ze zdalnym serwerem za pomocą OPENDATASOURCE.,

Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully., OPENDATASOURCE może zastąpić nazwę serwera w czteroczęściowej nazwie tabeli lub widoku w instrukcji SELECT, INSERT, UPDATE lub DELETE. Może być również użyty w instrukcji EXECUTE do uruchomienia Zdalnej procedury składowanej.

OPENROWSET

drugim sposobem odpytywania bazy danych hostowanej na zdalnym serwerze SQL jest funkcja OPENROWSET T-SQL. Aby użyć metody ad hoc OPENROWSET, musisz podać wszystkie informacje o połączeniu, które są wymagane do połączenia ze zdalnym serwerem SQL i wieloma innymi zasobami., Może być również używany do operacji zbiorczej za pośrednictwem wbudowanego dostawcy zbiorczego do odczytu danych z plików. OPENROWSET jest używany w klauzuli FROM jako nazwa tabeli w poleceniach SELECT, INSERT, UPDATE lub DELETE. Chociaż zapytanie może zwrócić wiele zestawów wyników, OPENROWSET zwraca tylko pierwszy.

Korzystanie z OPENROWSET wymaga włączenia opcji Ad Hoc Distributed Queries advanced configuration tak samo jak funkcja OPENDATASOURCE.,979d8a2e”>

możesz napisać poprzednie zapytanie OPENDATASOURCE przy użyciu funkcji OPENROWSET w następujący sposób:

linked server

serwer SQL linked server służy do uzyskiwania dostępu do zdalnych źródeł danych OLE DB, takich jak instancja SQL Server znajdująca się poza serwerem SQL lub innymi systemami, takimi jak Oracle, Microsoft Access i Excel, i wykonywania rozproszonego T-SQL zapytania przeciwko nim.,

serwer połączony SQL różni się od zapytań ad hoc tym, że zapytania ad hoc otwierają tymczasowe połączenie ze zdalnym serwerem i zamykają je, gdzie stały serwer połączony jest zawsze dostępny do użycia. Gdy użytkownik wykonuje rozproszone zapytanie przeciwko zdalnemu źródłu danych za pomocą połączonego serwera, Silnik serwera SQL przetwarza to polecenie i wysyła żądania do OLE DB. Żądanie to może być zapytaniem do wykonania lub tabelą do otwarcia na tym zdalnym serwerze.,

połączony Serwer można skonfigurować za pomocą SQL Server Management Studio lub za pomocą instrukcji T-SQL sp_addlinkedserver.

aby skonfigurować serwer połączony za pomocą SQL Server Management Studio, rozwiń węzeł obiektów serwera w oknie Object Explorer. Kliknij prawym przyciskiem myszy węzeł Linked Server i wybierz New Linked Server.

w zakładce Ogólne nowego okna połączonego serwera wybierz nazwę połączonego serwera, a następnie wybierz typ serwera, z którym chcesz się połączyć za pomocą tego połączonego serwera., Wybierz SQL Server, jeśli uda Ci się połączyć ze zdalną instancją SQL Server, lub wybierz inne źródło danych, aby wybrać spośród dostępnych typów serwerów OLE DB z listy rozwijanej Provider inne niż SQL Server. Jeśli wybierzesz SQL Server jako typ serwera, nazwa serwera połączonego powinna być nazwą sieciową zdalnego serwera SQL.

wypełnij pole Nazwa Produktu nazwą produktu wybranego źródła danych OLE DB, na przykład SQL Server, Jeśli łączysz się ze zdalnym serwerem SQL., Wpisz nazwę wybranego źródła danych w polu Źródło danych, na przykład nazwę instancji SQL Server, Jeśli łączysz się ze zdalną instancją SQL Server. Wypełnij pole Provider String ciągiem połączenia OLE DB specyficznym dla dostawcy, który identyfikuje unikalne źródło danych. Pole Lokalizacja może być wypełnione lokalizacją zdalnej bazy danych, jak zinterpretował dostawca OLE DB. Wpisz nazwę katalogu używanego przez dostawcę OLE DB w polu Katalog.,

nie musisz używać wszystkich opisanych argumentów razem, ponieważ niezbędne argumenty zależą od wybranego dostawcy. Na przykład, używając SQL Server provider, użyjesz tylko dwóch argumentów.

na stronie Bezpieczeństwo nowego okna Linked Server określ kontekst bezpieczeństwa, którego serwer linked server będzie używał do połączenia oryginalnego serwera SQL ze zdalnym źródłem danych., Jak sama nazwa wyjaśnia, w części mapowania loginów serwera lokalnego do loginów serwera zdalnego okna zabezpieczeń można określić listę użytkowników, którzy mogą używać połączonego serwera do łączenia się z serwerem zdalnym, mapując tych użytkowników lokalnych za pomocą loginów serwera zdalnego. Użytkownicy mogą korzystać z uwierzytelniania SQL Server lub loginu uwierzytelniania Windows.

w przypadku loginów, które nie zostały zdefiniowane na liście mapowania, musisz wybrać kontekst zabezpieczeń dla ich połączeń ze zdalnym serwerem za pomocą tego połączonego serwera., Wybór opcji nie bądź uczyniony uniemożliwi użytkownikowi spoza poprzedniej listy korzystanie z tego połączonego serwera. Wybierając opcję be made without a security context, użytkownicy spoza poprzedniej listy mapowania połączą się z serwerem zdalnym za pomocą tego połączonego serwera bez określania dla nich kontekstu zabezpieczeń. Jeśli wybierzesz być wykonane przy użyciu bieżącego kontekstu bezpieczeństwa logowania, połączenie ze zdalnym serwerem zostanie nawiązane przy użyciu łączącego się użytkownika, co jest najlepszym wyborem., W opcji be made using this security context określ poświadczenia uwierzytelniania SQL Server, które będą używane do nawiązania połączenia ze zdalnym serwerem dla użytkowników nie zdefiniowanych na liście mapowania.

istnieje wiele opcji, które można dostroić w zależności od wymagań na stronie Opcje serwera w nowym oknie połączonego serwera. Na przykład ustaw opcję zgodną z kolacją na true, Jeśli masz pewność, że zdalne źródło danych ma taki sam zestaw znaków i kolejność sortowania jak serwer lokalny., Domyślnie SQL Server ocenia porównania kolumn znaków lokalnie, jeśli nie używasz tej opcji. Opcja dostęp do danych służy do włączania i wyłączania rozproszonego dostępu do zapytań dla połączonego serwera. Aby włączyć zdalne wywołanie procedury z podanego serwera Ustaw RPC na true, a aby włączyć zdalne wywołanie procedury do podanego serwera Ustaw RPC na true.

decydowanie, czy w zapytaniu zostanie użyta Kolacja serwera lokalnego czy zdalnego, zależy od opcji Użyj kolacji zdalnej., Jeśli wartość tej opcji jest true, możesz określić nazwę sortowania, która będzie używana przez zdalny serwer w opcji Nazwa sortowania. Ma to zastosowanie, jeśli zdalnym źródłem danych nie jest SQL Server, gdzie można określić dowolną nazwę kolacji obsługiwaną przez SQL Server w tym polu.

możesz zastąpić domyślny czas zdalnego logowania serwera dla tego połączonego serwera, zmieniając opcję limit czasu połączenia na dowolną wartość większą niż 0.Wartość ta określa czas oczekiwania na połączenie z połączonym serwerem w sekundach., Możesz również określić czas oczekiwania w sekundach dla zapytań łączących się z połączonym serwerem, zmieniając opcję Query Timeout na dowolną wartość większą niż 0. Spowoduje to nadpisanie limitu czasu zdalnych zapytań serwera dla połączonego serwera.

kolejna użyteczna opcja, która jest używana do rozpoczęcia transakcji rozproszonej podczas wywoływania Zdalnej procedury składowanej, w której ta transakcja będzie zarządzana i chroniona przez MS DTC. Opcja ta nazywa się Enable Promotion of Distributed Transactions.,poniżej wiadomość, w przeciwnym razie zostanie wyświetlony komunikat o błędzie pokazujący, że coś uniemożliwia otwarcie połączenia:

możesz łatwo utworzyć poprzedni połączony serwer za pomocą polecenia sp_addlinkedserver T-SQL przekazującego wymagane argumenty w następujący sposób:

Po pomyślnym utworzeniu połączonego serwera będziesz mógł go używać, podając czteroczęściową nazwę zawierającą: Linked_Server_Name.Remote_Database_Name.Schema_Name.Nazwa tabeli jak w poniższym przykładzie:

openquery

ostatnią metodą SQL Server używaną do łączenia się ze zdalnym źródłem danych jest funkcja OPENQUERY. Jest to alternatywna Jednorazowa metoda ad hoc do łączenia się ze zdalnym serwerem za pomocą serwera połączonego. W przypadku częstszych połączeń z serwerem zdalnym lepiej jest użyć serwera połączonego zamiast funkcji OPENQUERY.

funkcja OPENQUERY może być używana w klauzuli FROM instrukcji SELECT, INSERT, UPDATE lub DELETE zastępującej nazwę tabeli., Pobiera dwa argumenty; nazwę połączonego serwera i zapytanie. Parametry te nie mogą być zmienne, w następujący sposób:

OPENQUERY ( linked_server ,’query' )

Poniżej znajduje się prosty przykład użycia OPENQUERY:

porównanie

podczas korzystania z połączonego serwera do odpytywania zdalnego serwera, optymalizator zapytań utworzy plan wykonania po sklasyfikowaniu i podzieleniu zapytania na zapytania lokalne i zdalne, gdzie zapytania lokalne będą wykonywane lokalnie, a zapytania zdalne zostaną wysłane na serwer zdalny, a następnie połączone ze sobą w celu wyświetlenia ostateczny wynik dla użytkownika jako pojedynczy wynik ustawiony., Inną Wadą Linked Server jest to, że nie będzie stosowane filtrowanie na serwerze zdalnym, jeśli zapytanie ma klauzulę WHERE, gdzie pobierze wszystkie rekordy ze zdalnej tabeli i zrobi filtrowanie i łączenie lokalnie.

W przypadku funkcji OpenQuery Silnik SQL nie będzie próbował sklasyfikować zapytania ani sprawdzić, co zrobi, po prostu wyśle zapytanie tak, jak jest na zdalny serwer. Parsowanie zapytania SQL, generowanie planu wykonania i wszystkie filtrowanie będą wykonywane na zdalnym serwerze.,

Ogólnie Rzecz Biorąc, OpenQuery jest szybsze od połączonego serwera, ponieważ silnik SQL nie złamie zapytania przed wysłaniem go na serwer zdalny, ale użyteczne tylko dla jednorazowych, rzadszych połączeń zdalnych.

korzystając z funkcji OPENROWSET i OPENDATASOURCE, musisz podać wszystkie szczegóły połączenia, w tym nazwę użytkownika i hasło za każdym razem, gdy go używasz. Chociaż funkcje te nie zapewniają wszystkich funkcji połączonych serwerów, takich jak zarządzanie bezpieczeństwem, zużywają mniej zasobów z serwera., Ponieważ funkcje te otwierają jednorazowe połączenie ze zdalnym serwerem, lepiej jest używać połączonego serwera do częstego dostępu do zdalnego serwera.

• Autor
• Ostatnie posty

Ahmad Yaseen jest inżynierem Microsoft big data z głęboką wiedzą i doświadczeniem w SQL BI, SQL Server database administration and development Fields.,
jest Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
także swoje porady SQL ' owe wnosi na wielu blogach.,
wyświetl wszystkie posty użytkownika Ahmad Yaseen

najnowsze posty użytkownika Ahmad Yaseen (Zobacz wszystkie)

• Jak monitorować fabrykę danych Azure – 15 stycznia 2021
• Korzystanie z kontroli źródeł w fabryce danych Azure – styczeń 12, 2021
• korzystanie z szablonów w fabryce danych usługi Azure – 8 stycznia 2021