SQL Server
SQL Server e' il
sistema di gestione di database relazionale (RDBMS) robusto e versatile sviluppato da Microsoft.
SQL Server offre un ricco set di caratteristiche e capacita' che lo rendono un RDBMS potente e versatile:
- Transact-SQL: (T-SQL) l'SQL che estende lo standard con funzionalita' aggiuntive.
- Gestione delle Transazioni: SQL Server garantisce la coerenza e l'integrita' dei dati
attraverso le proprieta' ACID (Atomicita', Coerenza, Isolamento, Durabilita').
Le transazioni consentono di raggruppare piu' operazioni in una singola unita' di lavoro,
assicurando che tutte le operazioni abbiano successo o che nessuna lo faccia.
- Controllo della Concorrenza: SQL Server gestisce l'accesso simultaneo ai dati,
prevenendo conflitti e assicurando che piu' utenti possano accedere e modificare i dati contemporaneamente
senza compromettere l'integrita' dei dati.
- Sicurezza: SQL Server fornisce solide funzionalita' di sicurezza, tra cui autenticazione,
autorizzazione e crittografia, per proteggere i dati sensibili.
- Alta Disponibilita' e Ripristino di Emergenza: SQL Server offre varie funzionalita' per garantire
l'alta disponibilita' e il ripristino di emergenza, come mirroring, replica e Gruppi di Disponibilita' Always On.
- Ottimizzazione delle Prestazioni: SQL Server fornisce strumenti e funzionalita' per ottimizzare
le prestazioni delle query, come l'indicizzazione, l'ottimizzazione delle query e il monitoraggio delle prestazioni.
- Scalabilita': SQL Server puo' scalare per gestire grandi volumi di dati e carichi utente elevati,
rendendolo adatto per applicazioni di livello aziendale.
- Integrazione Cloud: SQL Server si integra perfettamente con Azure, la piattaforma cloud di Microsoft,
fornendo varie opzioni di distribuzione e servizi basati su cloud.
Lo scopo di questa pagina e' quella di introdurre le funzionalita'
e caratteristiche di SQL Server.
Questo documento e' stato preparato utilizzando la versione 2022
di SQL Server ma e', mutatis mutandis, valido anche per le altre versioni.
Questo documento presenta diversi aspetti di SQL Server:
Installazione,
Edizioni,
Utilizzo (SQL, Database/Utenti),
Amministrazione,
Architettura,
Evoluzione,
...
Installazione
E' possibile installare SQL Server in locale o utilizzarlo in Cloud in molteplici modi.
Il
sito ufficiale
riporta le principali modalita':
Scelto il tipo di installazione la procedura guidata e' molto semplice
ed in pochi minuti e' possibile utilizzare la base dati.
Editions
Oltre alla scelta sul tipo di deployment e' importante utilizzare l'edizione corretta di SQL Server.
SQL Server e' disponibile in varie edizioni, ciascuna adattata ad esigenze ed a budget specifici:
- Enterprise Edition: L'edizione piu' completa, che offre tutte le caratteristiche e le capacita' di SQL Server.
- Standard Edition: Un'edizione di fascia media, adatta alla maggior parte delle applicazioni aziendali.
- Web Edition: Progettata per ambienti di web hosting.
- Developer Edition: Un'edizione completa utilizzabile per sviluppo e test.
- Express Edition: Un'edizione gratuita e leggera per piccole applicazioni.
La Developer e la Express Edition sono di uso gratuito ma hanno limiti sulla modalita' di
utilizzo e/o sulle funzionalita'
(eg. Express: size <10GB per database, CPU up to 4 core, RAM up to 1GB;
funzionalita' limitate su Reporting e Analysis Services, SQL Server Agent non disponibile)
Utilizzo
E' possibile utilizzare SQL Server da linea di comando con sqlcmd
ma su Windows e' piu' comune l'uso di interfacce grafiche.
Microsoft SQL Server Management Studio (SSMS) e' lo strumento principale per la gestione ma
consente anche di utilizzare direttamente la base dati con un interfaccia SQL:
L'accesso da esterno avviene indicando l'host, la porta (default 1433)
ed ovviamente username/password.
La stringa di connessione JDBC e' semplice
jdbc:sqlserver://serverName:1433
ma se e' installata un'istanza denominata di SQL Server e' necessario specificare il nome dell'istanza.
Ecco quindi una stringa di connessione piu' completa:
jdbc:sqlserver://serverName[\\instanceName]:port;databaseName=database;user=user;password=password;
Se l'istanza denominata utilizza una porta dinamica, il servizio SQL Server Browser deve essere attivo
altrimenti si deve specificare il numero di porta esatto nella stringa di connessione.
Per utilizzare l'autenticazione di Windows va aggiunto integratedSecurity=true;
L'SQL di SQL Server e' molto completo ed aderente agli standard piu' recenti.
E' quindi molto semplice da utilizzare per chiunque conosca gia' l'SQL
di un qualsiasi altro database relazionale.
Naturalmente si possono utilizzare innumerevoli
client grafici come MS Visual Studio, Azure Data Studio, DBeaver, ...
SQL
SQL Server ha un SQL vicino allo standard e fornisce un numero notevole di funzioni ulteriori
ed e' uno dei database piu' ricchi da questo punto di vista.
Naturalmente sono presenti tutti gli statement DML SQL:
SELECT, INSERT, DELETE, UPDATE;
gli statement di DDL sono i classici CREATE, ALTER, DROP;
la gestione degli accessi si effettua ccon GRANT e REVOKE ...
Sono supportati molteplici datatype e conversioni
sono disponibili CTE, windows functions,
...
Per default e' attivo l'autocommit e l'isolation level e' Read Committed.
E' possibile disabilitare l'autocommit e gestire le transazioni in modo esplicito
utilizzando le istruzioni BEGIN TRANSACTION, COMMIT e ROLLBACK.
READ_COMMITTED_SNAPSHOT e' un ulteriore parametro che consente di utilizzare il
row versioning anziche' i lock per la read consistency.
Datatype
SQL Server supporta molteplici datatype.
I principali datatype per stringhe SQL Server sono: VARCHAR(N), CHAR(N), NVARCHAR(N), NCHAR(N).
I principali datatype sulle date SQL Server sono: DATE, TIME, DATETIME.
I principali datatype numerici SQL Server sono:
SMALLINT, INT, BIGINT;
DECIMAL o NUMERIC;
REAL, FLOAT.
Sono disponibili altri datatype specializzati:
UNIQUEIDENTIFIER, XML, GEOGRAPHY, TABLE, ...
Database, Schema ed Utenti
Un istanza SQL Server puo' ospitare molteplici database
ma naturalmente bisogna tenere conto del carico di ciascun database e delle prestazioni del sistema ospite.
Un database particolare e' il database master che raccoglie le informazioni
sugli altri database presenti e che non e' opportuno utilizzare per ospitare dati applicativi:
per le applicazioni e' opportuno creare database separati.
Altro database particolare e' model che viene utilizzato
per creare nuovi database. Se viene modificato le creazioni successive di database
saranno variate di conseguenza.
Su ogni database sono sempre presenti gli schema:
dbo (database owner), guest, sys, INFORMATION_SCHEMA.
Lo schema dbo e' quello di default.
Gli utenti e gli schema sono distinti in SQL Server
ed e' possibile referenziare gli oggetti secondo
questa gerarchia: Server.Database.DatabaseSchema.DatabaseObject.
SQL Server utilizza un sistema di autorizzazioni granulare per controllare l'accesso ai database e agli oggetti all'interno di essi. La gestione degli utenti avviene attraverso i seguenti concetti chiave:
- Login:
Un login rappresenta un account di accesso al server SQL Server.
I login possono essere autenticati da Windows o da SQL Server stesso.
- Utenti:
Un utente e' un account all'interno di un database specifico.
Gli utenti sono associati a un login e hanno autorizzazioni specifiche all'interno del database.
- Ruoli:
I ruoli sono gruppi di autorizzazioni che semplificano la gestione degli utenti.
SQL Server fornisce ruoli predefiniti, come db_owner e db_datareader, e consente di creare ruoli personalizzati.
- Autorizzazioni:
Le autorizzazioni controllano l'accesso a specifici oggetti del database, come tabelle, viste e stored procedure.
Le autorizzazioni possono essere concesse o revocate a singoli utenti o ruoli.
In sintesi, la gestione degli utenti in SQL Server prevede la creazione di login, l'associazione di utenti ai database e l'assegnazione di autorizzazioni tramite ruoli o direttamente agli utenti.
Amministrazione
Microsoft SQL Server Management Studio (SSMS) e' lo strumento principale
del DBA per la gestione e l'amministrazione di SQL Server.
Con le versioni piu' recenti di SQL Server alcune delle attivita'
di gestione piu' significative sulla base dati sono effettuate automaticamente
e con l'utilizzo in cloud alcuni compiti sono completamente automatizzati.
Il ruolo del DBA di SQL Server si e' evoluto da un ruolo principalmente tecnico
a un ruolo piu' strategico e orientato al business.
I DBA devono essere in grado di adattarsi alle nuove tecnologie e alle nuove esigenze aziendali
per garantire che i dati siano gestiti in modo efficiente e sicuro.
Tra i piu' importanti compiti che restano al DBA:
- Gestione degli utenti.
- Migrazioni dati.
- Gestione delle strutture del database.
- Controllo attento degli spazi.
- Controllo dei log e delle attivita' sul sistema.
- Controllo di backup e restore.
- Pianificazione degli upgrade.
SQL Server offre una vasta gamma di viste di sistema che forniscono informazioni preziose sullo stato
e sulla configurazione del server e dei database.
Ecco le principali viste di sistema raggruppate per categoria:
1. Informazioni sul server e sull'istanza:
sys.servers: Elenca i server registrati.sys.configurations: Visualizza le impostazioni di configurazione del server.sys.dm_os_performance_counters: Fornisce contatori di prestazioni del sistema operativo e di SQL Server.sys.dm_os_process_memory: Informazioni sull'utilizzo della memoria per i processi di SQL Server.sys.dm_os_sys_info: Informazioni sul sistema operativo e sull'hardware.
2. Informazioni sui database:
sys.databases: Elenca tutti i database presenti sull'istanza.sys.database_files: Visualizza i file di dati e di log del database corrente.sys.master_files: Visualizza i master file di tutti database.sys.dm_db_file_space_usage: Informazioni sull'utilizzo dello spazio dei file di database.sys.dm_db_index_physical_stats(database_id, object_id, partition_number, mode): Statistiche fisiche degli indici.sys.dm_db_index_operational_stats(database_id, object_id, partition_number): Statistiche operative degli indici.sys.dm_db_log_space_usage: Informazioni sul transaction log.
3. Informazioni sugli oggetti di database:
sys.tables: Elenca tutte le tabelle nel database corrente.sys.views: Elenca tutte le viste nel database corrente.sys.columns: Elenca tutte le colonne di una tabella o vista.sys.indexes: Elenca tutti gli indici di una tabella.sys.procedures: Elenca tutte le stored procedure nel database corrente.sys.triggers: Elenca tutti i trigger nel database corrente.
4. Informazioni sulla sicurezza:
sys.server_principals: Elenca tutti i login del server.sys.database_principals: Elenca tutti gli utenti del database.sys.server_role_members: Elenca i membri dei ruoli del server.sys.database_role_members: Elenca i membri dei ruoli del database.sys.permissions: Elenca le autorizzazioni concesse agli oggetti di database.
5. Informazioni sulle sessioni e sulle query:
sys.dm_exec_sessions: Elenca tutte le sessioni attive sul server.sys.dm_exec_connections: Elenca tutte le connessioni attive al server.sys.dm_exec_requests: Elenca tutte le richieste attive in esecuzione sul server.sys.dm_exec_query_stats: Statistiche sull'esecuzione delle query.sys.dm_tran_locks: Informazioni sui lock attivi sul server.
6. Query Store (se abilitato):
sys.query_store_query: contiene informazioni sulle query.sys.query_store_plan: contiene informazioni sui piani di esecuzione delle query.sys.query_store_runtime_stats: contiene statistiche di runtime sulle query.sys.query_store_runtime_stats_interval: contiene statistiche di runtime aggregate per intervalli di tempo.
Inoltre e' disponibile il data dictionary standard information_schema.
Architettura
In questo capitolo cerchiamo di riassumere gli aspetti dell'architettura di SQL Server.
SQL Server e' un sistema complesso composto da diversi componenti chiave:
- Motore di Database (Database Engine): Questo e' il cuore di SQL Server, responsabile dell'archiviazione, dell'elaborazione e della protezione dei dati. Gestisce l'esecuzione delle query, la gestione delle transazioni e l'archiviazione dei dati.
- Agente SQL Server (SQL Server Agent): Questo servizio automatizza le attivita' amministrative, come backup, pianificazione dei processi e avvisi.
- Analysis Services (SSAS): SSAS fornisce funzionalita' di elaborazione analitica online (OLAP) e data mining, consentendo agli utenti di analizzare grandi volumi di dati e ottenere informazioni.
- Integration Services (SSIS): SSIS e' una piattaforma per la creazione di soluzioni di integrazione e trasformazione dei dati ad alte prestazioni. Consente di estrarre, trasformare e caricare (ETL) i dati da varie fonti.
- Reporting Services (SSRS): SSRS fornisce strumenti per la creazione e la distribuzione di report interattivi e stampabili.
- Machine Learning Services: Questa funzionalita' integra le capacita' di machine learning direttamente in SQL Server, consentendo di eseguire script Python e R all'interno del database.
L'architettura e' costituita da due componenti principali:
- Livello di Protocollo: Questo livello gestisce la comunicazione tra i client e il motore di SQL Server. Supporta vari protocolli, come TCP/IP, Named Pipes e Shared Memory.
- Motore Relazionale (Relational Engine): Questo livello e' responsabile dell'elaborazione e dell'esecuzione delle query SQL. e' composto da diversi sottocomponenti:
- Analizzatore di Comandi (Command Parser): Analizza e convalida le query SQL.
- Ottimizzatore di Query (Query Optimizer): Determina il piano di esecuzione piu' efficiente per una query.
- Motore di Esecuzione (Execution Engine): Esegue il piano di esecuzione della query e recupera o modifica i dati.
- Motore di Archiviazione (Storage Engine): Gestisce l'archiviazione e il recupero dei dati dal disco.
SQL Server archivia i dati in file di dati, organizzati in pagine.
Le pagine sono l'unita' fondamentale di archiviazione e contengono righe di dati.
SQL Server utilizza varie tecniche di indicizzazione per ottimizzare il recupero dei dati,
per default le primary key vengono implementate come indici cluster.
Il log delle transazioni e' un componente critico di SQL Server,
responsabile della registrazione di tutte le modifiche al database.
Garantisce la durabilita' dei dati e consente il ripristino in caso di guasti del sistema.
Evoluzione di SQL Server
E' disponibile una paginetta con i piu' utili comandi per SQL Server.
SQL Server ha origini lontane e le prime versioni sono state influenzate da Sybase.
Microsoft ha fatto evolvere il database negli anni seguendo le richieste del mercato
ed implementando le piu' recenti tecnologie.
La versione piu' recente disponibile come
in produzione e' SQL Server 2022 che introduce l'integrazione ad
Azure Synapse native e miglioramenti sul Query Store ed il Intelligent Query Processing.
A breve e' prevista la disponibilita' di SQL Server 2025
con nuove funzionalita' orientate all'AI (Artificial Intelligence).
L'elenco delle versioni di SQL Server e delle relative funzionalita'
e' riportato in questa pagina web.
Oltre che on-premises SQL Server e' disponibile in Cloud come servizio gestito
su tutti i principali Cloud Provider:
Microsoft Azure Database,
Amazon RDS,
Google Cloud SQL,
...
