Khởi tạo và quản trị database, tạo user trong SQL Server - Web888 chia sẻ kiến thức lập trình, kinh doanh, mmo

Tóm Tắt

Sửa đổi dữ liệu hệ thống

Người dùng không được phép update trực tiếp thông tin trong những đối tượng người dùng cơ sở tài liệu mạng lưới hệ thống, ví dụ điển hình như bảng mạng lưới hệ thống ( system tables ), thủ tục tàng trữ mạng lưới hệ thống ( system store procedures ) và catalog views ( mục lục ). Tuy nhiên, người dùng hoàn toàn có thể tận dụng một bộ công cụ quản trị hoàn hảo được cho phép họ quản trị hàng loạt mạng lưới hệ thống và quản trị tổng thể người dùng và những đối tượng người dùng cơ sở tài liệu như sau :

SSMS Administration ultilities: Từ SQL Server 2015 trở đi, một số tiện ích quản trị SQL Server được tích hợp vào SSMS. Đây là bảng điều khiển quản trị cốt lõi cho cài đặt SQL Server. Nó cho phép hoàn thiện các chức năng quản trị cấp cao, lên lịch các nhiệm vụ bảo trì định kỳ, v.v.
SQL Server Management Objects (SQL-SMO) API: bao gồm các tính năng hoàn chỉnh để quản trị ứng dụng SQL Server
Transact-SQL scripts and stored procedures: đây là những store procedure hệ thống và các câu lệnh DDL T-SQL

User-defined database

Trong SQL Server, nguời dùng hoàn toàn có thể tự tạo database, được gọi là user-defined database và thao tác với chúng. Mục đích của những cơ sở tài liệu này là tàng trữ tài liệu người dùng .

Mỗi SQL Server instance có thể chứa tối đa 32767 databases, mỗi database chứa tối đa 32767 files. Khi bạn tạo database trong SQL Server sẽ có tối thiểu hai files trên hệ điều hành, một data file và một log file. Data file chứa data và các đối tượng như bảng, indexes, stored procedure và views. Log file ghi lại những thao tác thay đổi database với mục đích lưu trữ và hỗ trợ quay lại các trạng thái của database

Về cơ bản SQL Server database chúng ta chỉ cần quan tâm tới các files đó, tất cả những gì thuộc về một database đều gói gọn trong các files này và bạn có thể mang database từ nơi này sang nơi khác chỉ đơn giản bằng cách copy tất cả những files này sang máy khác (tất nhiên còn có những cách khác như backup/restore) và attach chúng vào SQL Server trên server đó.

Bạn đang đọc: Khởi tạo và quản trị database, tạo user trong SQL Server – Web888 chia sẻ kiến thức lập trình, kinh doanh, mmo

Tóm lại Database SQL Server có 2 loại file chính là :

.mdf file được gọi như file database chính, chứa schema (lược đồ) và dữ liệu
.ldf file chứa logss
Ngooài ra database có thể sử dụng file database thứ 2, có đuôi mở rộng là .ndf

Create Database by T-SQL (Tạo database bằng T-SQL)

