door: Eric Blinn / bijgewerkt: 2019-06-28 | Comments (4)/verwant: meer > Linked Servers
probleem
Ik zie dat er een optie is in SQL Server Management Studio (SSMS) voor gekoppelde Servers en ik wil meer weten over wat ze doen en hoe ze werken.
oplossing
gekoppelde Servers zijn een methode waarmee een SQL-Server kan praten met een andere ODBC-compliantdatabase, zoals een andere SQL-Server-instantie of een Oracle-database, met een directt-SQL-query.,
Er zijn een aantal belangrijke instellingen om te weten wanneer u een gekoppelde Server instelt.Deze tip zal betrekking hebben op het maken van een gekoppelde Server naar een andere SQL Server instantie met behulp van de SSMS GUI samen met de juiste beveiligingsopties. Het zal ook laten zien hoe de gekoppelde Server te gebruiken in een T-SQL statement. Er is een grote bibliotheek van meer geavanceerde onderwerpen beschikbaar zodra de basisprincipes onder de knie zijn.,
een SQL Server gekoppelde Server aanmaken
in SQL Server Management Studio, vouw in het deelvenster Object Explorer het gedeelte “ServerObjects” uit, klik met de rechtermuisknop op “Linked Servers” en kies “NewLinked Server…” uit het menu.
nieuwe gelinkte Server – algemene pagina
die een scherm opent dat lijkt op het onderstaande scherm. Kies het servertype als SQL Server en typ de naam van het andere exemplaar van SQL Server. In dit voorbeeld wordt de verbinding gemaakt met een benoemde instantie van SQL Server op dezelfde machine., Dit is zeker toegestaan, maar vaker wordt de verbinding volledig op een andere machine gemaakt. De gekoppelde servernaam wordt opgegeven als deservernaam \ InstanceName. In het voorbeeld hieronder maak ik verbinding met de localserver en de instantie naam is “SECURITY_TEST”.
nieuwe gekoppelde Server – beveiligingspagina
ga vervolgens van de Algemene pagina naar de beveiligingspagina met behulp van het menu aan de linkerkant.Deze pagina staat de beheerder toe om deze SQL Server te vertellen hoe hij zich moet aanmelden bij een andere instantie., De bovenste helft van het scherm zorgt ervoor dat individuele logins toegang krijgen om de gekoppelde Server te gebruiken.
Er zijn 2 methoden om gebruikers te authenticeren. De eerste is het selecteren van een login voor hen te gebruiken bij het gebruik van de gekoppelde Server. In de screenshot hieronder zal de Eric-gebruiker Automatisch inloggen op de secundaire server als de login LinkedServerTeston de andere instantie. Deze referentie wordt opgeslagen in de SQL Server Engine en is versleuteld. Een van de tweede rij, de LinkedServerTest login (een SQL Login) de Impersonatebox wordt gecontroleerd., Dit zal proberen om dezelfde gebruikersnaam en wachtwoord te gebruiken op de LinkedServer als op de originele server. Imitatie van een Windows-Login kan ook worden gedaan, maar vereist een goede delegatie worden ingesteld.
focus nu op de onderste helft van het scherm. Er zijn 4 opties beschikbaar. Welke optie hier is geselecteerd, is van toepassing op elke gebruiker die niet in de bovenste sectie wordt vermeld.,
| optie | beschrijving | niet worden gemaakt | dit geeft een fout aan elke gebruiker die probeert de gekoppelde server te gebruiken die niet in de bovenstaande lijst staat. Om deze optie te selecteren, moet er minstens 1 login in de bovenste helft van het scherm. Dit is de fout die alle andere gebruikers krijgen. MSG 7416, niveau 16, Status 1, Regel 1 Toegang tot de externe server wordt geweigerd omdat er geen login-mapping bestaat., |
|---|---|
| worden gemaakt zonder gebruik te maken van een beveiligingscontext | dit zal een anonieme verbinding naar de gekoppelde Server sturen en zal waarschijnlijk geen toegang geven tot de secundaire server. |
| worden gemaakt met behulp van de huidige beveiligingscontext | dit is hetzelfde als het plaatsen van elke gebruiker in de bovenste sectie met het selectievakje imiteren aangevinkt., |
| worden gemaakt met behulp van deze beveiligingscontext: | deze optie vereist een login en wachtwoord worden ingevoerd en zal hetzelfde functioneren als wanneer elke gebruiker aan het bovenste gedeelte werd toegevoegd en exact dezelfde gebruikersnaam en wachtwoord wordt gegeven. Het is de minst veilige methode om een gekoppelde Server in te stellen omdat iedereen met enige toegang tot de eerste SQL Server in staat zal zijn om de gekoppelde Server te gebruiken met behulp van de opgeslagen referenties., |
nieuwe gekoppelde Server – pagina met Serveropties
Er zijn ook instellingen op de pagina met opties, maar voor eenvoudige query ‘ s kunnen deze instellingen als de standaardwaarden worden gelaten zoals hieronder getoond.
de instellingen hoeven in de meeste gevallen niet gewijzigd te worden en deze tabel geeft aan wat de Instellingen doen.
| optie | beschrijving | Collation Compatible | standaard is false., Dit kan worden ingesteld op true als de doelserver exact dezelfde collatie gebruikt als de hoofdserver. De wijzigingen kunnen de prestaties van sommige query ‘ s verbeteren door externe kolomvergelijkingen op kolommen van het teksttype toe te staan. |
|---|---|
| gegevenstoegang | standaard is waar. Bij false zullen alle afzonderlijke externe query-uitvoeringen falen. Deze instelling is onafhankelijk van RPC Out. In een scenariowhere alleen remote procedure aanroepen moeten worden uitgevoerd deze instelling kan worden veranderd naar false terwijl RPC Out true blijft., |
| RPC / RPC Out | dit staat voor Remote Procedure Call en staat u toe om storedprocedures uit te voeren op de gekoppelde Server. RPC schakelt externe ProcedureCalls van de opgegeven server in en RPC OUT schakelt externe ProcedureCalls van de opgegeven server in. |
| gebruik Remote Collation / Collation Name | standaard is False. Wanneer false wordt de lokale serververzameling gebruikt voor alle externe query ‘ s. Indien ingeschakeld zal de selling van de remoteserver worden gebruikt voor queries., Indien true en de remote server geen SQLServer is, kan de lokale server niet automatisch de juiste collationvalue opzoeken. In dit geval gebruikt u de instelling Collation Name om de collationatie van de remote-server in te typen. |
| verbindingstijd | standaard is 0. Wanneer niet-nul, is dit het aantal seconden dat moet worden gewacht voordat de timing wordt uitgeschakeld bij het verbinden met een gekoppelde server. Als 0 dan wordt de waarde gelezen van een standaard server die gevonden kan worden door dit commando uit te voeren.
exec sp_configure 'remote login timeout (s)' |
| Query Time-Out | standaard is 0., Wanneer niet-nul, is dit het aantal seconden dat moet worden gewacht voordat een query die wordt uitgevoerd tegen een gekoppelde server wordt getimed. Als 0 dan wordt de waarde gelezen van een standaard server die gevonden kan worden door dit commando uit te voeren.
exec sp_configure 'remote query timeout (s)' |
| distributeur / uitgever / abonnee | deze instellingen worden alleen gebruikt wanneer de gekoppelde Server wordt gebruikt om replicatie te ondersteunen. |
| Lazy schemavalidatie | standaard is False. Wanneer false het schema van een externe query wordt gecontroleerd vÃ3Ã3r elke query uitvoering., Wijzigingen in het schema op afstand worden van tevoren gedetecteerd en een goed query-plan kan opnieuw worden gemaakt om rekening te houden met de wijziging. Indien Waar wordt het schema pas gevalideerd op het moment van uitvoering. Als de wijziging in het externe schema het queryplan ongeldig maakt, veroorzaakt dit een runtime-fout. |
| promotie van gedistribueerde transacties inschakelen | standaard is True. Indien ingeschakeld zal elke externe query die gegevens wijzigt, de DTC (Distributed Transaction Coordinator) aanroepen., Dit proces beschermt dergelijke query ‘ s doordat beide servers een gedeelde transactie kunnen hebben die ofwel 100% of 0% voltooit – net als een reguliere transactie met een lokale query. Wanneer uitgeschakeld wordt de DTC niet aangeroepen en kan een mislukte remotequery mislukken en niet volledig worden teruggedraaid. |
gegevens opvragen Over een SQL Server gekoppelde Server
om gegevens van een gekoppelde Server te lezen moet naar tabellen of weergaven worden verwezen met een 4-delige identifier die bestaat uit de gekoppelde servernaam, databasenaam, schemanaam en objectnaam – in die volgorde.,
bijvoorbeeld:
SELECT * FROM .master.sys.databasesSELECT * FROM .WideWorldImporters.Sales.Orders
- eerst wordt de gelinkte servernaam verstrekt, wat bijvoorbeeld is. In dit voorbeeld moet het worden tussen haakjes vanwege het formaat. Niet alle LinkedServer namen moeten tussen haakjes staan.
- volgende zijn respectievelijk de databasenaam, master en WideWorldImporters.
- derde is de schemanaam, sys en Sales respectievelijk.
- ten slotte wordt de objectnaam weergegeven. In deze voorbeelden zijn de objecten databases en Orders.
dat voorbeeld is behoorlijk lelijk vanwege de aard van de gelinkte servernaam., Gelukkig laat deze geweldige tip zien hoe je de namen gemakkelijker kunt lezen in scenario ‘ s als deze.
eenmaal vermeld in een van-clausule kunnen deze tabellen en weergaven op dezelfde manier worden behandeld als andere lokale tabellen. Ze kunnen worden opgevraagd, aliased, joined, endif juiste machtigingen zijn ingesteld, ze kunnen worden gewijzigd met behulp van insert, update, en deletestatements.
aanroepen van opgeslagen Procedures via een gekoppelde Server
ten behoeve van deze demo werd deze kleine procedure aangemaakt in de masterdatabase op de gekoppelde server.,
USE masterGOCREATE PROCEDURE dbo.SQLTips1 AS SELECT 'I was able to be called';
de procedure wordt vervolgens uitgevoerd op de primaire instantie met behulp van dit commando dat hetzelfde 4-delige identifierschema volgt als de statements in de vorige sectie.
EXEC .master.dbo.SQLTips1
echter, in plaats van het verwachte resultaat wordt een fout gemaakt.
RPC staat voor Procedure op afstand aanroep. Standaard, gekoppelde Servers zijn opmerkelijk om opgeslagen procedure calls. Dit wordt gemakkelijk verholpen door het inschakelen van een enkele instelling op de gekoppelde Server., Blader naar de gekoppelde servereigenschappen,kies Serveropties en de 4thoption in de lijst zal RPC Out zijn. Verander gewoon die waarde naar True en klik op OK.
de instelling met het label RPC is niet de waarde die moet worden gewijzigd. Dit lijkt verwarrend omdat het foutbericht het specifiek lijkt te noemen, maar dit is eigenlijk een verouderde instelling die RPC-aanroepen niet mogelijk maakt.
het opnieuw uitvoeren van de procedure levert nu een veel beter resultaat op.,
SQL Server Linked Serverbeperkingen
gelinkte Servers kunnen zeer nuttig zijn, maar er zijn een paar beperkingen waarvan u op de hoogte moet zijn voordat u ze implementeert. Gekoppelde Servers kunnen niet worden gebruikt om tablevalued of scalar functies aan te roepen. Ook is het niet toegestaan om het commando “afkappen” op te roepen met behulp van een Gelinkte Server.
SQL Server Linked Server Use Cases
de prestaties van gekoppelde Servers zijn niet altijd geweldig, vooral met grote gegevenssets en veel joins tussen lokale en gekoppelde tabellen. Maar ze kunnen zeer nuttig zijn voor het opvragen van kleinere datasets.,
soms kan aan een ETL-vereiste voor een kleinere dataset worden voldaan met behulp van een LinkedServer in plaats van een ETL-tool zoals SSIS. Wanneer de rijen betrokken aantal in de duizendenof lagere 10s van duizenden een gekoppelde Server gebaseerde ETL zou kunnen voltooien inless tijd dan het duurt om de SSIS engine te starten.
volgende stappen
- Er is een hele familie van geavanceerde gelinkte Server tips op MSSQLTips.,com –
- het Maken van Gekoppelde Server namen makkelijker te lezen
Laatst Bijgewerkt: 2019-06-28
Over de auteur
Eric Blinn is de Senior Data Architect voor Squire Patton Boggs. Hij is ook een SQL auteur en PASS lokale groepsleider.Bekijk al mijn tips
- meer SQL Server DBA-Tips…