SQLShack (Magyar)

a T-SQL lekérdezések írásakor gyakori tevékenység a helyi adatbázisokhoz való csatlakozás és az adatok közvetlen feldolgozása. De lesznek olyan helyzetek, amikor csatlakozni kell egy távoli adatbázishoz, amely ugyanazon a kiszolgálón vagy egy másik fizikai kiszolgálón található, és az adatokat a helyi adatfeldolgozással párhuzamosan dolgozza fel.,

az SQL Server négy hasznos módszert kínál számunkra a távoli adatbázis-kiszolgálókhoz való csatlakozáshoz, még más adatbázis-kiszolgáló típusokhoz is, valamint az adatok lekérdezéséhez a T-SQL utasításán belül. Ebben a cikkben megvitatjuk ezt a négy módszert, valamint azt, hogyan lehet használni a távoli SQL Server adatbázisok lekérdezéséhez.

OPENDATASOURCE

a távoli SQL Server adatbázis lekérdezésének első módja az alábbi OPENDATASOURCE T-SQL funkció:

OPENDATASOURCE (provider_name as char, init_string)

ahol a provider_name az OLE DB szolgáltató, amelyet az adatforrás eléréséhez használnak., Az init_string pedig a távoli szerver csatlakozási karakterlánca.

az OPENDATASOURCE utasítás használatához meg kell győződnie arról, hogy a DisallowAdhocAccess rendszerleíró kulcs 0 értékre van állítva az SQL szerveren kívüli szolgáltatóhoz, amely megtalálható a rendszerleíró kulcsok alábbi elérési útján :

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

engedélyeznie kell az Ad Hoc elosztott lekérdezések speciális konfigurációs opcióját is, amely alapértelmezés szerint le van tiltva az SQL Serverben., Ha megpróbálja futtatni az alábbi egyszerű lekérdezést, amely az OPENDATASOURCE T-SQL utasítást használja, akkor a következő hibát kapja:

Msg 15281, Level 16, State 1, Line 1
SQL Server blokkolt hozzáférés az “OpenRowset/OpenDatasource “összetevőhöz” Ad Hoc elosztott lekérdezések”, mivel ez az összetevő ki van kapcsolva a szerver biztonsági konfigurációjának részeként. A rendszergazda sp_configure használatával engedélyezheti az “Ad Hoc elosztott lekérdezések” használatát., Az “Ad Hoc elosztott lekérdezések” engedélyezésével kapcsolatos további információkért keresse meg az “Ad Hoc elosztott lekérdezéseket” az SQL Server könyveiben online.

amint az a hibaüzenetből látható, engedélyezni kell az Ad Hoc elosztott lekérdezések speciális konfigurációs opcióját annak érdekében, hogy megnyissa a kapcsolatot egy távoli kiszolgálóval az OPENDATASOURCE segítségével.,

Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully., OPENDATASOURCE helyettesítheti a szerver nevét a négy részből álló neve a táblázat vagy nézet egy SELECT, INSERT, UPDATE, vagy DELETE nyilatkozatot. Azt is fel lehet használni az EXECUTE nyilatkozatot futtatni egy távoli tárolt eljárás.

OPENROWSET

a távoli SQL-kiszolgálón tárolt adatbázis lekérdezésének második módja az OPENROWSET T-SQL funkció. Az OPENROWSET ad hoc módszer használatához meg kell adnia minden olyan csatlakozási információt, amely a távoli SQL szerverhez való csatlakozáshoz szükséges., Azt is fel lehet használni a tömeges művelet a beépített ömlesztett szolgáltató olvasni az adatokat a fájlokat. Az OPENROWSET a FROM záradékban táblázatnévként használatos a SELECT, INSERT, UPDATE vagy DELETE utasításokban. Bár a lekérdezés több eredménykészletet is visszatérhet, az OPENROWSET csak az elsőt adja vissza.

