Il primo post di una serie che descriverà come creare un database, come generare oggetti al suo interno e come modificarli quando necessario utilizzando solo gli script in T-SQL
Introduzione
Creare un Database in SQL Server è una cosa molto facile da fare, basta utilizzare SQL Management studio in versione Developer o in versione Express e con pochi Click Del mouse abbiamo ottenuto un Database Perfetto. Se siamo sviluppatori e produciamo una applicazione che usa un Database SQL Server possiamo fornire il database vuoto già creato da noi con il setup dell’applicazione e fornire le istruzioni per restorarlo sul server di produzione dei clienti ed abbiamo risolto.
E se invece volessimo saperne di più, se volessimo poter calibrare il database in base all’installazione, se volessimo capire i concetti fondamentali da DBA legati alla gestione di un database, sapere come crearlo potrebbe essere una cosa interessante. Per fare questo tipo di Tuning sui database, è opportuno saperne di più. Con SQL Management studio possiamo fare tutto, ma SQL Management studio non fa altro che costruire degli Script T-SQL ed eseguirli, vediamo come costruirci uno script di base per creare qualsiasi database.
Per chi non avesse mai installato SQL Server, vi consiglio di guardare questo Webcast che mostra l’installazione di SQL Express 2008 Per capirne di più, prometto che appena ho un oretta da perdere faccio un Webcast per SQL 2012 ma sono sicura che i concetti di base di una installazione SQL Server non cambiano molto da versione a versione.
L’esempio che farò è stato creato su un server SQL 2005, ma funziona perfettamente su 2008 e 2012 perché anche nel caso degli script, i concetti di base non cambiano, possono solo esserci dei miglioramenti funzionali e delle nuove features fornite dalle versioni più recenti di SQL Server.
Com’è installato il mio SQL Server
Io lavoro su un PC Windows 7 Ultimate, ho installato sulla mia macchina la versione Developer di SQL Server 2005 che è fornita con la MSDN, è un SQL Server paragonabile alla versione Enterprise ma installabile su un sistema operativo non server. In ogni caso, gli script che vedremo funzionano allo stesso modo anche sulla versione Express di SQL Server che ha solo alcune limitazioni “fisiche” impostate, non ha limitazioni funzionali.
Le due caratteristiche base della mia installazione di SQL Server che ho adottato da quando usavo SQL Server 2000 sono le seguenti:
- Il server è installato in “Mixed Mode”, ovvero l’autenticazione utenti può essere effettuata sia tramite Active Directory (Trusted connection) e quindi mappatura di utenti e gruppi di Windows come Login di SQL Server che utilizzando l’autenticazione SQL Server, con uno Username ed una Password specifici del server dati.
- La cartella dati del server non si trova sulla mappatura di default, che la mette in una sottocartella della cartella “Programmi” di Windows, ma è mappata sul mio disco dati sulla cartella D:\Sql.dir\Data. Decidere dove vanno messi i dati da parte di SQL Server è una delle prime azioni da vero DBA che chiunque si avvicini a SQL Server e voglia usarlo per sviluppare applicazioni dovrebbe imparare.
Lo script in dettaglio
Iniziamo ora a creare il nostro script, e da bravi DB developer partiamo dalle cose fondamentali:
/* ID:2013.00.00.00_00 00-CreateDb.sql ******************************************************************************* Ricordarsi di sostituire ##DNWDbName## con il nome che si intende dare al DataBase E CORREGGERE IL NOME DELLA CARTELLA CHE CONTERRà I DATABASE PRIMA DI LANCIARE LO SCRIPT!!!!!!!!!!!!! ******************************************************************************* */ USE [master] GO
Per Iniziare bene il nostro script iniziamo a inserire un commento, sulla prima riga inseriamo un ID, a cosa ci serve? In questo particolare script a nulla, ma se come è naturale, i database vengono generati e poi si evolvono dovremo creare script di aggiornamento e di manutenzione del database, quindi dare ad ogni script un ID, inoltre un nome di file progressivo che ci ricordi che cosa lo script esegue anche solo guardando i file di una cartella ci aiuterà a costruire un sistema di manutenzione del database solido e coerente.
E il commento “Ricordarsi di sostituire ##DNWDbName## con il nome…” Ci serve perché per principio non è bello cablare nelle applicazioni il nome del Database, perché potremmo trovarci nella necessità di avere più database collegati alla nostra applicazione, perché potremo trovarci a dover installare il database su un server di produzione dove c’è la necessità di una specifica nomenclatura per i database e così via. Quindi iniziamo subito a pensare le cose in modo flessibile.
Veniamo al codice vero e proprio, USE [master], il solo vero comando di questo primo pezzo del nostro script, a cosa serve? E’ legato a quello che stiamo per fare, la creazione di un Database deve essere effettuata collegandosi al database master di SQL Server perché è master che gestisce gli oggetti che generiamo nel nostro database, e la generazione di un nuovo database va a modificare le tabelle di sistema inserendovi tutti i dati necessari al server per sapere dov’è e come è fatto il nuovo database.
E GO? che cosa fa GO? Ne troverete molti sparsi negli script di generazione e modifica dei database, servono a indicare a SQL Server che in quel punto, deve eseguire lo script prima di procedere all’operazione successiva, non solo, vi sono dei comandi, fra cui la creazione e la modifica di un database e degli oggetti che contiene, che pretendono di essere eseguiti in modo autonomo come uno script indipendente.
Seconda parte del nostro script, serve anche come prima dimostrazione di uso di controllo di flusso su uno script SQL, ci permette di generare un login SQL ovvero un utente all’interno del Server a cui assegneremo poi i diritti necessari ad accedere e utilizzare il database per inserire e modificare dati.
IF NOT EXISTS (select 1 from sys.syslogins where name = 'dnwappuser') BEGIN CREATE LOGIN [dnwappuser] WITH PASSWORD='dnwappuser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF END ELSE BEGIN Print 'Il login dnwappuser esiste gia' END GO
Cosa annotare su questa porzione di script? Dovete avere diritti amministrativi per eseguirla, quindi appartenere ad un ruolo SecurityAdmin oppure SysAdmin, sys.syslogins è una delle viste di sistema che permettono di effettuare delle query per vedere quali sono gli utenti che sono stati generati nel nostro server. La password, che si trova nella creazione del Login va ovviamente impostata ad un valore meno banale, il nome utente ‘dnwappuser’ è un altro parametro che potete modificare a piacere. Le due opzioni CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, indicano che la password non scade mai e che non c’è una policy che stabilisce lunghezza e complessità della stessa.
CREATE DATABASE [##DNWDbName##] ON PRIMARY (NAME = '##DNWDbName##', FILENAME = 'D:\SQL.DIR\Data\##DNWDbName##\##DNWDbName##.mdf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 50% ), FILEGROUP [BINARYDATA] (NAME = '##DNWDbName##_Bin', FILENAME = 'D:\SQL.DIR\Data\##DNWDbName##\##DNWDbName##_Bin.ndf', SIZE = 20MB, MAXSIZE = UNLIMITED, FILEGROWTH = 50% ) LOG ON (NAME = '##DNWDbName##_Log', FILENAME = 'D:\SQL.DIR\Data\##DNWDbName##\##DNWDbName##_Log.ldf', SIZE = 50MB, MAXSIZE = UNLIMITED, FILEGROWTH = 50%) GO
Finalmente la parte centrale dello script, lo script di creazione, per fare un esempio interessante, abbiamo predisposto la creazione di un database che contenga due Filegroup e due files per i dati principali, il Master Data File (PRIMARY) che conterrà tutte le tabelle e i dati di base, e il (BINARYDATA) a cui abbiamo assegnato questo nome perché in esso inseriremo tutti i dati di tipo BLOB del nostro database, quindi Image,Ntext,NVarchar(max),Varbinary(max). A cosa serve separare i dati normali dai dati di tipo binario? Per creare migliori piani di backup, per differenziare la crescita automatica e la massima dimensione del file dati, per poter se necessario mettere i files su dischi diversi.
Guardiamo un po’ le opzioni di generazione file, dove abbiamo inserito il nostro placeholder.
- NAME indica il nome logico del file dati all’interno del server.
- FILENAME indica il path ove questo file sarà creato e dove il server andrà a cercarlo quando necessario. Un Database avrà sempre un solo file .mdf e zero o più files .ndf. nonché uno o più files .ldf.
- SIZE indica la dimensione iniziale di ogni file su disco, è consigliabile assegnare una dimensione che non faccia crescere i file dati troppo spesso.
- MAXSIZE, indica fino a quale dimensione il file può crescere, è opportuno limitarlo ad una dimensione massima quando usiamo un SQL Server non Express, la versione express infatti ha una limitazione di 2, 4, 10 GB rispettivamente nelle versioni 2005, 2008 e 2012 di SQL Server. Limitare la massima crescita dei files o impedirne l’autocrescita serve quando il DBA vuole controllare personalmente le dimensioni dei database e modificarle in modo manuale, o quando lo spazio su disco potrebbe venire completamente occupato dai database, per evitare di trovarsi nella situazione di non avere neppure lo spazio per creare un Backup dei dati ed avere un blocco del sistema.
- FILEGROWTH indica di quanto far crescere automaticamente il file quando viene riempito di dati.
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [##DNWDbName##].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
Un ulteriore pezzetto di codice interessante, ci serve per attivare/disattivare la creazione degli indici full text su un database, ovviamente se l’opzione è installata e se abbiamo creato o creeremo gli indici full text sul database stesso.
USE [##DNWDbName##] GO IF NOT EXISTS (select 1 from sys.sysusers where name = 'db_workuser') CREATE ROLE [db_workuser] GO IF NOT EXISTS (select 1 from sys.sysusers where name = 'dnwappuser') CREATE USER [dnwappuser] FOR LOGIN [dnwappuser] WITH DEFAULT_SCHEMA=[dbo] GO EXEC sp_addrolemember 'db_datareader', 'dnwappuser' GO EXEC sp_addrolemember 'db_datawriter', 'dnwappuser' GO EXEC sp_addrolemember 'db_workuser', 'dnwappuser' GO
L’ultima porzione dello script, a cosa ci serve? a fare 3 cose:
- Creare un ruolo sul database che si chiama db_workuser, il ruolo è un oggetto a cui è possibile assegnare permessi sugli oggetti del database, ci potrà servire per assegnare ad esempio i permessi di esecuzione delle stored procedure o delle User defined functions che inseriremo nel nostro database, il ruolo poi può essere assegnato agli utenti per trasferire loro i permessi.
- Permettiamo al login creato all’inizio dello script di accedere al database, vi ricordo che il LOGIN è solo un modo per accedere al server, se il login non viene mappato come USER sul database non può accedervi.
- Infine assegnamo all’utente mappato sul database tre ruoli che gli daranno accesso ai dati, db_datareader, che permette di effettuare query di selezione su tutti gli oggetti. db_datawriter, che permette di aggiungere e modificare dati su tutti gli oggetti. db_workuser, il ruolo da noi creato, a cui assegneremo i permessi di esecuzione su stored procedure e user defined function o altri permessi diversi da quelli dei due ruoli predefiniti.
Conclusioni
Adesso sappiamo come generare un database in SQL Server, come creare un login di accesso, come creare un ruolo su un database, come assegnare uno o più ruoli e l’accesso ad un database ad un login.
Gli script T-SQL del codice di esempio sono scaricabili al seguente link e sono comprensivi di tutto quello che è spiegato nella breve serie di articoli.
