SQLShack (Nederlands)

een veel voorkomende activiteit bij het schrijven van T-SQL-query ‘ s is verbinding maken met lokale databases en direct gegevens verwerken. Maar er zullen situaties zijn waarin u verbinding moet maken met een externe database die zich in een andere instantie op dezelfde server of op een andere fysieke server bevindt, en de gegevens moet verwerken parallel met de lokale gegevensverwerking.,

SQL Server biedt ons vier handige methoden om verbinding te maken met de externe databaseservers, zelfs met andere typen databaseservers, en de gegevens binnen uw T-SQL statement te bevragen. In dit artikel zullen we deze vier methoden bespreken en hoe het te gebruiken om externe SQL Server databases te bevragen.

OPENDATASOURCE

de eerste methode om een externe SQL Server-database op te vragen is de OPENDATASOURCE T-SQL-functie hieronder:

OPENDATASOURCE ( provider_name als char, init_string )

waarbij de provider_name de OLE DB-provider is die wordt gebruikt om toegang te krijgen tot de gegevensbron., En de init_string is de verbindingsstring van de remote server.

om het opendatasource-statement te kunnen gebruiken, moet u ervoor zorgen dat de DisallowAdhocAccess registersleutel is ingesteld op 0 voor de provider waarmee u verbinding wilt maken met een andere dan de SQL Server, die u kunt vinden in het onderstaande pad van de registersleutels:

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

ook moet u de Ad-Hoc gedistribueerde queries geavanceerde configuratie optie inschakelen die standaard is uitgeschakeld in SQL Server., Als u de onderstaande eenvoudige query probeert uit te voeren met behulp van het opendatasource T-SQL-statement, krijgt u de fout:

Msg 15281, Level 16, State 1, Line 1
SQL Server blokkeerde de toegang tot STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ omdat dit component is uitgeschakeld als onderdeel van de beveiligingsconfiguratie voor deze server. Een systeembeheerder kan het gebruik van ‘Ad Hoc gedistribueerde Queries’ inschakelen met behulp van sp_configure., Voor meer informatie over het inschakelen van ‘Ad Hoc gedistribueerde Queries’, zoek naar ‘Ad Hoc gedistribueerde Queries’ in SQL Server Books Online.

zoals u kunt zien in de foutmelding, moet de optie Geavanceerde configuratie voor Ad-Hoc gedistribueerde Queries worden ingeschakeld om verbinding met een externe server te openen met behulp van de OPENDATASOURCE.,

Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully., OPENDATASOURCE kan de servernaam vervangen in de vierdelige naam van de tabel of Weergeven in een statement selecteren, invoegen, bijwerken of verwijderen. Het kan ook worden gebruikt in de opdracht uitvoeren om een op afstand opgeslagen procedure uit te voeren.

OPENROWSET

de tweede manier om een database te bevragen die wordt gehost op een externe SQL-Server is de OPENROWSET T-SQL-functie. Als u de AD-hocmethode OPENROWSET wilt gebruiken, moet u alle verbindingsinformatie verstrekken die nodig is om verbinding te maken met de externe SQL server en vele andere bronnen., Het kan ook worden gebruikt voor een bulk operatie via de ingebouwde BULK provider om gegevens uit bestanden te lezen. OPENROWSET wordt in De FROM-clausule gebruikt als tabelnaam in de statements selecteren, invoegen, bijwerken of verwijderen. Hoewel de query meerdere resultaatsets kan retourneren, geeft OPENROWSET alleen de eerste terug.

het gebruik van OPENROWSET vereist het inschakelen van de Ad-Hoc gedistribueerde query ‘ s geavanceerde configuratie optie hetzelfde als de OPENDATASOURCE functie.,979d8a2e”>

U kunt het schrijven van de vorige OPENDATASOURCE query met behulp van de OPENROWSET functie als volgt:

Gekoppelde Server

EEN SQL Server Gekoppeld Server wordt gebruikt om toegang te krijgen tot externe OLE DB-gegevensbronnen, zoals een exemplaar van SQL Server die zich buiten de SQL Server-of andere systemen, zoals Oracle, Microsoft Access en Excel, en het uitvoeren van gedistribueerde T-SQL query ‘ s tegen hen.,

een SQL Server gekoppelde Server verschilt van ad-hoc query ’s in die zin dat ad-hoc query’ s een tijdelijke verbinding met de externe server openen en sluiten, waarbij de permanent gekoppelde server altijd beschikbaar is voor gebruik. Wanneer de gebruiker een gedistribueerde query uitvoert tegen een externe gegevensbron met behulp van een gekoppelde server, ontleedt de SQL Server-Engine die opdracht en stuurt de verzoeken naar OLE DB. Dit verzoek kan een uit te voeren query zijn of een tabel die op die externe server moet worden geopend.,

een gekoppelde Server kan worden geconfigureerd met behulp van SQL Server Management Studio of met behulp van de sp_addlinkedserver T-SQL statement.

