Microsoft SQL Server

server SQL Descrizione dell'immagine Logo-sql2012.png. Descrizione dell'immagine Microsoft SQL Server 1 11 Network Enhancements.png. Informazione
Sviluppato da Microsoft
Prima versione 24 aprile 1989
Ultima versione 2019 (4 novembre 2019)
Scritto in C++ , C e C#
Sistema operativo Linux , Microsoft Windows e Windows Server
Leggi formati SQL Server Reporting Services Report Definition Language ( d ) e SQL Server Reporting Services Report Definition Language (UTF-8) ( d )
Formati scritti SQL Server Reporting Services Report Definition Language ( d ) e SQL Server Reporting Services Report Definition Language (UTF-8) ( d )
genere Sistema di gestione di database relazionali ( in )
Licenza Licenza proprietaria e EULA
Sito web www.microsoft.com/sql-server

Microsoft SQL Server è un sistema di gestione di database (DBMS) in linguaggio SQL cheincorpora, tra l'altro, un RDBMS (DBMS relazionale  ) sviluppato e commercializzato dalla società Microsoft . Funziona sotto Windows e Linux OS (damarzo 2016), ma è possibile avviarlo su Mac OS tramite Docker , perché esiste una versione scaricabile sul sito Microsoft.

Storico

Sebbene sia stato originariamente sviluppato da Sybase e Microsoft, Ashton-Tate è stato anche associato alla sua prima versione, rilasciata nel 1989. Questa versione è stata rilasciata su piattaforme Unix e OS/2 . Da allora Microsoft ha portato questo sistema di database su Windows .

Quando è stato creato, Sybase SQL Server ha ereditato i principi del motore Ingres originariamente sviluppato dall'Università di Berkeley.

Nel 1994, interrotta la partnership tra le due società, Microsoft ha rilasciato la versione 6.0 e poi la 6.5 da sola, sulla piattaforma Windows NT.

Microsoft SQL Server fa ora parte della strategia di database tecnico di Microsoft. Il motore MSDE, che è alla base di SQL Server, dovrebbe eventualmente sostituire il motore Jet (quello che gestisce i database di Access ) in applicazioni come Exchange e Active Directory .

La versione 2005 di SQL Server è stata rilasciata il3 novembre 2005contemporaneamente a Visual Studio 2005 . Il supporto per Windows Vista e Windows Server 2008 non è stato aggiunto fino al Service Pack 2 (SP2). Attualmente è disponibile il Service Pack 3. L'ottimizzatore è stato completamente rifatto, così come il motore delle relazioni.

La versione 2008 di SQL Server (nome in codice Katmaï) è stata rilasciata nell'agosto 2008 . La versione minore 2008 R2 è stata rilasciata nel 2010.

La versione 2012 di SQL Server è stata rilasciata nell'aprile 2012 .

La versione 2014 di SQL Server è stata rilasciata il 1 ° aprile 2014 con un motore “in memory” complementare al motore relazionale.

La versione di SQL Server 2016 è stata rilasciata il 1 ° giugno 2016.

Durante lo sviluppo della versione 2005, il progetto era all'epoca uno dei più grandi progetti IT al mondo. Più di 1.600 sviluppatori hanno partecipato alla codifica del kernel e si stima che più di 10.000 altre persone ci abbiano lavorato direttamente o indirettamente ( interazioni uomo-macchina , documentazione, traduzione, ecc.).

Particolarità

SQL Server si distingue dalla concorrenza per la sua grande ricchezza che non richiede alcuna opzione aggiuntiva a pagamento entro il limite della versione scelta.

Multi-base, multi-schema

Rispetto ai suoi concorrenti Oracle , MySQL o PostgreSQL , SQL Server si distingue per il fatto di essere un RDBMS (Relational Database Management System) originariamente multibase e multi schema . È possibile effettuare query interbase native. Ad esempio, la seguente query collega due tabelle di due database diversi:

SELECT * FROM   BASE_A.dbo.TABLE1 AS T1        INNER JOIN BASE_B.dbo.TABLE2 AS T2              ON T1.ID = T2.ID;

L'ottimizzatore è in grado di creare un piano di query perfettamente ottimizzato anche se la query consulta i dati di diversi database ...

Sebbene PostGreSQL sia multibase e multischema, questa possibilità di interrogazione simultanea non è nativa ed è necessario passare per l'intermediario di "dblink" che vieta i join e quindi qualsiasi possibilità di ottimizzazione ... Oracle con la sua versione 12 cerca di integrare questo stesso concetto di multibase (chiamato multi-tenant ) ma soffre dello stesso problema di PostGreSQL. MySQL è un singolo schema, multibase.

Gestione dello schema SQL

La flessibilità della gestione dello schema SQL è tale che è possibile trasferire un oggetto da uno schema all'altro con un solo comando ALTER SCHEMA.

I proprietari sono distinti dagli schemi ed è possibile trasferire la proprietà di un database, di uno schema o di un oggetto da un utente SQL ad un altro, tramite il comando ALTER AUTHORIZATION.

Parallelismo

SQL Server funziona nativamente in parallelo. Non appena si stima che una query superi la soglia di costo alla quale un piano di query può essere parallelizzato, SQL Server riscrive il piano utilizzando algoritmi multi-thread e se il nuovo piano risulta essere meno costoso, la sostituzione avviene automaticamente. Questa funzione esiste in tutte le edizioni e non è un modulo a pagamento da aggiungere in aggiunta come nel caso di Oracle.

Anche le operazioni di lettura e scrittura fisiche beneficiano del parallelismo sistematico poiché le operazioni di I/O vengono eseguite direttamente da SQL Server e non attraverso il livello di sistema come nel caso di PostGreSQL o MySQL.

Compressione

Nella versione Enterprise, il motore è in grado di comprimere i dati a livello di riga o pagina nelle tabelle e negli indici. La compressione del backup è disponibile dall'edizione standard dalla versione 2008 R2.

Backup a caldo

Da SQL Server 7 (1998), SQL Server ha reso possibile eseguire il backup di "database aperti" senza interrompere il servizio dati e senza disturbare l'attività dell'utente. Questo backup può essere eseguito globalmente, per filegroup, per file o tramite il registro delle transazioni.

Unico ... tranne NULL!

In SQL Server, i vincoli di unicità tengono conto del contrassegno NULL come valore e quindi vietano la presenza di più NULL nelle colonne di tale vincolo. Questo non è conforme allo standard SQL poiché NULL essendo un'assenza di valore, l'unicità non si pone. Tuttavia, esiste un modo semplice per aggirare questo problema, ovvero creare un indice filtrato univoco correlato ai dati valutati.

Esempio, la tabella:

CREATE TABLE T_EMPLOYE_EMP (EMP_ID            INT PRIMARY KEY,  EMP_NOM           VARCHAR(32) NOT NULL,  EMP_MATRICULE     CHAR(8));

Poiché il numero del personale deve essere univoco per i valori noti, la creazione di un indice univoco filtrato sui valori espressi nella colonna del numero del personale, permette di risolvere il problema:

CREATE UNIQUE INDEX X_EMP_MATRICULE    ON T_EMPLOYE_EMP(EMP_MATRICULE)    WHERE EMP_MATRICULE IS NOT NULL;

Reinserimento dei trigger (tabella "mutante")

Nei trigger di SQL Server è possibile aggiornare la tabella di destinazione dell'evento all'origine dell'esecuzione del trigger, a differenza di molti database che vietano questa possibilità (errore "mutant table" in Oracle). Va ricordato che è stata Sybase ad inventare originariamente il concetto di trigger nel 1986, inserendo così per la prima volta codice eseguibile in un database relazionale.

Ad esempio, un trigger UPDATEsu una tabella può modificare i dati in attesa di modifica:

CREATE TRIGGER E_U_PERSONNE ON T_PERSONNE_PRS FOR UPDATE AS IF UPDATE(PRS_NOM) UPDATE T_PERSONNE_PRS SET PRS_NOM = UPPER(PRS_NOM) WHERE PRS_ID IN (SELECT PRS_ID FROM inserted);

Nell'esempio sopra, i nomi dei nomi appena inseriti o modificati sono in maiuscolo.

Poiché il rientro può portare alla ricorsione, diverse impostazioni consentono di risolvere questo problema a livello di database, nonché a livello di istanza.

Viste indicizzate automatizzate

A differenza di Oracle o PostGreSQL, in cui le viste materializzate devono essere aggiornate per la maggior parte del tempo prima dell'uso, le viste indicizzate di SQL Server sono sempre sincrone. Inoltre, l'ottimizzatore è in grado di sostituire al volo la vista indicizzata per tutta o parte della query (versione Enterprise) cosa molto pratica quando il DBA non ha accesso al codice dei programmi client (caso di applicazioni di editor) .

Raggruppamento automatico

Per le applicazioni progettate in .net e che utilizzano il connettore ADO, SQL Server esegue il pool automatico. Il semplice fatto di distinguere due catene di connessione è sufficiente per creare due pool differenti. Affinché il pool sia efficace, le connessioni devono essere chiuse una volta che l'elaborazione del computer è avvenuta.

Amministrazione centralizzata

Attraverso vari mezzi, SQL Server consente di gestire centralmente un insieme di server. È possibile creare un server di amministrazione centralizzato, e aggiungere un elenco dei server SQL dell'azienda per lanciare query inviate contemporaneamente su tutti i server oppure creare o eseguire la stessa procedura su tutti i server contemporaneamente, questo nello strumento SSMS (Studio di gestione di SQL Server).

Allo stesso modo, l'utilità di pianificazione e gestione avvisi (SQL Agent) consente di definire processi multi-server, come il backup, la deframmentazione degli indici, il ricalcolo delle statistiche o il controllo dell'integrità fisica degli spazi.

Funzionalità per lo sviluppo

Il motore OLTP di SQL Server ha molte funzionalità che sarebbe difficile elencarle tutte. Eccone alcuni che fanno la differenza con DBMS più leggeri come MySQL o PostGreSQL...

Tabella, indice e procedura "In Memory"

SQL Server è dotato di un motore "In Memory" dalla versione 2014. Tabelle e indici possono essere totalmente in memoria (struttura e dati: dati e schema) o persistenti a livello di struttura (solo dati). Le procedure, entro limiti ben definiti, possono essere compilate in modo nativo, piuttosto che interpretate. Gli aggiornamenti rimangono transazionali, senza che sia necessario scrivere sui file di log delle transazioni (versioning "in memory"), questo consente miglioramenti molto significativi delle prestazioni per l'elaborazione di calcoli complessi relativi a grandi volumi di dati. Un tipico esempio è il calcolo in tempo reale dell'andamento finanziario delle quotazioni di borsa.

Intra join (APPLICA)

