O activitate comună atunci când scrieți T-SQL interogări este conectarea la baze de date locale și de prelucrare a datelor direct. Dar vor exista situații în care trebuie să vă conectați la o bază de date la distanță care se află într-o instanță diferită în același server sau într-un server fizic diferit și să procesați datele sale în paralel cu procesarea locală a datelor., SQL Server ne oferă patru metode utile pentru a vă conecta la serverele de baze de date la distanță, chiar și la alte tipuri de servere de baze de date și pentru a interoga datele sale în declarația T-SQL. În acest articol, vom discuta aceste patru metode și cum să le folosim pentru a interoga bazele de date SQL Server la distanță.
OPENDATASOURCE
prima metodă pentru a interoga de la distanță o bază de date SQL Server este OPENDATASOURCE T-SQL funcție de mai jos:
OPENDATASOURCE ( provider_name ca char, init_string )
Unde provider_name este OLE DB provider folosit pentru a accesa sursa de date., Iar init_string este șirul de conectare al serverului de la distanță.
Pentru a fi capabil de a utiliza OPENDATASOURCE declarație, trebuie să vă asigurați că DisallowAdhocAccess cheie de registry este setată la 0 pentru furnizorul de la care doriți să vă conectați la alte decât SQL Server, care poate fi găsit în partea de jos calea Cheile de Registry :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Furnizorii\<ProviderName>
de Asemenea, aveți nevoie pentru a permite Ad-Hoc Interogări Distribuite avansate opțiune de configurare care este dezactivată în mod implicit în SQL Server., Dacă încercați să executați de mai jos interogare simplă care utilizează OPENDATASOURCE T-SQL, veți primi eroarea:
Msg 15281, Nivel 16, Stat 1, Linia 1
SQL Server blocat accesul la DECLARAȚIA ‘OpenRowset/OpenDatasource „componentă” Ad-Hoc Interogări Distribuite pentru că această componentă este oprit, ca parte a configurației de securitate pentru acest server. Un administrator de sistem poate permite utilizarea „interogărilor distribuite Ad-Hoc” folosind sp_configure., Pentru mai multe informații despre activarea „interogărilor distribuite Ad-hoc”, căutați „interogări distribuite Ad-Hoc” în cărțile SQL Server Online.
după cum puteți vedea din mesajul de eroare, opțiunea de configurare avansată a interogărilor distribuite Ad Hoc ar trebui să fie activată pentru a deschide conexiunea la un server la distanță folosind 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 poate înlocui numele serverului în numele din patru părți al tabelului sau poate fi vizualizat într-o instrucțiune SELECT, INSERT, UPDATE sau DELETE. Acesta poate fi, de asemenea, utilizat în instrucțiunea EXECUTE pentru a rula o procedură stocată la distanță.
OPENROWSET
al doilea mod de a interoga o bază de date găzduită într-un server SQL la distanță este funcția OPENROWSET T-SQL. Pentru a utiliza metoda ad hoc OPENROWSET, trebuie să furnizați toate informațiile de conectare necesare pentru conectarea la serverul SQL de la distanță și multe alte resurse., De asemenea, poate fi utilizat pentru o operațiune în vrac prin intermediul furnizorului încorporat în vrac pentru a citi datele din fișiere. OPENROWSET este utilizat în clauza FROM Ca nume de tabel în instrucțiunile SELECT, INSERT, UPDATE sau DELETE. Deși interogarea poate returna mai multe seturi de rezultate, OPENROWSET returnează doar primul.
utilizarea OPENROWSET necesită activarea opțiunii de configurare avansată a interogărilor distribuite Ad Hoc la fel ca funcția OPENDATASOURCE.,979d8a2e”>
puteți scrie anterioare OPENDATASOURCE interogare folosind OPENROWSET funcție, după cum urmează:
Linked Server
UN Server SQL Server Legat este folosit pentru a accesa de la distanță de date OLE DB surse, cum ar fi o instanță de SQL Server situat în afara SQL Server sau alte sisteme, cum ar fi Oracle, Microsoft Access și Excel, și să execute distribuite T-SQL interogări împotriva lor.,
un server legat de SQL Server este diferit de interogările ad hoc prin faptul că interogările ad hoc deschid o conexiune temporară cu serverul de la distanță și îl închid, unde serverul conectat permanent este întotdeauna disponibil pentru utilizare. Când utilizatorul execută o interogare distribuită împotriva unei surse de date la distanță folosind un server conectat, motorul SQL Server analizează acea comandă și trimite cererile către OLE DB. Această solicitare poate fi o interogare de executat sau un tabel care va fi deschis pe acel server de la distanță.,
un server conectat poate fi configurat folosind SQL Server Management Studio sau folosind declarația sp_addlinkedserver T-SQL.pentru a configura un server conectat folosind SQL Server Management Studio, extindeți nodul obiecte Server din fereastra Object Explorer. Faceți clic dreapta pe nodul serverului conectat și alegeți noul server conectat.
În fila General din Noul Server Legat fereastră, pentru a alege un nume pentru server legat, apoi alegeți tipul de server, aveți nevoie pentru a conecta folosind ca server legat., Selectați SQL Server dacă reușiți să vă conectați la o instanță SQL Server la distanță sau alegeți altă sursă de date pe care să o selectați dintre tipurile de servere OLE DB disponibile din lista derulantă furnizor, alta decât SQL Server. Dacă alegeți SQL Server ca tip de Server, numele serverului conectat ar trebui să fie numele rețelei serverului SQL la distanță.
completați câmpul Nume produs cu numele produsului sursei de date OLE DB selectate, cum ar fi SQL Server dacă vă conectați la un server SQL la distanță., Tastați numele sursei de date selectate în câmpul sursă de date, cum ar fi numele instanței SQL Server dacă vă conectați la o instanță SQL Server la distanță. Completați câmpul Provider String cu șirul de conexiune specific furnizorului OLE DB care identifică o sursă de date unică. Câmpul Locație poate fi completat cu locația bazei de date la distanță interpretată de furnizorul OLE DB. Introduceți numele catalogului care va fi utilizat de furnizorul OLE DB în câmpul Catalog.,
nu vi se cere să utilizați toate argumentele descrise împreună, deoarece argumentele necesare depind de furnizorul selectat. De exemplu, folosind SQL Server provider, veți folosi doar două argumente.
în pagina de securitate a noii ferestre a serverului conectat, specificați contextul de securitate pe care serverul conectat îl va utiliza pentru a conecta serverul SQL original la sursa de date la distanță., După cum explică numele, în conectările serverului Local la conectările serverului la distanță mapează o parte din fereastra de securitate, puteți specifica o listă de utilizatori care pot utiliza serverul conectat pentru a se conecta la serverul de la distanță prin maparea acestor utilizatori locali cu conectările serverului la distanță. Acești utilizatori pot fi prin autentificare SQL Server sau autentificare Windows.
pentru autentificările care nu au fost definite în lista de mapare, trebuie să alegeți contextul de securitate pentru conexiunile lor la serverul de la distanță folosind acel server conectat., Alegerea Not be made va împiedica orice utilizator care nu este inclus în lista anterioară să utilizeze acel server conectat. Selectarea se face fără a utiliza un context de securitate, utilizatorii care nu sunt incluși în lista de mapare anterioară se vor conecta la serverul de la distanță folosind acel server conectat fără a specifica un context de securitate pentru ei. Dacă alegeți să fie făcută folosind contextul de securitate curent de conectare, atunci conexiunea la serverul de la distanță va fi stabilită folosind utilizatorul de conectare, care este cea mai bună alegere., În opțiunea be made folosind acest context de securitate, specificați acreditările de autentificare SQL Server care vor fi utilizate pentru a stabili conexiunea la serverul de la distanță pentru utilizatorii care nu sunt definiți în lista de mapare.
există multe opțiuni pe care le puteți regla în funcție de cerințele dvs. din pagina Opțiuni Server a noii ferestre server conectat. De exemplu, setați opțiunea compatibilă cu colaționarea la true dacă sunteți sigur că sursa de date la distanță are același set de caractere și ordine de sortare ca serverul local., În mod implicit, SQL Server evaluează comparațiile locale pe coloanele de caractere dacă nu utilizați această opțiune. Opțiunea de acces la date este utilizată pentru a activa și dezactiva accesul de interogare distribuit pentru serverul conectat. Pentru a activa apelul de procedură la distanță de pe serverul specificat setați RPC-ul la true și pentru a activa apelul de procedură la distanță către serverul specificat setați RPC-ul la true.
a decide dacă colaționarea serverului local sau la distanță va fi utilizată în interogare este determinată de opțiunea Utilizare colaționare la distanță., Dacă valoarea acestei opțiuni este adevărată, puteți specifica numele de colaționare care va fi utilizat de serverul de la distanță în opțiunea nume de colaționare. Acest lucru se aplică dacă sursa de date la distanță nu este SQL Server, unde puteți specifica orice nume de colaționare acceptat de SQL Server în acel câmp.
puteți suprascrie timeout-ul de conectare la distanță al serverului implicit pentru acel server conectat modificând opțiunea Timeout de conexiune la orice valoare mai mare decât 0.Această valoare specifică intervalul de timp în câteva secunde pentru conectarea la serverul conectat., De asemenea, puteți specifica time-out în câteva secunde pentru interogările conectarea la serverul conectat prin schimbarea opțiunii Timeout interogare la orice valoare mai mare decât 0. Aceasta va suprascrie timpul de interogare de la distanță al serverului pentru serverul conectat.o altă opțiune utilă care este utilizată pentru a începe o tranzacție distribuită atunci când apelați o procedură stocată la distanță, în care această tranzacție va fi gestionată și protejată de MS DTC. Această opțiune se numește activare promovare a tranzacțiilor distribuite.,el mesajul de mai jos, în caz contrar un mesaj de eroare va fi afișat arată că există ceva prevenirea conexiune de a fi deschis:
puteți crea cu ușurință anterioare legate de server folosind sp_addlinkedserver T-SQL trecerea argumente necesare, după cum urmează:
|
1
2
3
4
|
UTILIZARE
GO
SECURITY master.,dbo.sp_addlinkedserver @server = N’DEV_SQL’, @srvproduct=N’SQL Server’
GO
|
Odată ce legate de server este creat cu succes, va fi capabil să-l folosească prin specificarea patru-parte numele, care include: Linked_Server_Name.Remote_Database_Name.Schema_Name.Table_Name ca în exemplul de mai jos:
|
1
2
|
SELECT * FROM DEV_SQL.,testdb.dbo.Profil
GO
|
OPENQUERY
ultimul SQL Server metodă care este utilizat pentru a vă conecta la o distanță de sursa de date este OPENQUERY funcție. Este o metodă alternativă ad-hoc unică pentru conectarea la un server la distanță folosind serverul conectat. Pentru conexiuni mai frecvente la serverul de la distanță, este mai bine să utilizați serverul conectat în locul funcției OPENQUERY.funcția OPENQUERY poate fi utilizată în clauza FROM din instrucțiunea SELECT, INSERT, UPDATE sau DELETE care înlocuiește numele tabelului., Este nevoie de două argumente; numele serverului legat și interogarea. Acești parametri pot fi variabile, după cum urmează:
OPENQUERY ( linked_server , „query”)
mai Jos este un exemplu simplu de OPENQUERY utilizare:
|
1
|
SELECTAȚI * DE la OPENQUERY(DEV_SQL,’SELECT * FROM testdb.dbo.,Profil’)
|
comparație
când utilizați serverul conectat pentru a interoga un server la distanță, Optimizatorul de interogări va crea planul de execuție după clasificarea și împărțirea interogării în interogări locale și la distanță, unde interogările locale vor fi executate local, iar interogările la distanță vor fi trimise către serverul rezultat pentru utilizator ca set de rezultate unice., Un alt dezavantaj al serverului legat este că, nu se va aplica nici o filtrare pe serverul de la distanță dacă interogarea are clauza WHERE, unde va prelua toate înregistrările din tabelul de la distanță și va face filtrarea și îmbinarea la nivel local.
În cazul funcției OpenQuery, motorul SQL nu va încerca să clasifice interogarea sau să verifice ce va face, pur și simplu va trimite interogarea așa cum este la serverul de la distanță. Parsarea interogării SQL, generarea planului de execuție și toate filtrările vor fi efectuate pe serverul de la distanță., în general, OpenQuery este mai rapid decât serverul conectat, deoarece motorul SQL nu va rupe interogarea înainte de a o trimite la serverul de la distanță, dar este util doar pentru conexiuni la distanță mai puțin frecvente. folosind funcțiile OPENROWSET și OPENDATASOURCE, trebuie să specificați toate detaliile conexiunii, inclusiv numele de utilizator și parola de fiecare dată când îl utilizați. Deși aceste funcții nu oferă toate funcționalitățile serverului conectat, cum ar fi gestionarea securității, consumă mai puține resurse de pe serverul dvs., Deoarece aceste funcții deschid o conexiune unică la serverul de la distanță, este mai bine să utilizați serverul conectat pentru acces frecvent la serverul de la distanță.
- Autor
- Mesajele Recente
este expert Microsoft Certified Solution în managementul și analiza datelor, Microsoft Certified Solution Associate în administrarea și dezvoltarea bazelor de date SQL, Azure Developer Associate și Microsoft Certified Trainer.
De asemenea, el contribuie cu sfaturile sale SQL în multe bloguri.,
Vezi toate posturile de Ahmad Yaseen
- Cum de a monitoriza Azure data Factory – 15 ianuarie 2021
- Folosind Sursa de Control în Azure data Factory – ianuarie 12, 2021
- Folosind Template-uri în Azure data Factory – 8 ianuarie 2021