om een gekoppelde server te configureren met SQL Server Management Studio, vouwt u het knooppunt serverobjecten uit in het venster Objectverkenner. Klik met de rechtermuisknop op het gekoppelde serverknooppunt en kies nieuwe gekoppelde Server.

kies in het tabblad Algemeen van het venster Nieuwe gekoppelde Server een naam voor uw gekoppelde server en kies vervolgens het type server waarmee u verbinding moet maken met die gekoppelde server., Selecteer SQL Server als u verbinding kunt maken met een extern SQL Server-exemplaar of kies een andere gegevensbron om te selecteren uit de beschikbare OLE DB-servertypen in de vervolgkeuzelijst Provider, behalve SQL Server. Als u SQL Server als servertype kiest, moet de gekoppelde servernaam de netwerknaam van de externe SQL Server zijn.

vul het veld Productnaam in met de productnaam van de geselecteerde OLE DB-gegevensbron, zoals SQL Server als u verbinding maakt met een externe SQL Server., Typ de naam van de geselecteerde gegevensbron in het veld gegevensbron, zoals de naam van het SQL Server-exemplaar als u verbinding maakt met een extern SQL Server-exemplaar. Vul het veld Providerstring in met de OLE DB-providerspecifieke verbindingsstring die een unieke gegevensbron identificeert. Het veld Locatie kan worden gevuld met de remote database locatie zoals geïnterpreteerd door de OLE DB provider. Typ de naam van de catalogus die door de OLE DB-provider moet worden gebruikt in het veld catalogus.,

u hoeft niet alle beschreven argumenten samen te gebruiken, omdat de benodigde argumenten afhankelijk zijn van de geselecteerde provider. Als u bijvoorbeeld SQL Server provider gebruikt, gebruikt u slechts twee argumenten.

geef op de beveiligingspagina van het venster Nieuwe gekoppelde Server de beveiligingscontext op die de gekoppelde server zal gebruiken om de oorspronkelijke SQL-Server te verbinden met de externe gegevensbron., Zoals in de naam wordt uitgelegd, kunt u in het gedeelte toewijzingen voor lokale serveraanmeldingen bij externe serveraanmeldingen van het Beveiligingsvenster een lijst opgeven van gebruikers die de gekoppelde server kunnen gebruiken om verbinding te maken met de externe server door deze lokale gebruikers toe te wijzen aan externe serveraanmeldingen. Deze gebruikers kunnen via SQL Server-verificatie of een Windows-authenticatie-login.

voor de aanmeldingen die niet zijn gedefinieerd in de lijst met toewijzingen, moet u de beveiligingscontext kiezen voor hun verbindingen met de externe server met behulp van die gekoppelde server., Kiezen niet worden gemaakt zal voorkomen dat een gebruiker die niet in de vorige lijst van het gebruik van die gekoppelde server. Als u wordt gemaakt zonder een beveiligingscontext te gebruiken, maken de gebruikers die niet in de vorige toewijzingslijst zijn opgenomen, verbinding met de externe server via die gekoppelde server zonder daarvoor een beveiligingscontext op te geven. Als u kiest voor gemaakt worden met behulp van de huidige beveiligingscontext van de login, wordt de verbinding met de externe server tot stand gebracht met behulp van de verbindende gebruiker, wat de beste keuze is., Geef in de optie worden gemaakt met behulp van deze beveiligingscontext de SQL Server-verificatiereferenties op die worden gebruikt om verbinding tot stand te brengen met de externe server voor de gebruikers die niet zijn gedefinieerd in de toewijzingslijst.

Er zijn veel opties die u afhankelijk van uw vereisten kunt afstemmen op de pagina Serveropties van het nieuwe gekoppelde Servervenster. Stel de optie Collation Compatible bijvoorbeeld in op true als u er zeker van bent dat de externe gegevensbron dezelfde tekenset-en sorteervolgorde heeft als de lokale server., Standaard evalueert SQL Server vergelijkingen op tekenkolommen lokaal als u deze optie niet gebruikt. De optie gegevenstoegang wordt gebruikt om de gedistribueerde query-toegang voor de gekoppelde server in-en uit te schakelen. Als u de externe procedureaanroep van de opgegeven server wilt inschakelen, stelt u de RPC in op true en als u de externe procedureaanroep naar de opgegeven server wilt inschakelen, stelt u de RPC in op true.

bepalen of de lokale of externe server ‘ s collation zal worden gebruikt in de query wordt bepaald door de optie Remote Collation gebruiken., Als de waarde van deze optie Waar is, kunt u de naam van de collation opgeven die door de externe server zal worden gebruikt in de optie Collation Name. Dit is van toepassing als de externe gegevensbron niet SQL Server is, waarbij u in dat veld een door SQL Server ondersteunde collationienaam kunt opgeven.