Docs : https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database?view=sql-server-ver15
Cú pháp vừa đủ ( quan tâm, những mệnh đề trong dấu [ ] là optional :

CREATE DATABASE DATABASE_NAME
[ON
[PRIMARY] [[,...n]
[,[,...n]]
[LOGON {

Giải thích :

DATABASE_NAME: tên database cần tạo
ON: chỉ ra file lưu trữ ở đâu trong ổ đĩa cứng.
PRIMARY: liên kết với định nghĩa các file primary
: control của thuộc tính files
: controls của thuộc tính
LOG ON: chỉ ra file lưu trữ cho các file logs (nhật ký)
COLLATE : chỉ ra mã đối chiếu (collation) cho database, Đối chiếu xác định các quy tắc để so sánh và sắp xếp dữ liệu ký tự dựa trên tiêu chuẩn của ngôn ngữ cụ thể và địa phương ( thường chọn collation trong trường hợp cần database có thể lưu trữ dữ liệu unicode

Ví dụ :

CREATE DATABASE [Customer] ON PRIMARY 
(NAME = 'Customer_DBX', FILENAME = 'C:\DATA\Customer_DB.mdf')
LOG ON
(NAME = 'Customer_DB_log', FILENAME = 'C:\DATA\Customer_DB_log.ldf')
COLLATE SQL_Latin1_General_CP1_CI_AS

Sau khi chạy lệnh SQL Server sẽ tạo 1 database mới với file cứng lưu trong đường dẫn C:\data\

Refresh lại object Explorer để hiển thị lại database Customer

Hoặc ngắn gọn hơn :

CREATE DATABASE [CustomerDB]
-- Voi cach tao nay duong dan file database se do Microsoft SQL dinh nghia

Modify Databases (Chỉnh sửa Databases)

Khi cơ sở tài liệu do người dùng định nghĩa lớn lên hoặc giảm đi, kích cỡ cơ sở tài liệu sẽ được lan rộng ra hoặc thu nhỏ tự động hóa hoặc bằng tay thủ công. Dựa trên những nhu yếu biến hóa theo thời hạn, một số ít trường hợp phải sửa đổi cơ sở tài liệu .

ALTER DATABASE database_name
{

| 
| 
| MODIFY NAME = new_database_name
| COLLATE collation_name
}
[;]

Giải thích :

database_name: tên db
MODIFY NAME = new_database_name: là tên database mới muốn đổi thành
collate collation_name: collation name của db
: thêm files, xóa file hoặc chỉnh sửa
: filegroup có thể đợc thêm, sửa hoặc xóa khỏi database
: là tùy chọn mức cơ sở dữ liệu ảnh hưởng đến các đặc tính của cơ sở dữ liệu có thể được thiết lập cho mỗi cơ sở dữ liệu. Các tùy chọn này là duy nhất cho mỗi cơ sở dữ liệu và không ảnh hưởng đến các cơ sở dữ liệu khác.

Ví dụ sửa tên database :

ALTER DATABASE Customer MODIFY NAME = CusDB

Create User SQL Server (tạo người dùng SQL Server)

Docs : https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver15
Ví dụ :

-- 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;  
GO

Database owner (Quyền sở hữu database)

Trong SQL Server, quyền sở hữu của user-defined database có thể đợc thay đổi. Quyền sở hữu của database hệ thống không thể thay đổi. System procedure tên là sp_changedbowner được sử dụng để thay đổi quyền sở hữu của database.

Cú pháp :

sp_changedbowner [@loginname=]'login'

Với login là username đang tồn tại.

Sau khi sp_changedbowner được thực thi, owner mới (gọi là dbo) sẽ được lựa chọn làm chủ sở hữu của database. Dbo sẽ có quyền xử lý toàn bộ các hành động của database. Owner của master,model,tempdb ( các db trong system database) không thể đổi chủ sở hữu.

Ví dụ :

use 'CusDB'
sp_changedbowner 'toanngo92'

Sau khi chạy lệnh, database CusDB đã đổi chủ sở hữu thành ‘ toanngo92 ’, khi đó thông tin tài khoản login có tên ‘ toanngo92 ’ có thể thao tác trực tiếp với database CusDB

Setting Database Options

Database-level options chỉ ra đặc tính của database và có thể đặt cho mỗi database. Các options này là duy nhất cho mỗi database, và không ảnh hưởng tới database khác. Các database options được gán giá trị mặc định khi database được tạo, sau đó có thể thay đổi bằng cách sử dụng mệnh đề SET trong câu lệnh ALTER DATABASE.

Các database options trong SQL Server

Option type	Description
Automatic options	Kiểm soát các hành vi tự động của database
Cursor options	Kiểm soát hành vi con trỏ
Recovery options	Kiểm soát mô hình phục hồi (dự phòng) của database
Miscellaneous options	Kiểm soát tiêu chuẩn ANSI
State options	Kiểm soát trạng thái của database, như là online/offline, kết nối user

Database options trong SQL Server 2019

Lưu ý: cấu hình các settings database thông qua procedure có tên sp_configure system stored procedure hoặc SQL Management Studio.

Ví dụ về thực thi gán option AUTO_SHRINK cho database CusDB thành ON. Options khi chuyển thành on, database sẽ tự động hóa co lại / thu nhỏ giúp tiết kiệm chi phí vùng nhớ hơn ( tuy nhiên tính năng này không được recommend )
Docs AUTO_SHRINK : https://docs.microsoft.com/vi-vn/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 ON

Filegroups

Trong SQL Server, những files dữ liệu trong ổ đĩa cứng sử dụng để tàng trữ database. Các tệp tài liệu hoàn toàn có thể được chia nhỏ hơn nữa thành những nhóm tệp ( filegroups ) vì để cải tổ hiệu năng. Mỗi nhóm tệp ( filegroup_ được sử dụng để nhóm những tệp có tương quan với nhau cùng tàng trữ một đối tượng người tiêu dùng cơ sở tài liệu. Mọi cơ sở tài liệu đều có một nhóm tệp chính theo mặc định. Nhóm tệp này chứa tệp tài liệu chính. Nhóm tệp chính và tệp tài liệu được tạo tự động hóa với những giá trị thuộc tính mặc định tại thời gian quản trị, phân chia tài liệu và mục tiêu vị trí .

Ví dụ, có 3 file tên là customer_data1.ndf, customer_data2.ndf, customer_data3.ndf, có thể tạo ở 3 ổ đĩa cứng tương ứng, chúng có thể được gán vào 1 filegroup có tên customer_fgroup1. một bảng sau đó có thể được tạo cụ thể trên nhóm tệp customer_fgroup1. Sau đó, một bảng có thể được tạo cụ thể trên filgroup customer_fgroup1. Các truy vấn cho dữ liệu từ bảng sẽ được trải rộng trên ba ổ đĩa do đó cải thiện hiệu suất hơn.

Thêm Filegroups vào database hiện thời

Filegroups có thể được tạo khi database được tạo lần đầu tiên hoặc có thể tạo sau khi các files đã đưojc đưa vào database. Tuy nhiên, files không thể chuyển toiws một filegroup khác sau khi file được đưa vào database.

Xem thêm: Hướng Dẫn Cách Tạo Database trong SQL Sever và phpMyAdmin – Nhật Nam Media

File không hề là một thành viên của nhiều honw một filegroup trong cùng một thời gian. Chỉ được phép tạo tối đa 32,767 filegroup cho mỗi database. Filegroups hoàn toàn có thể chỉ chứa data files. Transaction log file không hề thuộc về filegroup .
Ví dụ tạo filegroup đưa filegroup vào database trong khi khởi tạo :

CREATE DATABASE [SalesDB] ON PRIMARY
(NAME = 'SalesDB', FILENAME = 'C:\data\SalesDB.mdf', SIZE = 3072KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB),
FILEGROUP [MyFileGroup]
(NAME = 'SalesDB_FG', FILENAME='C:\data\SalesDB_FG.ndf', SIZE = 3072KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON
(NAME = 'SalesDB_log', FILENAME='C:\data\SalesDB_log.ldf', SIZE = 2048KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS

Filegroup được thêm vào khi khởi tạo database SalesDBCú pháp khi đưa filegroup vào database hiện thời :

ALTER DATABASE database_name
(
|
|
|MODIFY NAME=new_database_name
|COLLATE collation_name
)[;]

Ví dụ :

USE 'CusDB'
ALTER DATABASE 'CusDB'
ADD FILEGROUP FG_Readonly

Sau khi caua lệnh thực thi, SQL Server hiển thị thông tin ‘ Command ( s ) completed successfully ’ và filegroup FG_Readonly được đưa vào database hiện thời là ‘ CusDB ’ .

Filegroup mặc định

Các đối tượng người tiêu dùng được gán cho nhóm tệp mặc định khi chúng được tạo trong cơ sở tài liệu. Filegroup PRIMARY là filegroup mặc định. filegroup mặc định hoàn toàn có thể được đổi khác bằng cách sử dụng câu lệnh ALTER DATABASE. Các đối tượng người dùng mạng lưới hệ thống và bảng vẫn nằm trong filegroup PRIMARY, dù ALTER vẫn không đi vào filegroup mới .
Ví dụ cách tạo một file mới, đưa vào filegroup FG_ReadOnly và gán FG_ReadOnly thành filegroup mặc định

USE 'CusDB'
ALTER DATABASE 'CusDB'
ADD FILE (NAME = CusDB1, FILENAME = 'C:\data\CusDB1.ndf')
TO FILEGROUP FG_ReadOnly ALTER DATABASE CusDB
MODIFY FILEGROUP FG_Readonly Default

Transaction Log

Transaction log ( nhật ký thanh toán giao dịch ) trong SQL Server ghi lại hàng loạt những thanh toán giao dịch và những sửa đổi trong database tạo ra bởi mỗi thanh toán giao dịch. Transaction log là một thành phần quan trọng trong database. Nó hoàn toàn có thể là giải pháp duy nhất để tiếp cận nguồn tài liệu gần đây trong trường hợp mạng lưới hệ thống bị lỗi
Transaction log tương hỗ những hoạt động giải trí như sau :

Một giao dịch chưa hoàn thành sẽ được khôi phục lại trạng thái cũ (roll back) hoặc công cụ CSDL nhận diện một lỗi. Các bản ghi nhật ký được sử dụng để quay lại các sửa đổi.
Nếu máy chủ đang chạy SQL Server bị lỗi, cơ sở dữ liệu có thể ở trạng thái không nhất quán. Khi một phiên bản của SQL Server được khởi động, nó sẽ chạy khôi phục từng cơ sở dữ liệu.
Database có thể được khôi phục đến điểm bị lỗi sau khi mất dữ liệu phần cứng ảnh hưởng đến các tệp cơ sở dữ liệu.
Log Reader Agent giám sát nhật ký giao dịch của từng cơ sở dữ liệu được định cấu hình để nhân rộng các giao dịch
Các giải pháp máy chủ dự phòng, cơ sở dữ liệu phản ánh (database mirroring) và log shipping phụ thuộc vào transaction log.

Làm việc với transaction logs:

Cơ sở tài liệu trong SQL Server có tối thiểu một file data và một file transaction log. Dữ liệu và thông tin transaction log được tàng trữ riêng không liên quan gì đến nhau, tốt nhất là trên những ổ đĩa riêng không liên quan gì đến nhau. Các file này được sử dụng bởi một cơ sở tài liệu .
SQL Server sử dụng transaction log của mỗi cơ sở tài liệu để Phục hồi những transaction. Transaction log là một bản ghi tiếp nối đuôi nhau tàng trữ lại toàn bộ những sửa đổi đã xảy ra trong cơ sở tài liệu cũng như những transaction đã triển khai sửa đổi. Nhật ký này lưu giữ đủ thông tin để hoàn tác những sửa đổi được thực thi trong mỗi thanh toán giao dịch. Nhật ký thanh toán giao dịch ghi lại việc phân chia và phân chia những trang cũng như cam kết hoặc Phục hồi của mỗi thanh toán giao dịch. Tính năng này được cho phép SQL Server linh động hơn trong việc Phục hồi trạng thái tài liệu .
Rollback của mỗi transaction hoàn toàn có thể đợc thực thi theo những cách sau :

Giao dịch được chuyển về phía trước khi transaction log được áp dụng
Một giao dịch được khôi phục khi một transaction chưa hoàn thành được sao lưu.

Thêm Log files vào database

Cú pháp để chỉnh sửa database và thêm log files :

ALTER DATABASE database_name
(
...
)
[;]
::=
{ADD FILE [,...n]
[TO FILEGROUP {filegroup_name|DEFAULT}]
| ADD LOG FILE [,...n]
| REMOVE FILE logical_file_name
| MODIFY FILE 
}

Tạo database bằng SSMS

Các bước tạo database bằng SSMS :

Trong Object Explorer, kết nối vào instance của SQL Server Database Engine, bức tiếp theo mở rộng instance ra bằng cách ấm vào icon ‘+’ bên cạnh instance
Click chuột phải vào database, click New Database như hình 1.1
Trong dialog box New Database, điền tên CSDL muốn tạo
Nếu database tạo với các giá trị mặc định, click OK là hoàn thành. Nếu không, tiếp tục lựa chọn các thông số, cấu hình cho database như hình 1.2
Để thay đổi owner name, click vào nút […] và chọn một owner khác
Để thay đổi dèault values của primary data và transaction log files, trong bảng Database files, click vào ô tương ứng và nhapaj giá trị
Để thay đổi collation của CSDL, bấm vào tab options, sau đó lựa chọn collation từ danh sách như hình 1.3
Để thay đổi recovery model, chọn tab options, sau đó chọn recovery model từ selectbox như hình 1.4
Để thay đổi các options database khác, chỉnh sửa thông tin trong tab options.
Để thêm một filegroup moiws, click vào tab Filegroups. Bước tiếp theo click nút Add, sau đó nhập lieuej filegroup như hình 1.5
Để thêm các thuộc tính nanag cao cho database, chọn tab Extended Properties
1. Trong cột Name, nhập tên cho extend property
2. Trong cột Value, nhập giá trị cho extend property. Ví dụ, chúng ta có thể điền thêm 1,2 dòng để mô tả cho CSDL.
Bấm OK để tạo CSDL

Hình 1.1 tạo databaseHình 1.2: cửa sổ databaseHình 1.3: danh sách collationHình 1.4: Recovery ModelThêm Filegroup vào database

Drop Database ( xóa database)

Trước khi drop DATABASE, hãy chắc như đinh là bạn còn giữ một vài bản backup gần nhất nếu database quan trọng, đây là nguyên tắc trong mọi trường hợp. Database đã bị xóa chỉ hoàn toàn có thể khởi tạo lại bằng cách Phục hồi ( restore ) backup .
Cú pháp Drop Database :

DROP DATABASE [databasename]

Để xóa database bằng SSMS, thao tác như sau :

Trong Object Explorer, kết nối vào instance cúa SQL Seerver Database Engine, sau đó mở rộng (expand) instance ra bằng cách bấm dấu ‘+’
Sau khi expand, chọn database, click chuột phải và bấm Delete
Chắc chắn là database đã được chọn, click DELETE

Xóa database bằng SSMS

Tạo database Snapshots

Database snapshot là tính năng được giới thieuej từ SQL Server 215. Tính năng này phân phối năng lực readonly, stic view cho database SQL. Nếu người dùng thao tác, chạy lệnh sai khiến database gặp lỗi, source database sẽ quy đổi lại trạng thái khi snapshot đưojc tạo ra. SSMS không tương hỗ tạo snapshot mà buộc phải sử dụng câu lệnh T-SQL thao tác này .

Ưu điểm của snapshot:

Cung cấp phiên bản copy của dữ liệu một cách thuận tiện và read-only
Khi được truy vấn, không bị suy giảm hiệu suất
Snapshot file nhẹ và khởi tạo nhanh

Nhược điểm của snapshot:

Không thể tạo snapshot backup
Snapshot phải tồn tại trên cùng database server như là source của dataabase
Người dùng mới không thể được gán quyền truy cập dữ liệu trong snapshot

Cú pháp :
Docs : https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database-snapshot-transact-sql?view=sql-server-ver15

CREATE DATABASE database_snapshot_name
ON (
NAME = logical_file_name, FILNAME = 'os_file_name'
)[,...n]
AS SNAPSHOT OF source_database_name
[;]

Giải thích :

database_snapshot_name: tên của database snapshot
ON (NAME = logical_file_new, FILENAME = ‘os_file_name’): danh sách các files trong sourrce database. Để snapshot hoạt động, tất cả file dữ liệu phải được xác định rõ ràng
AS SNAPSHOT OF source_database_name: source database có tên là source_database_name

Ví dụ tạo snapshot cho database AdventureWorks2019:

Xem thêm: Học lập trình có khó không? Top 20 sai lầm cần tránh

CREATE DATABASE AdvventureWorks_snapshot on (NAME = AdventureWorks2017, FILENAME = 'C:\data\AdventureWorks_snapshot.ss')
AS SNAPSHOT OF AdventureWorks2019;
GO

Lý do tại sao câu lệnh phê trên NAME = AdventureWorks2017 mà không phải là 2019 vì nếu viết là 2019 sẽ gặp lỗi, đọc docs này :
https://docs.microsoft.com/en-us/answers/questions/412065/error-all-files-must-be-specified-for-database-sna.html
Kết quả :

Source: https://final-blade.com
Category : Kiến thức Internet

admin

final-blade.com