az OPENROWSET használatához engedélyeznie kell az Ad Hoc elosztott lekérdezések speciális konfigurációs opcióját, amely megegyezik az OPENDATASOURCE funkcióval.,979d8a2e”>

lehet írni az előző OPENDATASOURCE lekérdezés segítségével a OPENROWSET funkció a következők szerint:

Kapcsolódó Kiszolgáló

az SQL Server Kapcsolódó Kiszolgáló eléréséhez használt távoli OLE DB adatok, források, mint például egy SQL Szerver példány kívül található, az SQL Server vagy más rendszerek, mint például az Oracle, Microsoft Access, illetve Excel, illetve teljesíti az elosztott T-SQL-lekérdezések ellenük.,

az SQL Server Linked Server különbözik az Ad hoc lekérdezésektől, mivel az Ad hoc lekérdezések ideiglenes kapcsolatot nyitnak meg a távoli kiszolgálóval, majd bezárják azt, ahol az állandó kapcsolt szerver mindig használható. Amikor a felhasználó elosztott lekérdezést hajt végre egy távoli adatforrás ellen egy kapcsolt szerver segítségével, az SQL Server Engine elemzi a parancsot, majd elküldi a kéréseket az OLE DB-nek. Ez a kérés lehet egy végrehajtandó lekérdezés vagy egy táblázat, amelyet meg kell nyitni a távoli kiszolgálón.,

egy kapcsolt kiszolgáló konfigurálható SQL Server Management Studio vagy a sp_addlinkedserver T-SQL utasítás használatával.

a kapcsolódó kiszolgáló SQL Server Management Studio használatával történő konfigurálásához bontsa ki a Szerverobjektumok csomópontját az objektum Explorer ablakból. Kattintson a jobb gombbal a kapcsolódó szerver csomópontra, majd válassza az új kapcsolt kiszolgáló lehetőséget.

az új kapcsolt kiszolgáló ablak Általános lapján válassza ki a kapcsolt kiszolgáló nevét, majd válassza ki annak a kiszolgálónak a típusát, amelyhez kapcsolódnia kell a kapcsolt kiszolgáló használatával., Válassza SQL Server ha sikerül csatlakozni egy távoli SQL Server példány, vagy válasszon más adatforrás közül választhat a rendelkezésre álló OLE DB szerver típusok a Szolgáltató legördülő listából eltérő SQL Server. Ha az SQL Server-t választja Kiszolgálótípusként, akkor a kapcsolt Kiszolgálónévnek a távoli SQL-kiszolgáló hálózati nevének kell lennie.

töltse ki a terméknév mezőt a kiválasztott OLE DB adatforrás terméknevével, például az SQL Serverrel, ha távoli SQL szerverhez csatlakozik., Írja be a kiválasztott adatforrás nevét az adatforrás mezőbe, például az SQL Server példány nevét, ha távoli SQL Server példányhoz csatlakozik. Töltse ki a Szolgáltató karakterlánc mezőt az OLE DB szolgáltató-specifikus csatlakozási karakterlánccal, amely egyedi adatforrást azonosít. A hely mező kitölthető a távoli adatbázis helyével az OLE DB szolgáltató értelmezése szerint. Írja be az OLE DB szolgáltató által használt katalógus nevét a katalógus mezőbe.,

nem szükséges az összes leírt argumentumot együtt használni, mivel a szükséges érvek a kiválasztott szolgáltatótól függenek. Például az SQL Server provider használatával csak két érvet fog használni.

az új összekapcsolt kiszolgáló ablak biztonsági oldalán adja meg azt a biztonsági környezetet, amelyet a kapcsolt kiszolgáló az eredeti SQL-kiszolgáló távoli adatforráshoz történő csatlakoztatásához használ., Ahogy a neve is mutatja, a helyi szerver bejelentkezések távoli szerver bejelentkezések leképezések része a biztonsági ablak, megadhatja a felhasználók listáját, amelyek segítségével a kapcsolt szerver csatlakozni a távoli szerver feltérképezésével ezek a helyi felhasználók távoli szerver bejelentkezések. Ezek a felhasználók SQL Server-hitelesítésen vagy Windows-hitelesítési bejelentkezésen keresztül lehetnek.