SQL Server è stato il primo con la versione 2005 a introdurre l'operatore intra-join APPLYper facilitare il recupero dei dati incapsulati in una tabella virtuale da una colonna di tabella, come nel caso dell'utilizzo di una funzione di tabella in una query in cui uno degli argomenti proviene da una delle tabelle nella query o quando viene tabulato XML. L'operatore APPLYdeve essere preceduto dalla parola chiave CROSSo OUTERper definirne il comportamento. Con CROSSse l'argomento è NULL, le righe interessate vengono rimosse dal risultato mentre con OUTEResse vengono conservate.

L'operatore APPLYè simile all'operatore LATERALdello standard SQL, ma la sua sintassi più estesa consente operazioni che LATERALnon possono essere eseguite. Oracle ha recentemente introdotto questo operatore nella versione 12.1 (2014).

SELECT top 10 * FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY total_worker_time DESC

In questo esempio, usiamo la funzione di tabella sys.dm_exec_query_plan, che restituisce il piano di esecuzione, dal piano "handle" fornito dall'argomento "plan_handle" trovato nella vista sys.dm_exec_query_stats. Questa query consente di scoprire le 10 query che hanno consumato la maggior parte del tempo della CPU dall'avvio dell'istanza.

Spuntini (raccolta letterale)

SQL Server dispone di una delle raccolte di collation più ricche (3.887 nella versione 2014), che consente di controllare 101 lingue tra le quali, oltre al francese, ci sono il bretone e il corso! Le collazioni consentono di effettuare ricerche tenendo conto o meno del caso (maiuscolo, minuscolo - CS/CI), accenti e altri caratteri diacritici (accenti, cediglia, legature... - AS/AI), i caratteri di larghezza (2 = 2  ? - WS) o diversi tipi di kana (katakana e hiragana dal giapponese - KS), o anche per eseguire confronti binari tenendo conto o meno del codice esadecimale. Le regole di confronto sono completamente indipendenti dai set di caratteri (set Iso_1) sebbene permettano di simularli (ASCII, UNICODE, UTF16, EBCDIC ...). Il supporto delle regole di confronto è fornito a quattro livelli: server (1), base (2), colonna della tabella o della vista (3) e infine predicato (4) che gestisce le stringhe di caratteri e ordina le colonne di testo nelle query.

SELECT * FROM T_UTILISATEUR_APPLICATIF_UTA WHERE UTA_CONNEXION = 'Éric Blüm' COLLATE French_CI_AI AND UTA_MOT_DE_PASE = 'a²E3wx+Z' COLLATE Latin1_General_CS_AS_WS

Nell'esempio seguente, cerchiamo un nome di connessione con regole di confronto "sciolte" (indipendentemente da maiuscole e minuscole o accenti) e la password con regole di confronto "strette" (binario):

L'elenco delle regole di confronto disponibili sul server può essere ottenuto dalla query:

SELECT * FROM sys.fn_helpcollations()

Dati XML

Dalla versione 2005, SQL Server supporta il tipo XML (frammento) e consente di manipolare XML tramite query XQuery/XPath. Una colonna di tipo XML può essere digitata tramite una XML Schema Collection (XSD - Xml Schema Definition). L'esempio seguente mostra la creazione di una raccolta di schemi XML a elemento singolo, quindi la creazione di una tabella con una colonna XML tipizzata con quella raccolta di schemi:

-- création d'un schéma XML pour validation des données d'une colonne XML CREATE XML SCHEMA COLLECTION XSC_DONNEES_ENTREPRISE as N' <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:company="http://www.exemple.com/entreprise" targetNamespace="http://www.exemple.com/entreprise" elementFormDefault="qualified"> <xs:element name="personnel"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="employe"> <xs:complexType> <xs:all> <xs:element name="nom" type="xs:string" /> </xs:all> <xs:attribute name="matricule" type="xs:ID" /> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>'; -- création d'une table qui possède une colonne de type XML dont les données doivent se conformer au schéma ci avant CREATE TABLE T_ENTREPRISE_EMPLOIS_EEP (EEP_ID INTEGER PRIMARY KEY IDENTITY, EEP_NAME VARCHAR(12), EEP_XML_DATA XML (XSC_DONNEES_ENTREPRISE) --> création d'une colonne XML typée par XSC_DONNEES_ENTREPRISE (collection de schémas XML) );

Una query SELECT di estrazione può generare un output XML invece di un risultato tabulare, tramite la clausola "FOR XML" di Transact-SQL per la quale sono disponibili molte opzioni di presentazione. L'esempio seguente viene utilizzato per presentare una gerarchia XML che elenca i vincoli di una tabella sotto forma di elementi XML:

SELECT TABLE_SCHEMA AS schemaSQL, TABLE_NAME AS nomTable, CONSTRAINT_NAME AS nomContrainte, CONSTRAINT_TYPE AS typeContrainte FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS contrainte WHERE TABLE_NAME = 'DepartementFrancais' FOR XML AUTO, ELEMENTS, ROOT('Contraintes');

Questa query restituirà, ad esempio, i seguenti dati:

<Contraintes> <contrainte> <schemaSQL>Reference</schemaSQL> <nomTable>DepartementFrancais</nomTable> <nomContrainte>pk_DepartementFrancais</nomContrainte> <typeContrainte>PRIMARY KEY</typeContrainte> </contrainte> <contrainte> <schemaSQL>Reference</schemaSQL> <nomTable>DepartementFrancais</nomTable> <nomContrainte>chk_DepartementFrancais_ForceSTSRid</nomContrainte> <typeContrainte>CHECK</typeContrainte> </contrainte> <contrainte> <schemaSQL>Reference</schemaSQL> <nomTable>DepartementFrancais</nomTable> <nomContrainte>UK_DepartementFrancais_NomChefLieu</nomContrainte> <typeContrainte>UNIQUE</typeContrainte> </contrainte> <contrainte> <schemaSQL>Reference</schemaSQL> <nomTable>DepartementFrancais</nomTable> <nomContrainte>UK_DepartementFrancais_Nom</nomContrainte> <typeContrainte>UNIQUE</typeContrainte> </contrainte> </Contraintes>

Infine, è possibile gestire direttamente i dati XML nelle routine (ad esempio provenienti da file), utilizzando le funzioni e le procedure OPENXML, sp_xml_preparedocument, sp_xml_removedocument...

Sistema Informativo Territoriale Integrato (GIS)

Dalla versione 2008, SQL Server integra un GIS in tutte le versioni. Questo permette la costruzione di oggetti geometrici (geometria euclidea) o geografici (tenendo conto della curvatura spaziale della crosta terrestre) utilizzando i tipi GEOMETRIA e GEOGRAFIA (POINT, LINESTRING, POLIGON e combinazioni...). Si basa sullo standard OGC , proprio come l'add-on spaziale PostGIS per PostgreSQL . la versione 2012 ha aggiunto oggetti curvi (CircularStrings, CompoundCurves, CurvePolygons...) e una migliore precisione dei calcoli (48 bit invece dei 27 precedenti).

Gli indici spaziali consentono di creare determinati metodi come STContains, STDistance, STEquals, STIntersects, STOverlaps, STTouches, STWithin...

In conformità con lo standard SQL che offre il DATALINK, SQL Server implementa uno strumento simile chiamato FileStream che consente di archiviare i file come file nel sistema operativo, ma sotto la responsabilità e il controllo del server del database (serializzazione, transazione, affidabilità, sicurezza . ..). Ciò consente, tra l'altro, un backup integrato e sincrono di dati relazionali e non relazionali, ad esempio per immagini di prodotto su un sito web, come manuali elettronici PDF per macchine in una flotta per manutenzione. Questo strumento è particolarmente utilizzato per l'EDM. File elettronici che possono essere soggetti a indicizzazione testuale.

Inoltre e dalla versione 2012, SQL Server offre il concetto di FileTable, che altro non è che una tabella virtuale associata ad un punto di ingresso (directory) del sistema informatico, al fine di consentire la gestione bidirezionale dei file elettronici: i file gestiti da il linguaggio SQL è accessibile nel file system di Windows e viceversa: un file o una directory può essere inserita nella tabella tramite una query SQL e quest'ultima verrà visualizzata nell'albero del sistema.

Lo strumento di gestione della conoscenza di Sharepoint utilizza massicciamente questo concetto per gestire i documenti elettronici che vi sono depositati e tutte le versioni transitorie.

Servizi web

Introdotta con la versione 2008, la possibilità di creare servizi web nativi direttamente nel server utilizzando endpoint SOAP/HTTP, è stata considerata deprecata nella versione 2008 R2 e ritirata nella versione 2012. Tuttavia, SQL Server consente di creare, e utilizza internamente, molti endpoint per servizi particolari come il mirroring del database o Service broker...

Servizio di brokeraggio

L'obiettivo di Service Broker è fornire uno strumento di messaggistica del database per la gestione dei flussi di dati tra i server SQL in modo asincrono, serializzati e transati tramite code che sono tabelle di sistema. Ciò consente di creare SODA (Service Oriented Database Architecture), ovvero database di istanze diverse che dialogano tra loro a livello dati e non più a livello applicativo come nel caso della SOA.

Ciò rende possibile creare database distribuiti, collaborativi e distribuiti, "grid computing", o anche WorkFlow, e ad esempio fornire una migliore superficie di attacco per accessi molto competitivi. Questo sistema si differenzia dalla replica dei dati, per il fatto che le informazioni sono veicolate principalmente in forma XML (criptate), sul livello HTTP (Internet ad esempio) tramite servizi Web e che non vi è alcun obbligo per i database di origine e destinazione e tabelle siano strutturalmente identiche.

Un esempio interessante è sostituire i tradizionali strumenti di messaggistica come MQ Series, con un dispositivo del genere, molto più affidabile...

Service Broker può essere utilizzato tramite l'edizione gratuita SQL Server Express, è stato utilizzato in particolare nel settore per gestire le informazioni di produzione di molte macchine della stessa flotta al fine di inviare questi dati a un server centrale. È il caso, in particolare, della filiera di produzione dei vaccini di un famoso istituto francese.

Funzionalità per l'amministratore

Oltre alle funzionalità per lo sviluppatore, SQL Server presenta molti strumenti per facilitare l'amministrazione. Eccone alcuni.

Replica dei dati

Da non confondere con l'"alta disponibilità" che consiste nella duplicazione di un intero database, SQL Server dispone di 8 modalità di replica dei dati (replica di determinate informazioni da determinate tabelle provenienti da database ad altri database su altri server) incluse transazionali, point-to-point modalità , snapshot o unione. La replica da e verso Oracle è supportata anche nell'edizione Enterprise.

I siti di vendita online come Cdiscount , Fnac.com o Vente-privee.com utilizzano la replica, sia per dissociare i dati dal FO (Front Office - essenzialmente dati del sito web) che dal BO (Back Office - gestione), fatture, spedizioni, stock. ..), ma anche per allargare la superficie di attacco in modo da assorbire il carico dei tanti utenti.

