en almindelig aktivitet, når du skriver T-s .l-forespørgsler, opretter forbindelse til lokale databaser og behandler data direkte. Men der vil være situationer, hvor du skal oprette forbindelse til en ekstern database, der er placeret i et andet tilfælde på den samme server eller i en anden fysisk server, og behandle dens data parallelt med den lokale databehandling.,
s .l Server giver os fire nyttige metoder til at oprette forbindelse til de eksterne databaseservere, endda andre databaseservertyper, og forespørge dens data i din T-s .l-erklæring. I denne artikel vil vi diskutere disse fire metoder, og hvordan man bruger den til at forespørge eksterne s .l Server-databaser.
OPENDATASOURCE
Den første metode til at søge en ekstern SQL Server-database, er det OPENDATASOURCE T-SQL funktion nedenfor:
OPENDATASOURCE ( provider_name som char, init_string )
Hvor provider_name er OLE DB-provider, der anvendes til at få adgang til datakilden., Og init_string er forbindelsesstrengen på den eksterne server.
At være i stand til at bruge OPENDATASOURCE erklæring, skal du sørge for, at DisallowAdhocAccess nøgle i registreringsdatabasen er angivet til 0 for den udbyder, du vil forbinde til andre end SQL-Server, som kan findes i nedenstående sti af Nøgler i Registreringsdatabasen :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Udbydere\<ProviderName>
du er nødt til at aktiverer Ad-Hoc-Fordelt Forespørgsler avanceret konfiguration, der er som standard deaktiveret i SQL Server., Hvis du forsøger at køre nedenfor simpel forespørgsel, der bruger OPENDATASOURCE T-SQL-sætning, vil du få fejl:
Msg 15281, Level 16, Staten 1, Linje 1
SQL Server blokeret adgang til UDSAGNET: “kommandoen openrowset/OpenDatasource’ komponent ‘Ad Hoc Fordelt Søgninger”, fordi denne komponent er slået fra som en del af den sikkerhed, konfiguration for denne server. En systemadministrator kan aktivere brugen af’ Ad Hoc distribuerede forespørgsler ‘ ved hjælp af sp_configure., For mere information om, hvordan du aktiverer Ad-Hoc-Fordelt Søgninger’, søg efter “Ad Hoc Fordelt Søgninger’ i SQL Server Books Online.
som du kan se fra fejlmeddelelsen, skal Ad Hoc distribuerede forespørgsler avanceret konfigurationsindstilling være aktiveret for at åbne forbindelse til en ekstern server ved hjælp af 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 servernavnet i bordets firedelede navn eller visning i en SELECT, INSERT, UPDATE eller DELETE-sætning. Det kan også bruges i E .ecute-sætningen til at køre en fjernlagret procedure.
kommandoen openrowset
Den anden måde forespørgsler til en database i en ekstern SQL Server er kommandoen openrowset T-SQL-funktion. For at bruge OPENRO .set ad hoc-metoden skal du angive alle forbindelsesoplysninger, der kræves for at oprette forbindelse til den eksterne s .l-server og mange andre ressourcer., Det kan også bruges til en bulk operation gennem den indbyggede BULK udbyder til at læse data fra filer. OPENRO .set bruges i fra-klausulen som et bordnavn i select, INSERT, UPDATE eller DELETE-sætningerne. Selvom forespørgslen muligvis returnerer flere resultatsæt, returnerer OPENRO .set kun den første.brug af OPENRO .set kræver aktivering af Ad Hoc distribuerede forespørgsler avanceret konfigurationsindstilling samme som opendatasource-funktionen.,979d8a2e”>
Du kan skrive tidligere OPENDATASOURCE forespørgsel ved hjælp af kommandoen openrowset funktion som følger:
Linkede Server
EN SQL-Server koblet Server, der bruges til adgang til fjern-OLE DB-datakilder såsom en SQL Server-forekomst, der ligger uden for SQL Server eller andre systemer, såsom Oracle, Microsoft Access og Excel, og udføre den distribuerede T-SQL-forespørgsler mod dem.,
en S .l Server-linket Server adskiller sig fra ad hoc-forespørgsler, idet ad hoc-forespørgsler åbner en midlertidig forbindelse med fjernserveren og lukker den, hvor den permanente linkede server altid er tilgængelig til brug. Når brugeren udfører en distribueret forespørgsel mod en ekstern datakilde ved hjælp af en linket server, analyserer s .l Server-motoren denne kommando og sender anmodningerne til OLE DB. Denne anmodning kan være en forespørgsel, der skal udføres, eller en tabel, der skal åbnes på den eksterne server.,
en linket Server kan konfigureres ved hjælp af S .l Server Management Studio eller ved hjælp af sp_addlinkedserver T-s .l-sætningen.for at konfigurere en linket server ved hjælp af S .l Server Management Studio skal du udvide Server Objects-noden fra Object e .plorer-vinduet. Højreklik på den linkede Serverknude og vælg Ny linket Server.
under fanen Generelt I den Nye Knyttet Server-vinduet, vælge et navn til din tilknyttede server, og vælg derefter den type af server, du har brug for til at oprette forbindelse til hjælp, der er knyttet server., Vælg S .l Server, hvis du formår at oprette forbindelse til en ekstern s .l Server-forekomst, eller vælg anden datakilde for at vælge fra de tilgængelige OLE DB-servertyper fra udbyderens rulleliste bortset fra S .l Server. Hvis du vælger S .l Server som servertype, skal det linkede servernavn være netværksnavnet på den eksterne s .l Server.
udfyld feltet Produktnavn med produktnavnet på den valgte OLE DB-datakilde, f.eks. s .l Server, hvis du opretter forbindelse til en ekstern s .l Server., Indtast navnet på den valgte datakilde i feltet datakilde, f.eks. navnet på S .l Server-instansen, hvis du opretter forbindelse til en ekstern s .l Server-instans. Udfyld Provider String-feltet med den OLE DB provider-specifikke forbindelsesstreng, der identificerer en unik datakilde. Placeringsfeltet kan udfyldes med den eksterne databaseplacering som fortolket af OLE DB-udbyderen. Indtast navnet på kataloget, der skal bruges af OLE DB-udbyderen, i Katalogfeltet.,
Du er ikke forpligtet til at bruge alle de beskrevne argumenter sammen, så de nødvendige argumenter afhænger af den valgte leverandør. For eksempel bruger du kun to argumenter ved hjælp af S .l Server provider.
På den Sikkerhedsmæssige side af den Nye Knyttet Server vindue, angiv sikkerhedsindstillingerne, at forbundet serveren vil bruge til at forbinde den oprindelige SQL-Server til den eksterne datakilde., Som navnet fortæller, i den Lokale server logins til remote server logins tilknytninger en del af den Sikkerhed vindue, kan du angive en liste af brugere, der kan bruge linked server til at oprette forbindelse til fjernserveren ved at kortlægge disse lokale brugere med ekstern server logins. Disse brugere kan være via S .l Server-godkendelse eller en Authenticationindo .s-godkendelse login.
for de logins, der ikke er defineret i kortlægningslisten, skal du vælge sikkerhedskonteksten for deres forbindelser til den eksterne server ved hjælp af den linkede server., Hvis du vælger Not be made, forhindres enhver bruger, der ikke er inkluderet i den forrige liste, i at bruge den linkede server. Valg foretages uden brug af en sikkerhedskontekst, brugerne, der ikke er inkluderet i den forrige kortlægningsliste, opretter forbindelse til den eksterne server ved hjælp af den linkede server uden at specificere en sikkerhedskontekst for dem. Hvis du vælger ske ved hjælp af login aktuelle sikkerhed kontekst, så forbindelsen til den eksterne server vil blive etableret ved hjælp af den forbindende bruger, som er det bedste valg., I Be made using this security Conte .t option skal du specificere de S .l Server-godkendelsesoplysninger, der vil blive brugt til at etablere forbindelse til fjernserveren for de brugere, der ikke er defineret i kortlægningslisten.
Der er mange muligheder, som du kan indstille afhængigt af dine krav fra Serverindstillingssiden i Det Nye linkede Servervindue. Indstillingen Collation Compatible til true, hvis du er sikker på, at den eksterne datakilde har det samme tegnsæt og sorteringsrækkefølge som den lokale server., Som standard evaluerer s .l Server sammenligninger på tegnkolonner lokalt, hvis du ikke bruger denne indstilling. Indstillingen dataadgang bruges til at aktivere og deaktivere den distribuerede forespørgselsadgang for den linkede server. For at aktivere remote procedure opkald fra den angivne server indstille RPC til true, og for at aktivere remote procedure opkald til den angivne server indstille RPC ud til true.beslutning om, hvorvidt den lokale eller eksterne servers sortering vil blive brugt i forespørgslen, bestemmes af indstillingen Brug Fjern sortering., Hvis denne indstillings værdi er sand, kan du angive det sorteringsnavn, der skal bruges af den eksterne server i indstillingen Sorteringsnavn. Dette gælder, hvis fjerndatakilden ikke er S .l Server, hvor du kan angive et hvilket som helst sorteringsnavn, der understøttes af s .l Server i dette felt.
Du kan tilsidesætte standardserverens fjernlogin-timeout for den linkede server ved at ændre indstillingen Connection Timeout til en værdi, der er større end 0.Denne værdi angiver timeout i sekunder for tilslutning til den linkede server., Du kan også angive time-out i sekunder for forespørgsler, der forbinder til den linkede server ved at ændre indstillingen Queryuery Timeout til en værdi større end 0. Dette vil tilsidesætte serverens fjernforespørgsel timeout for den linkede server.
en anden nyttig mulighed, der bruges til at starte en distribueret transaktion, når du ringer til en fjernlagret procedure, hvor denne transaktion vil blive administreret og beskyttet af MS DTC. Denne mulighed kaldes aktiv Promotionr promovering af distribuerede transaktioner.,han nedenstående besked, ellers en fejl, vises en meddelelse, der viser, at der er noget at forhindre forbindelsen i at blive åbnet:
Du kan nemt oprette den tidligere knyttet serveren ved hjælp af sp_addlinkedserver T-SQL-sætning, der passerer det krævede argumenter som følger:
|
1
2
3
4
|
GO
EXEC master.,dbo.sp_addlinkedserver @server = N’DEV_SQL’, @srvproduct=N’SQL Server’
GO
|
Når forbundet serveren er oprettet, vil du være i stand til at bruge det ved at angive den i fire dele-navn, som omfatter: Linked_Server_Name.Remote_Database_Name.Schema_Name.Table_Name som i nedenstående eksempel:
|
1
2
|
SELECT * FROM DEV_SQL.,testdb.dbo.Profil
GO
|
OPENQUERY
Den sidste SQL Server metode, der bruges til at oprette forbindelse til en ekstern datakilde er OPENQUERY funktion. Det er en alternativ engangs-ad hoc-metode til at oprette forbindelse til en ekstern server ved hjælp af den linkede server. For hyppigere forbindelser til fjernserveren er det bedre at bruge den linkede server i stedet for open .uery-funktionen.
funktionen open .uery kan bruges i fra-klausulen i sætningen SELECT, INSERT, UPDATE eller DELETE, der erstatter tabelnavnet., Det tager to argumenter; det linkede servernavn og forespørgslen. Disse parametre kan ikke være variabel, som følger:
OPENQUERY ( linked_server , “query”)
Nedenfor er et simpelt eksempel på OPENQUERY brug:
|
1
|
VÆLG * FRA OPENQUERY(DEV_SQL,’SELECT * FROM testdb.dbo.,Profil”)
|
Forhold
Når du bruger Knyttet Server til forespørgsel til en ekstern server, query optimizer vil oprette forbindelse med gennemførelsen af planen efter klassificering og dividere forespørgsel i lokale og eksterne forespørgsler, hvor de lokale søgninger vil blive udført lokalt og remote forespørgsler vil blive sendt til den eksterne server, så er kombineret sammen til at vise det endelige resultat til den bruger, som en enkelt resultatsæt., En anden ulempe ved den er Knyttet Server er, at ingen filtrering vil blive anvendt på den eksterne server, hvis forespørgslen er, HVOR klausul, hvor det vil hente alle poster fra den eksterne tabel og gøre filtrering og deltage lokalt.
i Open .uery-funktionssagen forsøger s .l-motoren ikke at klassificere forespørgslen eller kontrollere, hvad den vil gøre, simpelthen sender den forespørgslen, som den er til fjernserveren. Parsing af S .l-forespørgslen, generering af eksekveringsplanen og al filtrering udføres på den eksterne server., generelt er open .uery hurtigere end den linkede server, da S .l-motoren ikke vil bryde forespørgslen, før den sendes til fjernserveren, men kun nyttig til engangs mindre hyppige fjernforbindelser. brug af OPENRO .set-og OPENDATASOURCE-funktionerne skal du specificere alle forbindelsesoplysninger inklusive brugernavn og adgangskode, hver gang du bruger det. Selvom disse funktioner ikke giver alle tilknyttede serverfunktioner, såsom sikkerhedsstyring, bruger den færre ressourcer fra din server., Da disse funktioner åbner en engangsforbindelse til fjernserveren, er det bedre at bruge den linkede server til hyppig fjernserveradgang.
- Forfatter
- Seneste Indlæg
Han er en Microsoft Certified Solution Expert i Data Management og Analytics, Microsoft Certified Solution Lektor i SQL-Databasen Administration og Udvikling, Azure Developer partner og Microsoft Certified Trainer.
han bidrager også med sine S .l-tip i mange blogs.,
Vis alle indlæg af Ahmad Yaseen
- Hvordan man kan overvåge Azure Data Fabrik – januar 15, 2021
- Brug af Source Control i Azure Data Fabrik – januar 12, 2021
- ved Hjælp af Skabeloner i Azure Data Fabrik – 8 januar 2021