En felles aktivitet når du skriver T-SQL-spørringer er å koble til lokale databaser og behandling av data direkte. Men det vil være situasjoner der du trenger for å koble til en ekstern database som befinner seg i et annet eksempel i samme server eller på en annen fysisk server, og behandle sine data i parallell med den lokale data-behandling.,
SQL Server gir oss fire nyttige metoder for å koble til den eksterne database-servere, selv database server typer, og spørring data i T-SQL-setningen. I denne artikkelen vil vi diskutere disse fire metodene og hvordan du kan bruke det til å søke ekstern SQL Server-databaser.
OPENDATASOURCE
Den første metoden til å spørre en ekstern SQL Server-database er OPENDATASOURCE T-SQL-funksjonen nedenfor:
OPENDATASOURCE ( provider_name som røye, init_string )
Hvor provider_name er OLE DB-leverandøren som benyttes for å få tilgang til data kilde., Og init_string er tilkoblingsstreng for den eksterne serveren.
for Å være i stand til å bruke OPENDATASOURCE uttalelse, må du sørge for at DisallowAdhocAccess registernøkkelen er satt til 0 for den leverandøren du ønsker å koble til andre enn SQL Server, som kan bli funnet i under banen til registernøkler :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Leverandører\<ProviderName>
du må Også aktivere Ad Hoc Distribuert Søk avansert konfigurasjon alternativet som er deaktivert som standard i SQL Server., Hvis du prøver å kjøre nedenfor enkel spørring som bruker OPENDATASOURCE T-SQL-setningen vil du få feil:
Msg 15281 Nivå 16, Tilstand 1, Line 1
SQL Server blokkert tilgang til SETNINGEN «OpenRowset/OpenDatasource’ av komponent ‘Ad Hoc Distribuert Spørring’ fordi denne komponenten er slått av som en del av sikkerhetskonfigurasjon for denne serveren. Et system administrator kan aktivere bruk av «Ad Hoc Distribuert Spørring’ ved hjelp av sp_configure., For mer informasjon om aktivering av «Ad Hoc Distribuert Spørring’, søk etter ‘Ad Hoc Distribuert Spørring i SQL Server Books Online.
Som du kan se fra feilmelding, Ad Hoc Distribuert Søk avansert konfigurasjon alternativet være aktivert for å åpne tilkobling til en ekstern server ved hjelp av 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 kan erstatte server navn i fire-del navnet på bordet eller visning i en SELECT, INSERT, UPDATE eller DELETE uttalelse. Det kan også brukes i kjør-setningen for å kjøre et eksternt lagrede prosedyren.
OPENROWSET
Den andre måten å spørre en database vert i en ekstern SQL Server er OPENROWSET T-SQL-funksjon. For å bruke OPENROWSET ad hoc-metoden, må du oppgi all forbindelse med informasjon som er nødvendig for å koble til den eksterne SQL server, og mange andre ressurser., Det kan også brukes for en bulk drift gjennom bygget-i BULK tjenesteleverandøren for å lese data fra filer. OPENROWSET brukes i FRA klausulen som et bord navn i SELECT, INSERT, UPDATE eller DELETE uttalelser. Selv om spørringen kan gå tilbake flere resultatsett, OPENROWSET returnerer bare den første.
ved Hjelp av OPENROWSET krever at du aktiverer Ad Hoc-Distribuert Søk avansert konfigurasjon alternativ samme som OPENDATASOURCE funksjon.,979d8a2e»>
Du kan skrive den forrige OPENDATASOURCE spørring ved hjelp av den OPENROWSET funksjon som følger:
Knyttet Server
EN SQL Server Koblet Server brukes til å få tilgang til eksterne OLE DB-datakilder, for eksempel en SQL Server-forekomsten ligger utenfor SQL Server eller andre systemer som Oracle, Microsoft Access og Excel, og utføre distribuerte T-SQL-spørringer mot dem.,
EN SQL Server Koblet Server er forskjellig fra ad hoc-spørringer i at ad hoc-spørringer åpne en midlertidig forbindelse med den eksterne serveren, og lukk det igjen, der permanent koblet server er alltid tilgjengelig for bruk. Når brukeren utfører en distribuert spørring mot en ekstern datakilde ved hjelp av en koblet server, SQL Server Engine analyserer den kommandoen, og sender forespørsler til OLE DB. Denne forespørselen kan være en spørring til å utføre eller en tabell for å være åpnet på den eksterne serveren.,
En Koblet Server kan konfigureres ved hjelp av SQL Server Management Studio eller ved å bruke sp_addlinkedserver T-SQL-setningen.
for å konfigurere en koblet server med SQL Server Management Studio, utvide Server Objekter node fra Object Explorer-vinduet. Høyre-klikk på Lenkede Server node og velge Nye Koblet Server.
I kategorien Generelt i dialogboksen Ny Koblet Server i vinduet, velger du et navn for den tilknyttede serveren, og velg deretter type serveren, trenger du å koble til ved hjelp av det tilknyttede serveren., Velg SQL Server-hvis du klarer å koble til en ekstern SQL Server-instans, eller velge Andre datakilden for å velge fra de tilgjengelige OLE DB server typene fra Leverandør rullegardinlisten andre enn SQL Server. Hvis du velger SQL Server som Server Type, Knyttet Server-navn skal være navn på nettverk av eksterne SQL Server.
Fyll Produkt Navn-feltet med produktet navnet på den valgte OLE DB-datakilder, for eksempel SQL Server hvis du kobler til en ekstern SQL Server., Skriv inn navnet på den valgte datakilde i datakilden feltet, for eksempel SQL Server-Forekomsten navn hvis du kobler til en ekstern SQL Server-forekomsten. Fyll Leverandør String feltet med OLE DB-leverandøren for bestemte forbindelse streng som identifiserer en unik datakilde. Plasseringen feltet kan være fylt med den eksterne databasen sted som tolkes av OLE DB-leverandøren. Skriv inn navnet på katalogen for å bli brukt av OLE DB-leverandøren i Katalogen feltet.,
Du er ikke nødt til å bruke alle de som er beskrevet argumenter sammen, som de nødvendige argumenter er avhengig av den valgte leverandør. For eksempel, ved hjelp av SQL Server leverandøren, vil du bare bruker to argumenter.
i Sikkerhet siden av den Nye Koblet Server i vinduet, må du angi sikkerhet sammenheng at det er knyttet server vil bruke til å koble den opprinnelige SQL Server til den eksterne datakilden., Som navnet forteller, i den Lokale serveren innlogginger til en ekstern server innlogginger tilordninger del av Sikkerhets-vinduet, kan du angi en liste over brukere som kan bruke knyttet server til å koble deg til den eksterne serveren ved å kartlegge disse lokale brukere med ekstern server innlogginger. Disse brukerne kan være via SQL Server-Godkjenning eller en Windows-Godkjenning logg inn.
For logikk som ikke er definert i kartlegging listen, må du velge sikkerhetskonteksten for sine forbindelser til den eksterne serveren bruker som er knyttet server., Å velge å Ikke gjøres vil hindre en bruker som ikke er inkludert i den forrige liste fra en bruker som er knyttet server. Valg gjøres uten å bruke en sikkerhet i sammenheng, brukere som ikke er inkludert i den forrige kartlegging listen vil koble til den eksterne serveren bruker som er knyttet server uten å angi en sikkerhetskonteksten for dem. Hvis du velger gjøres ved hjelp av innlogging er gjeldende sikkerhets-sammenheng, da tilkoblingen til den eksterne serveren vil bli etablert ved hjelp av koble til brukeren, noe som er det beste valget., I den lages ved hjelp av denne sikkerhetsoppdateringen sammenheng alternativet, angir SQL Server-Godkjenning legitimasjonsbeskrivelsene som brukes for å opprette tilkobling til den eksterne serveren for brukere som ikke er definert i kartlegging listen.
Det er mange alternativer du kan stille, avhengig av dine krav fra Serveren til Alternativer-siden av den Nye Koblet Server i vinduet. Du kan For eksempel angi Sortering Kompatible alternativ til true hvis du er sikker på at den eksterne datakilden har samme tegnsett og sortere rekkefølgen som den lokale serveren., Standard SQL Server vurderer sammenligninger på karakter kolonner lokalt hvis du ikke bruker dette alternativet. De Data Access alternativet brukes til å aktivere og deaktivere distribuert spørring tilgang til koblede server. For å aktivere remote procedure call fra den spesifiserte serveren sett RPC til true, og for å aktivere remote procedure call for den angitte serveren sett RPC Ut til å true.
du Bestemmer om lokal eller ekstern server er sortering vil bli brukt i spørringen er fastsatt ved Bruk av Ekstern Sortering alternativ., Hvis dette alternativet er verdien er true, kan du angi sortering navn som vil bli brukt av den eksterne serveren i Sortering Navn alternativ. Dette er aktuelt hvis den eksterne datakilden er ikke SQL Server, hvor du kan angi hvilken som helst sortering navn støttes av SQL Server i dette feltet.
Du kan overstyre standard serveren for ekstern pålogging for tidsavbrudd for strømsparer for at knyttet server ved å endre Tidsavbrudd for Tilkobling alternativet til en verdi større enn 0.Denne verdien angir tiden i sekunder for å koble til den tilknyttede serveren., Du kan også angi tidsavbruddet (i sekunder) for spørsmål tilkobling til den sammenkoblede server ved å endre Spørringen alternativ for Tidsavbrudd for en verdi større enn 0. Dette vil overstyre server ekstern spørring for tidsavbrudd for strømsparer for den tilknyttede serveren.
en Annen nyttig alternativ som brukes til å starte en distributed transaction når du ringer en remote lagret prosedyre, som denne transaksjonen vil bli administrere og beskytte av MS DTC. Dette alternativet er kalt Enable Markedsføring av Distribuerte Transaksjoner.,han nedenfor melding, ellers en feilmelding som viser at det er noe som hindrer forbindelsen blir åpnet:
Du kan enkelt lage den forrige knyttet server med sp_addlinkedserver T-SQL-setningen bestått de nødvendige argumenter som følger:
|
1
2
3
4
|
BRUKER
GÅ
EXEC master.,dbo.sp_addlinkedserver @server = N’DEV_SQL’, @srvproduct=N’SQL Server’
GÅ
|
Når den tilknyttede serveren er opprettet, vil du være i stand til å bruke det ved å spesifisere fire-del navn som inneholder: Linked_Server_Name.Remote_Database_Name.Schema_Name.Table_Name som i eksempelet nedenfor:
|
1
2
|
SELECT * FROM DEV_SQL.,testdb.dbo.Profil
GÅ
|
OPENQUERY
Den siste SQL Server-metoden som brukes til å koble til en ekstern datakilde er OPENQUERY funksjon. Det er et alternativ en gang ad hoc-metoden for å koble til en ekstern server ved hjelp av den tilknyttede serveren. For mer hyppige tilkoblinger til den eksterne serveren, det er bedre å bruke koblet server i stedet for OPENQUERY funksjon.
OPENQUERY funksjonen kan brukes i FRA punkt av SELECT, INSERT, UPDATE eller DELETE uttalelse sette på bordet., Det tar to argumenter; de koblede server navn og spørring. Disse parametrene kan ikke være variabel, som følger:
OPENQUERY ( linked_server ,’query’ )
Nedenfor er et enkelt eksempel på OPENQUERY bruk:
|
1
|
VELG * FRA OPENQUERY(DEV_SQL,’SELECT * FROM testdb.dbo.,Profil’)
|
Sammenligning
Når du bruker Koblet Server til å spørre en ekstern server, query optimizer vil lage gjennomføringsplan etter å klassifisere og dividere søk i lokale og eksterne henvendelser, der hvor de lokale spørsmål vil bli utført lokalt og eksterne henvendelser vil bli sendt til den eksterne serveren, og deretter satt sammen for å vise det endelige resultatet til brukeren som ett resultat., En annen ulempe med den Tilknyttede Serveren er at ingen filtrering vil bli brukt på den eksterne serveren hvis spørringen har DER punkt, hvor det vil hente alle postene fra den eksterne bordet og gjør filtrering og bli med lokalt.
I OpenQuery funksjon tilfelle, SQL-Motoren vil ikke prøve å klassifisere spørsmål eller sjekk hva det vil gjøre, bare det vil sende spørringen som er til den eksterne serveren. Analysering av SQL-spørring, generere gjennomføringsplan og alle filtrering vil bli utført på en ekstern server.,
generelt, OpenQuery er raskere enn koblet server som SQL-Motoren vil ikke bryte spørringen før du sender den til en ekstern server, men bare nyttig for mindre hyppige eksterne tilkoblinger.
ved Hjelp av OPENROWSET og OPENDATASOURCE funksjonene, må du angi alle tilkoblingen informasjon, inkludert brukernavn og-passord for hver gang du bruker den. Selv om disse funksjonene ikke gi alle knyttet server funksjonalitet slik som sikkerhet ledelse, det bruker mindre ressurser fra din server., Som disse funksjonene åpne en tilkobling til den eksterne serveren, det er bedre å bruke koblet server for hyppige ekstern server tilgang.
- Forfatter
- Siste Innlegg
Han er en Microsoft Certified Solution Ekspert på Data Management og Analytics, Microsoft Certified Solution som arbeider i SQL-Database Administrasjon og Utvikling, Azure Developer Førsteamanuensis og Microsoft Certified Trainer.
Også, han bidrar med sin SQL tips i mange blogger.,
Vis alle innlegg av Ahmad Yaseen
- Hvordan å overvåke Azure Data Fabrikk – 15. januar 2021
- ved Hjelp av Source Control i Azure Data Fabrikk – januar 12, 2021
- ved Hjelp av Maler i Azure Data Fabrikk – 8. januar 2021