SQLShack (Svenska)

en gemensam aktivitet när du skriver T-SQL-frågor ansluter till lokala databaser och bearbetar data direkt. Men det kommer att finnas situationer där du måste ansluta till en fjärrdatabas som ligger i en annan instans i samma server eller i en annan fysisk server och bearbeta dess data parallellt med den lokala databehandlingen.,

SQL Server ger oss fyra användbara metoder för att ansluta till fjärrdatabasservrar, även andra databasservertyper, och fråga dess data i din T-SQL-sats. I den här artikeln kommer vi att diskutera dessa fyra metoder och hur man använder den för att fråga fjärr SQL Server-databaser.

OPENDATASOURCE

den första metoden att fråga en fjärrdatabas för SQL Server är funktionen OPENDATASOURCE T-SQL nedan:

OPENDATASOURCE ( provider_name as char, init_string)

där provider_name är OLE DB-leverantören som används för att komma åt datakällan., Och init_string är anslutningssträngen på fjärrservern.

för att kunna använda opendatasource-satsen måste du se till att registernyckeln DisallowAdhocAccess är inställd på 0 för den leverantör som du vill ansluta till annan än SQL Server, som finns i Registernyckelns sökväg nedan :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mssqlserver\Providers\<ProviderName<ProviderName>

Du måste också aktivera alternativet Ad Hoc-distribuerade frågor Avancerad konfiguration som är inaktiverad som standard i SQL Server., Om du försöker att köra nedan enkel fråga som använder OPENDATASOURCE T-SQL-uttryck som du kommer att få fel:

Msg 15281, Nivå 16, Tillstånd 1, Rad 1
SQL Server blockeras tillgång till UTTALANDE ”OpenRowset/OpenDatasource’ komponent ’Ad Hoc-Distribuerade Frågor” eftersom denna komponent är avslagen som en del av säkerheten konfiguration för denna server. En systemadministratör kan aktivera användningen av ”Ad Hoc distribuerade frågor” med hjälp av sp_configure., För mer information om att aktivera ”Ad Hoc distribuerade frågor”, Sök efter ”Ad Hoc distribuerade frågor” i SQL Server Books Online.

som du kan se från felmeddelandet bör alternativet Ad Hoc-distribuerade frågor avancerad konfiguration aktiveras för att öppna anslutning till en fjärrserver med hjälp av OPENDATASOURCE.,

Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully., OPENDATASOURCE kan ersätta servernamnet i tabellens fyra delars namn eller visa i en SELECT, INSERT, UPDATE eller DELETE-sats. Det kan också användas i execute-uttalandet för att köra en fjärrlagrad procedur.

OPENROWSET

det andra sättet att fråga en databas som är värd i en fjärr SQL Server är OPENROWSET T-SQL-funktionen. För att kunna använda openrowset ad hoc-metoden måste du tillhandahålla all anslutningsinformation som krävs för att ansluta till REMOTE SQL server och många andra resurser., Det kan också användas för en bulkoperation genom den inbyggda BULKLEVERANTÖREN för att läsa data från filer. OPENROWSET används i FROM-klausulen som tabellnamn i uttagen välj, infoga, uppdatera eller ta bort. Även om frågan kan returnera flera resultatuppsättningar returnerar OPENROWSET endast den första.

använda OPENROWSET kräver att Ad Hoc distribuerade frågor avancerade konfigurationsalternativ samma som OPENDATASOURCE-funktionen.,979d8a2e”>

Du kan skriva den tidigare opendatasource-frågan med hjälp av openrowset-funktionen enligt följande:

länkad server

en SQL Server länkad server används för att komma åt fjärranslutna OLE DB-datakällor som en SQL Server-instans som ligger utanför SQL Server eller andra system som Oracle, Microsoft Access och Excel, och utföra de distribuerade T-SQL-frågorna mot dem.,

en SQL Server-länkad Server skiljer sig från Ad hoc-frågor genom att Ad hoc-frågor öppnar en tillfällig anslutning till fjärrservern och stänger den, där den permanenta länkade servern alltid är tillgänglig för användning. När användaren utför en distribuerad fråga mot en fjärrdatakälla med en länkad server tolkar SQL Server-motorn det kommandot och skickar begärandena till OLE DB. Denna begäran kan vara en fråga att köra eller en tabell som ska öppnas på den fjärrservern.,

en länkad Server kan konfigureras med hjälp av SQL Server Management Studio eller med hjälp av sp_addlinkedserver T-SQL-satsen.

för att konfigurera en länkad server med SQL Server Management Studio, expandera noden Serverobjekt från objektet Explorer-fönstret. Högerklicka på den länkade Servernoden och välj Ny länkad Server.

på fliken Allmänt i det nya länkade Serverfönstret väljer du ett namn för den länkade servern och väljer sedan vilken typ av server du behöver ansluta till med den länkade servern., Välj SQL Server om du lyckas ansluta till en fjärransluten SQL Server-instans, eller välj annan datakälla för att välja från de tillgängliga OLE DB-servertyperna från leverantörens rullgardinslista annat än SQL Server. Om du väljer SQL Server som servertyp ska det länkade servernamnet vara nätverksnamnet på REMOTE SQL Server.

fyll i fältet Produktnamn med produktnamnet för den valda OLE DB-datakällan, till exempel SQL Server om du ansluter till en fjärransluten SQL Server., Skriv namnet på den valda datakällan i fältet datakälla, t.ex. SQL Server-Instansnamnet om du ansluter till en SQL Server-instans. Fyll i fältet Leverantörssträng med den OLE DB-leverantörsspecifika anslutningssträngen som identifierar en unik datakälla. Platsfältet kan fyllas med fjärrdatabasens plats som tolkats av OLE DB-leverantören. Skriv namnet på katalogen som ska användas av OLE DB-leverantören i Katalogfältet.,

du behöver inte använda alla beskrivna argument tillsammans, eftersom de nödvändiga argumenten beror på den valda leverantören. Till exempel använder du SQL Server provider endast två argument.

på säkerhetssidan i det nya länkade Serverfönstret anger du säkerhetssammanhang som den länkade servern kommer att använda för att ansluta den ursprungliga SQL Server till fjärrdatakällan., Som namnet förklarar, i den lokala servern inloggningar till fjärrservern inloggningar mappningar del av säkerhetsfönstret, du kan ange en lista över användare som kan använda den länkade servern för att ansluta till fjärrservern genom att kartlägga dessa lokala användare med fjärrserver inloggningar. Dessa användare kan vara via SQL Server-autentisering eller en Windows-autentisering inloggning.

för de inloggningar som inte har definierats i mapplistan måste du välja säkerhetssammanhang för deras anslutningar till fjärrservern med den länkade servern., Att välja inte göras kommer att hindra alla användare som inte ingår i den tidigare listan från att använda den länkade servern. Om du väljer göras utan att använda ett säkerhetssammanhang, kommer de användare som inte ingår i den tidigare mapplistan att ansluta till fjärrservern med den länkade servern utan att ange ett säkerhetssammanhang för dem. Om du väljer att göras med hjälp av inloggningens aktuella säkerhetssammanhang, kommer anslutningen till fjärrservern att upprättas med hjälp av den anslutande användaren, vilket är det bästa valet., Ange autentiseringsuppgifter för SQL Server som ska användas för att upprätta anslutning till fjärrservern för användare som inte definieras i mappningslistan.

det finns många alternativ som du kan ställa in beroende på dina krav från sidan serveralternativ i det nya länkade Serverfönstret. Ange till exempel alternativet Sorteringskompatibel till true om du är säker på att fjärrdatakällan har samma teckenuppsättning och sorteringsordning som den lokala servern., Som standard utvärderar SQL Server jämförelser på teckenkolumner lokalt om du inte använder det här alternativet. Alternativet dataåtkomst används för att aktivera och inaktivera den distribuerade frågeåtkomsten för den länkade servern. För att aktivera fjärrproceduranropet från den angivna servern ställer du in RPC till true och för att aktivera fjärrproceduranropet till den angivna servern ställer du RPC ut till true.

besluta om den lokala eller fjärrserverns sortering kommer att användas i frågan bestäms av alternativet Använd fjärr sortering., Om det här alternativets värde är sant kan du ange sorteringsnamnet som ska användas av fjärrservern i alternativet Sorteringsnamn. Detta gäller om fjärrdatakällan inte är SQL Server, där du kan ange alla sorteringsnamn som stöds av SQL Server i det fältet.

