Press "Enter" to skip to content

1- SQL Server Nozioni base

Come si crea un database in SQL Server 2005, usando SQL Management Studio quali sono le opzioni che si possono adottare, qual’è il loro uso e quali sono i motivi per sceglierle.

Introduzione

Perché scrivere un articolo su una cosa così banale come la creazione di un database su SQL Server, cosa ci vuole, basta aprire SQL Server Management Studio oppure Visual Studio e lo si fa in 2 minuti.

Vero, ma il livello di questo articolo è 1 quindi absolute beginners, e spesso proprio le cose più semplici sono difficili da trovare in rete, dove si pensa sempre ad insegnare a fare Cursori Multidimensionali (lo so che non esistono) ma difficilmente si pensa a chi si trova davanti lo strumento per la prima volta.

Non solo, ci sono alcune cose che, a meno che non abbiate una lunga esperienza con SQL Server, non sono così banali nella creazione di un database. Scoprirle subito potrebbe evitarvi dei mal di testa quando il vostro DB inizia a divenire uno strumento utile ai vostri utenti e iniziate a ricevere proteste di lentezza oppure messaggi di errore dal server.

Usare SQL Management Studio

Gli screenshot sono quelli di SQL Management Studio Dev edition in inglese, le traduzioni italiane sono orrende pertanto chi avesse la sfortuna 😛 di usare una versione italiana che io per principio evito, può emendare l’articolo traducendo i termini i nomi delle opzioni e dei menu. Anche alcune delle maschere ed opzioni potrebbero essere un po’ diversi da quelli della versione Express, ma gli script SQL che illustrerò in seguito sono certamente utilizzabili su tutte le versioni.

sqlmgr01

Un click con il tasto destro del mouse sulla cartella databases e appare il menu contestuale che ci permette di creare il database:

sqlmgr02

Se selezioniamo l’opzione appare questa finestra:

sqlmgr03

Digitiamo il nome del nostro database nell’apposita textbox e premiamo il tasto OK. Abbiamo creato il database, nulla di più semplice, però facendo così abbiamo utilizzato il metodo standard da utente medio smanettone: “Basta che funzioni”. Forse, sapendo qualcosina in più su ciò che si può definire in questa finestra e soprattutto sapendo “A cosa serve?” magari funzionerà adesso, funzionerà quando lo installeremo dal nostro cliente, e funzionerà anche quando ci saranno 10 utenti che inseriranno dati da mattina a sera. Non solo, potremo anche essere certi che se qualcosa non va, SQL Server ci avvertirà.

Chiedo scusa per aver gigioneggiato col nome del database, ma pippo e paperinik li ho già usati sul mio server. Facciamo gli utenti curiosi e vediamo cosa c’è su questa finestra oltre alla textbox del nome database.

  • Una checkbox che indica “Use fulltext-indexing” è vuota, e se selezionata ci darà modo di generare un Full Text Index sui campi NText del nostro database. Non so se è ammesso in SQL Server 2005 Express Edition, ma potrebbe essere utile per una ricerca su un catalogo descrittivo con testi estesi.
  • Le 2 righe della lista in centro alla pagina ci mostrano i seguenti dati:
    • Logical_name
    • File Type
    • Filegroup
    • Initial Size (MB)
    • Autogrowth

    Se allarghiamo la finestra ancora un pochino, troviamo dell’altro:

    • Path
    • FileName

createdb01

Se osserviamo meglio, quando abbiamo digitato goofy nella textbox del nome database, automaticamente nella lista è stato inserito nel logical name di entrambe le righe, anche se i due nomi differiscono per un suffisso _log e sul File Type c’è scritto Data per il primo e Log per il secondo.
L’Initial Size ci dice che il database sarà creato con una dimensione di 3 megabyte, mentre il log avrà una dimensione di 1 megabyte. Perciò se premiamo OK e andiamo a guardare sulla cartella indicata nella colonna Path possiamo quasi con certezza affermare che troveremo due files che si chiamano goofy e goofy_log.

createdb02

Ebbene si è proprio così, vediamo anche che è stata assegnata loro una estensione .mdf (Master Data File) e .ldf (Log Data File). Provate anche voi e vedrete che la dimensione dei 2 files è rispettivamente 3072KB e 1024KB ovvero 3 Megabyte e 1 Megabyte. Se siamo un po’ osservatori, però avremo notato che nella nostra lista ci sono dei bottoncini con i 3 puntini segno che c’è qualcosa che possiamo fare. Non solo, quelli che non hanno paura di sperimentare vedranno che facendo click con il mouse possiamo modificare il Logical Name, che la dimensione iniziale del file può essere modificata, che i 3 puntini sulla casella Autogrowth aprono una dialog:

createdb03

Questa dialog box ci permette varie opzioni interessanti, infatti possiamo decidere se il file su cui ci troviamo può crescere automaticamente, e in che modo crescerà, inoltre possiamo fissare un tetto alla sua crescita.

A cosa serve tutto questo? Serve a porci delle domande:

  1. Quanti dati prevediamo che saranno memorizzati sul database inizialmente?
  2. Quanto crescerà il nostro database?
  3. Con Che velocità crescerà?
  4. Quali sono le capacità del disco che ospita il database (o dei dischi).

Se nel nostro database inizialmente prevediamo di inserire una certa quantità di dati, anagrafici e storici, magari importati da un’applicazione precedente, 3 megabyte forse sono pochi, pertanto è opportuno dare spazio sufficiente ai nostri dati iniziali, per evitare che il database cresca 100 volte durante l’importazione e magari il file venga frammentato in vari segmenti sul disco divenendo da subito più lento per gli accessi in scrittura.

Quanto crescerà è qualcosa che in base alla nostra applicazione dovremmo sapere bene, quando si genera un database è opportuno regolare i parametri di crescita per evitare che il database cresca una volta all’ora o anche una volta al giorno, perché l’auto crescita è un’operazione che rallenta il server, pertanto se vogliamo evitare utenti furiosi, regoliamolo perché non cresca più di una volta al mese.

Per delle applicazioni particolari, potrebbe essere anche opportuno che ci sia qualcuno incaricato di sorvegliare e far crescere il database quando necessario, e SQL Server mette a disposizione strumenti per avvisare l’incaricato a questo compito via e-mail, pager, messaggio di sistema.

Il parametro di restrizione della crescita può sembrare strano e spesso c’è chi non lo usa, un collega che ha tolto questo parametro ad uno dei database di cui sono responsabile è la causa ultima di questo articolo.

Non limitare la massima crescita di un database può portare a conseguenze di tipo catastrofico quando per qualsiasi motivo il disco che ospita i database si riempisse, perché non avere spazio su disco potrebbe impedirci di fare ad esempio un Backup Full Seguito ad un Backup del Transaction Log che ci permetterebbe di liberare spazio su disco e far riprendere le attività agli utenti mentre provvediamo a cambiare dischi, ordinare un server nuovo più capiente, oppure spezziamo il file dati in più segmenti per poter trasferire parte dei dati su un disco vuoto.

Quindi è sempre opportuno valutare quanto spazio sul disco abbiamo a disposizione per SQL Server e tenere almeno un Gigabyte o due liberi per ogni evenienza.

Possiamo modificare il parametro per un tempo limitato, ad esempio per un’aggiornamento della struttura database che provocherà una crescita temporanea smodata del transaction log, per poi ripristinarlo una volta terminato l’aggiornamento e riportato il Log a dimensioni normali.

Per chi non sapesse che cos’è il Transaction Log, si tratta di un file che contiene tutte le transazioni che si svolgono su un database a partire da uno starting point, usualmente un Backup Full. Non mi dilungo su questo tema perché ne parleremo specificamente in seguito, magari in un articolo dedicato ai Backup database.

Se continuiamo a pigiare bottoni, il prossimo è quello della colonna Path, che ci apre un Folder Explorer che ci permette di posizionare i nostri files in una cartella diversa da quella proposta come standard:

createdb04

Possiamo generare i nostri files dove vogliamo, ma assicuriamoci interpellando il sistemista che ha installato e configurato SQL Server, che l’utente con cui il servizio MSSQLSERVER viene eseguito su questo computer abbia accesso completo in lettura e scrittura sui files. Ricordiamo di non fare affidamento sul fatto che il database viene generato, perché SQL Management Studio lavora utilizzando l’utente Windows con cui lo abbiamo lanciato, che nel 99,9 percento dei casi è un Administrator, mentre il servizio di SQL Server è opportuno sia configurato con diritti limitati (Questo è argomento dedicato ai Sistemisti, prendetene però nota perché poi la colpa è sempre dei programmatori.)

