Da: Eric Blinn | Aggiornamento: 2019-06-28 | Commenti (4) | Correlati: Più > Server Collegati
Problema
vedo che c’è un’opzione in SQL Server Management Studio (SSMS) per i Server Collegati andI volete saperne di più su cosa fanno e come funzionano.
Soluzione
I server collegati sono un metodo con cui un server SQL può comunicare con un’altra base di dati conforme ODBC, ad esempio un’altra istanza di SQL Server o un database Oracle, con una query directT-SQL.,
Ci sono diverse impostazioni importanti da sapere quando si imposta un server collegato.Questo suggerimento riguarderà la creazione di un server collegato a un’altra istanza di SQL Server utilizzando la GUI SSMS insieme alle opzioni di sicurezza appropriate. Mostrerà anche come usareil Server collegato in un’istruzione T-SQL. C’è una libreria agreat di argomenti più avanzati disponibili una volta che le basi sono state padroneggiate.,
Creazione di un server collegato a SQL Server
In SQL Server Management Studio, nel riquadro Esplora oggetti, espandere la sezione “ServerObjects”, fare clic destro su “Server collegati” e scegliere “NewLinked Server…” dal menu.
Nuovo Server collegato – Pagina generale
Che aprirà una schermata simile alla schermata sottostante. Scegliere il servertype come SQL Server e digitare il nome dell’altra istanza di SQL Server. In questo esempio la connessione verrà effettuata a un’istanza denominata di SQL Server sulla stessa macchina., Questo è certamente permesso, ma più comunemente la connessione è fattaa un’altra macchina interamente. Il nome del server collegato viene specificato come theServerName \ InstanceName. Nell’esempio seguente mi collego al localserver e il nome dell’istanza è “SECURITY_TEST”.
New Linked Server – Security Page
Quindi, passare dalla pagina Generale alla pagina di sicurezza utilizzando il menu a sinistra.Questa pagina consente all’amministratore di indicare a questo server SQL come autenticarsi all’altra istanza., La metà superiore dello schermo consente ai singoli accessi di accedere per utilizzare il server collegato.
Esistono 2 metodi per autenticare gli utenti. Il primo è selezionare un login persono da utilizzare quando si utilizza il server collegato. Nello screenshot qui sotto l’utente Eric accederà automaticamente al server secondario come login LinkedServerTeston l’altra istanza. Questa credenziale è memorizzata all’interno del motore SQL Server eè crittografato. Una seconda riga, il login LinkedServerTest (un login SQL) è selezionato Impersonatebox., Questo tenterà di utilizzare lo stesso nome utente e password sul LinkedServer come ha fatto sul server originale. La rappresentazione di un login di Windows può anche essere eseguita, ma richiede l’impostazione di una delega adeguata.
Ora concentrati sulla metà inferiore dello schermo. Ci sono 4 opzioni disponibili. Whicheveroption è selezionato qui si applica a qualsiasi utente non elencato nella sezione superiore.,
| Opzione | Descrizione |
|---|---|
| Non essere fatto | Questo darà un errore a qualsiasi utente di tentare di utilizzare il Collegato Serverthat non è nell’elenco di cui sopra. Per selezionare questa opzione, ildeve essere almeno 1 login nella metà superiore dello schermo. Questo èl’errore che tutti gli altri utenti otterranno. Msg 7416, Livello 16, Stato 1,Linea 1 L’accesso al server remoto è negato perché non esiste alcuna mappatura di login., |
| Essere fatto senza utilizzare un contesto di sicurezza | Questo invierà una connessione anonima al server collegato e probabilmente non fornirà alcun accesso al server secondario. |
| Essere realizzato utilizzando il contesto di sicurezza corrente del login | Questo è lo stesso di mettere ogni singolo utente nella sezione superiore con la casella di controllo impersona selezionata., |
| Essere realizzato utilizzando questo contesto di sicurezza: | Questa opzione richiederà un login e una password da inserire e funzionerà come se ogni singolo utente fosse stato aggiunto alla sezione superiore e dato esattamente lo stesso nome utente e password. È il metodo meno sicuro per impostare un server collegato in quanto chiunque abbia accesso al primo SQL Server sarà in grado di utilizzare il server collegato utilizzando le credenziali salvate., |
Nuovo Server collegato – Pagina delle opzioni del server
Ci sono anche impostazioni nella pagina delle opzioni, ma per le query semplici queste impostazionipuò essere lasciato come valori predefiniti come mostrato di seguito.
Le impostazioni non devono essere modificate nella maggior parte dei casi d’uso e questa tabella spiegherà cosa fanno le impostazioni.
| Opzione | Descrizione |
|---|---|
| Compatibile con le regole di confronto | Il valore predefinito è false., Questo può essere impostato su true se il server di destinazione utilizza esattamente le stesse regole di confronto del server principale. Il changecan migliorare le prestazioni di alcune query consentendo a colonne remote comparisonsson tipo di testo colonne. |
| Accesso ai dati | Il valore predefinito è True. Quando false tutte le singole esecuzioni di query remote falliranno. Questa impostazione è indipendente da RPC Out. In uno scenario in cui è necessario eseguire SOLO le chiamate di procedura remota, questa impostazione può essere modificata su false mentre RPC Out rimane true., |
| RPC/RPC Out | Questo sta per Remote Procedure Call e consente di eseguire storedprocedures sul server collegato. RPC abilita le chiamate procedurali remote dal server specificato e RPC OUT abilita le chiamate procedurali remote al server specificato. |
| Usa Remote Collation / Collation Name | Il valore predefinito è False. Quando false le regole di confronto del server locale sarannoutilizzato su tutte le query remote. Quando è vero, le regole di confronto del remoteserver verranno utilizzate nelle query., Se true e il server remoto NON è un SqlServer, il server locale non può cercare automaticamente il valore di confronto corretto. In questo caso, utilizzare l’impostazione Nome regole di confronto per digitare regole di confronto del server theremote. |
| Timeout di connessione | Il valore predefinito è 0. Quando diverso da zero, questo è il numero di secondi towait prima del timeout durante la connessione a un server collegato. Quando 0, il valore viene effettivamente letto da un server predefinito che può essere trovato eseguendo questo comando.
exec sp_configure 'remote login timeout (s)' |
| Il timeout della query | Il valore predefinito è 0., Quando diverso da zero, questo è il numero di secondi towait prima di temporizzare una query in esecuzione su un server collegato. Quando 0 thenthe valore viene effettivamente letto da un server predefinito che può essere trovato eseguendo questo comando.
exec sp_configure 'remote query timeout (s)' |
| Distributore / Editore / Abbonato | Queste impostazioni vengono utilizzate solo quando il server collegato viene utilizzato per supportare la replica. |
| Lazy Schema Validation | Default è False. Quando false viene controllato lo schema di una query remotaprima di ogni esecuzione della query., Eventuali modifiche allo schema remoto sono rilevate in anticipo e un piano di query corretto può essere ricreato per conto della modifica. Quando true lo schema non viene convalidato fino al momento dell’esecuzione. Se la modifica nello schema remoto rende il piano di query invalidthat causerà un errore di runtime. |
| Abilita la promozione delle transazioni distribuite | Il valore predefinito è True. Se abilitata qualsiasi query remota che modifica i datiinvocherà il DTC (Distributed Transaction Coordinator)., Questo processoprotegge tali query consentendo a entrambi i server di avere una transazione condivisache completa il 100% o lo 0%, proprio come una normale transazione con una query locale. Se disabilitato, il DTC non viene richiamato e una remotequery non riuscita può fallire e non essere completamente ripristinata. |
Interrogare i dati su un server collegato a SQL Server
Per leggere i dati da un server collegato, è necessario fare riferimento a qualsiasi tabella o vista utilizzando un identificatore in 4 parti costituito dal nome del server collegato, dal nome del database, dallo schema e dal nome dell’oggetto, in quest’ordine.,
Ad esempio:
SELECT * FROM .master.sys.databasesSELECT * FROM .WideWorldImporters.Sales.Orders
- Prima viene fornito il nome del server collegato che è per il nostro esempio. In questo esempio deve essere tra parentesi a causa del suo formato. Non tutti i nomi LinkedServer devono essere tra parentesi.
- Il prossimo è il nome del database, master e WideWorldImporters rispettivamente.
- Terzo è il nome dello schema, sys e Sales rispettivamente.
- Infine, viene elencato il nome dell’oggetto. In questi esempi gli oggetti sono databasee Ordini.
Questo esempio è piuttosto brutto a causa della natura del nome del server collegato., Fortunatamente, questo grande suggerimento mostra come rendere i nomi più facili da leggere in scenari come questo.
Una volta elencate in una clausola FROM, queste tabelle e viste possono essere trattate nello stesso modo di qualsiasi altra tabella locale. Possono essere interrogati, alias, uniti, ese sono impostate le autorizzazioni appropriate, possono essere modificati utilizzando insert, update e deletestatements.
Chiamata di stored procedure su un server collegato
Ai fini di questa demo, questa piccola procedura è stata creata nella masterdatabase sul server collegato di destinazione.,
USE masterGOCREATE PROCEDURE dbo.SQLTips1 AS SELECT 'I was able to be called';
La procedura viene quindi eseguita sull’istanza primaria utilizzando questo comando che segue lo stesso schema di identificatore di 4 parti delle istruzioni nella sezione precedente.
EXEC .master.dbo.SQLTips1
Tuttavia, al posto del risultato atteso viene generato un errore.
RPC sta per Chiamata di procedura remota. Per impostazione predefinita, i server collegati sono notevoli per effettuare chiamate stored procedure. Questo può essere facilmente risolto abilitando l’impostazione asingle sul server collegato., Passare alle proprietà del server collegato, scegliere Opzioni server e la 4thoption nella lista sarà RPC fuori. Basta cambiare quel valore a True e clickOK.
L’impostazione denominata RPC NON è il valore che deve essere modificato. Questo può sembrare confuso poiché il messaggio di errore sembra chiamarlo in modo specifico, ma in realtà è un’impostazione legacy che non abilita le chiamate RPC.
Rieseguendo la procedura ora si ottiene un risultato molto migliore.,
Limitazioni del server collegato a SQL Server
I server collegati possono essere molto utili, ma ci sono alcune limitazioni di cui essere consapevoli prima di implementarli. I server collegati non possono essere utilizzati per chiamare funzioni tablevalued o scalar. Inoltre, il comando truncate non può esserechiamato utilizzando un server collegato.
SQL Server Linked Server Use Cases
Le prestazioni dei server collegati non sono sempre eccezionali, specialmente con set di dati di grandi dimensioni e molti join tra tabelle locali e collegate. Ma possono essere molto utili per interrogare set di dati più piccoli.,
A volte un requisito ETL per un set di dati più piccolo può essere soddisfatto utilizzando un LinkedServer piuttosto che uno strumento ETL come SSIS. Quando le righe coinvolte numero nel thousandsor inferiore 10s di migliaia di un server collegato basato ETL potrebbe essere in grado di completare inless tempo di quanto ci vuole per avviare il motore SSIS.
Passi successivi
- Esiste un’intera famiglia di suggerimenti avanzati per server collegati su MSSQLTips.,com
- Fare i nomi di Server Collegati più facile da leggere
Ultimo Aggiornamento: 2019-06-28
Circa l’autore
Eric Blinn è il Sr. Dati Architetto per Squire Patton Boggs. Egli è anche un autore SQL e passare leader del gruppo locale.Visualizza tutti i miei suggerimenti
- Altri suggerimenti DBA di SQL Server…