Alta disponibilità

La duplicazione dei database per stabilire un sistema altamente resiliente viene realizzata attraverso quattro diversi mezzi:

  • Il "  Log Shipping  " inviando il backup del registro delle transazioni e riapplicando le transazioni su basi di emergenza. Sistema di ribaltamento manuale asincrono. Perdite limitate alla latenza di invio dei log. Funziona base per base.
  • Il "  mirroring  " inviando a impulsi le pagine modificate del registro delle transazioni in modalità standby. Sistema trasparente per applicazioni (.net o ODBC) in modalità sincrona o asincrona con commutazione manuale o automatica (se sincrona) base per base. Latenza di failover zero se sincrono e in modalità automatica. È tollerato un solo mirror per database e per il failover automatico è necessario un server testimone (ad esempio la versione Express gratuita) per evitare la sindrome del “cervello diviso”.
  • Il "  clustering  " per ridondanza dei server (fino a 64 nodi attivi o passivi) che condividono un alloggiamento per unità ( Share Nothing ), a cui è possibile aggiungere il sistema di replica IO dell'alloggiamento per evitare SPOF ( Single Point Of Failure ). Consente di commutare l'intera istanza manualmente o automaticamente in base alle regole da implementare. Dati sempre sincroni. Tempo di cambio formato a seconda dello scenario stabilito, ma generalmente da poche decine di secondi a qualche minuto.
  • Tecnologia "  AlwaysOn  ": combinazione di mirroring e Failover Cluster (FCI per Failover Cluster Instance ) che consente di associare diverse basi all'interno di gruppi di disponibilità che garantisce il failover collettivo di tutte le basi in caso di malfunzionamento di una di esse all'interno di un gruppo. Per quanto riguarda il mirroring, il sistema è trasparente per le applicazioni (.net o ODBC) in modalità sincrona o asincrona con commutazione manuale o automatica (se sincrona) gruppo per gruppo. La latenza di failover è zero se viene adottata la modalità sincrona. Il numero di "repliche" dello stesso gruppo di disponibilità è limitato a 10: 8 asincrone e 2 sincrone. Un unico gruppo di disponibilità che può contenere più di cento database.

Gestione della strategia (politiche amministrative - gestione delle politiche)

È possibile creare regole aziendali e pianificarne il funzionamento. Si tratta di verificare un attributo di una sfaccettatura, tramite una regola che può essere verificata in diversi modi. Qualche esempio:

  • controlla una volta al giorno che sia stato eseguito il backup di ogni database
  • forzare una regola di denominazione per tabelle, viste, procedure
  • proibire l'uso sistematico dello schema SQL predefinito dbo

Istantanea del database

Questo processo consente di creare un nuovo database da un database esistente che rappresenta i dati nel momento in cui è stato lanciato lo "snapshot". Questa copia di sola lettura viene eseguita istantaneamente indipendentemente dal volume del database iniziale. Ciò consente ad esempio:

  • ottenere con certezza tutti i dati dal database in un momento specifico
  • essere in grado di confrontare in fase di sviluppo i dati iniziali e finali dopo l'esecuzione di una procedura
  • tornare indietro tramite richiesta di modifica tra database o ripristinando il database dallo snapshot

partizionamento

Presente dalla versione 2005 nell'edizione Enterprise, il partizionamento consente di suddividere tabelle e indici secondo un criterio di dati della tabella. la soluzione riutilizzabile è molto semplice e si basa su tre fasi:

  • La creazione dei valori cardine del partizionamento tramite una funzione di partizionamento (CREATE PARTITION FUNCTION...);
  • La creazione di uno schema di ventilazione divisoria nei diversi spazi di stoccaggio (CREATE PARTITION SCHEME ...);
  • La creazione della tabella o dell'indice sfruttando lo schema di partizionamento utilizzando un criterio che deve essere una colonna non NULL presente nella definizione dell'oggetto.

Una volta completati questi passaggi, la funzione e lo schema di partizionamento possono essere riutilizzati per un altro oggetto ai fini dell'allineamento delle partizioni, ad esempio una tabella delle fatture e i relativi dettagli subordinati o una tabella delle righe.

E' quindi possibile gestire le partizioni (sposta, unisci, divide...) a caldo utilizzando i comandi ALTER PARTITION FUNCTION ... SPLIT RANGE / MERGE RANGE e ALTER TABLE ... SWITCH PARTITION ... che rendono la mano immediatamente e agire a basso livello, in sottofondo, per azionare i movimenti.

Piano di manutenzione

Microsoft SQL Server offre molte procedure guidate per eseguire attività spesso ingrate come l'importazione/esportazione di dati o il backup/ripristino. Ma se è utile per i principianti, è lo strumento di gestione del piano di manutenzione che consente in pochi clic di eseguire tutte le attività essenziali per la sopravvivenza e il mantenimento delle prestazioni di una base. Questo strumento consente, tra l'altro, di svolgere le seguenti attività su base ricorrente:

  • backup;
  • controllo dell'integrità fisica;
  • deframmentazione degli indici;
  • ricalcoli delle statistiche.

... e pianificarne l'esecuzione.

Il governatore delle risorse

Poiché la convivenza su un piano di parità di tutti gli utenti della stessa base può porre rapidamente problemi, SQL Server integra un governo di risorse che consente di allocare più o meno risorse (RAM, CPU, dischi ... ) a un particolare gruppo di utenti. Il caso classico è la commistione di utenti appassionati di cruscotti e report di ogni genere (ad esempio controller di gestione) che consumano molti dati, penalizzando la produzione di dati da parte degli utenti che effettuano l'inserimento di unità...

Gestione della qualità dei dati

SQL Server integra due moduli distinti per gestire i dati di qualità nei database:

  • Data Quality Services (DQS): consente di gestire la qualità dei dati in database relazionali e decisionali in modo interattivo e attraverso una knowledge base: pulizia, correzione, arricchimento, standardizzazione, deduplicazione, ecc.
  • Master Data Services (MDS): è la soluzione di gestione dei dati master di SQL Server.

Servizio di invio email integrato

Database Mail è lo strumento di gestione della posta in uscita di SQL Server ed è basato su Service Broker. Questo sistema permette di definire più profili di invio della posta (ad esempio uno per le applicazioni e l'altro per l'amministrazione) con eventuale ridondanza dei server SMTP. L'invio di e-mail viene utilizzato in particolare da SQL Agent per notificare a volte il successo, ma spesso gli errori, delle attività pianificate (ad esempio, che non è stato possibile eseguire un backup). La procedura msdb.dbo.sp_send_dbmail viene utilizzata per inviare una mail, anche con allegati, risultato query, contenuto in formato testo o arricchito...

Strumenti di controllo

SQL Server è ricco di strumenti di controllo, sia per la sicurezza, l'evoluzione dei dati o le prestazioni. Ne descriveremo alcuni.

Audit di sicurezza (Audit del database)

Oltre ad essere obsoleto l'audit C2 per essere sostituito dai criteri comuni (ISO 15408 ), SQL Server offre la possibilità di tracciare tutto ciò che riguarda la sicurezza attraverso il database auditing, e tutte le azioni possibili sia a livello di server che a livello di particolare banca dati.

Questo sistema è perfezionato al punto che è possibile arrestare automaticamente il server in caso di guasto del sistema di audit, in modo che gli intrusi non approfittino di questo stato di malfunzionamento della tracciabilità per effettuare manovre illecite!

Evoluzione dei dati

SQL Server dispone di due modalità che consentono di tracciare automaticamente l'evoluzione dei dati (INSERT, UPDATE, DELETE...):

  • CHANGE TRACKING: disponibile dall'edizione Standard, è possibile indicare quali righe di quali tabelle sono cambiate entro un periodo di tempo target e configurabile.
  • CHANGE DATA CAPTURE (CDC): disponibile nell'edizione Enterprise, copia le informazioni di aggiornamento in tabelle di lavoro con l'indicazione della data e dell'ora delle modifiche apportate.

CHANGE TRACKING forza la scansione delle tabelle nel database, ma riduce il volume complessivo, mentre CDC non interroga i dati di produzione per alleviare il blocco.

Questi due moduli sono generalmente utilizzati, l'uno o l'altro, per alimentare in modo differenziale grandi data warehouse .

spettacoli

La quantità dello strumento è così abbondante che molti utenti li ignorano, guardando altrove in strumenti di terze parti - spesso a pagamento - per ciò che già hanno e gratuitamente a portata di mano.

Vista gestione dati (DMV)

Si tratta di dati di sistema che presentano statistiche di esecuzione del motore SQL che possono essere utilizzate in forma tabulare da pseudo viste (infatti la maggior parte delle volte le funzioni interne restituiscono uno stato tabulare dei dati raccolti in memoria). Ciò consente di stabilire diverse diagnosi come:

  • tempi di attesa: sys.dm_os_wait_stats
  • l'attività corrente nel server: sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests;
  • l'uso e la frammentazione degli indici: sys.dm_db_index_usage_stats, sys.dm_db_index_operational_stats, sys.dm_db_index_physical_stats;
  • gli indici mancanti stimati: sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats;
  • l'elaborazione più costosa: sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, sys.dm_exec_trigger_stats;
  • le transazioni (e il loro corollario le serrature): sys.dm_os_tran_ * (più di 10 DMV) ...
  • gestione della cache: sys.dm_os_memory_ * (più di 10 DMV) ...
  • gestione file: sys.dm_db_file_space_usage, sys.dm_db_log_space_usage, sys.dm_io_virtual_file_stats, sys.dm_os_volume_stats ...
  • i thread: sys.dm_os_threads, sys.dm_os_tasks, sys.dm_os_workers, sys.dm_os_dispatcher_pools, sys.dm_os_nodes ...
Rapporti

Sulla base del DMV, i report sfruttano e presentano i dati in modo sintetico. Sono disponibili a diversi livelli della struttura Esplora oggetti di SQL Server Management Studio, principalmente a livello di server (istanza) e di base.

Contatori di prestazioni

SQL Server pubblica molti contatori delle prestazioni che possono essere interrogati direttamente in SQL o che possono essere analizzati utilizzando il monitor delle prestazioni (perfmon.exe). Questi contatori sono leggibili nella vista sys.dm_os_performance_counters.

Avvisi

SQL Server consente di impostare avvisi su errori o metriche che superano una soglia tramite SQL Agent eseguendo una query sui dati del contatore delle prestazioni o tramite WMI (Windows Management Instrumentation). Questo tipo di alert permette ad esempio di essere informato via email della saturazione dei log delle transazioni o del riempimento dei dischi del server.

Consulente per l'ottimizzazione del motore di database

Questo strumento viene utilizzato per eseguire una diagnostica di indicizzazione, statistica, partizionamento o creazione di viste indicizzate quando viene fornito con un batch di query.

