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

Người dùng không được phép cập nhật trực tiếp thông tin trong các đối tượng cơ sở dữ liệu hệ thống, chẳng hạn như bảng hệ thống (system tables), thủ tục lưu trữ hệ thống (system store procedures) và catalog views (mục lục). Tuy nhiên, người dùng có thể tận dụng một bộ công cụ quản trị hoàn chỉnh cho phép họ quản trị toàn bộ hệ thống và quản lý tất cả người dùng và các đối tượng cơ sở dữ 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 có thể tự tạo database, được gọi là user-defined database và làm việc với chúng. Mục đích của các cơ sở dữ liệu này là lưu trữ dữ 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 đó.

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 đầy đủ (lưu ý, các mệnh đề trong dấu [ ] là optional:

CREATE DATABASE DATABASE_NAME
[ON
[PRIMARY] [<filespec>[,...n]
[,<filegroup>[,...n]]
[LOGON {<filespec[,...n]}]
]
[collate collation_name]
[;]

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 <filespec> định nghĩa các file primary
• <filespec> : control của thuộc tính files
• <filegroup>: controls của thuộc tính <filegroup>
• 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\

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ở dữ liệu do người dùng định nghĩa lớn lên hoặc giảm đi, kích thước cơ sở dữ liệu sẽ được mở rộng hoặc thu nhỏ tự động hoặc thủ công. Dựa trên các yêu cầu thay đổi theo thời gian, một số tình huống phải sửa đổi cơ sở dữ liệu.

ALTER DATABASE database_name
{<add_or_modify_files>

| <add_or_modify_filegroups>
| <set_database_options>
| 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
• <add_or_modify_files>: thêm files, xóa file hoặc chỉnh sửa
• <add_or_modify_filegroups>: filegroup có thể đợc thêm, sửa hoặc xóa khỏi database
• <set_database_options>: 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 đó 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

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 co lại/thu nhỏ giúp tiết kiệm 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, các files dữ liệu trong ổ đĩa cứng sử dụng để lưu trữ database. Các tệp dữ liệu có thể được chia nhỏ hơn nữa thành các nhóm tệp (filegroups) vì để cải thiện hiệu năng. Mỗi nhóm tệp (filegroup_ được sử dụng để nhóm các tệp có liên quan với nhau cùng lưu trữ một đối tượng cơ sở dữ liệu. Mọi cơ sở dữ 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 dữ liệu chính. Nhóm tệp chính và tệp dữ liệu được tạo tự động với các giá trị thuộc tính mặc định tại thời điểm quản trị, phân bổ dữ liệu và mục đích 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.

File không thể là một thành viên của nhiều honw một filegroup trong cùng một thời điểm. Chỉ được phép tạo tối đa 32,767 filegroup cho mỗi database. Filegroups có thể chỉ chứa data files. Transaction log file không thể 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

Cú pháp khi đưa filegroup vào database hiện thời:

ALTER DATABASE database_name
(<add_or_modify_files>
|<add_or_modify_filegroups>
|<set_database_options>
|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 báo ‘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 được gán cho nhóm tệp mặc định khi chúng được tạo trong cơ sở dữ liệu. Filegroup PRIMARY là filegroup mặc định. filegroup mặc định có thể được thay đổi bằng cách sử dụng câu lệnh ALTER DATABASE. Các đối tượng 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ý giao dịch) trong SQL Server ghi lại toàn bộ các giao dịch và các sửa đổi trong database tạo ra bởi mỗi giao dịch. Transaction log là một thành phần quan trọng trong database. Nó có thể là giải pháp duy nhất để tiếp cận nguồn dữ liệu gần đây trong trường hợp hệ thống bị lỗi

Transaction log hỗ trợ các hoạt động 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ở dữ liệu trong SQL Server có ít nhất một file data và một file transaction log. Dữ liệu và thông tin transaction log được lưu trữ riêng biệt, tốt nhất là trên các ổ đĩa riêng biệt. Các file này được sử dụng bởi một cơ sở dữ liệu.

SQL Server sử dụng transaction log của mỗi cơ sở dữ liệu để khôi phục các transaction. Transaction log là một bản ghi nối tiếp lưu trữ lại tất cả các sửa đổi đã xảy ra trong cơ sở dữ liệu cũng như các transaction đã thực hiện sửa đổi. Nhật ký này lưu giữ đủ thông tin để hoàn tác các sửa đổi được thực hiện trong mỗi giao dịch. Nhật ký giao dịch ghi lại việc phân bổ và phân bổ các trang cũng như cam kết hoặc khôi phục của mỗi giao dịch. Tính năng này cho phép SQL Server linh hoạt hơn trong việc khôi phục trạng thái dữ liệu.

Rollback của mỗi transaction có thể đợc thực thi theo các 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_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>
}

Tạo database bằng SSMS

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

1. 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
2. Click chuột phải vào database, click New Database như hình 1.1
3. Trong dialog box New Database, điền tên CSDL muốn tạo
4. 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
5. Để thay đổi owner name, click vào nút […] và chọn một owner khác
6. Để 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ị
7. Để 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
8. Để thay đổi recovery model, chọn tab options, sau đó chọn recovery model từ selectbox như hình 1.4
9. Để thay đổi các options database khác, chỉnh sửa thông tin trong tab options.
10. Để 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
11. Để 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.
12. Bấm OK để tạo CSDL

Drop Database ( xóa database)

Trước khi drop DATABASE, hãy chắc chắn 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ỉ có thể khởi tạo lại bằng cách khôi phục (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

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 cung cấp khả năng 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ẽ chuyển đổi lại trạng thái khi snapshot đưojc tạo ra. SSMS không hỗ trợ tạo snapshot mà buộc phải sử dụng câu lệnh T-SQL làm việ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:

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ả: