Un’attività comune durante la scrittura di query T-SQL è la connessione a database locali e l’elaborazione diretta dei dati. Ma ci saranno situazioni in cui è necessario connettersi a un database remoto che si trova in un’istanza diversa nello stesso server o in un server fisico diverso ed elaborare i suoi dati in parallelo con l’elaborazione dei dati locale.,
SQL Server ci fornisce quattro metodi utili per connettersi ai server di database remoti, anche altri tipi di server di database, e interrogare i suoi dati all’interno dell’istruzione T-SQL. In questo articolo, discuteremo questi quattro metodi e come usarlo per interrogare i database SQL Server remoti.
OPENDATASOURCE
Il primo metodo per interrogare un database SQL Server remoto è la funzione OPENDATASOURCE T-SQL di seguito:
OPENDATASOURCE ( provider_name as char, init_string)
Dove provider_name è il provider OLE DB utilizzato per accedere all’origine dati., E init_string è la stringa di connessione del server remoto.
Per essere in grado di utilizzare il OPENDATASOURCE istruzione, è necessario assicurarsi che il DisallowAdhocAccess chiave del registro di sistema è impostato a 0 per il provider che si desidera collegare ad altri di SQL Server, che può essere trovato nel seguente percorso del Registro di sistema di Chiavi :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\<Provider>
Inoltre, è necessario attivare la Query Distribuite Ad Hoc opzione di configurazione avanzata che è disattivato per impostazione predefinita di SQL Server., Se si tenta di eseguire il seguente semplice query che utilizza la OPENDATASOURCE istruzione T-SQL, verrà visualizzato l’errore:
Msg 15281, Livello 16, Stato 1, Riga 1
SQL Server bloccato l’accesso all’ISTRUZIONE ‘OpenRowset/OpenDatasource’ di componente Ad Hoc Query Distribuite ” a causa di questo componente è spento come parte della configurazione di sicurezza per il server. Un amministratore di sistema può abilitare l’uso di ‘Query distribuite ad hoc’ utilizzando sp_configure., Per ulteriori informazioni sull’abilitazione di “Query distribuite ad Hoc”, cercare “Query distribuite ad Hoc” in SQL Server Books Online.
Come si può vedere dal messaggio di errore, l’opzione di configurazione avanzata delle query distribuite Ad Hoc dovrebbe essere abilitata per aprire la connessione a un server remoto utilizzando 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 può sostituire il nome del server nel nome in quattro parti della tabella o della vista in un’istruzione SELECT, INSERT, UPDATE o DELETE. Può essere utilizzato anche nell’istruzione EXECUTE per eseguire una stored procedure remota.
OPENROWSET
Il secondo modo per interrogare un database ospitato in un server SQL remoto è la funzione OPENROWSET T-SQL. Per utilizzare il metodo ad hoc OPENROWSET, è necessario fornire tutte le informazioni di connessione necessarie per connettersi al server SQL remoto e a molte altre risorse., Può anche essere utilizzato per un’operazione di massa attraverso il provider di massa integrato per leggere i dati dai file. OPENROWSET viene utilizzato nella clausola FROM come nome di tabella nelle istruzioni SELECT, INSERT, UPDATE o DELETE. Sebbene la query possa restituire più set di risultati, OPENROWSET restituisce solo il primo.
L’utilizzo di OPENROWSET richiede l’attivazione dell’opzione di configurazione avanzata delle query distribuite ad Hoc come la funzione OPENDATASOURCE.,979d8a2e”>
È possibile scrivere il precedente OPENDATASOURCE query utilizzando la funzione OPENROWSET come segue:
Server Collegato
UN Server Collegato SQL Server viene utilizzato per l’accesso a origini dati OLE DB come un’istanza di SQL Server ubicato al di fuori di SQL Server o altri sistemi come ad esempio Oracle, Microsoft Access e di Excel, ed eseguire distribuito query T-SQL contro di loro.,
Un server collegato a SQL Server è diverso dalle query ad hoc in quanto le query ad hoc aprono una connessione temporanea con il server remoto e la chiudono, dove il server collegato permanente è sempre disponibile per l’uso. Quando l’utente esegue una query distribuita su un’origine dati remota utilizzando un server collegato, il motore SQL Server analizza tale comando e invia le richieste a OLE DB. Questa richiesta può essere una query da eseguire o una tabella da aprire su quel server remoto.,
Un server collegato può essere configurato utilizzando SQL Server Management Studio o utilizzando l’istruzione sp_addlinkedserver T-SQL.
Per configurare un server collegato utilizzando SQL Server Management Studio, espandere il nodo Oggetti Server dalla finestra Esplora oggetti. Fare clic con il pulsante destro del mouse sul nodo Server collegato e scegliere Nuovo server collegato.
Nella scheda Generale della finestra Nuovo server collegato, scegli un nome per il tuo server collegato, quindi scegli il tipo di server a cui devi connetterti usando quel server collegato., Selezionare SQL Server se si riesce a connettersi a un’istanza remota di SQL Server oppure scegliere Un’altra origine dati da selezionare tra i tipi di server OLE DB disponibili dall’elenco a discesa Provider diverso da SQL Server. Se si sceglie SQL Server come tipo di server, il nome del server collegato deve essere il nome di rete del server SQL remoto.
Riempire il campo Nome prodotto con il nome prodotto dell’origine dati OLE DB selezionata, ad esempio SQL Server se ci si connette a un server SQL remoto., Digitare il nome dell’origine dati selezionata nel campo Origine dati, ad esempio il nome dell’istanza di SQL Server se ci si connette a un’istanza di SQL Server remota. Riempire il campo Stringa provider con la stringa di connessione specifica del provider OLE DB che identifica un’origine dati univoca. Il campo Posizione può essere riempito con la posizione del database remoto come interpretato dal provider OLE DB. Digitare il nome del catalogo da utilizzare dal provider OLE DB nel campo Catalogo.,
Non è necessario utilizzare tutti gli argomenti descritti insieme, poiché gli argomenti necessari dipendono dal provider selezionato. Ad esempio, utilizzando il provider SQL Server, verranno utilizzati solo due argomenti.
Nella pagina Sicurezza della finestra Nuovo Server collegato, specificare il contesto di sicurezza che il server collegato utilizzerà per connettere il server SQL originale all’origine dati remota., Come spiega il nome, nella parte mappature accessi server locali a accessi server remoti della finestra di protezione, è possibile specificare un elenco di utenti che possono utilizzare il server collegato per connettersi al server remoto mappando questi utenti locali con accessi server remoti. Questi utenti possono essere tramite autenticazione SQL Server o un login di autenticazione di Windows.
Per gli accessi che non sono stati definiti nell’elenco di mappatura, è necessario scegliere il contesto di sicurezza per le loro connessioni al server remoto utilizzando quel server collegato., La scelta di Non essere effettuata impedirà a qualsiasi utente non incluso nell’elenco precedente di utilizzare quel server collegato. Selezionando Be made senza utilizzare un contesto di sicurezza, gli utenti non inclusi nell’elenco di mappatura precedente si connetteranno al server remoto utilizzando quel server collegato senza specificare un contesto di sicurezza per loro. Se si sceglie Be made utilizzando il contesto di sicurezza corrente del login, la connessione al server remoto verrà stabilita utilizzando l’utente di connessione, che è la scelta migliore., Nell’opzione Be made using this security context, specificare le credenziali di autenticazione di SQL Server che verranno utilizzate per stabilire la connessione al server remoto per gli utenti non definiti nell’elenco di mappatura.
Ci sono molte opzioni che è possibile regolare a seconda delle esigenze dalla pagina Opzioni server della nuova finestra Server collegato. Ad esempio, impostare l’opzione Collation Compatible su true se si è certi che l’origine dati remota abbia lo stesso set di caratteri e lo stesso ordinamento del server locale., Per impostazione predefinita, SQL Server valuta i confronti sulle colonne di caratteri localmente se non si utilizza questa opzione. L’opzione di accesso ai dati viene utilizzata per abilitare e disabilitare l’accesso alla query distribuita per il server collegato. Per abilitare la chiamata di procedura remota dal server specificato, impostare RPC su true e per abilitare la chiamata di procedura remota al server specificato impostare RPC su true.
Decidere se le regole di confronto del server locale o remoto verranno utilizzate nella query è determinato dall’opzione Usa regole di confronto remote., Se il valore di questa opzione è true, è possibile specificare il nome delle regole di confronto che verrà utilizzato dal server remoto nell’opzione Nome regole di confronto. Ciò è applicabile se l’origine dati remota non è SQL Server, in cui è possibile specificare qualsiasi nome di confronto supportato da SQL Server in tale campo.
È possibile ignorare il timeout di accesso remoto del server predefinito per quel server collegato modificando l’opzione di timeout della connessione su un valore maggiore di 0.Questo valore specifica il timeout in secondi per la connessione al server collegato., È inoltre possibile specificare il timeout in secondi per le query che si connettono al server collegato modificando l’opzione di timeout della query su un valore maggiore di 0. Questo sovrascriverà il timeout di query remota del server per il server collegato.
Un’altra opzione utile che viene utilizzata per avviare una transazione distribuita quando si chiama una stored procedure remota, in cui questa transazione verrà gestita e protetta da MS DTC. Questa opzione è chiamata Abilita promozione delle transazioni distribuite.,egli qui di seguito il messaggio, altrimenti un messaggio di errore verrà visualizzato che c’è qualcosa che impedisce la connessione apertura:
Si può facilmente creare il precedente server collegato tramite il sp_addlinkedserver istruzione T-SQL, passando gli argomenti richiesti come segue:
|
1
2
3
4
|
USA
ANDARE
EXEC master.,dbo.sp_addlinkedserver @server = N’DEV_SQL’, @srvproduct=N’Server SQL’
GO
|
Una volta creato correttamente il server collegato, sarà possibile utilizzarlo specificando il nome in quattro parti che include: Linked_Server_Name.Remote_Database_Name._NOME schema.Table_Name, come nel seguente esempio:
|
1
2
|
SELECT * FROM DEV_SQL.,testdb.dbo.Profile
GO
|
OPENQUERY
L’ultimo metodo SQL Server utilizzato per connettersi a un’origine dati remota è la funzione OPENQUERY. È un metodo ad hoc una tantum alternativo per connettersi a un server remoto utilizzando il server collegato. Per connessioni più frequenti al server remoto, è preferibile utilizzare il server collegato anziché la funzione OPENQUERY.
La funzione OPENQUERY può essere utilizzata nella clausola FROM dell’istruzione SELECT, INSERT, UPDATE o DELETE che sostituisce il nome della tabella., Ci vogliono due argomenti; il nome del server collegato e la query. Questi parametri non possono essere variabile come indicato di seguito:
OPENQUERY ( server_collegato ,’query’ )
di Seguito è un semplice esempio di OPENQUERY di utilizzo:
|
1
|
SELECT * FROM OPENQUERY(DEV_SQL,’SELECT * FROM testdb.dbo.,Profilo’)
|
Confronto
Quando si utilizza il Server Collegato per interrogare un server remoto, query optimizer creare il piano di esecuzione dopo la classificazione e dividendo la query in locale e in remoto query, dove il locale query verrà eseguita in locale e remoto query vengono inviate al server remoto, quindi unite insieme per visualizzare il risultato finale per l’utente singolo set., Un altro svantaggio del server collegato è che nessun filtro verrà applicato sul server remoto se la query ha la clausola WHERE, dove recupererà tutti i record dalla tabella remota e eseguirà il filtraggio e l’unione localmente.
Nel caso della funzione OpenQuery, il motore SQL non tenterà di classificare la query o controllare cosa farà, semplicemente invierà la query così com’è al server remoto. L’analisi della query SQL, la generazione del piano di esecuzione e tutti i filtri verranno eseguiti sul server remoto.,
In generale, OpenQuery è più veloce del server collegato in quanto il motore SQL non interromperà la query prima di inviarla al server remoto, ma utile solo per connessioni remote meno frequenti una tantum.
Utilizzando le funzioni OPENROWSET e OPENDATASOURCE, è necessario specificare tutti i dettagli della connessione inclusi il nome utente e la password ogni volta che lo si utilizza. Sebbene queste funzioni non forniscano tutte le funzionalità del server collegato, come la gestione della sicurezza, consumano meno risorse dal server., Poiché queste funzioni aprono una connessione una tantum al server remoto, è preferibile utilizzare il server collegato per l’accesso frequente al server remoto.
- Autore
- Post Recenti
È un esperto di soluzioni certificate Microsoft in gestione e analisi dei dati, Microsoft Certified Solution Associate in amministrazione e sviluppo di database SQL, Azure Developer Associate e Microsoft Certified Trainer.
Inoltre, sta contribuendo con i suoi suggerimenti SQL in molti blog.,
Visualizza tutti i messaggi di Ahmad Yaseen
- Come per monitorare Azure Dati di Fabbrica – il 15 gennaio 2021
- Uso del Controllo del codice Sorgente in Azzurro i Dati di Fabbrica – gennaio 12, 2021
- Utilizzo di Modelli di Azure Dati di Fabbrica – 8 gennaio 2021