Profilazione SQL

Il profiler SQL consente di annusare le query prima e dopo l'esecuzione per fornire metriche a scopo di analisi principalmente per migliorare le prestazioni (sebbene esista una modalità "deprecata" per visualizzare il codice obsoleto da alcune applicazioni).

Questi dati vengono salvati in una tabella o in un file (trasferibili in una tabella) e devono poi essere analizzati. Diversi modelli consentono livelli maggiori o minori di finezza della scansione.

Il modello "replay" consente di acquisire un carico e le sue metriche che possono essere riprodotti nelle stesse condizioni (simulazione di utenti diversi in parallelo) a fini di benchmark, in particolare per modifiche nelle versioni di SQL Server, come nell'hardware. Per riprodurre e confrontare, è necessario utilizzare il pacchetto RML disponibile gratuitamente sul sito Microsoft.

Eventi estesi (XE: eventi estesi)

Come complemento e futura sostituzione del profiler, XE consente un'intrusione più profonda rispetto al profiler poiché può scansionare quasi tutte le metriche delle operazioni in memoria, nei thread o negli IO del disco (livello di sistema). Questo è in aggiunta a ciò che sta già facendo il profiler.

Raccoglitore di dati

Poiché i dati delle motorizzazioni sono in linea di massima volatili (esistendo solo in memoria, vengono persi ad ogni riavvio) o limitati (in un anello di memoria) si è deciso di creare questo strumento che registra ciclicamente dati diversi provenienti da fonti diverse (per lo più da DMV) a scopo di feedback attraverso specifici report. Per fare ciò, è necessario creare un database di raccolta e avviare i vari raccoglitori predefiniti o creare i propri raccoglitori.

Replay distribuito

Questo dispositivo permette di riprodurre l'attività di un server catturata da una traccia dell'SQL Profiler, simulando un'attività vicina alla realtà, mediante una distribuzione di richieste su diverse postazioni, il tutto orchestrato da un controller. L'obiettivo è ad esempio confrontare la conformità di esecuzione di un'applicazione su una nuova versione di SQL Server o durante un cambio di macchina (miglioramento delle prestazioni).

operazione

Microsoft SQL Server offre diverse funzioni:

Le lingue

Per le query, SQL Server utilizza il linguaggio SQL in uno dei dialetti più conformi a SQL, Microsoft ha reso un punto d'onore correggere di versione in versione alcuni errori passati ereditati dal motore Sybase. Il dialetto utilizzato è il T-SQL (Transact-SQL), un'implementazione SQL che supporta stored procedure, funzioni definite dall'utente o UDF (funzione definita dall'utente) e trigger ( trigger ). I linguaggi XQuery e XPath vengono utilizzati a diversi livelli per manipolare i dati XML all'interno delle query SQL.

Per i trasferimenti di dati, SQL Server utilizza il formato TDS (Tabular Data Stream) che è stato implementato in altri database (soprattutto nella sua controparte Sybase ) e le cui specifiche sono pubbliche. È disponibile un'implementazione open source di un client TDS che costituisce la base del client SQL Server per il progetto Mono  : FreeTDS .

Essendo SQL Server dotato di due motori di database, uno relazionale e l'altro decisionale, è possibile effettuare query in linguaggio MDX o DMX specifiche per l'analisi dei dati per basi decisionali.

I linguaggi R e Python sono stati integrati dalla versione 2016. È inoltre possibile sviluppare routine SQL (UDF, procedure, trigger) utilizzando un linguaggio .net come VB per .net o C#.

Link di integrità

Nel motore relazionale di SQL Server è possibile definire collegamenti tra tabelle tramite vincoli dichiarativi in ​​modo da garantire fortemente l'integrità dei dati tra tabella di riferimento e tabella figlia. Questi collegamenti di integrità possono essere utilizzati per modificare o eliminare linee collegate in catena e supportare regole di evoluzione di tipo NO ACTION, CASCADE, SET DEFAULT e SET NULL.

Transazioni

SQL Server è un DBMS transazionale. È in grado di preparare modifiche ai dati di un database e di convalidarli o cancellarli atomicamente, cioè in "tutto o niente". Ciò garantisce la coerenza e l'integrità delle informazioni memorizzate nel database. Durante una transazione, i dataset contenenti le righe di dati modificati da questa transazione vengono bloccati. Gli altri utenti devono attendere la fine della transazione per poterli modificare nuovamente. D'altra parte, gli utenti concorrenti possono facoltativamente leggere i dati, anche se sono stati bloccati per la modifica da un'altra transazione, a seconda del livello di isolamento scelto.

Livello di isolamento e bloccaggio

I blocchi vengono eseguiti a livello di riga, pagina, estensione, tabella o database. SQL Server blocca solo le risorse di cui ha bisogno (righe per impostazione predefinita) e, se necessario, può bloccare a un livello superiore (partizione o tabella). Ciò elimina la necessità per gli utenti di attendere la fine di una transazione per aggiornare le righe di dati che non sono state interessate da una modifica e aiuta a ridurre la quantità di risorse consumate. Il blocco può essere pessimistico: gli utenti simultanei non saranno in grado di accedere alle righe in questa transazione o ottimistico, nel qual caso gli utenti simultanei potranno accedere all'ultima versione delle righe.

Per farlo funzionare, SQL Server consente di controllare il livello di isolamento in sei modi diversi. I 4 livelli previsti dallo standard SQL

  • LEGGI SENZA IMPEGNO
  • LEGGI IMPEGNATO
  • LEGGI RIPETIBILI
  • SERIALIZZABILE

... che si basa sul blocco pessimistico e su due livelli "ottimisti":

  • ISTANTANEA
  • LEGGI L'ISTANTANEA IMPEGNATA

Poiché il livello di isolamento è dinamico, può cambiare durante la stessa transazione. Il livello di isolamento READ COMMITTED SNAPSHOT funziona esattamente come Oracle o PostGreSQL sovrascrivendo il livello di isolamento READ COMMITTED con il versionamento delle righe per il blocco ottimistico.

Registrazione delle transazioni

SQL Server consente di eseguire transazioni in tre modi diversi:

  • validazione automatica: validazione immediata o cancellazione (in caso di errore) di ogni ordine SQL lanciato (di default);
  • modalità implicita: la connessione avvia una transazione e la finalizzazione (COMMIT o ROLLBACK manuale) termina la transazione per avviarne una nuova (SET IMPLICIT_TRANSACTIONS ON);
  • modalità esplicita: l'inizializzazione di una transazione deve essere eseguita dal comando BEGIN TRANSACTION.

A differenza di Oracle o PostGreSQL, SQL Server gestisce anche ordini DDL (CREATE, ALTER, DROP ...) e DCL (GRANT, REVOKE ...).

E' possibile definire dei checkpoint di transazione (comando SAVE TRANSACTION) in modo da consentire una cancellazione parziale (ROLLBACK...).

Le transazioni vengono registrate nel log delle transazioni ("REDO log"), le modifiche ai dati sono immediatamente disponibili in memoria e verranno scritte nei file di dati durante i check point avviati periodicamente e in modo asincrono. Tuttavia, è possibile forzare un checkpoint utilizzando l'istruzione CHECKPOINT.

Il registro delle transazioni registra i dati in modo diverso e lo fa in tre modi diversi:

  • Modalità completa: tutte le modifiche vengono registrate nel registro. Le transazioni completate con dati scritti su disco verranno eliminate dal registro delle transazioni durante i backup transazionali.
  • Modalità di registrazione di massa (BULK LOGGED): registra in modo minimo le operazioni riproducibili (ad esempio inserimento di dati di file, creazione di indici, ecc.). Questa modalità permette di velocizzare il funzionamento del giornale. Ma come in modalità completa, il registro dovrà essere eliminato da un backup transazionale.
  • Modalità semplice: modalità simile alla modalità journal in blocco, tranne per il fatto che il journal viene eliminato automaticamente a ogni checkpoint.

Nelle modalità di registro completo e di massa, il registro delle transazioni deve essere salvato periodicamente ("Registro RIPRISTINO"), altrimenti crescerà indefinitamente. E grazie al backup transazionale è possibile ripristinare il database così com'era in qualsiasi momento al secondo o alla transazione più vicina (con alcuni limiti per la modalità bulk journaling).

È possibile nidificare le transazioni, ma poiché il concetto di transazione è uno stato della transazione, non è possibile effettuare cancellazioni parziali in questo modo. Tuttavia, esistono altri modi per preservare alcuni dati dal rollback della transazione, anche tramite variabili di tabella.

SQL Server supporta le transazioni distribuite tramite Database Transaction Coordinator (DTC) che implementa il "commit a due fasi" secondo il modello XA.

File

I database sono contenuti fisicamente nei file. I file di solito hanno le estensioni:

  • MDF (Main Database File) per il primo file di dati, comprese alcune pagine tecniche vitali, inclusa la struttura di archiviazione del database
  • NDF (Next Database File) per altri file di dati
  • LDF (file di database di registro) per i file di registro delle transazioni

I file sono divisi in blocchi da 8196 byte chiamati pagine e organizzati in insiemi di 8 pagine consecutive chiamate estensioni. Fino alla versione 2000, le righe erano limitate a una dimensione massima di 8060 byte (le colonne di tipo LOB (image, text, ntext) non vengono conteggiate in questo limite). Dalla versione 2005 è possibile superare notevolmente questo limite e arrivare fino a 2 miliardi di byte. D'altra parte, la dimensione utile della pagina è 8096 byte .

I database possono funzionare solo quando tutti i file sono presenti, ma la lettura è possibile durante il ripristino, attraverso il concetto di ripristino frammentario.

I file di dati sono raggruppati logicamente nel database in gruppi di file che costituiscono la destinazione degli oggetti del database (tabella, indice, ecc.) Un gruppo di file è considerato l'unità di memorizzazione di qualsiasi tabella o indice e se questo contiene diversi file, quindi SQL Server distribuisce i dati in tutti i file (equivalente a un RAID 0), le operazioni di lettura e scrittura vengono eseguite in parallelo nei diversi file, sotto il controllo diretto del motore di archiviazione SQL Server. È possibile eseguire il backup di questi file e gruppi di file in modo indipendente anche se esiste un'interdipendenza logica tra gli oggetti di un file e dell'altro (integrità referenziale in particolare).

sicurezza

A causa del suo aspetto multibase, SQL Server dispone di due livelli di protezione: livello server, tramite account di accesso, e livello base, tramite utenti SQL.

A partire dalla versione 2005, sono state apportate modifiche significative alla sicurezza nei criteri di SQL Server.

Inoltre, la strategia di sicurezza copre anche la funzionalità del server. Ad esempio, la stored procedure xp_cmdshell per l'avvio dei comandi di sistema è disabilitata e solo il DBA può riattivarla. Allo stesso modo, non appena il server viene aperto, tutti i file di tutti i database vengono bloccati in lettura e scrittura per impedire accessi indesiderati.

