Erstellen und verwalten Sie die Datenbank, erstellen Sie Benutzer in SQL Server
Mục lục
Systemdaten ändern
Benutzern ist es nicht gestattet, Informationen in Systemdatenbankobjekten wie Systemtabellen, Systemspeicherprozeduren und Katalogansichten (Inhaltsverzeichnis) direkt zu aktualisieren. Benutzer können jedoch einen vollständigen Satz von Verwaltungstools nutzen, mit denen sie das gesamte System verwalten und alle Benutzer und Datenbankobjekte wie folgt verwalten können:
- SSMS-Verwaltungsdienstprogramme : Ab SQL Server 2015 sind mehrere SQL Server-Verwaltungsdienstprogramme in SSMS integriert. Dies ist die zentrale Verwaltungskonsole für eine SQL Server-Installation. Es ermöglicht vollständige Verwaltungsfunktionen auf hoher Ebene, die Planung routinemäßiger Wartungsaufgaben usw.
- SQL Server Management Objects (SQL-SMO) API : Enthält vollständige Funktionen zur Verwaltung von SQL Server-Anwendungen
- Transact-SQL-Skripts und gespeicherte Prozeduren : Dies sind gespeicherte Systemprozeduren und T-SQL-DDL-Anweisungen
Benutzerdefinierte Datenbank
In SQL Server können Benutzer ihre eigenen Datenbanken erstellen, die als benutzerdefinierte Datenbanken bezeichnet werden, und mit ihnen arbeiten. Zweck dieser Datenbanken ist die Speicherung von Benutzerdaten.
Jede SQL Server-Instanz kann bis zu 32767 Datenbanken enthalten, jede Datenbank enthält bis zu 32767 Dateien. Wenn Sie eine Datenbank in SQL Server erstellen, gibt es mindestens zwei Dateien auf dem Betriebssystem, eine Datendatei und eine Protokolldatei. Datendateien enthalten Daten und Objekte wie Tabellen, Indizes, gespeicherte Prozeduren und Ansichten. Die Protokolldatei zeichnet Datenbankänderungen auf, um die Rückgabe von Datenbankzuständen zu speichern und zu unterstützen
Grundsätzlich müssen wir uns bei der SQL Server-Datenbank nur um diese Dateien kümmern, alles, was zu einer Datenbank gehört, ist in diesen Dateien gekapselt, und Sie können die Datenbank von einem Ort zum anderen bringen, indem Sie einfach alle diese Dateien auf einen anderen Computer kopieren (natürlich gibt es andere Methoden wie Sicherung/Wiederherstellung) und hängen Sie sie an SQL Server auf diesem Server an.
Zusammenfassend hat die SQL Server-Datenbank zwei Hauptdateitypen:
- .mdf -Datei wird als Hauptdatenbankdatei bezeichnet, enthält Schema und Daten
- Die .ldf -Datei enthält Protokolle
- Außerdem kann die Datenbank die zweite Datenbankdatei mit der Erweiterung .ndf verwenden
Datenbank mit T-SQL erstellen (Datenbank mit T-SQL erstellen)
Vollständige Syntax (Hinweis, Klauseln in [ ] sind optional:
CREATE DATABASE DATABASE_NAME [ON [PRIMARY] [<filespec>[,...n] [,<filegroup>[,...n]] [LOGON {<filespec[,...n]}] ] [collate collation_name] [;]Erklären:
- DATABASE_NAME: Der Name der zu erstellenden Datenbank
- EIN: Zeigt an, wo die Datei auf der Festplatte gespeichert ist.
- PRIMARY: zugeordnet zu <filespec> definiert primäre Dateien
- <filespec> : Kontrolle über das Dateiattribut
- <filegroup>: Steuerelemente des <filegroup> .-Attributs
- LOG ON: gibt die Archivdatei für die Protokolldateien an.
- COLLATE : gibt den Kollationscode für die Datenbank an, Collation definiert die Regeln zum Vergleichen und Sortieren von Zeichendaten basierend auf lokalen und sprachspezifischen Kriterien (normalerweise wird bei Bedarf Kollatierung gewählt). Die Datenbank kann Unicode-Daten speichern
Zum Beispiel:
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_ASNach dem Ausführen des Befehls erstellt SQL Server eine neue Datenbank mit einer festen Datei, die im Pfad C:data gespeichert ist.
Aktualisieren Sie den Objekt-Explorer, um die Kundendatenbank erneut anzuzeigen
Oder kürzer:
CREATE DATABASE [CustomerDB] -- Voi cach tao nay duong dan file database se do Microsoft SQL dinh nghia
Datenbanken ändern
Wenn die benutzerdefinierte Datenbank wächst oder schrumpft, wird die Datenbankgröße automatisch oder manuell erweitert oder reduziert. Aufgrund von Anforderungen, die sich im Laufe der Zeit ändern, gibt es einige Situationen, in denen Datenbankänderungen erforderlich sind.
ALTER DATABASE database_name {<add_or_modify_files> | <add_or_modify_filegroups> | <set_database_options> | MODIFY NAME = new_database_name | COLLATE collation_name } [;]Erklären:
- Datenbankname: Datenbankname
- MODIFY NAME = new_database_name: ist der neue Datenbankname, zu dem Sie wechseln möchten
- collate collation_name: Kollationsname von db
- <add_or_modify_files>: Dateien hinzufügen, Dateien löschen oder Dateien bearbeiten
- <add_or_modify_filegroups>: Dateigruppen können der Datenbank hinzugefügt, bearbeitet oder gelöscht werden
- <set_database_options>: ist eine Option auf Datenbankebene, die sich auf Datenbankeigenschaften auswirkt, die für jede Datenbank festgelegt werden können. Diese Optionen sind für jede Datenbank einzigartig und wirken sich nicht auf andere Datenbanken aus.
Beispiel zum Bearbeiten des Datenbanknamens:
ALTER DATABASE Customer MODIFY NAME = CusDBSQL Server-Benutzer erstellen (SQL Server-Benutzer erstellen)
Zum Beispiel:
-- 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; GODatenbankbesitzer (Datenbankbesitz)
In SQL Server kann der Besitz der benutzerdefinierten Datenbank geändert werden. Der Besitz der Systemdatenbank kann nicht geändert werden. Die Systemprozedur namens sp_changedbowner wird verwendet, um den Besitz der Datenbank zu ändern.
Syntax:
sp_changedbowner [@loginname=]'login'Mit Login als vorhandenem Benutzernamen.
Nachdem sp_changedbowner ausgeführt wurde, wird ein neuer Eigentümer (namens dbo) als Eigentümer der Datenbank ausgewählt. Dbo hat die Berechtigung, alle Datenbankaktionen durchzuführen. Der Besitzer von master, model, tempdb (db in der Systemdatenbank) kann den Besitzer nicht ändern.
Zum Beispiel:
use 'CusDB' sp_changedbowner 'toanngo92'Nach dem Ausführen des Befehls hat die CusDB-Datenbank ihren Besitzer in „toanngo92“ geändert, dann kann das Anmeldekonto mit dem Namen „toanngo92“ die CusDB-Datenbank direkt manipulieren
Datenbankoptionen einstellen
Optionen auf Datenbankebene geben Datenbankeigenschaften an und können für jede Datenbank festgelegt werden. Diese Optionen sind für jede Datenbank eindeutig und wirken sich nicht auf andere Datenbanken aus. Den Datenbankoptionen werden beim Erstellen der Datenbank Standardwerte zugewiesen, die dann mithilfe der SET- Klausel in der ALTER DATABASE-Anweisung geändert werden können.
Datenbankoptionen in SQL Server
| Optionstyp | Bezeichnung |
| Automatische Optionen | Steuern Sie das automatische Verhalten der Datenbank |
| Cursor-Optionen | Steuerung des Cursorverhaltens |
| Wiederherstellungsoptionen | Steuern Sie das Wiederherstellungsmodell (Fallback) der Datenbank |
| Verschiedene Optionen | ANSI Standardsteuerung |
| Staatliche Optionen | Kontrollieren Sie den Status der Datenbank, z. B. online/offline, Benutzerverbindung |
Hinweis: Konfigurieren Sie die Datenbankeinstellungen über die Prozedur namens sp_configure system Stored Procedure oder SQL Management Studio.
Beispiel für die Ausführung der AUTO_SHRINK-Option für die CusDB-Datenbank auf ON. Optionen Wenn aktiviert, wird die Datenbank automatisch verkleinert / verkleinert, um mehr Speicher zu sparen (diese Funktion wird jedoch nicht empfohlen)
Dokumente 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 ONDateigruppen
In SQL Server werden die Datendateien auf der Festplatte zum Speichern der Datenbank verwendet. Datendateien können weiter in Dateigruppen unterteilt werden, um die Leistung zu verbessern. Jede Dateigruppe (Dateigruppe_, die zum Gruppieren zusammengehöriger Dateien verwendet wird, speichert ein Datenbankobjekt. Jede Datenbank hat standardmäßig eine Hauptdateigruppe. Diese Dateigruppe enthält Datendateien. Die Hauptdateigruppe und die Datendatei werden automatisch mit Standardattributwerten erstellt zum Zeitpunkt der Verwaltung, Datenzuordnung und Standortbestimmung.
Beispielsweise gibt es 3 Dateien namens customer_data1.ndf , customer_data2.ndf , customer_data3.ndf , die auf 3 entsprechenden Festplatten erstellt werden können, sie können 1 Dateigruppe namens customer_fgroup1 zugewiesen werden. eine Tabelle kann dann speziell für die Dateigruppe customer_fgroup1 erstellt werden. Dann kann eine Tabelle speziell für Filgroup customer_fgroup1 erstellt werden. Abfragen für Daten aus der Tabelle werden auf drei Laufwerke verteilt, wodurch die Leistung weiter verbessert wird.
Dateigruppen zur aktuellen Datenbank hinzufügen
Dateigruppen können erstellt werden, wenn die Datenbank zum ersten Mal erstellt wird, oder sie können erstellt werden, nachdem Dateien in die Datenbank eingefügt wurden. Dateien können jedoch nicht in eine andere Dateigruppe verschoben werden, nachdem die Datei in die Datenbank aufgenommen wurde.
Die Datei kann nicht gleichzeitig Mitglied mehrerer Dateigruppen sein. Pro Datenbank sind nur maximal 32.767 Dateigruppen zulässig. Dateigruppen können nur Datendateien enthalten. Die Transaktionsprotokolldatei darf nicht zur Dateigruppe gehören.
Beispiel für das Erstellen einer Dateigruppe, die die Dateigruppe während der Initialisierung in die Datenbank stellt:
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
Syntax beim Einfügen der Dateigruppe in die aktuelle Datenbank:
ALTER DATABASE database_name (<add_or_modify_files> |<add_or_modify_filegroups> |<set_database_options> |MODIFY NAME=new_database_name |COLLATE collation_name )[;]Zum Beispiel:
USE 'CusDB' ALTER DATABASE 'CusDB' ADD FILEGROUP FG_ReadonlyNachdem der Befehl ausgeführt wurde, zeigt SQL Server die Meldung „Befehl(e) erfolgreich abgeschlossen“ an und die Dateigruppe „FG_Readonly“ wird in die aktuelle Datenbank als „CusDB“ aufgenommen.
Standarddateigruppe
Objekte werden der Standarddateigruppe zugewiesen, wenn sie in der Datenbank erstellt werden. Dateigruppe PRIMARY ist die Standarddateigruppe. Die Standarddateigruppe kann mit der ALTER DATABASE-Anweisung geändert werden. System- und Tabellenobjekte verbleiben in der PRIMARY-Dateigruppe, obwohl ALTER immer noch nicht in die neue Dateigruppe eintritt.
Beispiel zum Erstellen einer neuen Datei, Einfügen in die Dateigruppe FG_ReadOnly und Festlegen von FG_ReadOnly als Standarddateigruppe
USE 'CusDB' ALTER DATABASE 'CusDB' ADD FILE (NAME = CusDB1, FILENAME = 'C:dataCusDB1.ndf') TO FILEGROUP FG_ReadOnly ALTER DATABASE CusDB MODIFY FILEGROUP FG_Readonly DefaultTransaktionsprotokoll
Das Transaktionsprotokoll in SQL Server zeichnet alle Transaktionen und Änderungen in der Datenbank auf, die von jeder Transaktion erstellt werden. Das Transaktionsprotokoll ist eine wichtige Komponente in der Datenbank. Es kann die einzige Lösung sein, um im Falle eines Systemausfalls auf aktuelle Datenquellen zuzugreifen
Das Transaktionsprotokoll unterstützt die folgenden Vorgänge:
- Eine unvollständige Transaktion wird rückgängig gemacht oder die Datenbank-Engine erkennt einen Fehler. Protokolle werden verwendet, um Änderungen rückgängig zu machen.
- Wenn der Server, auf dem SQL Server ausgeführt wird, ausfällt, befindet sich die Datenbank möglicherweise in einem inkonsistenten Zustand. Wenn eine Instanz von SQL Server gestartet wird, führt sie eine Wiederherstellung jeder Datenbank aus.
- Die Datenbank kann bis zum Ausfallpunkt wiederhergestellt werden, nachdem sich ein Hardwaredatenverlust auf die Datenbankdateien ausgewirkt hat.
- Der Protokollleseagent überwacht das Transaktionsprotokoll jeder konfigurierten Datenbank, um Transaktionen zu replizieren
- Sicherungsserver, Datenbankspiegelung und Protokollversandlösungen hängen vom Transaktionsprotokoll ab.
Arbeiten mit Transaktionsprotokollen:
SQL Server-Datenbanken haben mindestens eine Datendatei und eine Transaktionsprotokolldatei. Transaktionsprotokolldaten und -informationen werden separat gespeichert, vorzugsweise auf separaten Laufwerken. Diese Dateien werden von einer Datenbank verwendet.
SQL Server verwendet das Transaktionsprotokoll jeder Datenbank, um Transaktionen rückgängig zu machen. Das Transaktionsprotokoll ist ein fortlaufender Datensatz, der alle Änderungen speichert, die in der Datenbank aufgetreten sind, sowie die Transaktionen, die die Änderungen vorgenommen haben. Dieses Protokoll enthält genügend Informationen, um die in jeder Transaktion vorgenommenen Änderungen rückgängig zu machen. Das Transaktionsprotokoll zeichnet die Zuweisung und Zuweisung von Seiten sowie den Commit oder Rollback jeder Transaktion auf. Diese Funktion ermöglicht SQL Server mehr Flexibilität beim Wiederherstellen des Datenstatus.
Das Rollback jeder Transaktion kann auf folgende Weise implementiert werden:
- Transaktionen werden nach vorne verschoben, wenn das Transaktionsprotokoll angewendet wird
- Eine Transaktion wird zurückgesetzt, wenn eine unvollständige Transaktion gesichert wird.
Protokolldateien zur Datenbank hinzufügen
Syntax zum Bearbeiten der Datenbank und Hinzufügen von Protokolldateien:
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> }Erstellen Sie eine Datenbank mit SSMS
Schritte zum Erstellen einer Datenbank mit SSMS:
- Stellen Sie im Objekt-Explorer eine Verbindung mit der Instanz von SQL Server Database Engine her und erweitern Sie dann die Instanz, indem Sie auf das Symbol „+“ neben der Instanz klicken.
- Klicken Sie mit der rechten Maustaste auf die Datenbank, klicken Sie auf Neue Datenbank , wie in Abbildung 1.1 gezeigt
- Geben Sie im Dialogfeld Neue Datenbank den Namen der Datenbank ein, die Sie erstellen möchten
- Wenn die Datenbank mit Standardwerten erstellt wurde, klicken Sie zum Beenden auf OK . Wenn nicht, fahren Sie mit der Auswahl der Parameter fort und konfigurieren Sie die Datenbank wie in Abbildung 1.2 gezeigt
- Um den Eigentümernamen zu ändern, klicken Sie auf die Schaltfläche […] und wählen Sie einen anderen Eigentümer aus
- Um die Standardwerte von Primärdaten und Transaktionsprotokolldateien zu ändern, klicken Sie in der Tabelle Datenbankdateien auf die entsprechende Zelle und geben Sie den Wert ein.
- Um die Sortierung der Datenbank zu ändern, klicken Sie auf die Registerkarte „ Optionen “ und wählen Sie dann die Sortierung aus der Liste aus, wie in Abbildung 1.3 gezeigt
- Um das Wiederherstellungsmodell zu ändern, wählen Sie die Registerkarte „Optionen“ und dann das Wiederherstellungsmodell aus dem Auswahlfeld aus, wie in Abbildung 1.4 gezeigt
- Um andere Datenbankoptionen zu ändern, bearbeiten Sie die Informationen auf der Registerkarte „Optionen“.
- Um eine Dateigruppe hinzuzufügen, klicken Sie auf die Registerkarte Dateigruppen. Der nächste Schritt besteht darin, auf die Schaltfläche Hinzufügen zu klicken und dann die lieej-Dateigruppe einzugeben, wie in Abbildung 1.5 gezeigt
- Um der Datenbank High-Nanag-Eigenschaften hinzuzufügen, wählen Sie die Registerkarte Erweiterte Eigenschaften aus
- Geben Sie in der Spalte Name einen Namen für die Erweiterungseigenschaft ein
- Geben Sie in der Spalte Wert einen Wert für die Erweiterungseigenschaft ein. Zum Beispiel können wir 1,2 Zeilen ausfüllen, um die Datenbank zu beschreiben.
- Klicken Sie auf OK , um die Datenbank zu erstellen
Datenbank löschen (Datenbank löschen)
Stellen Sie vor dem Löschen von DATABASE sicher, dass Sie einige aktuelle Sicherungen aufbewahren, wenn die Datenbank wichtig ist. Dies ist in allen Fällen die Regel. Gelöschte Datenbanken können nur durch Wiederherstellen der Sicherung wiederhergestellt werden.
Syntax zum Löschen der Datenbank:
DROP DATABASE [databasename]Gehen Sie wie folgt vor, um die Datenbank mit SSMS zu löschen:
- Stellen Sie im Objekt-Explorer eine Verbindung mit der Instanz der SQL Seserver-Datenbank-Engine her und erweitern Sie dann die Instanz, indem Sie auf das „+“-Zeichen drücken.
- Wählen Sie nach dem Erweitern die Datenbank aus, klicken Sie mit der rechten Maustaste und klicken Sie auf Löschen
- Stellen Sie sicher, dass die Datenbank ausgewählt ist, klicken Sie auf LÖSCHEN
Erstellen Sie Datenbank-Snapshots
Der Datenbank-Snapshot ist eine Funktion, die mit SQL Server 215 eingeführt wurde. Diese Funktion bietet eine schreibgeschützte, statische Ansicht für die SQL-Datenbank. Wenn der Benutzer den falschen Befehl manipuliert und ausführt, wodurch die Datenbank fehlschlägt, wechselt die Quelldatenbank zurück in den Zustand, in dem der Snapshot erstellt wurde. SSMS unterstützt das Erstellen von Snapshots nicht, muss dazu aber T-SQL-Anweisungen verwenden.
Vorteile von Schnappschüssen:
- Stellen Sie eine bequeme und schreibgeschützte Kopie der Daten bereit
- Bei Abfrage kein Leistungsabfall
- Snapshot-Datei ist leicht und schnell zu initialisieren
Nachteile von Schnappschüssen:
- Snapshot-Sicherung kann nicht erstellt werden
- Snapshot muss auf demselben Datenbankserver wie die Quelle der Datenbank vorhanden sein
- Neuen Benutzern können im Snapshot keine Datenzugriffsrechte zugewiesen werden
Syntax:
CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name , FILNAME = 'os_file_name' )[,...n] AS SNAPSHOT OF source_database_name [;]Erklären:
- database_snapshot_name: Name des Datenbank-Snapshots
- ON (NAME = logical_file_new, FILENAME = ‚os_file_name‘): Liste der Dateien in der Quelldatenbank. Damit Snapshots funktionieren, müssen alle Datendateien eindeutig identifiziert werden
- AS SNAPSHOT OF source_database_name: Quelldatenbank mit dem Namen source_database_name
Beispiel für das Erstellen eines Snapshots für die AdventureWorks2019-Datenbank:
CREATE DATABASE AdvventureWorks_snapshot on (NAME = AdventureWorks2017, FILENAME = 'C:dataAdventureWorks_snapshot.ss') AS SNAPSHOT OF AdventureWorks2019; GODer Grund, warum die Aussage zu NAME = AdventureWorks2017 und nicht zu 2019 darin besteht, dass 2019 einen Fehler erhält, lesen Sie diese Dokumentation:
Ergebnis:
Bài viết liên quan:
Dịch vụ thiết kế Wesbite