u kunt de remote login time-out van de standaardserver voor die gekoppelde server overschrijven door de Time-outoptie verbinding te wijzigen naar een waarde groter dan 0.Deze waarde specificeert de time-out in seconden voor het verbinden met de gekoppelde server., U kunt ook de time-out in seconden opgeven voor de query ‘ s die verbinding maken met de gekoppelde server door de query time-out optie te wijzigen in een waarde groter dan 0. Dit zal de time-out van de externe query van de server voor de gekoppelde server overschrijven.

een andere handige optie die wordt gebruikt om een gedistribueerde transactie te starten bij het aanroepen van een op afstand opgeslagen procedure, waarbij deze transactie wordt beheerd en beveiligd door de MS DTC. Deze optie heet bevordering van gedistribueerde transacties inschakelen.,hij zal het onderstaande bericht, anders wordt er een foutmelding worden weergegeven, waaruit blijkt dat er iets is, waardoor de verbinding worden geopend:

U kunt eenvoudig de vorige gekoppelde server met behulp van de sp_addlinkedserver T-SQL-instructie het passeren van de vereiste argumenten als volgt:

1 2 3 4

GEBRUIK GO EXEC master.,dbo.sp_addlinkedserver @server = N ‘EV_SQL’, @srvproduct = n’ QL Server ‘ GO

zodra de gekoppelde server met succes is aangemaakt, kunt u deze gebruiken door de vierdelige naam op te geven waaronder: Linked_Server_Name.Remote_Database_Name.Schema_naam.Table_Name zoals in het onderstaande voorbeeld:

1 2	selecteer * uit dev_sql.,testdb.dbo.Profiel GO

OPENQUERY

de laatste SQL Server-methode die wordt gebruikt om verbinding te maken met een externe gegevensbron is de OPENQUERY-functie. Het is een alternatieve eenmalige ad hoc methode om verbinding te maken met een externe server met behulp van de gekoppelde server. Voor frequentere verbindingen met de externe server is het beter om de gekoppelde server te gebruiken in plaats van de OPENQUERY functie.

De OPENQUERY-functie kan worden gebruikt in De FROM-clausule van het statement selecteren, invoegen, bijwerken of verwijderen dat de tabelnaam vervangt., Er zijn twee argumenten nodig; de gekoppelde servernaam en de query. Deze parameters kunnen niet worden variabele, als volgt:

queryopenen (openquery) ( linked_server ,’query’ )

Hieronder is een eenvoudig voorbeeld van de queryopenen (openquery) gebruik:

1	SELECTEER * UIT queryopenen (openquery) (DEV_SQL,’SELECT * FROM testdb.dbo.,Profiel’)

vergelijking

bij het gebruik van de gekoppelde Server om een externe server te bevragen, zal de query optimizer het uitvoerplan maken na het classificeren en opdelen van de query in lokale en externe query ‘s, waar de lokale query’ s lokaal zullen worden uitgevoerd en de externe query ‘ s naar de externe server zullen worden verzonden, en vervolgens gecombineerd worden om de uiteindelijke resultaat aan de gebruiker als enkele resultaat set., Een ander nadeel van de gelinkte Server is dat er geen filtering zal worden toegepast op de remote server als de query WHERE-clausule heeft, waar het alle records uit de remote tabel zal ophalen en de filtering en de verbinding lokaal zal uitvoeren.

In het geval van de OpenQuery-functie zal de SQL-Engine niet proberen de query te classificeren of te controleren wat het zal doen, maar zal de query gewoon naar de externe server sturen. Het ontleden van de SQL query, het genereren van het uitvoeringsplan en alle filtering zal worden uitgevoerd op de externe server.,

over het algemeen is OpenQuery sneller dan de gekoppelde server, omdat de SQL-Engine de query niet zal verbreken voordat deze naar de externe server wordt verzonden, maar alleen bruikbaar voor eenmalige minder frequente externe verbindingen.

met behulp van de OPENROWSET-en OPENDATASOURCE-functies moet u alle verbindingsdetails opgeven, inclusief de gebruikersnaam en het wachtwoord elke keer dat u deze gebruikt. Hoewel deze functies niet alle gekoppelde serverfunctionaliteit bieden, zoals het beveiligingsbeheer, verbruikt het minder bronnen van uw server., Aangezien deze functies een eenmalige verbinding met de remote server openen, is het beter om de gekoppelde server te gebruiken voor frequente remote server toegang.

• auteur
• recente berichten

Ahmad Yaseen is een Microsoft Big Data Engineer met diepgaande kennis en ervaring in SQL bi, SQL Server database administratie en ontwikkeling velden., hij is een Microsoft Certified Solution Expert in Data Management en Analytics, Microsoft Certified Solution Associate in SQL Database administratie en ontwikkeling, Azure Developer Associate en Microsoft Certified Trainer.
ook draagt hij bij met zijn sql tips in veel blogs.,
Bekijk alle berichten van Ahmad Yaseen

Laatste berichten door Ahmad Yaseen (zie all)

• Hoe monitor Azure Data Factory – 15 januari 2021
• met Behulp van Source Control in Azure Data Factory – 12 januari 2021
• met Behulp van de Sjablonen in Azure Data Factory – januari 8, 2021