I linked server rappresentano una funzionalità avanzata di Microsoft SQL Server che consente l'accesso a dati distribuiti su più server, come se fossero presenti su un unico server locale. Questa capacità viene realizzata tramite l'utilizzo di provider OLE DB (Object Linking and Embedding Database) che permettono la connessione a varie fonti di dati, inclusi altri SQL Server, Oracle, MySQL, e persino file di Excel. La possibilità di eseguire query distribuite (distributed queries) semplifica l'integrazione e l'interoperabilità tra diverse origini dati, rendendo i linked server uno strumento estremamente diffuso per amministratori di database e sviluppatori.
Architettura dei Linked Server
Come anticipato nel paragrafo precedente, l’architettura di un linked server si basa sull'utilizzo dei provider OLE DB, che fungono da intermediari tra SQL Server e la fonte di dati remota. Le fonti dati remote più comuni verso le quali viene instaurato un linked server sono Oracle, MySQL e lo stesso SQL Server, ad esempio nel caso ci fossero più istanze che non comunicano direttamente tra loro. Quando viene eseguita una query su un linked server, SQL Server passa la query al provider OLE DB appropriato, che si occupa di tradurre e inviare la query al server remoto.
Più nel dettaglio, un Linked Server è costituito da due componenti principali:
- OLE DB Provider: Una DLL responsabile della gestione e dell'interazione con una specifica fonte di dati.
- OLE DB Data Source: Identifica il database specifico accessibile tramite OLE DB.
A partire da SQL Server 2019, il provider predefinito è il Microsoft OLE DB Driver per SQL Server (MSOLEDBSQL). Questo provider è preferito rispetto ai precedenti, come SQLNCLI, che sono stati deprecati nelle versioni più recenti di SQL Server.
Data la sua diffusione, particolare attenzione viene posta verso la connessione a Oracle. Per questo tipo di connessione, SQL Server utilizza il provider OLE DB specifico per Oracle (tipicamente OraOLEDB.Oracle o Oracle Instantclient). Questo consente di eseguire query distribuite, aggiornamenti e altre operazioni sui dati di un database Oracle come se fossero locali. L'interazione tra SQL Server e Oracle tramite Linked Server prevede la traduzione delle query T-SQL in un formato comprensibile da Oracle, inviando poi questi comandi attraverso il provider OLE DB al server Oracle.
Questa architettura permette una grande flessibilità, ma richiede una configurazione attenta per garantire prestazioni ottimali e sicurezza. La configurazione include la definizione delle opzioni di accesso ai dati, la gestione delle credenziali e l'ottimizzazione delle query per ridurre il traffico di rete e migliorare la reattività del sistema. Parleremo più approfonditamente delle best practices nel paragrafo Prestazioni.
Configurazione di un Linked Server
La configurazione di un Linked Server in SQL Server consiste nella definizione di entrambi i componenti che compongono un linked server: Provider e Data Source. Vediamo di seguito un esempio di configurazione di un linked server verso una fonte dati remota.
Dopo aver installato i componenti forniti dal wizard di installazione del provider corretto, si procede con la configurazione tramite SQL Server Management Studio (SSMS) o script T-SQL. In SSMS, si espande il nodo "Server Objects", si clicca con il tasto destro su "Linked Servers" e si seleziona "New Linked Server". Nel modulo di configurazione, si assegna un nome al Linked Server e si seleziona il nome del provider (nel caso di Oracle potrebbe essere: "Oracle Provider for OLE DB"). Successivamente, si specifica il data source (il nome del server remoto o l'alias del TNS) e altre proprietà di connessione – come il nome del catalogo – se necessario. È cruciale impostare correttamente le credenziali di sicurezza nella sezione "Security", dove si definiscono le modalità di autenticazione per l'accesso alla fonte dati remota. Vediamo più approfonditamente il perché nel paragrafo dedicato alla Sicurezza.
Una volta configurato, il Linked Server consente di eseguire query distribuite e operazioni DML (Data Manipulation Language) tra SQL Server e la fonte dati remota, facilitando l'integrazione dei dati e l'interoperabilità tra i due sistemi. Questo approccio permette di sfruttare le capacità di entrambe le piattaforme per soluzioni avanzate di gestione dei dati.
Sicurezza
La configurazione della sicurezza per i Linked Server in SQL Server richiede una configurazione attenta e dettagliata per evitare vulnerabilità significative. Una delle configurazioni errate più comuni è l'impersonificazione di un account sysadmin, la quale può compromettere la sicurezza dell'intero sistema, consentendo a utenti non autorizzati di eseguire operazioni con privilegi elevati. È cruciale definire le modalità di autenticazione e autorizzazione in modo che l'accesso sia gestito tramite credenziali specifiche, limitando i privilegi in base al Principle of Least Privilege (PoLP) – il principio del privilegio minimo.
Come anticipato nella sezione precedente, quando si configura un Linked Server è possibile specificare le opzioni di sicurezza nella sezione "Security" della finestra di dialogo "New Linked Server". Qui, è fondamentale evitare l'opzione "Be made using the login's current security context" per account ad alto privilegio. Invece, si dovrebbe optare per "Be made using this security context" e fornire un account con privilegi limitati, appropriato per le operazioni richieste.
Un altro aspetto importante è la gestione delle grant. Utilizzando lo stesso utente di destinazione, è possibile configurare diversi permessi dalla sorgente, assicurandosi che l'utente abbia solo le autorizzazioni necessarie. Questo si realizza attraverso la definizione precisa delle grant a livello di database e di oggetto nel server di origine. Ad esempio, si può configurare l'utente del Linked Server in modo che abbia solo permessi di SELECT su determinate tabelle e non permessi di INSERT, UPDATE o DELETE, a meno che non siano strettamente necessari.
In sintesi, la configurazione della sicurezza per i Linked Server deve essere eseguita con attenzione, definendo chiaramente i contesti di sicurezza, evitando l'impersonificazione di account privilegiati, e configurando grant specifici per ogni utente di destinazione. Questi passaggi aiutano a proteggere il sistema da accessi non autorizzati e a mantenere l'integrità e la sicurezza dei dati.
Prestazioni
La configurazione e la gestione delle prestazioni dei Linked Server in SQL Server sono aspetti cruciali per l'integrazione efficace con database remoti come Oracle. Di seguito vengono descritti alcuni dei principali fattori ed errori comuni che influenzano le prestazioni dei Linked Server, insieme a strategie per ottimizzare queste configurazioni. Di seguito viene fornita una panoramica generale dei problemi prestazionali più comuni ed alcune strategie per ovviare a tali problemi.
Problemi di Prestazioni Comuni
- Query Inefficienti e Traduzione delle Funzioni SQL: Le query che coinvolgono Linked Server possono essere inefficaci se non sono ottimizzate correttamente. Ad esempio, funzioni SQL Server che non vengono tradotte efficacemente nel linguaggio SQL di Oracle possono causare un incremento del carico di lavoro sul server locale, anziché essere delegate al server remoto, con conseguente aumento del traffico di rete e dei tempi di risposta.
- Configurazioni Inadeguate: Errori nella configurazione delle proprietà di collegamento e sicurezza sono comuni. L'uso improprio della modalità "RPC Out" (Remote Procedure Call Out) può aggiungere un overhead non necessario alle comunicazioni tra server. Inoltre, la scelta errata del provider OLE DB o una configurazione inadeguata delle proprietà di connessione, come il timeout e le impostazioni di pooling dele connessioni, possono peggiorare significativamente le prestazioni. l
- Uso di Account con Privilegi Elevati: Utilizzare account con privilegi eccessivi, come un sysadmin, può creare colli di bottiglia nelle prestazioni a causa delle autorizzazioni elevate richieste per ogni operazione. Questo non solo espone il sistema a rischi di sicurezza, ma può anche rallentare le operazioni quotidiane.
Gestione delle Attese (Wait Types)
- Async I/O Waits: Le operazioni di I/O asincrone sono cruciali per le prestazioni dei Linked Server. Se il sistema non gestisce correttamente queste operazioni, si possono verificare ritardi significativi. Ad esempio, le attese di tipo "ASYNC_NETWORK_IO" indicano problemi di rete o configurazioni del Linked Server che impediscono un trasferimento efficiente dei dati. Monitorare e ottimizzare queste operazioni è essenziale per migliorare le prestazioni.
- Altri Tipi di Wait: Altri tipi di attese possono influire sulle prestazioni dei Linked Server, tra cui "CXPACKET" (associato alla parallelizzazione delle query), "LCK_M_*" (relativo ai lock) e "SOS_SCHEDULER_YIELD" (indicativo di task che cedono la CPU). Configurazioni inefficaci dei Linked Server possono amplificare questi problemi, portando a un degrado complessivo delle prestazioni del sistema.
Strategie di Ottimizzazione
- Ottimizzazione delle Query: Utilizzare strumenti come "OPENQUERY" per eseguire query direttamente sul server remoto, riducendo il carico sul server SQL Server locale. Questo aiuta a minimizzare il trasferimento di dati tra server e a migliorare l'efficienza delle query distribuite.
- Configurazione del Provider: Assicurarsi che le proprietà del provider OLE DB siano configurate correttamente, ottimizzando i parametri di connessione e di pooling delle connessioni per adattarsi alle specifiche esigenze del caso di utilizzo. Ciò include la regolazione del timeout delle connessioni e la gestione delle risorse di rete.
- Monitoraggio e Tuning: Monitorare costantemente le prestazioni dei Linked Server e regolare le configurazioni di rete e dei server in base ai risultati. Esistono svariati strumenti di monitoraggio per identificare e correggere tempestivamente le attese più frequenti, migliorando così la reattività del sistema.
- Implementazione del Privilegio Minimo: Utilizzare account con privilegi limitati per ridurre i rischi di sicurezza e migliorare le prestazioni delle operazioni. Questo approccio garantisce che gli utenti abbiano solo le autorizzazioni necessarie per eseguire le loro funzioni specifiche.
Conclusione
I Linked Server rappresentano una soluzione potente e flessibile per l'integrazione dei dati distribuiti in Microsoft SQL Server, permettendo di eseguire query e operazioni DML su dati remoti come se fossero locali. Tuttavia, per sfruttare appieno questa funzionalità, è fondamentale una configurazione accurata e una gestione attenta. La corretta scelta e configurazione dei provider OLE DB, la definizione delle opzioni di sicurezza, e l'ottimizzazione delle query sono aspetti cruciali per garantire prestazioni ottimali e sicurezza. Implementare queste best practices permette di ridurre il traffico di rete, migliorare la reattività del sistema e mantenere l'integrità e la sicurezza dei dati, offrendo una soluzione robusta e scalabile per le moderne esigenze di gestione dei dati distribuiti.
di Matteo Ambrosini, pubblicato il 26 novembre 2024