Du kan åsidosätta standardserverns tidsgräns för fjärrinloggning för den länkade servern genom att ändra alternativet tidsgräns för anslutning till ett värde som är större än 0.Detta värde anger tidsgränsen i sekunder för anslutning till den länkade servern., Du kan också ange time-out på några sekunder för frågor som ansluter till den länkade servern genom att ändra alternativet Query Timeout till något värde större än 0. Detta kommer att åsidosätta serverns fjärr query timeout för den länkade servern.

ett annat användbart alternativ som används för att starta en distribuerad transaktion när du anropar en fjärrlagrad procedur, där denna transaktion kommer att hantera och skydda av MS DTC. Detta alternativ kallas aktivera marknadsföring av distribuerade transaktioner.,ett felmeddelande visas som visar att det finns något som hindrar anslutningen från att öppnas:

Du kan enkelt skapa den tidigare länkade servern med hjälp av sp_addlinkedserver T-SQL-satsen som skickar de nödvändiga argumenten enligt följande:

När den länkade servern har skapats framgångsrikt kan du använda den genom att ange det fyrdelade namnet som innehåller: Linked_Server_Name.Remote_Database_Name.Schemanamn.Table_Name som i exemplet nedan:

OPENQUERY

den senaste SQL Server-metoden som används för att ansluta till en fjärrdatakälla är OPENQUERY-funktionen. Det är en alternativ engångs ad hoc-metod för att ansluta till en fjärrserver med den länkade servern. För mer frekventa anslutningar till fjärrservern är det bättre att använda den länkade servern istället för OPENQUERY-funktionen.

OPENQUERY-funktionen kan användas i FROM-satsen i Select, INSERT, UPDATE eller DELETE-satsen som ersätter tabellnamnet., Det tar två argument; det länkade servernamnet och frågan. Dessa parametrar kan inte vara variabel enligt följande:

OPENQUERY ( linked_server, ’query’ )

nedan är ett enkelt exempel på OPENQUERY-användningen:

jämförelse

När du använder den länkade servern för att fråga en fjärrserver, kommer query optimizer skapa exekveringsplanen efter att klassificera och dela frågan i lokala och fjärrfrågor, där de lokala frågor kommer att utföras lokalt och fjärrfrågor kommer att skickas till fjärrservern, sedan kombineras för att visa det slutliga resultatet till användaren som enda resultatuppsättning., En annan nackdel med den länkade servern är att, ingen filtrering kommer att tillämpas på fjärrservern om frågan Har där klausul, där det kommer att hämta alla poster från fjärrtabellen och göra filtrering och sammanfogning lokalt.

i openquery-funktionsfallet försöker SQL-motorn inte klassificera frågan eller kontrollera vad den ska göra, helt enkelt skickar den frågan som är till fjärrservern. Analysera SQL-frågan, generera exekveringsplanen och all filtrering kommer att utföras på fjärrservern.,

i allmänhet är OpenQuery snabbare än den länkade servern eftersom SQL-motorn inte bryter frågan innan den skickas till fjärrservern, men endast användbar för enstaka mindre frekventa fjärranslutningar.

med funktionerna OPENROWSET och OPENDATASOURCE måste du ange alla anslutningsdetaljer inklusive användarnamn och lösenord varje gång du använder det. Även om dessa funktioner inte tillhandahåller alla länkade serverfunktioner som säkerhetshantering, förbrukar den mindre resurser från din server., Eftersom dessa funktioner öppnar en engångsanslutning till fjärrservern är det bättre att använda den länkade servern för frekvent fjärrserveråtkomst.

• författare
• Senaste inlägg

Ahmad Yaseen är en Microsoft Big dataingenjör med djup kunskap och erfarenhet inom SQL BI, SQL Server databasadministration och utvecklingsfält.,
Han är en Microsoft Certified Solution Expert på Hantering av Data och analyser, Microsoft Certified Solution Docent i SQL-Databas Administration och Utveckling, Azure Developer partner och Microsoft Certified Trainer.
han bidrar också med sina SQL-tips i många bloggar.,
Visa alla inlägg av Ahmad Yaseen

Senaste inlägg av Ahmad Yaseen (se alla)

• hur man övervakar Azure Data Factory – 15 januari 2021
• använda källkontroll i Azure Data Factory – 12 januari 2021
• använda mallar i Azure Data Factory – 12 januari 2021
• använda mallar i Factory-8 januari 2021