Account di servizio

I servizi che eseguono ogni istanza di SQL Server utilizzano un account di servizio (l'account di sistema per l'esecuzione del servizio Windows). Quest'ultimo deve essere scelto con cura per evitare possibili violazioni della sicurezza sul server. Può essere: Servizio di sistema, Servizio locale (da Windows 2003), Servizio di rete (da Windows 2003), account utente Windows locale, account utente di dominio. Si consiglia vivamente di bloccare le autorizzazioni sugli account di servizio. In questo senso, e dalla versione 2012, vengono creati account specifici con autorizzazioni molto limitate durante l'installazione per ciascuno dei servizi di SQL Server (SQL Server, SQL Agent, SSAS, SSIS, SSRS, ecc.) .

Account di accesso

L'account di connessione viene utilizzato per connettersi a un'istanza di SQL Server. Per questi account, dovrebbero essere definiti i privilegi che si applicano a livello di server. Il privilegio minimo è il privilegio "CONNECT". Un esempio di privilegio a livello di server è la possibilità di creare qualsiasi database. In questo caso è il privilegio CREATE ANY DATABASE.

SQL Server si basa per impostazione predefinita sul sistema di autenticazione di Windows ( Kerberos o Native) che consente l'accesso a un'istanza di SQL Server tramite un gruppo Windows o un utente Windows. Quando si connette al database, l'utente viene identificato dal suo login di Windows. Non ha quindi bisogno di fornire la sua password durante la fase di autenticazione del server.

Poiché ci sono casi in cui l'utente non può essere identificato dal suo login di Windows (uso di GNU/Linux, di una pagina Web...) può essere implementato un metodo di identificazione diretta a SQL Server. Deve essere fatto esplicitamente dall'amministratore dell'istanza. Questo suppone la creazione di un nome account SQL e di una password, e per quest'ultima si può modellare la politica di conformazione della password su quella di Windows.

Quando si accede a una risorsa esterna, il processo SQL Server agisce in 3 modi diversi: per impersonificazione quando il livello Windows è configurato correttamente, l'utente può accedere solo alle risorse tramite SQL Server a cui avrebbe diritto se avesse accesso diretto; dall'account di servizio dell'istanza, quando l'utente è amministratore di sistema e per determinate attività; non consente l'accesso, in tutti gli altri casi. Nel caso di account di accesso SQL e per l'accesso a una risorsa esterna (ad esempio l'invio di un fax in una stored procedure) SQL Server aggiunge la possibilità di creare informazioni di accesso che sono account Windows registrati per questo scopo. Gli utenti SQL possono quindi essere associati a questi account Windows per l'accesso a risorse esterne.

Utente SQL

Un account di accesso può essere mappato a un utente SQL in un particolare database, in più o in tutti. Il nome dell'account di accesso e il nome utente potrebbero essere diversi (ed è meglio che lo sia). A un utente SQL in un database possono essere concessi privilegi che gli consentono di lanciare diversi comandi su vari oggetti. I privilegi possono essere stabiliti direttamente su un oggetto del database o su un "contenitore" come lo schema SQL o l'intero database.

Esempio di concessione di privilegi a livello di schema SQL diverso a un utente denominato USR_BANQUE:

GRANT SELECT, INSERT, UPDATE, EXECUTE ON SCHEMA::FINANCE TO USR_BANQUE; Ruoli

SQL Server ha ruoli prestabiliti sia a livello di server che a livello di base. È anche possibile creare i propri ruoli (novità a livello di server dal 2012). Un ruolo è un insieme di privilegi attribuibili a un account di connessione a livello di server o a un utente SQL quando questo ruolo è stato creato per un database specifico.

Schema SQL

Essendo uno schema un contenitore di oggetti in un database, SQL Server consente di definire quanti schemi SQL si desidera, al fine di organizzare i diversi oggetti nel database e soprattutto per gestire la sicurezza a livello di schema in modo proattivo. Quindi la creazione di nuovi oggetti, se questi oggetti vengono inseriti nei diagrammi corretti, automatizza l'implementazione della sicurezza per ereditarietà. Non c'è quindi nessun comando di alcun tipo per passare da un punto di vista della gestione dei privilegi, in modo che la sicurezza sia immediatamente applicata.

Infine, e a differenza di alcuni RDBMS (Oracle per esempio) non c'è privacy o confusione tra uno schema SQL e il suo proprietario (l'utente che lo ha creato) il che, ad esempio, facilita enormemente la migrazione dell' oggetto da uno schema all'altro e garantisce una maggiore sicurezza.

Esempio di migrazione di una tabella da uno schema SQL a un altro:

ALTER SCHEMA BANQUE TRANSFER dbo.T_CLIENT; Crittografia

SQL Server consente la crittografia dei dati della tabella mediante diversi algoritmi incorporati in SQL Server. Chiavi di crittografia e certificati possono essere importati da un file esterno o creati direttamente nel database, essendo SQL Server la propria autorità di certificazione. Esiste però una terza possibilità che consiste nell'utilizzare una scatola elettronica per la generazione e la conservazione delle chiavi denominata HSM ( Hardware Security Module ), destinata a garantire l'inviolabilità delle chiavi. Questi dispositivi elettronici vengono immessi in rete e si autodistruggono in caso di tentativo di accesso illegale, fisico o logico.

Un backup del database incorpora tutte le chiavi e i certificati per garantire la crittografia e la decrittografia. Ma il ripristino di un database su un server diverso da quello originale richiede la decrittografia seguita da una nuova crittografia. Gli elementi che compongono l'architettura di crittografia essendo interdipendenti tra loro da una chiave segreta conservata a livello di istanza. Tuttavia, per procedere con la crittografia o la decrittografia, è necessario aprire la chiave o con un certificato o fornendo la password.

SQL Server consente di crittografare i dati della tabella o crittografare il codice delle routine.

Infine, SQL Server consente di crittografare l'archiviazione dei dati e non più i dati tramite TDE (Transparent Data Encryption). I file (registri delle transazioni, dati di tabelle e indici) vengono crittografati quando vengono scritti fisicamente sui dischi e decrittografati quando vengono letti fisicamente dai dischi alla memoria. I dati vengono quindi visualizzati nella memoria, il che consente di mantenere le prestazioni durante la crittografia dell'intero database.

La versione 2016 ha aggiunto la crittografia end-to-end, vale a dire che la decrittazione controllata dal server, può essere eseguita a livello di applicazione e non più nel database.

Oggetti del database

Tabelle

Le tabelle sono gli oggetti che contengono effettivamente i dati nel database. Sono di due tipi:

  1. Le tabelle di sistema contengono le informazioni che consentono il corretto funzionamento del database. Per impostazione predefinita in SQL Server 2000 non possono essere modificati. In SQL Server 2005 vengono visualizzate nuove tabelle di sistema ma non sono accessibili agli utenti. Per poter sempre accedere a queste informazioni, il team di sviluppo ha aggiunto un gran numero di viste di sistema. Questi sostituiscono le vecchie tabelle di sistema e alcuni comandi DBCC. È stato inoltre aggiunto un gran numero di nuove viste di sistema. In SQL Server tutti gli oggetti di sistema fanno parte dello schema sys e sono archiviati in un database chiamato SystemResource.
  2. Le tabelle utente contengono dati utente.

Una tabella può contenere fino a 30.000 colonne la cui somma delle dimensioni non supera i 2 miliardi di byte . Colonne di tipi BLOB (VARCHAR (max), NVARCHAR (max), VARBINARY (max)) archiviate all'esterno della riga.

Le tabelle possono avere una chiave primaria . La chiave primaria è sempre indicizzata . Sono disponibili anche altri vincoli a livello di tabella come: vincoli univoci (indicizzati automaticamente), valori predefiniti, vincoli di controllo, chiavi esterne .

Visualizzazioni

Una vista è una query denominata del database. Viene interrogato allo stesso modo di una tabella.

I dati restituiti dalla vista vengono ricostruiti dai dati contenuti nelle tabelle ogni volta che viene chiamata la vista (ad eccezione delle viste indicizzate che memorizzano i risultati). SQL Server sostituisce il nome delle viste nella query che verrà eseguita con le relative definizioni, quindi la query risultante viene compilata, ottimizzata ed eseguita dal motore. Il piano compilato della query così ottenuta viene quindi memorizzato per consentirne il riutilizzo durante una chiamata successiva. Poiché SQL Server dispone dell'ottimizzazione semantica oltre all'ottimizzazione statistica, i rami dei join non necessari per il funzionamento di una vista vengono sistematicamente tagliati per l'ottimizzazione, quando possibile.

