Creare e amministrare il database, creare utenti in SQL Server
Mục lục
Modifica i dati di sistema
Gli utenti non sono autorizzati ad aggiornare direttamente le informazioni negli oggetti del database di sistema, come tabelle di sistema, procedure di archivio di sistema e viste del catalogo (indice). ). Tuttavia, gli utenti possono sfruttare un set completo di strumenti amministrativi che consentono loro di amministrare l’intero sistema e gestire tutti gli utenti e gli oggetti del database come segue:
- Utilità di amministrazione di SSMS : da SQL Server 2015 in poi, diverse utilità di amministrazione di SQL Server sono integrate in SSMS. Questa è la console di amministrazione principale per un’installazione di SQL Server. Consente funzioni amministrative complete di alto livello, pianificazione delle attività di manutenzione ordinaria, ecc.
- API SQL Server Management Objects (SQL-SMO) : include funzionalità complete per la gestione delle applicazioni SQL Server
- Script e stored procedure Transact-SQL : si tratta di stored procedure di sistema e istruzioni DDL T-SQL
Database definito dall’utente
In SQL Server, gli utenti possono creare i propri database, chiamati database definiti dall’utente, e lavorare con essi. Lo scopo di questi database è di memorizzare i dati dell’utente.
Ogni istanza di SQL Server può contenere fino a 32767 database, ogni database può contenere fino a 32767 file. Quando si crea un database in SQL Server ci saranno almeno due file nel sistema operativo, un file di dati e un file di registro. I file di dati contengono dati e oggetti come tabelle, indici, stored procedure e viste. Il file di registro registra le modifiche al database allo scopo di archiviare e aiutare a ripristinare lo stato del database
Fondamentalmente il database di SQL Server dobbiamo solo preoccuparci di quei file, tutto ciò che appartiene a un database è incapsulato in questi file e puoi portare il database da un posto all’altro semplicemente copiando tutti questi file su un’altra macchina (ovviamente ce ne sono altri modi come backup/ripristino) e allegarli a SQL Server su quel server.
In sintesi, il database di SQL Server dispone di 2 tipi principali di file:
- Il file .mdf viene chiamato come file di database principale, contiene schema e dati
- Il file .ldf contiene i registri
- Inoltre, il database può utilizzare il secondo file di database, con estensione .ndf
Crea database con T-SQL (Crea database con T-SQL)
Sintassi completa (nota, le clausole in [ ] sono facoltative:
CREATE DATABASE DATABASE_NAME [ON [PRIMARY] [<filespec>[,...n] [,<filegroup>[,...n]] [LOGON {<filespec[,...n]}] ] [collate collation_name] [;]Spiegare:
- DATABASE_NAME: il nome del database da creare
- ON: indica dove è memorizzato il file nel disco rigido.
- PRIMARY: associato a <filespec> definisce i file primari
- <filespec> : controllo dell’attributo file
- <filegroup>: controlli dell’attributo <filegroup>
- LOG ON: indica il file di archivio per i file di log.
- COLLATE : specifica il codice di confronto per il database, Collation definisce le regole per confrontare e ordinare i dati dei caratteri in base a criteri locali e specifici della lingua (di solito scegliendo la raccolta in caso di necessità) il database può memorizzare dati unicode
Per esempio:
CREATE DATABASE [Customer] ON PRIMARY (NAME = 'Customer_DBX', FILENAME = 'C:DATACustomer_DB.mdf') LOG ON (NAME = 'Customer_DB_log', FILENAME = 'C:DATACustomer_DB_log.ldf') COLLATE SQL_Latin1_General_CP1_CI_ASDopo aver eseguito il comando SQL Server creerà un nuovo database con un hard file memorizzato nel percorso C:data
Aggiorna Esplora oggetti per visualizzare nuovamente il database Clienti
O più sinteticamente:
CREATE DATABASE [CustomerDB] -- Voi cach tao nay duong dan file database se do Microsoft SQL dinh nghia
Modifica database
Man mano che il database definito dall’utente aumenta o diminuisce, le dimensioni del database verranno espanse o ridotte automaticamente o manualmente. In base a requisiti che cambiano nel tempo, ci sono alcune situazioni in cui sono necessarie modifiche al database.
ALTER DATABASE database_name {<add_or_modify_files> | <add_or_modify_filegroups> | <set_database_options> | MODIFY NAME = new_database_name | COLLATE collation_name } [;]Spiegare:
- nome_database: nome db
- MODIFICA NOME = new_database_name: è il nuovo nome del database in cui vuoi cambiare
- collate collation_name: nome di confronto di db
- <add_or_modify_files>: aggiungi file, elimina file o modifica file
- <add_or_modify_filegroups>: i filegroup possono essere aggiunti, modificati o eliminati dal database
- <set_database_options>: è un’opzione a livello di database che influisce sulle proprietà del database che possono essere impostate per ciascun database. Queste opzioni sono univoche per ciascun database e non influiscono sugli altri database.
Esempio per modificare il nome del database:
ALTER DATABASE Customer MODIFY NAME = CusDBCrea utente SQL Server (crea utente SQL Server)
Per esempio:
-- Creates the login toanngo92 with password '1234'. CREATE LOGIN toanngo92 WITH PASSWORD = '1234'; GO -- Creates a database user for the login created above. CREATE USER toanngo92 FOR LOGIN toanngo92; GOProprietario del database (Proprietà del database)
In SQL Server è possibile modificare la proprietà del database definito dall’utente. La proprietà del database di sistema non può essere modificata. La procedura di sistema denominata sp_changedbowner viene utilizzata per modificare la proprietà del database.
Sintassi:
sp_changedbowner [@loginname=]'login'Con login come nome utente esistente.
Dopo l’esecuzione di sp_changedbowner , un nuovo proprietario (denominato dbo) verrà selezionato come proprietario del database. Dbo avrà l’autorità per gestire tutte le azioni del database. Il proprietario di master, modello, tempdb (db nel database di sistema) non può cambiare proprietario.
Per esempio:
use 'CusDB' sp_changedbowner 'toanngo92'Dopo aver eseguito il comando, il database CusDB ha cambiato il suo proprietario in “toanngo92”, quindi l’account di accesso denominato “toanngo92” può manipolare direttamente il database CusDB
Impostazione delle opzioni del database
Le opzioni a livello di database specificano le proprietà del database e possono essere impostate per ciascun database. Queste opzioni sono univoche per ogni database e non influiscono sugli altri database. Alle opzioni del database vengono assegnati valori predefiniti al momento della creazione del database, che possono quindi essere modificati utilizzando la clausola SET nell’istruzione ALTER DATABASE .
Opzioni del database in SQL Server
| Tipo di opzione | descrizione |
| Opzioni automatiche | Controlla il comportamento automatico del database |
| Opzioni del cursore | Controllo del comportamento del cursore |
| Opzioni di recupero | Controllare il modello di ripristino (fallback) del database |
| Opzioni varie | ANSI Controllo standard |
| Opzioni di stato | Controlla lo stato del database, ad esempio online/offline, connessione utente |
Nota: configurare le impostazioni del database tramite la procedura denominata sp_configure system stored procedure o SQL Management Studio.
Esempio di esecuzione dell’opzione AUTO_SHRINK per il database CusDB su ON. Opzioni quando attivate, il database si ridurrà/rimpicciolirà automaticamente per risparmiare più memoria (ma questa funzione non è consigliata)
Documenti AUTO_SHRINK: https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/set-the-auto-shrink-database-option-to-off?view=sql-server -2017
USE 'CusDB' ALTER DATABASE 'CusDB' SET AUTO_SHRINK ONFilegroup
In SQL Server, i file di dati nel disco rigido vengono utilizzati per archiviare il database. I file di dati possono essere ulteriormente suddivisi in gruppi di file per migliorare le prestazioni. Ciascun gruppo di file (filegroup_ utilizzato per raggruppare i file correlati memorizza un oggetto database. Ogni database ha un gruppo di file principale per impostazione predefinita. Questo gruppo di file contiene file di dati. Il gruppo di file principale e il file di dati vengono generati automaticamente con valori di attributo predefiniti al momento dell’amministrazione, dell’allocazione dei dati e delle finalità di localizzazione.
Ad esempio, ci sono 3 file denominati customer_data1.ndf , customer_data2.ndf , customer_data3.ndf , che possono essere creati su 3 dischi rigidi corrispondenti, possono essere assegnati a 1 filegroup denominato customer_fgroup1. è quindi possibile creare una tabella specificatamente nel gruppo di file customer_fgroup1. Quindi è possibile creare una tabella specificatamente su filgroup customer_fgroup1. Le query per i dati dalla tabella verranno distribuite su tre unità migliorando ulteriormente le prestazioni.
Aggiungi Filegroup al database corrente
I filegroup possono essere creati quando il database viene creato per la prima volta o possono essere creati dopo che i file sono stati inseriti nel database. Tuttavia, i file non possono essere spostati in un altro filegroup dopo che il file è stato incluso nel database.
Il file non può essere un membro di più di un filegroup contemporaneamente. Sono consentiti solo un massimo di 32.767 filegroup per database. I filegroup possono contenere solo file di dati. Il file di registro delle transazioni non può appartenere al filegroup.
Esempio di creazione di un filegroup che inserisce il filegroup nel database durante l’inizializzazione:
CREATE DATABASE [SalesDB] ON PRIMARY (NAME = 'SalesDB' , FILENAME = 'C:dataSalesDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), FILEGROUP [MyFileGroup] (NAME = 'SalesDB_FG', FILENAME='C:dataSalesDB_FG.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) LOG ON (NAME = 'SalesDB_log', FILENAME='C:dataSalesDB_log.ldf', SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS
Sintassi quando si inserisce il filegroup nel database corrente:
ALTER DATABASE database_name (<add_or_modify_files> |<add_or_modify_filegroups> |<set_database_options> |MODIFY NAME=new_database_name |COLLATE collation_name )[;]Per esempio:
USE 'CusDB' ALTER DATABASE 'CusDB' ADD FILEGROUP FG_ReadonlyDopo l’esecuzione del comando, SQL Server visualizza il messaggio “Comando(i) completato correttamente” e il filegroup FG_Readonly è incluso nel database corrente come “CusDB”.
Gruppo di file predefinito
Gli oggetti vengono assegnati al filegroup predefinito quando vengono creati nel database. Filegroup PRIMARY è il filegroup predefinito. Il filegroup predefinito può essere modificato utilizzando l’istruzione ALTER DATABASE. Gli oggetti di sistema e tabella rimangono nel filegroup PRIMARY, sebbene ALTER non entri ancora nel nuovo filegroup.
Esempio di come creare un nuovo file, inserire il filegroup FG_ReadOnly e impostare FG_ReadOnly come filegroup predefinito
USE 'CusDB' ALTER DATABASE 'CusDB' ADD FILE (NAME = CusDB1, FILENAME = 'C:dataCusDB1.ndf') TO FILEGROUP FG_ReadOnly ALTER DATABASE CusDB MODIFY FILEGROUP FG_Readonly DefaultRegistro delle transazioni
Il registro delle transazioni in SQL Server registra tutte le transazioni e le modifiche nel database creato da ciascuna transazione. Il registro delle transazioni è un componente importante nel database. Può essere l’unica soluzione per accedere all’origine dati recente in caso di guasto del sistema
Il registro delle transazioni supporta le seguenti operazioni:
- Viene eseguito il rollback di una transazione incompleta o il motore di database rileva un errore. I log vengono utilizzati per ripristinare le modifiche.
- Se il server che esegue SQL Server si guasta, il database potrebbe trovarsi in uno stato incoerente. Quando un’istanza di SQL Server viene avviata, esegue un ripristino di ogni database.
- Il database può essere ripristinato fino al punto di errore dopo che la perdita di dati hardware ha interessato i file del database.
- Log Reader Agent monitora il registro delle transazioni di ogni database configurato per replicare le transazioni
- Il server di backup, il mirroring del database e le soluzioni di log shipping dipendono dal registro delle transazioni.
Lavorare con i registri delle transazioni:
I database di SQL Server hanno almeno un file di dati e un file di registro delle transazioni. I dati e le informazioni del registro delle transazioni vengono archiviati separatamente, preferibilmente su unità separate. Questi file vengono utilizzati da un database.
SQL Server utilizza il registro delle transazioni di ogni database per eseguire il rollback delle transazioni. Il registro delle transazioni è un record seriale che memorizza tutte le modifiche che si sono verificate nel database, nonché le transazioni che hanno apportato le modifiche. Questo registro contiene informazioni sufficienti per annullare le modifiche apportate in ciascuna transazione. Il registro delle transazioni registra l’allocazione e l’allocazione delle pagine, nonché il commit o il rollback di ciascuna transazione. Questa funzionalità consente a SQL Server una maggiore flessibilità nel ripristino dello stato dei dati.
Il rollback di ogni transazione può essere implementato nei seguenti modi:
- Le transazioni vengono spostate in avanti quando viene applicato il registro delle transazioni
- Viene eseguito il rollback di una transazione quando viene eseguito il backup di una transazione incompleta.
Aggiungi i file di registro al database
Sintassi per modificare il database e aggiungere file di registro:
ALTER DATABASE database_name ( ... ) [;] <add_or_modify_files>::= {ADD FILE <filespec>[,...n] [TO FILEGROUP {filegroup_name|DEFAULT}] | ADD LOG FILE <filespec>[,...n] | REMOVE FILE logical_file_name | MODIFY FILE <filespec> }Crea database utilizzando SSMS
Passaggi per creare un database utilizzando SSMS:
- In Esplora oggetti, connettiti all’istanza di Motore di database di SQL Server, quindi espandi l’istanza facendo clic sull’icona “+” accanto all’istanza.
- Fare clic con il pulsante destro del database, fare clic su Nuovo database come mostrato nella Figura 1.1
- Nella finestra di dialogo Nuovo database, immettere il nome del database che si desidera creare
- Se il database viene creato con valori predefiniti, fare clic su OK per terminare. In caso contrario, continuare a scegliere i parametri, configurare il database come mostrato in Figura 1.2
- Per modificare il nome del proprietario, fare clic sul pulsante […] e selezionare un altro proprietario
- Per modificare i valori di default dei dati primari e dei file di registro delle transazioni, nella tabella File di database, fare clic sulla cella corrispondente e inserire il valore.
- Per modificare le regole di confronto del database, fare clic sulla scheda delle opzioni , quindi selezionare le regole di confronto dall’elenco come mostrato nella Figura 1.3
- Per modificare il modello di ripristino, selezionare la scheda delle opzioni, quindi selezionare il modello di ripristino dalla casella di selezione come mostrato nella Figura 1.4
- Per modificare altre opzioni del database, modificare le informazioni nella scheda delle opzioni.
- Per aggiungere un filegroup moiws, fare clic sulla scheda Filegroup. Il passaggio successivo consiste nel fare clic sul pulsante Aggiungi , quindi inserire il filegroup lieej come mostrato nella Figura 1.5
- Per aggiungere proprietà nanag elevate al database, selezionare la scheda Proprietà estese
- Nella colonna Nome immettere un nome per la proprietà extend
- Nella colonna Valore immettere un valore per la proprietà extend. Ad esempio, possiamo compilare 1,2 righe per descrivere il database.
- Fare clic su OK per creare il database
Elimina database (elimina database)
Prima di eliminare DATABASE, assicurati di conservare alcuni backup recenti se il database è importante, questa è la regola in tutti i casi. Il database eliminato può essere ricreato solo ripristinando il backup.
Elimina la sintassi del database:
DROP DATABASE [databasename]Per eliminare il database utilizzando SSMS, procedere come segue:
- In Esplora oggetti, connettiti all’istanza di Motore di database di SQL Seerver, quindi espandi l’istanza premendo il segno “+”.
- Dopo l’espansione, selezionare il database, fare clic con il pulsante destro del mouse e fare clic su Elimina
- Assicurati che il database sia selezionato, fai clic su ELIMINA
Crea snapshot del database
Lo snapshot del database è una funzionalità introdotta da SQL Server 215. Questa funzionalità fornisce una visualizzazione stic di sola lettura per il database SQL. Se l’utente manipola ed esegue il comando sbagliato, causando il fallimento del database, il database di origine tornerà allo stato al momento della creazione dello snapshot. SSMS non supporta la creazione di snapshot, ma a tale scopo deve utilizzare istruzioni T-SQL.
Vantaggi delle istantanee:
- Fornire una versione di copia dei dati comoda e di sola lettura
- Quando richiesto, nessun degrado delle prestazioni
- Il file snapshot è leggero e veloce da inizializzare
Svantaggi delle istantanee:
- Impossibile creare il backup dell’istantanea
- Lo snapshot deve esistere sullo stesso server di database dell’origine del database
- Ai nuovi utenti non possono essere assegnati diritti di accesso ai dati nello snapshot
Sintassi:
CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name , FILNAME = 'os_file_name' )[,...n] AS SNAPSHOT OF source_database_name [;]Spiegare:
- database_snapshot_name: nome dell’istantanea del database
- ON (NAME = logical_file_new, FILENAME = ‘os_file_name’): elenco di file nel database di origine. Affinché gli snapshot funzionino, tutti i file di dati devono essere identificati in modo univoco
- COME ISTANTANEA DI nome_database_origine: database di origine denominato nome_database_origine
Esempio di creazione di uno snapshot per il database AdventureWorks2019:
CREATE DATABASE AdvventureWorks_snapshot on (NAME = AdventureWorks2017, FILENAME = 'C:dataAdventureWorks_snapshot.ss') AS SNAPSHOT OF AdventureWorks2019; GOIl motivo per cui la dichiarazione su NAME = AdventureWorks2017 e non 2019 è perché 2019 riceverà un errore, leggi questi documenti:
Risultato:
