společné aktivity při psaní T-SQL dotazů je připojení k místní databáze a zpracování dat přímo. Budou však situace, kdy se musíte připojit ke vzdálené databázi, která je umístěna v jiné instanci na stejném serveru nebo na jiném fyzickém serveru, a zpracovávat její data souběžně s místním zpracováním dat.,
SQL Server nám poskytuje čtyři užitečné metody pro připojení ke vzdálené databázové servery, i jiné databáze serveru typů, dotazu a jeho data ve své T-SQL. V tomto článku budeme diskutovat o těchto čtyřech metodách a o tom, jak je použít k dotazování vzdálených databází serveru SQL.
OPENDATASOURCE
první metoda k dotazu vzdáleného serveru SQL Server databáze je OPENDATASOURCE T-SQL funkce níže:
OPENDATASOURCE ( provider_name jako char, init_string )
Kde provider_name je OLE DB provider používá pro přístup k datům zdroj., A init_string je připojovací řetězec vzdáleného serveru.
Aby bylo možné používat OPENDATASOURCE prohlášení, musíte se ujistit, že DisallowAdhocAccess klíč registru je nastavena na 0 pro poskytovatele, který chcete připojit k jiné než SQL Server, které lze nalézt v níže cesta Klíče Registru :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\<Název_zprostředkovatele>
Také musíte povolit Ad Hoc Distributed Dotazy pokročilé konfigurační možnost, která je ve výchozím nastavení zakázána v systému SQL Server., Pokud se pokusíte spustit pod jednoduchý dotaz, který používá OPENDATASOURCE T-SQL se zobrazí chybová zpráva:
Msg 15281, Úroveň 16, Stav 1, Řádek 1
SQL Server zablokován přístup k PROHLÁŠENÍ ‚OpenRowset/OpenDatasource‘ součásti ‚Ad Hoc Distributed Dotazy, protože tato součást je vypnuto jako součást konfigurace zabezpečení pro tento server. Správce systému může povolit použití „Ad Hoc distribuovaných dotazů“ pomocí sp_configure., Pro více informací o povolení ‚Ad Hoc distribuované dotazy‘, hledat ‚Ad Hoc distribuované dotazy‘ v SQL Server Knihy on-line.
jak můžete vidět z chybové zprávy, měla by být povolena možnost rozšířené konfigurace Ad Hoc distribuovaných dotazů, aby se otevřelo připojení ke vzdálenému serveru pomocí OPENDATASOURCE.,
|
1
2
3
4
5
6
|
EXEC sp_configure ‚show advanced options‘, 1
RECONFIGURE
GO
EXEC sp_configure ‚ad hoc distributed queries‘, 1
RECONFIGURE
GO
|
Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully., OPENDATASOURCE může nahradit název serveru ve čtyřdílném názvu tabulky nebo zobrazit v příkazu SELECT, INSERT, UPDATE nebo DELETE. Může být také použit v příkazu EXECUTE ke spuštění vzdáleného uloženého postupu.
OPENROWSET
druhým způsobem dotazování databáze hostované na vzdáleném serveru SQL je funkce OPENROWSET T-SQL. Chcete-li použít metodu OPENROWSET ad hoc, musíte poskytnout všechny informace o připojení, které jsou potřebné pro připojení ke vzdálenému serveru SQL a mnoha dalším zdrojům., Může být také použit pro hromadnou operaci prostřednictvím vestavěného hromadného poskytovatele pro čtení dat ze souborů. OPENROWSET se používá v klauzuli FROM jako název tabulky v příkazech SELECT, INSERT, UPDATE nebo DELETE. Přestože dotaz může vrátit více sad výsledků, OPENROWSET vrátí pouze první.
použití OPENROWSET vyžaduje povolení ad Hoc distribuované dotazy Pokročilá konfigurace možnost stejná jako funkce OPENDATASOURCE.,979d8a2e“>
můžete napsat předchozí OPENDATASOURCE dotaz pomocí OPENROWSET takto:
Propojeného Serveru
SQL Server Propojený Server používá pro přístup vzdáleného zprostředkovatele OLE DB zdrojů dat, například SQL Server instance se nachází mimo SQL Server nebo jiné systémy, jako jsou Oracle, Microsoft Access a Excel, a spouštět distribuované T-SQL dotazy proti nim.,
Propojený Server SQL Server se liší od ad hoc dotazů v ad hoc dotazy otevřít dočasné připojení s dálkovým irc server a zavřete jej, kde je stálá propojený server je vždy k dispozici pro použití. Když uživatel provede distribuovaný dotaz proti vzdálenému zdroji dat pomocí propojeného serveru, Motor SQL Server analyzuje tento příkaz a odešle požadavky na OLE DB. Tento požadavek může být Dotaz k provedení nebo tabulka, která má být otevřena na tomto vzdáleném serveru.,
propojený Server lze konfigurovat pomocí SQL Server Management Studio nebo pomocí příkazu SP_ADDLINKEDSERVER T-SQL.
Chcete-li nakonfigurovat propojený server pomocí SQL Server Management Studio, rozbalte uzel objekty serveru z okna Průzkumníka objektů. Klepněte pravým tlačítkem myši na uzel Propojeného serveru a vyberte Nový propojený Server.
V kartě Obecné v dialogovém okně Nový Propojený Server, vybrat jméno pro vaše propojený server, pak zvolte typ serveru, budete muset připojit se pomocí propojeného serveru., Vyberte SQL Server, pokud se vám podaří připojit ke vzdálené instanci serveru SQL, nebo vyberte jiný zdroj dat a vyberte z dostupných typů serverů OLE DB z rozevíracího seznamu poskytovatele jiného než SQL Server. Pokud zvolíte SQL Server jako typ serveru, Název Propojeného serveru by měl být název sítě vzdáleného serveru SQL.
vyplňte pole Název produktu s názvem produktu vybraného zdroje dat OLE DB, například SQL Server, pokud se připojujete ke vzdálenému serveru SQL., Zadejte název vybraného zdroje dat do pole Zdroj dat,například název Instance serveru SQL, pokud se připojujete ke vzdálené instanci serveru SQL. Vyplňte pole řetězce poskytovatele pomocí připojovacího řetězce specifického pro poskytovatele OLE DB, který identifikuje jedinečný zdroj dat. Pole Umístění může být vyplněno vzdáleným umístěním databáze, jak je interpretováno poskytovatelem OLE DB. Do pole Katalog zadejte název katalogu, který má poskytovatel OLE DB použít.,
není nutné používat všechny popsané argumenty dohromady, jako třeba argumenty závisí na zvoleném poskytovateli. Například pomocí poskytovatele serveru SQL Server použijete pouze dva argumenty.
na stránce Zabezpečení nového okna Propojeného serveru zadejte bezpečnostní kontext, který propojený server použije k připojení původního serveru SQL ke vzdálenému zdroji dat., Jak název vysvětluje, v lokálním serveru přihlášení ke vzdálenému serveru přihlášení mapuje část okna zabezpečení, můžete zadat seznam uživatelů, kteří mohou pomocí propojeného serveru připojit ke vzdálenému serveru mapováním těchto místních uživatelů pomocí vzdáleného přihlášení serveru. Tito uživatelé mohou být prostřednictvím ověřování SQL Server nebo ověřování systému Windows přihlášení.
Pro přihlášení, která nebyla definována v mapování seznamu, budete muset vybrat kontext zabezpečení pro jejich připojení ke vzdálenému serveru pomocí propojeného serveru., Volba nebude provedena, zabrání jakémukoli uživateli, který není zařazen do předchozího seznamu, používat tento propojený server. Výběr Být vyrobeny bez použití kontextu zabezpečení, uživatelé nejsou zahrnuty v předchozím mapování seznamu, bude to připojit ke vzdálenému serveru pomocí propojeného serveru bez určení kontextu zabezpečení pro ně. Pokud se rozhodnete provést pomocí aktuálního bezpečnostního kontextu přihlášení, bude připojení ke vzdálenému serveru navázáno pomocí připojujícího uživatele, což je nejlepší volba., V pomocí tohoto kontextu zabezpečení možnost, zadat Ověřování serveru SQL Server pověření, která bude použita k vytvoření připojení ke vzdálenému serveru pro uživatele není definován v mapování seznamu.
Existuje mnoho možností, které můžete naladit v závislosti na vašich požadavcích ze Serveru Volby stránce v okně Nový Propojený Server. Například, nastavit Kolace Kompatibilní možnost platí, pokud jste si jisti, že vzdálená data zdroj má stejnou znakovou sadu a pořadí řazení jako místní server., Ve výchozím nastavení SQL Server vyhodnocuje srovnání na sloupcích znaků lokálně, pokud tuto možnost nepoužíváte. Možnost přístupu k datům se používá k povolení a deaktivaci přístupu distribuovaného dotazu pro propojený server. Chcete-li povolit volání vzdáleného postupu ze zadaného serveru, nastavte RPC na true a chcete-li povolit volání vzdáleného postupu na zadaný server, nastavte RPC na true.
rozhodování o tom, zda bude v dotazu použito místní nebo vzdálené řazení serveru, je určeno volbou Použít vzdálené řazení., Pokud je hodnota této volby pravdivá, můžete zadat název řazení, který bude použit vzdáleným serverem, ve volbě názvu řazení. To platí, pokud vzdálený zdroj dat není SQL Server, kde můžete v tomto poli zadat libovolný název řazení podporovaný SQL Serverem.
vzdálené přihlašovací timeout výchozího serveru pro tento propojený server můžete přepsat změnou volby časového limitu připojení na libovolnou hodnotu větší než 0.Tato hodnota určuje časový limit v sekundách pro připojení k propojenému serveru., Také můžete zadat časový limit v sekundách pro dotazy připojující se k propojenému serveru změnou volby časového limitu dotazu na libovolnou hodnotu větší než 0. Tím se přepíše časový limit vzdáleného dotazu serveru pro propojený server.
Další užitečné možnost, která se používá ke spuštění distribuované transakce při volání vzdáleného uložená procedura, ve které tato transakce bude spravovat a chránit tím, že MS DTC. Tato možnost se nazývá povolit propagaci distribuovaných transakcí.,že pod zprávu, jinak se zobrazí chybová zpráva, že ukazuje, že tam je něco, co brání spojení z otevření:
můžete snadno vytvořit předchozí propojený server pomocí sp_addlinkedserver T-SQL absolvování požadované argumenty takto:
|
1
2
3
4
|
EXEC master.,dbo.sp_addlinkedserver @server = N’DEV_SQL‘, @srvproduct=N’SQL Serveru‘
|
Jakmile propojený server je úspěšně vytvořen, budete moci použít zadáním název four-part, který zahrnuje: Linked_Server_Name.Remote_Database_Name.Schema_Name.Table_Name jako v příkladu níže:
|
1
2
|
SELECT * FROM DEV_SQL.,testdb.dbo.Profil
|
OTEVŘÍTDOTAZ
poslední SQL Server metoda, která slouží k připojení na vzdálený zdroj dat je openquery. Jedná se o alternativní jednorázovou metodu ad hoc pro připojení ke vzdálenému serveru pomocí propojeného serveru. Pro častější připojení ke vzdálenému serveru je lepší použít propojený server namísto funkce OPENQUERY.
funkci OPENQUERY lze použít v klauzuli z příkazu SELECT, INSERT, UPDATE nebo DELETE nahrazujícího název tabulky., Trvá dva argumenty; název propojeného serveru a dotaz. Tyto parametry mohou být proměnné, a to následovně:
OTEVŘÍTDOTAZ ( linked_server ,’query‘ )
Níže je uveden jednoduchý příklad OTEVŘÍTDOTAZ použití:
|
1
|
VYBERTE * Z OTEVŘÍTDOTAZ(DEV_SQL,’SELECT * FROM testdb.dbo.,Profil‘)
|
Srovnání
Při použití Propojený Server dotazu vzdáleného serveru, optimalizátor dotazu vytvoří plán spuštění po klasifikaci a rozdělení dotazu do lokální a vzdálené dotazy, kde místní dotazy budou provedeny místní a vzdálené dotazy budou odeslány na vzdálený server, pak se spojí dohromady k zobrazení konečný výsledek pro uživatele jako jediný výsledek nastavit., Další nevýhodou Propojeného Serveru je, že žádné filtrování bude použita na vzdáleném serveru, pokud dotaz obsahuje klauzuli where, kde to bude načíst všechny záznamy ze vzdáleného stolu a udělat filtrování a spojování lokálně.
v případě funkce OpenQuery se SQL Engine nepokusí klasifikovat dotaz nebo zkontrolovat, co udělá, jednoduše odešle dotaz tak, jak je na vzdálený server. Analýza dotazu SQL, generování plánu provádění a veškeré filtrování bude provedeno na vzdáleném serveru.,
obecně platí, Otevřítdotaz je rychlejší než propojeného serveru jako SQL Engine nebude zlomit dotazu před odesláním na vzdálený server, ale užitečné pouze pro jeden-čas méně časté vzdálené připojení.
pomocí funkcí OPENROWSET a OPENDATASOURCE musíte při každém použití zadat všechny podrobnosti o připojení včetně uživatelského jména a hesla. Přestože tyto funkce neposkytují všechny funkce propojeného serveru, jako je správa zabezpečení, spotřebovává méně zdrojů z vašeho serveru., Protože tyto funkce otevírají jednorázové připojení ke vzdálenému serveru, je lepší použít propojený server pro častý vzdálený přístup k serveru.
- Autora
- Poslední Příspěvky
je Microsoft Certified Solution Expert na Správu Dat a Analytics, Microsoft Certified Solution Associate v SQL Databáze Správa a Vývoj, Azure Developer partner a Microsoft Certified Trainer.
také přispívá svými tipy SQL v mnoha blogech.,
Zobrazit všechny příspěvky od Ahmad Yaseen
- Jak sledovat Azure Data Factory – 15. ledna 2021
- Pomocí Ovládání Zdroje v Azure Data Factory – 12. ledna, 2021
- Pomocí Šablony v Azure Data Factory – 8. ledna 2021