In determinate condizioni, è possibile aggiornare i valori in una vista. Esempi di queste condizioni sono:

  1. Una vista su più tabelle può modificare solo una di queste tabelle.
  2. Se la vista contiene funzioni, usa "DISTINCT" o "GROUP BY", la modifica non funzionerà.
  3. Non ci deve essere alcuna trasformazione dei dati di colonna (operazione, espressione, chiamata di funzione, ecc.)
  4. La vista ha un trigger di tipo INSTEAD OF (in quest'ultimo caso la logica di modifica è quella mostrata nel codice del trigger).

SQL Server implementa tipi speciali di viste che sono viste partizionate e viste indicizzate (equivalenti alle viste materializzate sincrone Oracle).

Indice

L'indicizzazione in SQL viene fornita in quattro modi: indicizzazione relazionale, indicizzazione verticale, indicizzazione testuale e indicizzazione specifica per determinati tipi di oggetti.

Indicizzazione relazionale

Riguarda l'intero contenuto delle colonne. Il meccanismo interno di questi indici si basa sulla nozione di albero bilanciato (B-Tree).

Esistono due tipi di indici  :

  1. Indici cluster (CLUSTERED INDEX)
  2. INDICE NON CLUSTER

Può esistere un solo indice cluster per tabella poiché questo tipo di indice è in realtà un "ordinamento" della tabella. Le righe della tabella sono quindi ordinate logicamente nell'ordine dell'indice cluster, il che spiega perché può essercene solo uno. In altre parole, essendo l'indice cluster un b-tree , le foglie dell'indice sono direttamente le righe nella tabella.

Un indice non cluster non riflette l'ordine fisico delle righe nella tabella.

Entrambi i tipi di indice sono costituiti da una chiave , costituita da un numero di colonne. Consentono di trovare rapidamente un dato da tutte o da parte delle colonne di questa chiave.

Quindi, se consideriamo una chiave (A, B, C), possiamo effettuare ricerche sui campi (A), (A, B) e (A, B, C). I campi sono considerati nell'ordine della chiave; non è quindi possibile effettuare una ricerca su, ad esempio, (B, C) o (C) con questo indice. D'altra parte, una ricerca su (A, C) beneficerà facilmente dell'indicizzazione della colonna A.

Una tabella può contenere 999 indici. Ogni indice può contenere 16 colonne la cui somma delle dimensioni non supera i 900 byte . Gli indici contengono oltre ai dati delle colonne dell'indice un segnalibro verso i dati della tabella. Può essere un puntatore a una riga o una delle chiavi dell'indice cluster (in inglese: clusterized index ).

L'ottimizzatore di SQL Server sceglie quale indice utilizzare. Potrebbe non utilizzare l'indice che hai creato perché il costo della ricerca in quell'indice potrebbe essere maggiore della lettura dell'intera tabella. Per effettuare queste scelte, l'ottimizzatore utilizza, tra l'altro, le statistiche della tabella e la presenza o meno di vincoli. Tuttavia, è possibile forzare l'ottimizzatore a utilizzare un indice con la clausola WITH (index_name), tuttavia questa pratica non è consigliata.

L'indice è organizzato in tree-b . Ciò consente di classificare le informazioni più rapidamente che se dovessero essere inserite in una tabella sequenziale. Poiché questa disposizione richiede molto spazio di archiviazione, la versione 2005 di SQL Server consente di separare i dati di ricerca ed elaborare i dati integrati nell'indice.

SQL Server consente di creare colonne calcolate che, se persistenti (PERSISTED), possono essere indicizzate.

Non esiste una struttura di indice diversa da b-tree . Ad esempio, non esiste un indice bitmap , che faciliterebbe le ricerche su campi con contenuto poco vario, ma è sufficiente creare una tabella di riferimento per simulare un indice bitmap. Allo stesso modo non esiste un indice hash. Ma basta creare una colonna hash calcolata (funzione CHECKSUM) e indicizzarla per ottenere un effetto simile. Infine, non è possibile creare un indice sull'espressione, ma basta creare una colonna calcolata con tale espressione e indicizzarla per ottenere lo stesso effetto.

Indicizzazione verticale

SQL Server permette di creare indici cosiddetti "verticali" (columnstore index) su un insieme di colonne consentendo l'accesso a tutte le colonne indicizzate sullo stesso piano. Infatti, in un indice relazionale a più colonne, l'ordine di ogni colonna nella chiave dell'indice consente solo determinate combinazioni di ricerche. Questo non è il caso dell'indicizzazione verticale, che consente tutte le combinazioni di ricerche. Ad esempio, con il tasto (A, B, C), è possibile ricercare sia su (A), (B) o (C), ma anche su qualsiasi combinazione di due o tre colonne (A, B ), ( B, A), (A, C), (C, A), (B, C), (C, B), (A, B, C), (A, C, B), (B , A, C), (B, C, A), (C, A, B), (C, B, A).

Nella versione 2012 questi indici sono di sola lettura e quindi destinati principalmente a database di tipo DataWarehouse per la business intelligence. Nella versione 2014, questi indici possono essere modificati.

Indicizzazione testuale e semantica

L'indicizzazione testuale (in pratica un catalogo di indicizzazione) consente ricerche in chiaro ("full text", conforme allo standard SQL con il predicato CONTAINS) o alla maniera di Google utilizzando il predicato FREETEXT. È consentito cercare parole diverse nello stesso documento (testo delle colonne di una tabella o documento elettronico memorizzato in una tabella) per uguaglianza, inizio, prossimità, ma anche per forma flessa (coniugazione, plurale, femminile, ecc.) , sinonimizzazione, estensione (soprattutto per gli acronimi) ... oltre che con i relativi pesi (funzione tabella CONTAINSTABLE e FREETEXTTABLE).

L'indicizzazione testuale può riguardare stringhe di caratteri o colonne di tipo XML, ma anche file elettronici sia che siano archiviati direttamente in una tabella (in un BLOB) sia come file tramite FILESTREAM o in un FILETABLE. SQL Server riconosce nativamente più di 50 formati di file e consente di aggiungere molti altri tipi di file elettronici utilizzando "ifilter".

SQL Server supporta la maggior parte delle lingue e consente ricerche combinate multilingue. Per questo, è possibile stabilire elenchi di "parole nere" per ciascuna delle lingue indicizzate. Inoltre SQL Server indicizza tutte le parole senza eccezioni, incluse le parole nere, dove alcuni RDBMS sono limitati a parole con una lunghezza minima (ad esempio MySQL) o escludono le parole nere (ad esempio PostgreSQL). Infine, se il caso non è importante per l'indicizzazione testuale, l'utente può scegliere se desidera un'indicizzazione testuale tenendo conto o meno dei caratteri diacritici, come accenti o legature. L'indicizzazione del testo consente inoltre di cercare metadati in file elettronici, ad esempio l'autore di un file Word.

Con la versione 2012, SQL Server ha aggiunto l'indicizzazione semantica che, come suggerisce il nome, permette di effettuare ricerche semantiche. Questa funzionalità richiede l'aggiunta di un database di elaborazione semantica destinato a stabilire statistiche sui testi indicizzati. la ricerca semantica si concentra sul significato dei documenti. Ciò consente l'estrazione automatica dei tag, l'individuazione del contenuto correlato e la navigazione gerarchica attraverso documenti con contenuto simile al testo di riferimento. Un esempio pratico è quello di interrogare l'indice di somiglianza del documento per identificare in un batch di curriculum, quali corrispondono meglio a una descrizione del lavoro.

Indicizzazione di oggetti

SQL Server consente l'indicizzazione di determinati tipi di dati oggetto, come XML (quattro tipi di indici) o spaziale (tipi geometria e geografia ). Come è possibile aggiungere i propri oggetti, a condizione di descriverli in forma .NET, è anche possibile indicizzarli, a condizione che si disponga che siano "  byte ordinati  " nell'organizzazione della loro memorizzazione.

È possibile definire stored procedure codificate in Transact-SQL e in .NET (SQL CLR). Una stored procedure è una serie di istruzioni che possono modificare i dati del database e incapsulare le transazioni e che restituiscono uno o più valori.

Le procedure memorizzate in SQL Server possono assumere come parametri e/o restituire numeri interi, stringhe di caratteri, date, cursori, tabelle, tabelle virtuali e qualsiasi altro tipo definito in SQL Server di default o dagli utenti.

I principali vantaggi dell'utilizzo delle stored procedure sono:

  • la complessa transazione di elaborazione (più transazioni possono essere collegate o sovrapposte)
  • maggiore sicurezza (il codice di una procedura può essere spersonalizzato)
  • migliore riutilizzo dei piani compilati
  • la capacità di gestire le dipendenze tra codice SQL e oggetti motore.

Trigger

Il trigger è un codice memorizzato che viene eseguito dopo (AFTER) o al posto di (INSTEAD OF) una particolare azione (inserisci, modifica, elimina) su una tabella o vista. È possibile codificare tutti i trigger AFTER desiderati su una singola azione (INSERT, UPDATE o DELETE) della stessa tabella o vista. In caso di più trigger sulla stessa azione della stessa tabella, l'ordine dei trigger è arbitrario, ad eccezione dei primi 3 che possono essere imposti. I trigger INSTEAD OF devono essere univoci per evento/tabella.

Vengono create due tabelle virtuali per gestire i dati inseriti, modificati o cancellati: inseriti (per inserimento e modifica) ed eliminati (per cancellazione e modifica). Non è possibile modificare il contenuto di queste tabelle virtuali. Al contrario, SQL Server consente di modificare la tabella di destinazione del trigger, ad esempio il rollback della transazione implicita che ha generato il trigger eseguendo il rollback della transazione (ROLLBACK).

Il particolare trigger INSTEAD OF può essere utilizzato quando si vuole intercettare l'evento, non per farlo accadere, ma per codificare un'altra azione. Questo tipo di trigger può essere utilizzato ad esempio nell'ambito di una mappatura relazionale/oggetto dove manipoliamo viste composte da più join in cui vogliamo che l'utente possa effettuare aggiornamenti (INSERT, UPDATE, DELETE) direttamente su queste viste. In questo caso i dati INSERT, UPDATE o DELETE vengono suddivisi per ciascuno dei tavoli in gioco e tramite una transazione viene lanciata una serie di ordini specifici per ogni tavolo da visualizzare.

Da SQL Server 2005 è stata aggiunta la possibilità di creare trigger DDL. Questi trigger DDL agiscono dopo il verificarsi di eventi di tipo CREATE, ALTER o DROP e questi eventi possono essere rilevati a livello di server (ad esempio CREATE DATABASE) o a livello di database (ad esempio DROP TABLE). Per ottenere dettagli sull'evento intercettato, un pacchetto dati XML (ottenuto dalla funzione EVENDATA()) fornisce più dettagli sull'origine e la natura dell'evento. C'è anche la query SQL che l'ha attivata.

Un particolare tipo di trigger viene utilizzato per intercettare le connessioni al server (trigger FOR LOGON).

Funzioni (UDF: funzione definita dall'utente)

Da SQL Server 2000 è possibile creare funzioni in Transact SQL e dal 2005 in .net (SQL CLR).

Queste funzioni sono di 3 tipi:

  • Scalare: diverse istruzioni restituiscono quindi un valore di tipo semplice
  • Tabella con più istruzioni: diverse istruzioni poi restituiscono una tabella
  • Tabella online (vista parametrizzata): un'istruzione di tipo SELECT restituisce una tabella

Tuttavia, esistono restrizioni sulla funzionalità che può essere utilizzata nel corpo di una funzione. Non è possibile utilizzare funzioni non deterministiche e la funzione non deve modificare dati o parametri di sistema in modo permanente o permanente. Ciò vieta, ad esempio, l'uso di un INSERT su una tabella o l'uso della funzione GETDATE().

SQL Server 2005 ha aggiunto:

  • la possibilità di creare funzioni di aggregazione (calcolo statistico) tramite CLR, ovvero codifica utilizzando un linguaggio della piattaforma .net;
  • funzioni specifiche legate al partizionamento dei dati (FUNZIONE DI PARTIZIONE).

A differenza di una stored procedure, una funzione non può contenere:

  • ordine che determina i confini di una transazione (BEGIN TRANSACTION, COMMIT, ROLLBACK)
  • ordine di aggiornamento dei dati (INSERT, UPDATE, DELETE, SELECT), ad eccezione delle funzioni di tabella multi-istruzione e solo per la tabella di ritorno.
  • codice non deterministico (barriera parzialmente rimossa con SQL Server 2005, funzioni come GETDATE consentite, ma RAND è ancora vietato)
  • SQL dinamico
  • cursore
  • una chiamata di procedura memorizzata

Questo ha senso, perché lo scopo di una funzione è soprattutto quello di essere utilizzato all'interno di una query.

Le funzioni della tabella in linea, agendo come viste con parametri, possono aggiornare le tabelle sottostanti.

Aspetti dell'oggetto

Dalla versione 2005, SQL Server incorpora aspetti degli oggetti tra cui:

  • la possibilità di realizzare tipi complessi strutturati tramite un linguaggio esterno come .net e di utilizzarli sia come colonna di tabella che come tipo di variabile in una routine (procedura, trigger o funzione).
  • Tipo XML ricercabile tramite XQuery/XPath e indicizzabile. Server SQL che integra un parser XML e procedure per la gestione dei flussi XML.
  • la possibilità di generare un feed XML dal risultato di una query SELECT invece di un "dataset".
  • il tipo gerarchiaId (comparso nella versione 2008) facilitando la strutturazione dei dati gerarchici (alberi).
  • un vero e proprio GIS (Geographic Information System - apparso nella versione 2008) con l'arrivo dei tipi GEOMETRY e GEOGRAPHY allo standard OGC con interfacciamento in forma di WKT (Well Known text) o WKB (Well Known Binary), e dotato di molti metodi di manipolazione.
  • l'implementazione di servizi Web che possono essere creati direttamente nell'RDBMS.
  • l'archiviazione di file sotto la responsabilità esclusiva o parziale dell'RDBMS (FILESTREAM) e la loro manipolazione transazionale consentendo un backup coerente dei dati relazionali e non (molto utile per un EDM ad esempio) - @ Dal 2008.
  • la rappresentazione trasparente sotto forma di tabelle (FILETABLE) degli alberi di archiviazione dei file del sistema operativo Windows (directory e file) e la loro manipolazione tramite query SQL - dal 2012.

Utensili

SQL Server Management Studio (SSMS)

Questo strumento è incluso nelle versioni a pagamento di SQL Server. Consente di connettere e amministrare i vari motori di SQL Server (SSRS, SSIS, SSAS e il motore relazionale). Consente al motore relazionale di sviluppare script TSQL, con la possibilità di raggrupparli tutti all'interno di una soluzione (come in Visual Studio). Questi possono anche essere salvati utilizzando Visual SourceSafe o Team Foundation Server. Per la versione Express (gratuita), lo strumento SQL Server Management Studio Express Edition (SSMSE) semplificato è disponibile gratuitamente.

Prima del 2005 questo strumento era separato in due moduli chiamati "Enterprise Manager" e "Query Analyzer".

SQL Server Business Intelligence Development Studio/SQL Server Data Tools

Come SSMS, questo strumento viene fornito con versioni a pagamento di SQL Server. È un overlay dello strumento di sviluppo generico Microsoft Visual Studio adatto alla creazione di progetti BI (Analysis Services, Integration Services o Reporting Services). Tutti questi progetti possono essere trovati nel gruppo "Progetti di Business Intelligence".

Profiler (generatore di profili)

Questo strumento consente di acquisire l'attività di un database. Consente agli amministratori di controllare gli elementi e le query che richiedono tempo nel database. Facoltativamente, è possibile riprodurre la cattura su un server di prova. Tende a essere gradualmente sostituito dal sistema degli "eventi estesi" (eXtended Events).

Servizi e Motori

Infatti MS SQL Server è una suite composta da cinque servizi principali:

  • Il motore relazionale (OLTP) chiamato SQL Server;
  • Il motore decisionale (OLAP) denominato SSAS ( SQL Server Analysis Services ) comprendente un motore di archiviazione per cubi, algoritmi di perforazione ( data mining ) e vari strumenti di BI (Business Intelligence);
  • Un ETL (Extract Transform and Load) denominato SSIS (SQL Server Integration Services) destinato all'impostazione di logiche di flusso di dati, in particolare per l'alimentazione di data warehouse ;
  • Uno strumento di generazione di report chiamato SSRS (SQL Server Reporting Services) che consente di produrre report in diverse forme e utilizzando le risorse del motore decisionale (database "resportServer...") sia per archiviare i report che per nasconderne dati per fare il "riscaldamento";
  • Un sistema di pianificazione del lavoro e gestione degli avvisi chiamato Agent SQL che utilizza anche i servizi del motore SQL (base msdb).

Ad essa sono associati altri servizi per determinate esigenze:

  • Lanciatore SQL Full-Text Filter Daemon  : per caricare alcuni documenti esterni (thesaurus e dll ifilter) richiesti per la ricerca "testo normale"
  • SQL Browser  : servizio di erogazione di servizi SQL destinato a facilitare la ricerca di servizi SQL in rete
  • MS DTC (Data Transaction Coordinator): coordinatore di transazioni distribuite, utilizzato per aggiornamenti di commit in due fasi (2PC) combinando diversi server transazionali, non necessariamente del mondo Microsoft ( XA , X / Open , TIP, WS ... )

SQL Server include anche molti strumenti di sviluppo:

  • per database transazionali, tramite SQL Server Management Studio (SSMS)
  • per basi decisionali, data mining, reporting ed ETL, attraverso un overlay di Visual Studio denominato BIDS ( Business Intelligence Development Studio ) poi SQL Server Data Tools dalla versione 2012.

Sono disponibili più di cento strumenti periferici, sia durante l'installazione (SQL Profiler, Database Tuning Advisor, Data Collector, ecc.), sia direttamente sul sito Microsoft (SQLdiag, SQLioSim, SQL Server Best Practices Analyzer...), o tramite il sito della comunità opensource Codeplex (RML Utilities, PAL, Open DBDiff ...).

Un'istanza di SQL Server è un'installazione di alcuni o tutti i servizi di SQL Server su una macchina Windows e può ospitare molti database. Lo stesso sistema operativo che supporta fino a 50 istanze diverse (cosa sconsigliata in produzione).

SQL Server 2014 non è limitato nel numero di core o RAM, solo dalla capacità di Windows. Un database è limitato a 524 peta byte (Po), ma poiché SQL Server può ospitare più database e 32760 basi nella stessa istanza, la capacità complessiva del sistema può teoricamente gestire 17166240  dati Po . Con 50 istanze sulla stessa macchina, questo limite teorico viene riportato a 858 312 000  Po ...

SQL Server esiste in diverse edizioni: CE (Compact Edition - soluzione integrata per smartphone), Express (diverse versioni - gratuite), Web (in modalità SPLA: Service Provider License Agreement - con host Web), Standard, BI ed Enterprise - questo per produzione. L'edizione Developper (equivalente all'edizione Enterprise) è per lo sviluppo.

server SQL

Questo è il motore del database. Ogni istanza di SQL Server dispone di un servizio Windows Sql Server corrispondente. Questo servizio è disponibile nei servizi Windows con i nomi MSSQL $ Instance_name per le istanze denominate e MSSQLServer per l'istanza predefinita.

Ogni istanza creata ha inizialmente da 4 a 6 database di sistema. Il motore di SQL Server si basa sul database di sistema principale che contiene la definizione degli altri database.

Le altre basi del sistema sono:

  • msdb  : utilizzato dall'agente SQL Server, replica , Data Transformation Services, Integration Services...
  • modello  : utilizzato come modello per creare nuovi database. Qualsiasi nuovo database ne è una copia.
  • tempdb  : Database temporaneo, viene utilizzato per archiviare dati temporanei creati dagli utenti o generati da cursori, creazioni di indici, durante un ordinamento di grandi dimensioni. Su SQL Server 2005, contiene anche le versioni dei record generate dai livelli di isolamento SNAPSHOT, i contenuti delle pseudo tabelle inserite ed eliminate utilizzate nei trigger e quelli generati dalle operazioni sugli indici in linea. Questo database di sistema viene svuotato sistematicamente all'avvio del servizio SQL Server.
  • distribuzione  : Presente solo nel contesto di una replica se il tuo server svolge il ruolo di distributore.
  • mssqlsystemresource  : presente solo da SQL Server 2005. Non è direttamente accessibile e contiene tutti gli oggetti di sistema (tabelle escluse).

Agente SQL Server

Questo è l'agente di manutenzione per l'istanza di SQL Server. Ogni istanza di SQL Server dispone di un servizio Windows Sql Server Agent corrispondente. Questo servizio è disponibile nei servizi Windows con i nomi SQLAgent $ InstanceName per le istanze denominate e SQLAgent per l'istanza predefinita. Il motore di SQL Server Agent è basato sul database msdb . Questo motore viene utilizzato per gestire i backup, i piani di manutenzione, il lavoro programmato, il monitoraggio del database, gli avvisi amministrativi...

L'agente ha anche il ruolo di monitorare il servizio SQL Server e di attivarne automaticamente il riavvio in caso di arresto imprevisto.

Ricerca a tutto testo

Indice full-text/motore di ricerca.

Coordinatore delle transazioni distribuite

Conosciuto anche come MS DTC, viene utilizzato per gestire le transazioni distribuite. Ovvero, transazioni tra diversi server di transazione SQL Server, tra SQL Server e altri server di database, tra diverse origini dati, siano essi motori di database o semplici componenti.

Servizi di notifica

Il servizio apparso in SQL Server 2000 consente di interrogare regolarmente il database e, a seconda di queste richieste, di notificare i gruppi iscritti a questi eventi. Questo servizio non è più fornito come standard da SQL Server 2008. SQL Server, nome in codice Denali, offre tuttavia funzionalità simili in SQL Server Reporting Services e Sharepoint 2010, noto come Alerting.

Il processo decisionale

La piattaforma di business intelligence di SQL Server è costituita da un ETL , apparso con SQL 7, un motore multidimensionale, apparso anche con SQL 7, e un motore di reporting, aggiunto nel 2004 con SQL 2000. Con SQL Server versione 2005, uno studio di sviluppo per la business intelligence è stata integrata in Visual Studio 2005: BI Development Studio.

Mappatura

SQL Server 2008 supporta un nuovo tipo di dati primitivi: il tipo geografico in cui si verifica la proiezione (latitudine e longitudine) e il tipo geometrico senza proiezione per manipolare i dati relativi a punti, linee e poligoni. Il primo software che supporta SQL Server è il software Manifold System  (en) e MapInfo, GeoMedia.

Servizi di integrazione (SSIS: Servizi di integrazione di SQL Server)

Un servizio apparso in SQL Server 2005, sostituisce Data Transformation Services (DTS). L' ETL è stato completamente ricostruito e compete con altri strumenti ETL professionali sul mercato.

L'obiettivo è l'integrazione dell'azienda attraverso i suoi dati. SQL Server Integration Services (SSIS) è una piattaforma completa per l'integrazione dei dati. SSIS, non è un semplice ETL. È una piattaforma completa per l'integrazione dei dati, che offre una serie di strumenti grafici di sviluppo e gestione, servizi, oggetti programmabili e API (Application Programming Interfaces). SSIS contiene un motore del flusso di lavoro che supporta una logica complessa e può essere utilizzato per un'ampia varietà di operazioni di manutenzione del database e sofisticate operazioni di trasferimento dei dati. L'architettura SSIS è sostanzialmente composta da due parti: il motore runtime di trasformazione dei dati, che gestisce il flusso di controllo di un pacchetto, e il motore del flusso di dati o motore della pipeline di trasformazione dei dati, che gestisce il flusso di dati dalle origini dati, attraverso trasformazioni, e agli obiettivi di destinazione.

Servizi di analisi

SQL Server Analysis Services (SSAS) è un servizio apparso in SQL Server 7, noto all'epoca come OLAP Services. Consente di generare cubi OLAP , dati aggregati e multidimensionali. Consente inoltre di implementare algoritmi di Data Mining .

Servizi di segnalazione

Questo servizio, apparso in SQL Server 2000, è un motore di generazione di report. Due servizi web lo compongono, uno che ne permette l'amministrazione, l'altro la generazione, la sottoscrizione, la resa dei report. I rendering vengono eseguiti in Excel, PDF, HTML e Word (dalla versione 2008).

Edizioni

Edizione Datacenter

Apparso con SQL Server 2008 R2, supporterà fino a 256 processori logici, oltre a virtualizzazione illimitata e una quantità illimitata di memoria.

Edizione Enterprise

Supporta un numero illimitato di processori e dimensioni della RAM (limitate dal sistema operativo). Esiste una versione a 32 bit (x86) e 2 versioni a 64 bit (ia64 e x64, x64 solo per SQL Server 2005) e non ha limiti alla dimensione dei database. Può essere installato solo su una versione server Windows . Include tutte le funzionalità del motore, comprese le funzionalità di alta disponibilità. Le funzionalità per la business intelligence sono tutte incluse dalla generazione di report con Reporting Services all'utilizzo di cubi OLAP con Analysis Services al trasferimento di dati con Integration Services (SQL Server 2005) o Data Transformation Services (SQL Server 7) & 2000) . Può anche operare in un cluster fino a 8 nodi.

Edizione per sviluppatori

Questa è un'edizione per sviluppatori che ha le stesse funzionalità dell'edizione Enterprise. Tuttavia, la licenza contiene restrizioni sul suo utilizzo. La licenza vieta il funzionamento in produzione. È destinato esclusivamente all'uso di prova. Tuttavia non ci sono restrizioni sui prodotti disponibili.

edizione standard

Supporta fino a 4 processori e una RAM illimitata (limitata dal sistema operativo). Esiste una versione a 32 bit (x86) e 2 versioni a 64 bit (ia64 e x64, entrambe solo per SQL Server 2005) e non ha limiti alla dimensione dei database. Tuttavia SQL Server 2005 ne consente l'installazione su desktop Windows . Include tutte le funzionalità del motore ad eccezione di alcune funzioni di alta disponibilità. Le funzionalità per la business intelligence sono tutte comprese dalla generazione dei report con Reporting Services (per SQL Server 2000 questo componente deve essere caricato separatamente) all'utilizzo dei cubi OLAP con Analysis Services, compreso il trasferimento dei dati con Integration Services (SQL Server 2005) o Servizi di trasformazione dati (SQL Server 7 e 2000). Può anche operare in un cluster fino a 2 nodi dalla sua versione del 2005.

Edizione per gruppi di lavoro

La nuova edizione è apparsa con SQL Server 2005. Supporta fino a 2 processori e 3  GB di RAM. Per il momento è disponibile solo in versione a 32 bit e non ha limiti alla dimensione dei database. Include la maggior parte delle funzionalità del motore, inclusa la possibilità di partecipare a una replica come editor. La funzionalità di business intelligence inclusa è limitata al reporting con Reporting Services. Questa edizione scompare con la versione 2012 di SQL Server.

Edizione web

La nuova edizione è apparsa in SQL Server 2008. Simile all'edizione Standard in termini di funzionalità, non supporta tuttavia né il mirroring né i componenti decisionali di SQL Server. La sua licenza ne consente solo l'uso come server di database per un sito Web o un servizio. È disponibile come abbonamento mensile, specialmente per gli host, oltre ad essere disponibile in modo più convenzionale per l'acquisto.

Edizione personale

Edizione esistente con SQL Server 7 e SQL Server 2000. Supporta fino a due processori e 2  GB di RAM . Disponibile solo nella versione a 32 bit e non ha limiti sulla dimensione dei database. Include la maggior parte delle funzionalità del motore, inclusa la possibilità di partecipare alla replica come abbonato. Non ha funzionalità per la business intelligence. In termini di licenza, per utilizzare questa edizione è sufficiente disporre di una licenza CAL (Client Access License) di SQL Server.

Database interno di Windows/SQL Server integrato

Simile all'edizione Express di SQL Server, con le limitazioni relative alla memoria, al numero di processori e alle dimensioni del database rimosse, tuttavia, può essere utilizzato solo localmente e può ospitare solo database di applicazioni Microsoft. Prodotti come Sharepoint o alcune funzionalità di Windows Active Directory ne fanno uso.

MSDE / Edizione Express

Microsoft ha pubblicato un'edizione gratuita composta dal solo motore di database relazionale, limitato a 4  GB di spazio su disco (10  GB nella versione 2008 R2). Tuttavia, può essere utilizzato per creare fino a 32.760 database diversi, ovvero più di 320  TB di dati dalla versione 2008 R2.

Edizione compatta

Precedentemente chiamato: Pocket PC / Mobile / Everywhere Edition. Questa è un'edizione leggera adatta per l'uso su PDA o smartphone dotati di Windows Mobile . La versione Compact (versione 3.5), che succede alle edizioni Mobile, è aperta alle classiche workstation basate su Windows . Questa edizione può partecipare alla replica come abbonato.

Licenze

SQL Server supporta 5 sistemi di licenza:

  • Licenza per utente  : SQL Server può utilizzare tutti i processori sul server (fino alla versione 2008 R2, massimo 16 (edizione Standard) o 20 (edizione Enterprise) Core nella versione 2012) ma è limitato al numero di utenti specificato. Ogni persona fisica che utilizza un'applicazione che utilizza SQL Server è considerata un utente del database.
  • Licenza per dispositivo (da SQL Server 2005): SQL Server può utilizzare tutti i processori sul server (fino alla versione 2008 R2, massimo 16 (edizione Standard) o 20 (edizione Enterprise) Core nella versione 2012) ma è limitato al numero di dispositivi specificato. Ogni dispositivo fisico che accede direttamente o indirettamente a SQL Server è considerato un utente del database
  • Licenza per processore  : SQL Server utilizza il numero di processori specificato nella licenza e può accettare qualsiasi numero di utenti. Questa modalità di licenza è valida fino all'edizione 2008 R2 e successivamente scompare.
  • Licenza per core  : devi acquistare tante licenze "Core" quanti sono i core nel server o nella macchina virtuale. Il numero di utenti è illimitato. Questa modalità di licenza è valida solo dall'edizione 2012. Il prezzo è conteggiato per coppie di core e parte da 4.
  • Licenza SPLA  : per l'edizione web, questa è una licenza a noleggio. Il prezzo contenuto permette di competere con RDBMS “gratuiti” come MySQL o PostGreSQL.

I tipi di licenza che possono essere presi in considerazione per l'utilizzo di SQL Server dietro un front-end Web sono la licenza per processore o core o la modalità SPLA limitata all'edizione Web.

Strumenti aggiuntivi che integrano un server SQL

Vale la pena menzionare alcuni strumenti relativi a SQL Server ...

BizTalk

Soluzione Microsoft per l'integrazione dei dati EAI (Enterprise Application Integration) e B2B (business-to-business).

Sharepoint

Soluzione di gestione della conoscenza aziendale globale (KB - Knowledge base) che consente la gestione dei contenuti (tramite sito Web e forum) e documenti elettronici condivisi, il recupero delle informazioni, nonché la possibilità di creare moduli e presentazione di statistiche decisionali come dashboard, indicatori chiave ( KPI - Indicatore chiave di prestazione) ...

Dinamica

Questo è l'ERP (Enterprise Resource Planning) di Microsoft.

TFS (Team Foundation Server)

È una fucina di software che consente la gestione del codice sorgente, build, monitoraggio delle risorse, pianificazione delle attività...

SCOM (Responsabile delle operazioni di System Center)

Sistema per la gestione centralizzata, supervisione e manutenzione del sistema informatico (server, PC, periferiche online) e dell'infrastruttura di rete.

WSUS (servizi di aggiornamento di Windows Server)

Servizio di distribuzione per OS Microsoft e applicazioni per l'aggiornamento delle varie macchine Windows in un parco informatico (utilizza la versione Express gratuita)

Strumenti aggiuntivi che possono utilizzare un server SQL

StreamInsight

StreamInsight è una piattaforma progettata per sviluppare e distribuire su larga scala applicazioni di elaborazione di eventi complessi (CEP). Si basa su due componenti essenziali: lo StreamInsight Server per la gestione dei flussi di dati e lo StreamInsight Framework per la creazione delle interfacce.

EDF utilizza StreamInsight per la gestione degli incidenti per tutte le sue apparecchiature IT.

SQL Server e Big Data

Molto impegnata in questo movimento, in particolare tramite Hadoop, Microsoft offre diverse soluzioni per gestire i big data.

HDInsight (Hadoop)

HDInsight è una soluzione Hadoop offerta nel cloud di Azure o in locale (per scopi di sviluppo).

Gli strumenti di linguaggio Map/Reduce, Hive, Sqoop e Pig facilitano il trasferimento e la manipolazione dei dati da SQL Server ad Hadoop. Un SDK basato sul framework .NET consente di scrivere batch utilizzando Visual Studio. Un'installazione locale di Hadoop destinata agli sviluppatori è disponibile tramite HDInsight Server.

polibase

Polybase è uno strumento destinato ad attaccare diverse fonti di dati relazionali (SQL Server), decisionali (SQL Server Parallel Data Warehouse - PDW) o big data, attraverso diversi processi come Map Reduce o MPP (Massive Parallel Processive)

Riferimenti

  1. Edizioni e funzionalità supportate di SQL Server 2019 (15.x)  " ,4 novembre 2019(consultato il 23 dicembre 2020 )
  2. (in) "  Docker: Installa contenitori per SQL Server su Linux - SQL Server  " su microsoft.com (accessibile il 4 settembre 2020 ) .
  3. FreeTDS
  4. Per impostazione predefinita, su SQL Server 2014, non è possibile avviare un comando di sistema da SQL Server
  5. https://technet.microsoft.com/fr-fr/library/ms175609%28v=sql.90%29.aspx
  6. "  Servizi di cloud computing/Microsoft Azure  " , su microsoft.com (consultato il 4 settembre 2020 ) .
  7. "  Servizi di cloud computing/Microsoft Azure  " , su microsoft.com (consultato il 4 settembre 2020 ) .
  8. SQL Server 2012, cosa c'è di nuovo?

Vedi anche

Articoli Correlati