Se proseguiamo sull’ultima colonna della lista dei files, vediamo che li invece non ci possiamo scrivere.

Abbiamo visto che ci sono 2 colonne che non possono essere modificate, File Type e FileGroup, però in fondo alla pagina c’è un bottone ADD, Provando a premerlo, scopriremo che viene generata una nuova riga, possiamo aggiungere un terzo file al database, possiamo scegliere il suo File Type come Data oppure Log, possiamo scegliere il suo FileGroup come PRIMARY oppure, possiamo anche generare un nuovo Filegroup e possiamo modificare tutti gli altri parametri.

Cosa ne possiamo dedurre? Che un database può essere formato da molti files, che questi files possono stare su cartelle e su dischi diversi, che esiste una cosa che si chiama Filegroup, e che in un database ce ne possono essere diversi.

A cosa serve? La possibilità di usare diversi files per i dati ci permette ad esempio di usare più partizioni o più dischi sul server per memorizzare il database. Quando un disco sta per divenire troppo pieno, blocchiamo la crescita del file dati, aggiungiamo un nuovo file su un altro disco o un’altra partizione e facciamo continuare a crescere il database su quel disco. E i FILEGROUP? sono un tipo di partizione logica “Verticale” dei dati, permettono di generare più file dati (uno o più per ogni Filegroup) e ogni tabella del database può essere assegnata ad un filegroup, per default il filegroup PRIMARY (obbligatoriamente generato in ogni database) oppure un altro da noi generato. Usualmente in Database molto grandi si separano le tabelle molto movimentate da quelle poco movimentate, perché è possibile predisporre piani di backup specifici per ogni file e ogni filegroup.

Ma a me serve? Per uno sviluppatore che crea programmi a livello aziendale per la propria azienda oppure per le piccole e medie imprese, non è detto che sia necessario utilizzare questi strumenti. Però saper progettare una base dati in modo che qualsiasi cosa accada siamo pronti a seguire l’evoluzione dell’azienda, ci può permettere di dare Valore Aggiunto a ciò che diamo ai nostri clienti.

createdb05

La finestra di creazione di un nuovo filegroup che ho aperto ci permette di dargli un nome e volendo di far divenire questo filegroup in sola lettura oppure il filegroup di Default. Il primo parametro permette di creare partizioni del database con dati di tipo anagrafico non modificabile oppure dati storici non modificabili. Il secondo, stabilisce quale filegroup ospiterà le tabelle generate senza essere assegnate ad uno specifico FILEGROUP. Usualmente, il filegroup di default è PRIMARY.

Dato che il logical filename ovvero il nome del file dati e del file di log può essere ciò che vogliamo, se siamo particolarmente perfidi, oppure se abbiamo degli scopi “sinistri” ad esempio vogliamo fare in modo di non trovare i files di database del cliente quando fra un anno torneremo per un aggiornamento e ci saremo dimenticati il loro nome, possiamo anche dare ai files del database nomi che non hanno a che vedere con il nome del db stesso.

E’ altamente sconsigliato in quanto inutile ai fini della sicurezza, i programmatori paranoici si tolgano dalla testa di poter nascondere database SQL Server all’amministratore di sistema o non far vedere dati contenuti nel loro database ai proprietari del sistema. I DATI SONO DI PROPRIETA’ DEL CLIENTE.

Per inciso, i nomi dei file sono scritti sulle property del database e accessibili al system administrator.

Mettere nomi strani ai file dei database e spostarli su cartelle diverse da quelle standard per i database, può confondere chi magari trova dei files con nomi strani e sconosciuti sul disco (e magari li cancella dopo aver fermato tutti i servizi per fare manutenzione al server). E’ possibile, ma cercate di non farlo sono altre le cose a cui dovete pensare.

A proposito, se create altri file dati oltre al primo, l’estensione degli altri non sarà mdf ma ndf [azzardo un : NaltroDataFile, o NonmasterDataFile, NonsapevochenomedargliDataFile come traduzione della sigla ndf. Chi ha pazienza lo cerchi su Wikipedia e ce lo renda noto, grazie.]

Abbiamo esplorato la finestra principale della creazione database di Management studio, ma se osserviamo la nostra finestra iniziale, abbiamo qualcos’altro da studiare, ci sono due pagine, una si chiama Options, l’altra Filegroups, e visto che l’ultima sembra avere a che vedere con quello che abbiamo appena finito di discutere vediamola per prima:

createdb06

Possiamo vedere come sia una lista dei filegroups definiti per il database che ci permette, volendo, di aggiungere dei nuovi filegroup, a cui però dovremo poi assegnare dei files, pertanto, è forse più semplice creare prima i files e usare la dialog precedentemente spiegata per creare un nuovo filegroup. Questa finestra, che apparirà anche sulle property del database dopo la sua creazione, ci permetterà di fare manutenzione sui filegroup.

Passiamo alla finestra Options, dove ci sono alcuni parametri importanti che spiegheremo per quanto possibile ed altri egualmente importanti ma troppo specifici per questo articolo.

createdb07

Con le frecce rosse ho segnato i 4 parametri fondamentali del database, che possono essere predisposti alla sua creazione oppure modificati in seguito, anche se modificare in seguito alcuni di essi potrebbe non essere proprio una cosa saggia.

  1. Collation, se leggete i 2 post che parlano di Collation sul mio blog, potete rendervi conto di quanto questo oggetto dal nome oscuro in lingua italiana sia importante, perché stabilisce quale sarà il criterio primario per il confronto ed il sort di stringhe all’interno del nostro database. Usualmente per i nuovi database si imposta al valore della collation del server, come in questo caso, se però importiamo database da vecchi backup è opportuno verificare con quale collation sono stati creati e utilizzare la loro collation come valore per questo parametro. Vi rimando ancora al mio blog per capire un po’ di quelli che sono i problemi a cui andiamo incontro altrimenti.
  2. Recovery Model, può essere Full, Simple, Bulk Logged. Tutti i database di produzione, usualmente lavorano in Full recovery model, quelli di test potete farli lavorare in Simple, il Bulk Logged è una modalità usualmente temporanea utilizzata per l’importazione di grandi quantità di dati. Qual’è la differienza fra Full e Simple? La principale che “colpisce” tutti quelli che lavorano con metodo “Avanti > Avanti> Invio>” è trovarsi con un log delle transazioni molto più grande del database, non sapere perché e come fare a ridurlo. Un database in modalità Full, va sempre corredato da un opportuno piano di backup del database e del log delle transazioni, affinché funzioni in modo efficiente. Ma di questo non parleremo qui. Il recovery Model Simple può essere usato su tutti i database di test e quando il carico di lavoro e i dati modificati ogni giorno sono così pochi che la perdita di una giornata di lavoro potrebbe non essere significativa. [A mio avviso qualsiasi perdita di dati è significativa, però è un’opinione.]
  3. Compatibility Level, per i nuovi DB creati con 2005 è sempre 90 (9.0 la versione di SQL 2005). Per i vecchi DB potrebbe essere inferiore, ma la possiamo modificare innalzandola per i vecchi DB o abbassandola per i nostri nuovi DB in un caso per attivare le funzionalità aggiunte alla nuova versione di SQL Server, nell’altro se abbiamo la necessità di impedire che all’interno del Database, Viste, User defined Function e Stored Procedure, utilizzino funzionalità di SQL 2005 ma invece si fermino alle funzionalità di una versione inferiore (2000 o anche 7.0), questo ci permette eventuali connessioni ed interrogazioni da parte di sistemi di versioni precedenti senza problemi di compatibilità del codice SQL.
  4. Restrict Access, è un parametro molto utile dopo la creazione del database, tutti i parametri di questa pagina si possono modificare anche dopo aver creato il database e appaiono sulle Property dello stesso. Questo parametro in particolare può servirci ad esempio durante aggiornamenti delle strutture per impedire connessioni ad altri che all’utente che aggiorna il database, oppure ai soli administrator.

Abbiamo esaminato le cose che si possono decidere prima di creare un database e alcune che si possono usare per modificarlo in seguito utilizzando SQL Management Studio, però questo strumento è semplicemente una interfaccia utente amichevole che utilizza ciò che SQL Server mette a disposizione tramite le sue librerie, pertanto ogni operazione fatta da questa console può essere effettuata anche via Codice SQL.
La seconda parte del presente articolo dimostrerà come effettuare le stesse cose utilizzando uno script in SQL, e sull’intero Blog troverete articoli più o meno avanzati che parlano di come effettuare operazioni ordinarie o straordinarie su database SQL Server.