a leképezési listában nem definiált bejelentkezésekhez ki kell választania a távoli kiszolgálóval való kapcsolataik biztonsági környezetét az adott kapcsolt kiszolgálóval., Ha a nem készítés lehetőséget választja, az megakadályozza, hogy az előző listában nem szereplő felhasználók használják ezt a kapcsolt kiszolgálót. Kiválasztása benyújtani használata nélkül biztonsági összefüggésben a felhasználók nem szerepel a korábbi térképezés lista csatlakozni kell a távoli szerverhez kapcsolódó kiszolgáló megadása nélkül biztonsági összefüggésben őket. Ha úgy dönt, hogy a Bejelentkezés jelenlegi biztonsági környezetével történik, akkor a távoli kiszolgálóval való kapcsolat a csatlakozó felhasználó segítségével jön létre, ami a legjobb választás., A biztonsági környezet használatával történő végrehajtáskor adja meg az SQL Server hitelesítési hitelesítő adatait, amelyeket a távoli kiszolgálóval való kapcsolat létrehozására használnak a leképezési listában nem meghatározott felhasználók számára.

sok lehetőség van, hogy lehet hangolni, attól függően, hogy a követelmények a szerver beállítások oldalon az új kapcsolódó szerver ablak. Állítsa például a Collation kompatibilis opciót true értékre, ha biztos benne, hogy a távoli adatforrás karakterkészlete megegyezik a helyi kiszolgálóval., Alapértelmezés szerint az SQL Server helyileg értékeli a karakteroszlopok összehasonlítását, ha nem használja ezt az opciót. Az adathozzáférés opció a kapcsolt kiszolgáló elosztott lekérdezési hozzáférésének engedélyezésére és letiltására szolgál. Ahhoz, hogy a távoli eljárás hívás a megadott szerver állítsa be az RPC true, valamint ahhoz, hogy a távoli eljárás hívás a megadott szerver állítsa be az RPC ki true.

annak eldöntése, hogy a helyi vagy távoli kiszolgáló összevonását használják-e a lekérdezésben, a Remote Collation opció határozza meg., Ha ez az opció értéke igaz, megadhatja a távoli kiszolgáló által használt kollációs nevet a Collation Name opcióban. Ez akkor alkalmazható, ha a távoli adatforrás nem SQL Server, ahol megadhatja az SQL Server által támogatott kollációs nevet ezen a területen.

felülbírálhatja az alapértelmezett kiszolgáló távoli bejelentkezési időkorlátját az adott kapcsolt kiszolgálóhoz, ha a kapcsolat Timeout opcióját 0-nál nagyobb értékre változtatja.Ez az érték másodpercben adja meg a kapcsolt kiszolgálóhoz való csatlakozáshoz szükséges időt., A kapcsolt kiszolgálóhoz csatlakozó lekérdezések időtúllépését másodpercek alatt is megadhatja, ha a lekérdezési időtúllépés opciót 0-nál nagyobb értékre változtatja. Ez felülírja a szerver távoli lekérdezési időtúllépését a kapcsolt kiszolgálóra.

egy másik hasznos lehetőség, amelyet egy elosztott tranzakció elindításához használnak távoli tárolt eljárás hívásakor, amelyben ezt a tranzakciót az MS DTC kezeli és védi. Ezt az opciót Elosztott tranzakciók engedélyezésének nevezik.,ő az alábbiakban az üzenetet, ellenkező esetben hibaüzenet jelenik meg, mutatja, hogy van valami, ami megakadályozza, hogy a kapcsolat megnyitását:

könnyen létrehozhat az előző kapcsolódó kiszolgáló használata a sp_addlinkedserver T-SQL-utasítás halad a szükséges érvek a következők szerint:

miután a kapcsolt szerver sikeresen létrejött, a következő négyrészes név megadásával tudja használni: Linked_Server_Name.Remote_Database_Name.Schema_Name.Table_Name mint az alábbi példában:

OPENQUERY

a távoli adatforráshoz való csatlakozáshoz használt utolsó SQL Server módszer az OPENQUERY funkció. Ez egy alternatív egyszeri ad hoc módszer a távoli kiszolgálóhoz való csatlakozáshoz a kapcsolt szerver segítségével. A távoli kiszolgálóhoz való gyakoribb kapcsolatok esetén jobb, ha a kapcsolt kiszolgálót használja az OPENQUERY funkció helyett.

az OPENQUERY funkció használható a SELECT, INSERT, UPDATE vagy DELETE utasítás FROM záradékában, amely a táblázat nevét helyettesíti., Két argumentumra van szükség: a kapcsolt szerver nevére és a lekérdezésre. Ezek a paraméterek nem lehetnek változóak, az alábbiak szerint:

OPENQUERY ( linked_server ,’query’ )

Az alábbiakban egy egyszerű példa az OPENQUERY használatára:

1	válassza a * lehetőséget az openquery(dev_sql,’select * from testdb.dbo.,Profil’)

Összehasonlítás

használatakor a Kapcsolódó, Kiszolgáló lekérdezés egy távoli szerverre, a lekérdezés optimalizáló jön létre a végrehajtási terv után minősítette, majd elosztjuk a lekérdezés a helyi, mind a távoli lekérdezés, ahol a helyi lekérdezések kivégzik helyi, mind a távoli lekérdezést küld a távoli szerver, akkor együttesen jelenítse meg a végső eredményt, hogy a felhasználó, mint egyetlen eredmény beállítva., A kapcsolt szerver másik hátránya, hogy a távoli kiszolgálón nem lesz szűrés, ha a lekérdezés rendelkezik a WHERE záradékkal, ahol az összes rekordot lekéri a távoli asztalról, majd a szűrést, a csatlakozást helyben végzi.

az OpenQuery funkció esetében az SQL motor nem próbálja meg osztályozni a lekérdezést, vagy ellenőrizni, hogy mit fog tenni, egyszerűen elküldi a lekérdezést a távoli kiszolgálónak. Az SQL lekérdezés elemzése, a végrehajtási terv generálása, valamint az összes szűrés a távoli kiszolgálón történik.,

általában az OpenQuery gyorsabb, mint a kapcsolt szerver, mivel az SQL motor nem fogja megszakítani a lekérdezést, mielőtt elküldené a távoli kiszolgálóra, de csak egyszeri, kevésbé gyakori távoli kapcsolatokhoz hasznos.

az OPENROWSET és OPENDATASOURCE funkciók használatával minden alkalommal meg kell adnia a kapcsolat adatait, beleértve a felhasználónevet és a jelszót is. Bár ezek a funkciók nem nyújtanak minden kapcsolódó kiszolgáló funkciót, például a biztonsági kezelést, kevesebb erőforrást fogyaszt a kiszolgálóról., Mivel ezek a funkciók egyszeri kapcsolatot nyitnak meg a távoli kiszolgálóval, jobb, ha a kapcsolt kiszolgálót használja a távoli szerver gyakori eléréséhez.

• Szerző
• Utolsó Hozzászólás

Ahmad Yaseen egy Microsoft Big Data mérnök, mély tudás, tapasztalat SQL BI, SQL Server Adatbázis-kezelés-Fejlesztési területeken.,
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.
emellett számos blogban hozzájárul az SQL tippjeihez.,
összes Megtekintése hozzászólások Ahmad Yaseen

Legújabb hozzászólások Ahmad Yaseen (minden)

• Hogyan monitor Azure Data Factory – január 15, 2021
• A Forrása az Azure Data Factory – január 12, 2021
• Sablonok Használata a Azure Data Factory – január 8, 2021