Thư viện tri thức trực tuyến
Kho tài liệu với 50,000+ tài liệu học thuật
© 2023 Siêu thị PDF - Kho tài liệu học thuật hàng đầu Việt Nam
Gt sqlsever cd cntt 2019
Nội dung xem thử
Mô tả chi tiết
-1-
ỦY BAN NHÂN DÂN TỈNH QUẢNG BÌNH
TRƯỜNG CAO ĐẲNG KỸ THUẬT CÔNG-NÔNG NGHIỆP
GIÁO TRÌNH
MÔ ĐUN: QUẢN TRỊ CƠ SỞ DỮ LIỆU VỚI SQL SERVER
NGÀNH/NGHỀ: CÔNG NGHỆ THÔNG TIN
TRÌNH ĐỘ: CAO ĐẲNG
(Ban hành kèm theo Quyết định số: 92/QĐ-CĐCNN, ngày 6 tháng 9 năm 2019
của Hiệu trưởng trường CĐ Kỹ thuật Công-Nông nghiệp Quảng Bình)
Quảng Bình, năm 2019
-2-
TUYÊN BỐ BẢN QUYỀN
Tài liệu này thuộc loại sách giáo trình nên các nguồn thông tin có thể được
phép dùng nguyên bản hoặc trích dùng cho các mục đích về đào tạo và tham khảo.
Mọi mục đích khác mang tính lệch lạc hoặc sử dụng với mục đích kinh doanh
thiếu lành mạnh sẽ bị nghiêm cấm.
MÃ TÀI LIỆU: MĐ 18
-3-
CHƯƠNG TRÌNH MÔ ĐUN
Tên mô đun: Quản trị cơ sở dữ liệu với SQL Server
Mã mô đun: MĐ 18
Thời gian thực hiện mô đun: 90 giờ (Lý thuyết: 30 giờ, Thực hành, thí nghiệm, thảo
luận, bài tập: 57 giờ, Kiểm tra: 3 giờ)
Vị trí, tính chất của mô đun
Vị trí: Là mô đun chuyên ngành trong chương trình đào tạo cao đẳng CNTT
Tính chất: Mô đun được bố trí giảng dạy sau khi học xong các mô đun/môn học cơ
sở.
Mục tiêu mô đun
Kiến thức
+ Trình bày được vai trò của hệ quản trị cơ sở dữ liệu trong việc khai thác và quản trị
cơ sở dữ liệu;
+ Trình bày được khái niệm về mô hình dữ liệu quan hệ: bảng/quan hệ, bản ghi/dòng
dữ liệu, thuộc tính (attributes), miền giá trị (domain), khóa (primary key, foreign key);
+ Trình bày được các mô hình truy nhập dữ liệu SQL Server;
+ Trình bày được kiến trúc bên trong của SQL Server: các thành phần và dịch vụ;
+ Trình bày được các ngôn ngữ lập trình tích hợp trong SQL Server;
+ Trình bày được các bước xây dựng hàm, thủ tục, trigger;
+ Trình bày đượcvai trò của khoá trong các giao dịch;
- Kỹ năng
+ Cài đặt và cấu hình được SQL Server;
+ Thực hiện thành thạo các thao tác quản trị tài khoản người dùng và tài khoản nhóm
đối với hệ thống SQL Server;
+ Bảo vệ tài nguyên dữ liệu trên các hệ thống SQL Server;
+ Xây dựng chính xác các điều kiện truy vấn;
+ Thiết kế được một số truy vấn cơ sở dữ liệu bằng các công cụ;
+ Xây dựng được một số hàm, thủ tục và trigger giúp cho việc truy cập, khai thác cơ
sở dữ liệu được hiệu quả hơn;
+ Quản lý được các giao dịch của hệ thống;
+ Sử dụng được một số dịch vụ của SQL server;
- Năng lực tự chủ và trách nhiệm
+ Chủ động, sáng tạo trong việc tìm hiểu các tài liệu phục vụ cho mô đun.
-4-
+ III. Nội dung mô đun
1. Nội dung tổng quát và phân bổ thời gian
Số
TT
Tên các bài trong mô đun
Thời gian
Tổng
số
Lý
thuyết
Thực hành, thí
nghiệm, thảo
luận, bài tập
Kiểm
tra
1 Bài 1: Tổng quan về SQL Server 7 2 5
2 Bài 2: Bộ công cụ Management Studio 8 3 5
3 Bài 3: Định nghĩa dữ liệu với T-SQL 11 4 7
4 Kiểm tra 1 1
5 Bài 4: Thao tác dữ liệu với T-SQL 20 5 15
6 Bài 5: Bảo mật cơ sở dữ liệu SQL Server. 8 3 5
7 Bài 6: Thủ tục lưu trữ. 9 4 5
8 Kiểm tra 1 1
9 Bài 7: Hàm do người dùng định nghĩa. 8 3 5
10 Kiểm tra 1 1
11 Bài 8: Trigger. 8 3 5
12 Bài 9: Quản lí giao dịch và khóa 8 3 5
Cộng 90 30 57 3
-5-
LỜI GIỚI THIỆU
Giáo trình “Quản trị cơ sở dữ liệu với SQL Server” được biên soạn theo chương trình
đào tạo Cao đẳng Công nghệ thông tin đã được Trường Cao đẳng Kỹ thuật Công Nông
nghiệp Quảng Bình ban hành.
- Chương trình Cao đẳng Công nghệ thông tin đã được xây dựng trên cơ sở phân tích
nghề, phần kỹ thuật nghề được kết cấu theo các môđun. Để tạo điều kiện thuận lợi cho
các cơ sở dạy nghề trong quá trình thực hiện, việc biên soạn giáo trình kỹ thuật nghề
theo theo các môđun đào tạo nghề là cấp thiết hiện nay.
- Mô đun 17: Quản trị cơ sở dữ liệu với SQL Server là mô đun đào tạo nghề được biên
soạn theo hình thức tích hợp lý thuyết và thực hành. Trong quá trình thực hiện, nhóm
biên soạn đã tham khảo nhiều tài liệu Quản trị cơ sở dữ liệu với SQL Server trong và
ngoài nước, kết hợp với kinh nghiệm trong thực tế.
- Thời lượng thiết kế 90 tiết. Trong đó 30 tiết lý thuyết, 60 tiết thực hành và kiểm tra.
Nội dung giáo trình gồm một bài mở đầu và 9 bài:
Bài 1: Tổng quan về SQL Server
Bài 2: Bộ công cụ Management Studio
Bài 3: Định nghĩa dữ liệu với T-SQL
Bài 4: Thao tác dữ liệu với T-SQL
Bài 5: Bảo mật cơ sở dữ liệu SQL Server.
Bài 6: Thủ tục lưu trữ.
Bài 7: Hàm do người dùng định nghĩa.
Bài 8: Trigger.
Bài 9: Quản lí giao dịch và khóa
Mặc dầu có rất nhiều cố gắng, nhưng không tránh khỏi những khiếm khuyết, rất
mong nhận được sự đóng góp ý kiến của độc giả để giáo trình được hoàn thiện hơn.
Xin chân thành cảm ơn!
Quảng Bình, ngày tháng năm 2019
Biên soạn
ThS. Lương Thanh Phương
ThS. Nguyễn Thành Minh
-6-
MỤC LỤC
BÀI 1: TỔNG QUAN VỀ SQL SERVER......................................................................9
1.1. Giới thiệu..................................................................................................................9
1.2. Kiến trúc truy cập cơ sở dữ liệu .............................................................................10
1.2.1. trúc chung - General Kiến ...................................................................................10
1.2.2. Kiến trúc bộ nhớ - Memory.................................................................................12
1.2.3. Kiến trúc file dữ liệu - Data file ..........................................................................13
1.2.4. Kiến trúc file nhật ký - Log file ..........................................................................14
1.3. Giới thiệu một số dịch vụ của SQL server.............................................................15
1.4. Ngôn ngữ lập trình trong SQL Server....................................................................16
1.4.1. Lệnh IF ................................................................................................................16
1.4.2. Lệnh IF ELSE .....................................................................................................16
1.4.3. Hàm Case When..................................................................................................17
1.4.4. Vòng lặp While ...................................................................................................19
BÀI 2: BỘ CÔNG CỤ MANAGEMENT STUDIO................................................20
2.1. Cài đặt Microsoft SQL Server ...............................................................................20
2.1.1. Cài đặt.Net FrameWork 3.5 ................................................................................20
2.1.2. Cài đặt bản nâng cấp Windows...........................................................................20
2.1.3. Cài đặt SQL Server .............................................................................................20
2.1.4. Khởi động SQL Server........................................................................................28
2.2. Một số thao tác với SQL Server.............................................................................29
2.2.1. Đăng nhập SQL Server .......................................................................................29
2.2.2. Tạo và thực thi các câu truy vấn với trình soạn thảo trên dữ liệu mẫu, kịch bản31
2.3. Quản lý cơ sở dữ liệu. ............................................................................................33
2.3.1. Tạo mới một Database ........................................................................................33
2.3.2. Sao lưu Database (Backup).................................................................................36
2.3.3. Xoá Database (Delete) ........................................................................................38
2.3.4. Phục hồi Database (Restore)...............................................................................38
2.3.5. Gỡ bỏ Database (Detach)....................................................................................40
2.3.6. Đính kèm Database (Attach)...............................................................................40
BÀI TẬP BÀI 2 ............................................................................................................42
BÀI 3: ĐỊNH NGHĨA DỮ LIỆU VỚI T-SQL..........................................................43
3.1. Ngôn ngữ định nghĩa dữ liệu .................................................................................43
3.2. Tạo và sửa đổi cấu trúc cơ sở dữ liệu.....................................................................44
3.2.1. Tạo tập tin cơ sở dữ liệu......................................................................................44
3.2.2. Tạo bảng..............................................................................................................48
3.3 Khung nhìn (View) .................................................................................................65
3.3.1. Tạo khung nhìn ...................................................................................................67
3.3.2 Cập nhật, bổ sung và xoá dữ liệu thông qua khung nhìn .....................................68
3.3.3 Sửa đổi khung nhìn ..............................................................................................71
-7-
BÀI TẬP CÓ HƯỚNG DẪN BÀI 3 ...........................................................................72
BÀI TẬP BÀI 3 ............................................................................................................78
BÀI 4: THAO TÁC DỮ LIỆU VỚI T-SQL..............................................................81
4.1.Truy xuất dữ liệu với câu lệnh SELECT.................................................................81
4.1.1.Mệnh đề FROM ...................................................................................................82
4.1.2. Danh sách chọn trong câu lệnh SELECT............................................................82
4.1.3.Chỉ định điều kiện truy vấn dữ liệu......................................................................86
4.1.4.Tạo mới bảng dữ liệu từ kết quả của câu lệnh SELECT......................................90
4.1.5. Sắp xếp kết quả truy vấn .....................................................................................90
4.1.6. Phép hợp..............................................................................................................91
4.1.7. Phép nối...............................................................................................................93
4.1.8.Thống kê dữ liệu với GROUP BY.......................................................................99
4.1.9. Truy vấn con (Subquery) ..................................................................................101
4.2. Bổ sung, cập nhật và xoá dữ liệu .........................................................................104
4.2.1.Bổ sung dữ liệu ..................................................................................................104
4.2.2.Cập nhật dữ liệu .................................................................................................106
4.2.3.Xoá dữ liệu.........................................................................................................108
BÀI TẬP CÓ HƯỚNG DẪN BÀI 4 ..........................................................................109
BÀI TẬP BÀI 4 .........................................................................................................121
BÀI 5: BẢO MẬT CƠ SỞ DỮ LIỆU SQL SERVER............................................124
5.1. Các khái niệm.......................................................................................................124
5.1.1. Các chế độ bảo mật ...........................................................................................124
5.1.2. Quản lý người dùng...........................................................................................125
5.2. Cấp phát quyền.....................................................................................................127
5.2.1.Cấp phát quyền cho người dùng trên các đối tượng cơ sở dữ liệu.....................127
5.2.2.Cấp phát quyền thực thi các câu lệnh.................................................................129
5.3.Thu hồi quyền........................................................................................................130
5.3.1.Thu hồi quyền trên đối tượng cơ sở dữ liệu:......................................................130
5.3.2.Thu hồi quyền thực thi các câu lệnh: .................................................................132
BÀI TẬP CÓ HƯỚNG DẪN BÀI 5 ..........................................................................132
BÀI TẬP BÀI 5 ..........................................................................................................135
BÀI 6: THỦ TỤC LƯU TRỮ...................................................................................139
6.1. Các khái niệm.......................................................................................................139
6.2. Tạo thủ tục lưu trữ................................................................................................141
6.3. Lời gọi thủ tục lưu trữ. .........................................................................................143
6.4. Định nghĩa và sử dụng biến. ................................................................................143
6.5. Giá trị trả về trong thủ tục lưu trữ. .......................................................................144
6.6. Sửa và xoá thủ tục lưu trữ. ...................................................................................146
6.6.1 Sửa thủ tục..........................................................................................................146
6.6.2. Xoá thủ tục ........................................................................................................146
-8-
BÀI TẬP CÓ HƯỚNG DẪN BÀI 6 ..........................................................................147
BÀI TẬP BÀI 6 ..........................................................................................................149
BÀI 7: HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA..................................................150
7.1. Hàm vô hướng......................................................................................................151
7.2. Hàm nội tuyến ......................................................................................................152
7.3. Hàm bao gồm nhiều câu lệnh bên trong. .............................................................152
7.4. Sửa và xoá hàm. ...................................................................................................153
7.4.1.Sửa đổi hàm........................................................................................................153
7.4.2. Xoá hàm ............................................................................................................154
7.5. Thực thi các hàm do người dùng định nghĩa. ......................................................154
BÀI TẬP CÓ HƯỚNG DẪN BÀI 7 ..........................................................................155
BÀI TẬP BÀI 7 ..........................................................................................................158
BÀI 8: TRIGGER .....................................................................................................159
8.1. Giới thiệu về trigger.............................................................................................159
8.2. Tạo, thay thế, loại bỏ triggers...............................................................................160
8.3. Kích hoạt trigger khi thay đổi dữ liệu cột (if update) ..........................................162
8.4. Sử dụng trigger và giao tác (transaction). ............................................................163
8.5. Vô hiệu hóa hay kích hoạt trigger........................................................................169
BÀI TẬP BÀI 8 ..........................................................................................................169
BÀI 9: QUẢN LÝ GIAO DỊCH VÀ KHÓA...........................................................171
9.1. Giao dịch(hay còn gọi Giao tác, tiếng Anh: transaction).....................................171
9.2. Khóa. ....................................................................................................................176
BÀI TẬP BÀI 9 ..........................................................................................................180
MỘT SỐ HÀM THƯỜNG SỬ DỤNG....................................................................183
1.Các hàm trên dữ liệu kiểu chuỗi...............................................................................183
2.Các hàm trên dữ liệu kiểu ngày giờ..........................................................................185
3.Hàm chuyển đổi kiểu ...............................................................................................186
CƠ SỞ DỮ LIỆU MẪU SỬ DỤNG TRONG GIÁO TRÌNH ...............................187
TÀI LIỆU THAM KHẢO.............................................................................................193
-9-
BÀI 1: TỔNG QUAN VỀ SQL SERVER
Mục tiêu của bài:
- Khái quát hóa được lịch sử phát triển của hệ quản trị cơ sở dữ liệu SQL Server;
- Trình bày được vai trò của SQL Server trong các ứng dụng quản trị dữ liệu;
- Vận dụng kiến trúc bên trong của hệ quản trị cơ sở dữ liệu SQL Server để nghiên
cứu lập trình trong bài toán thực tiễn trong các chương sau;
- Có thái độ làm việc cẩn thận, khoa học, chính xác.
1.1. Giới thiệu
SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ (Relational Database
Management System (RDBMS) ) sử dụng câu lệnh SQL (Transact-SQL) để trao đổi dữ
liệu giữa máy Client và máy cài SQL Server. Một RDBMS bao gồm databases, database
engine và các ứng dụng dùng để quản lý dữ liệu và các bộ phận khác nhau trong
RDBMS.
SQL Server được tối ưu để có thể chạy trên môi trường cơ sở dữ liệu rất lớn (Very
Large Database Environment) lên đến Tera-Byte và có thể phục vụ cùng lúc cho hàng
ngàn user. SQL Server có thể kết hợp “ăn ý” với các server khác như Microsoft Internet
Information Server (IIS), E-Commerce Server, Proxy Server….
Một vài ấn bản SQL Server:
Enterprise : chứa tất cả cá đặc điểm nổi bật của SQL Server, bao gồm nhân bộ máy
cơ sở dữ liệu và các dịch vụ đi kèm cùng với các công cụ cho tạo và quản lý phân cụm
SQL Server. Nó có thể quản lý các CSDL lớn tới 524 terabytes và đánh địa chỉ
12 terabytes bộ nhớ và hỗ trợ tới 640 bộ vi xử lý(các core của cpu)
Standard : Rất thích hợp cho các công ty vừa và nhỏ vì giá thành rẻ hơn nhiều so
với Enterprise Edition, nhưng lại bị giới hạn một số chức năng cao cấp (advanced
features) khác, edition này có thể chạy tốt trên hệ thống lên đến 4 CPU và 2 GB RAM
Developer : Có đầy đủ các tính năng của Enterprise Edition nhưng được chế tạo đặc
biệt như giới hạn số lượng người kết nối vào Server cùng một lúc…. Ðây là phiên bản sử
dụng cho phát triển và kiểm tra ứng dụng. Phiên bản này phù hợp cho các cá nhân, tổ
chức xây dựng và kiểm tra ứng dụng
Workgroup: ấn bản SQL Server Workgroup bao gồm chức năng lõi cơ sở dữ liệu
nhưng không có các dịch vụ đi kèm. Chú ý phiên bản này không còn tồn tại ở SQL
Server 2012.
Express : SQL Server Express dễ sử dụng và quản trị cơ sở dữ liệu đơn giản. Được
tích hợp với Microsoft Visual Studio, nên dễ dàng để phát triển các ứng dụng dữ liệu, an
toàn trong lưu trữ, và nhanh chóng triển khai. SQL Server Express là phiên bản miễn phí,
không giới hạn về số cơ sở dữ liệu hoặc người sử dụng, nhưng nó chỉ dùng cho 1 bộ vi
xử lý với 1 GB bộ nhớ và 10 GB file cơ sở dữ liệu. SQL Server Express là lựa chọn tốt
cho những người dùng chỉ cần một phiên bản SQL Server 2005 nhỏ gọn, dùng trên máy
chủ có cấu hình thấp, những nhà phát triển ứng dụng không chuyên hay những người yêu
thích xây dựng các ứng dụng nhỏ.
Lịch sử ra đời SQL Server và các phiên bản
-10-
Phiên bản đầu tiên của Microsoft SQL Server ra đời đầu tiên vào năm 1989 cho các
hệ điều hành chạy 16 bít với SQL Server phiên bản 1.0 và tiếp tục phát triển cho tới ngày
nay.
SQL Server của Microsoft được thị trường chấp nhận rộng rãi kể từ version 6.5. Sau
đó Microsoft đã cải tiến và hầu như viết lại một engine mới cho SQL Server 7.0. Cho nên
có thể nói từ version 6.5 lên version 7.0 là một bước nhảy vọt. Có một số đặc tính của
SQL Server 7.0 không tương thích với version 6.5. Trong khi đó từ Version 7.0 lên
version 8.0 (SQL Server 2000) thì những cải tiến chủ yếu là mở rộng các tính năng về
web và làm cho SQL Server 2000 đáng tin cậy hơn
Một điểm đặc biệt đáng lưu ý ở phiên bản 2000 là Multiple-Instance. Tức là bạn có
thể cài đặt phiên bản 2000 chung với các phiên bản trước mà không cần phải gỡ chúng.
Nghĩa là bạn có thể chạy song song version 6.5 hoặc 7.0 với phiên bản 2000 trên cùng
một máy (điều này không thể xảy ra với các phiên bản trước đây). Khi đó phiên bản cũ
trên máy bạn là Default Instance còn phiên bản 2000 mới vừa cài sẽ là Named Instance.
Từ tháng 10 năm 2016, các phiên bản sau được Microsoft hỗ trợ:
SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016
SQL Server 2017, SQL Server 2019
1.2. Kiến trúc truy cập cơ sở dữ liệu
Kiến trúc truy cập cơ sở dữ liệu của SQL Server có thể phân thành những phần
dưới sau đây:
➢ Kiến trúc chung - General
➢ Kiến trúc bộ nhớ - Memory
➢ Kiến trúc file dữ liệu - Data file
➢ Kiến trúc file nhật ký - Log file
1.2.1. trúc chung - General Kiến
- Client: Nơi khởi tạo yêu cầu.
- Query: Truy vấn SQL là ngôn ngữ bậc cao.
- Logical Units: Keyword, biểu thức, toán tử,...
- N/W Packets: Code liên quan đến mạng.
- Protocols: Trong SQL Server ta có 4 giao thức:
+ Shared memory: Dành cho các kết nối cục bộ và mục đích khắc phục sự cố.
+ Named pipes: Dành cho những kết nối trong mạng LAN.
+ TCP/IP: Dành cho các kết nối với mạng WAN.
+ VIA-Virtual Interface Adapter: Yêu cầu phần cứng đặc biệt được thiết lập bởi nhà
cung cấp và cũng không được hỗ trợ từ bản SQL 2012.
-11-
Hình 1.1 Sơ đồ Kiến trúc SQL Server
- Server: Nơi SQL Services được cài đặt và có database.
- Relational Engine: Đây là nơi sự thực hiện (execution) thực sự sẽ được hoàn thành.
Nó chứa bộ phân tích Query, tối ưu hóa Query và bộ thực thi Query.
- Query Parser (Command Parser) và Compiler (Translator): 2 thằng này chịu
trách nhiệm kiểm tra cú pháp của truy vấn và chuyển đổi truy vấn sang ngôn ngữ của
máy.
- Query Optimizer: Nó sẽ chuẩn bị output là Execution Plan bằng cách lấy input là
truy vấn, các thống kê và cây Algebrizer
- Execution Plan: Giống như một bản đồ chỉ đường, chứa thứ tự các bước thực hiện
như là một phần của việc thực hiện các truy vấn.
- Query Executor: Đây là nơi truy vấn được thực hiện từng bước một, với sự giúp đỡ
của Execution Plan và cũng là nơi Storage Engine sẽ được liên lạc.
- Storage Engine: Chịu trách nhiệm lưu trữ, truy xuất dữ liệu trong hệ thống lưu trữ
(ổ đĩa, SAN,...), thao tác dữ liệu, khóa và quản lý các transaction.
- SQL OS: Nằm giữa máy host (Windows OS) và SQL Server. Tất cả các hoạt động
được thực hiện trên engine cơ sở dữ liệu được "chăm sóc" bởi SQL OS. SQL OS cung
cấp các dịch vụ hệ điều hành khác nhau, chẳng hạn như hoạt động quản lý bộ nhớ với
buffer pool, log buffer, phát hiện deadlock (khóa chết) bằng cách sử dụng cấu trúc block
và lock.
-12-
- Checkpoint: Checkpoint là một tiến trình nội bộ, ghi tất cả các trang đã sửa đổi (gọi
là Dirty Page) từ Buffer Cache vào ổ đĩa vật lý. Ngoài ra, nó cũng ghi các bản log từ Log
Buffer vào file vật lý. Việc ghi lại các Dirty Page vào ổ đĩa còn được biết đến như là
Hardening of dirty pages (cứng hóa các Dirty Page).
- Lazy Writer: Lazy Writer sẽ đẩy các Dirty Page và ổ cứng vì một lý do hoàn toàn
khác, đó là giải phóng bộ nhớ trong Buffer Pool. Điều này xảy ra khi SQL Server đang bị
thiếu bộ nhớ. Tiến trình này được kiểm soát bởi một tiến trình Internal và không có thiết
lập cho nó.
SQL Server liên tục giám sát việc sử dụng bộ nhớ để đánh giá tính khả dụng và mức
cạnh tranh tài nguyên, giúp đảm bảo luôn có sẵn một dung lượng trống nhất định. Khi
phát hiện bất kỳ sự xung đột tài nguyên nào, nó sẽ kích hoạt Lazy Writer để chuyển một
số Dirty Page vào ổ đĩa và giải phóng bộ nhớ. Nó sử dụng thuật toán Least Recently
Used (LRU) để quyết định trang nào sẽ được đẩy vào ổ cứng. Nếu Lazy Writer luôn hoạt
động, nó có thể tạo ra nút thắt cổ chai với bộ nhớ.
1.2.2. Kiến trúc bộ nhớ - Memory
Sau đây là những tính năng nổi bật của kiến trúc bộ nhớ:
- Một trong những mục tiêu thiết kế cơ bản của tất cả phần mềm cơ sở dữ liệu là giảm
thiểu I/O ổ đĩa vì quá trình đọc và ghi đĩa là một trong những hành động sử dụng nhiều
tài nguyên nhất.
- Bộ nhớ trong Windows có thể được gọi với Virtual Address Space, được chia sẻ bởi
chế độ Kernel (chế độ OS) và User (ứng dụng như SQL Server).
- User address space của SQL Server được chia thành 2 phần: MemToLeave và Buffer
Pool.
- Kích thước của MemToLeave (MTL) và Buffer Pool (BPool) được quyết định bởi
SQL Server trong quá trình khởi động.
- Buffer Management là một thành phần quan trọng nếu muốn đạt được hiệu suất I/O
cao. Nó bao gồm 2 cơ chế: Buffer Manager để truy cập và cập nhật các trang cơ sở dữ
liệu và Buffer Pool để cắt giảm I/O file vào database.
- Buffer Pool được chia thành nhiều phần, quan trọng nhất là Buffer Cache và
Procedure Cache. Buffer Cache giữ các trang dữ liệu trong bộ nhớ để những dữ liệu
thường xuyên truy cập có thể trích xuất từ bộ nhớ cache. Quá trình thay thế sẽ đọc các
trang dữ liệu từ ổ đĩa. Đọc dữ liệu từ cache sẽ tối ưu hóa hiệu suất bằng cách giảm thiểu
số lượng các thao tác I/O, vốn chậm hơn so với truy xuất dữ liệu từ bộ nhớ.
- Procedure Cache giữ các thủ tục được lưu trữ và các Excecution Plan để tối ưu hóa
số lần Excecution Plan được tạo. Bạn có thể tìm thấy thông tin về dung lượng và hoạt
động trong Procedure Cache sử dụng lệnh DBCC PROCCACHE.
- Các phần khác của Buffer Pool bao gồm:
+ Các cấu trúc dữ liệu mức hệ thống: Chứa dữ liệu mức Instance về cơ sở dữ liệu,
khóa.
+ Log Cache: Dành riêng cho việc đọc và ghi các trang transaction.
-13-
+ Connection Context: Mỗi kết nối với Instance có một vùng nhỏ bộ nhớ để ghi trạng
thái hiện tại của kết nối. Thông tin này bao gồm thủ tục được lưu trữ và các thông số hàm
do người dùng xác định, vị trí con trỏ chuột và nhiều hơn nữa.
+ Stack Space: Windows phân bổ stack space cho mỗi luồng được bắt đầu với SQL
Server.
1.2.3. Kiến trúc file dữ liệu - Data file
Kiến trúc này có các thành phần sau:
File Group:
Các file cơ sở dữ liệu có thể nhóm lại với nhau thành các nhóm file để phân bổ và
quản lý theo mục đích. Một file chỉ có thể là thành viên của một nhóm file. Các file log
không thể nhóm vào File Group vì dung lượng file log được quản lý riêng biệt với dung
lượng dữ liệu.
Có hai loại File Group trong SQL Server là Primary và User-defined. Primary chứa
các file dữ liệu chính và bất kỳ file nào không được gán cụ thể cho File Group khác. Tất
cả các trang cho bảng hệ thống được cấp phát trong Primary. User-defined là các nhóm
file do người dùng định nghĩa, nó được chỉ định bằng cách sử dụng từ khóa File
group trong lệnh tạo cơ sở dữ liệu hoặc xóa cơ sở dữ liệu.
Một File Group trong mỗi cơ sở dữ liệu hoạt động như nhóm file mặc định. Khi
SQL Server chỉ định một trang cho bảng hoặc chỉ mục (không nằm trong File Group nào
khi tạo) thì trang đó sẽ nằm trong nhóm file mặc định. Để chuyển đổi nhóm file mặc định
từ File Group này sang File Group khác, cần có db_owner fixed database role.
Primary là nhóm tệp mặc định. User cần có db_owner fixed database role để sao lưu
tập tin và những nhóm file riêng lẻ.
File:
Cơ sở dữ liệu có 3 loại file Primary (file dữ liệu chính), Secondary (file dữ liệu phụ)
và Log (file nhật ký). Primary là điểm bắt đầu của cơ sở dữ liệu và trỏ đến các file khác
trong cơ sở dữ liệu.
Mỗi cơ sở dữ liệu có một Primary. Bạn có thể đặt phần mở rộng cho các file dữ liệu
chính là gì cũng được, nhưng khuyến nghị là nên để .mdf. File dữ liệu phụ là file khác
file dữ liệu chính. Một cơ sở dữ liệu có thể có nhiều hoặc chỉ có một file dữ liệu phụ.
Phần mở rộng cho file dữ liệu phụ nên đặt là .ndf.
Các file log giữ tất cả thông tin được sử dụng để phục hồi cơ sở dữ liệu. Cơ sở dữ
liệu phải có ít nhất một file log. Chúng ta có thể có nhiều file log cho một cơ sở dữ liệu.
Phần mở rộng nên đặt là .ldf.
Vị trí của tất cả các file trong cơ sở dữ liệu được ghi lại trong cả cơ sở dữ liệu tổng
thể và file Primary của cơ sở dữ liệu. Trong hầu hết trường hợp, công cụ cơ sở dữ liệu sử
dụng vị trí file từ cơ sở dữ liệu tổng thể.
File có 2 tên là Logical và Physical. Logical được sử dụng để tham chiếu đến file
trong tất cả các lệnh T-SQL. Tên Physical là OS_file_name, nó phải tuân theo quy tắc
của hệ điều hành. File dữ liệu và file log có thể được đặt trên hệ thống file FAT hoặc
-14-
NTFS, nhưng không thể đặt trên các hệ thống file nén. Có thể có tối đa 32.767 file trong
một cơ sở dữ liệu.
Extent:
Extent là một đơn vị cơ bản trong đó không gian được phân bổ cho mỗi bảng, chỉ
mục. Mỗi Extent là 8 trang liền kề hoặc 64KB. SQL Server có 2 loại Extent là Uniform
và Mixed. Uniform được tạo thành từ một object duy nhất, Mixed được tạo thành từ tối
đa 8 object.
Page:
Page (trang) là đơn vị cơ bản trong lưu trữ dữ liệu của SQL Server. Kích thước của
một trang là 8KB. Bắt đầu mỗi trang là 96byte tiêu đề, được sử dụng để lưu trữ thông tin
hệ thống như loại trang, số lượng không gian trống trên trang và ID của đối tượng sở hữu
trang. Có 9 loại trang dữ liệu trong SQL Server:
+ Data: Các hàng dữ liệu với tất cả dữ liệu từ text, ntext và ảnh.
+ Index: Các mục chỉ mục.
+ Tex\Image: Dữ liệu text, ntext và ảnh.
+ GAM: Thông tin về extent được chỉ định.
+ SGAM: Thông tin về extent được cấp phát ở mức hệ thống.
+ Page Free Space (PFS): Thông tin về không gian trống hiện có trên các trang.
+ Index Allocation Map (IAM): Thông tin về extent được sử dụng bởi bảng hoặc chỉ
mục.
+ Bulk Changed Map (BCM): Thông tin về extent được sử đổi bởi hoạt động hàng
loạt kể từ lệnh ghi sao lưu cuối cùng.
+ Differential Changed Map (DCM): Thông tin về extent đã thay đổi kể từ lệnh sao
lưu cơ sở dữ liệu cuối cùng.
1.2.4. Kiến trúc file nhật ký - Log file
Các log transaction trên SQL Server hoạt động hợp lý khi nó là chuỗi các bản ghi
log. Mỗi bản ghi log được xác định bởi Log Sequence Number (LSN), chứa ID của
transaction mà nó thuộc về.
Log ghi lại những sửa đổi dữ liệu hoặc các hoạt động được thực hiện hay lấy hình
ảnh trước và sau khi dữ liệu bị chỉnh sửa. Hình ảnh trước là bản sao của dữ liệu trước khi
thao tác được thực hiện, hình ảnh sau là bản sao của dữ liệu sau khi thao tác đã được thực
hiện.
Các bước để phục hồi một hoạt động phụ thuộc vào loại bản ghi log.
+Thao tác logic được log.
+Để đi tới thao tác logic ở trước, thao tác sẽ được thực hiện lại.
+Để quay lại thao tác logic phía sau, thao tác logic đảo ngược sẽ được thực hiện.
+Ảnh trước và sau được log.
+Để đi tới thao tác ở trước, ảnh sau sẽ được áp dụng.
+Để quay lại thao tác phía sau, ảnh trước sẽ được áp dụng.
-15-
Các thao tác khác nhau đã được ghi lại trong bản log transaction. Những thao tác
sau sẽ có trong đó:
+ Bắt đầu và kết thúc mỗi transaction.
+ Mọi sửa đổi dữ liệu (chèn, cập nhật, xóa), bao gồm các thay đổi các thủ tục lưu trữ
hệ thống hoặc lệnh ngôn ngữ định nghĩa dữ liệu (DDL) đến bảng, bao gồm cả bảng hệ
thống.
+ Mọi extent và phân bổ, hủy phân bổ trang.
+ Tạo hoặc xóa bảng, chỉ mục.
Các thao tác rollback cũng được log lại. Mỗi transaction sẽ giữ một khoảng không
gian trong bản log để chắc chắn rằng có đủ không gian log cần thiết cho rollback thực
hiện lệnh hoặc thông báo lỗi. Không gian này sẽ được giải phóng khi transaction hoàn tất.
Phần của file log từ bản log đầu tiên (bắt buộc phải có để khôi phục lại toàn bộ cơ
sở dữ liệu thành công) đến bản log cuối cùng được gọi là phần hoạt động của log hay log
hoạt động. Đây là phần bản log bắt buộc để có thể phục hồi cơ sở dữ liệu đầy đủ. Không
có phần nào trong log hoạt động được cắt xén. LSN của bản ghi log đầu tiên được gọi là
LSN phục hồi tối thiểu (Min LSN).
SQL Server Database Engine chia mỗi file log Physical thành một số file log ảo.
File log ảo không có kích thước cố định và không có số lượng file log ảo cố định cho mỗi
file log Physical.
Database Engine chọn dung lượng cho file log ảo một cách tự động khi nó tạo hoặc
mở rộng file log. Database Engine cố duy trì số lượng file ảo nhỏ. Kích thước của file log
ảo không thể cấu hình hay thiết lập bởi quản trị viên. Duy nhất chỉ có khi file log
Physical được xác định kích thước nhỏ và giá trị growth_increment thì file log ảo mới
ảnh hưởng đến hiệu suất hệ thống.
Giá trị kích thước là kích thước khởi tạo cho file log và growth_increment là lượng
không gian được thêm cho file mỗi khi file yêu cầu thêm không gian mới. Khi file log đạt
đến kích thước lớn vì có nhiều sự gia tăng nhỏ, chúng sẽ có nhiều file log ảo. Điều này có
thể làm chậm quá trình khởi động database và các hoạt động sao lưu, phục hồi log.
-
1.3. Giới thiệu một số dịch vụ của SQL server.
- Đây là trình quản lý các dịch vụ trên SQL Server như: MSSQL Server, SQL
Server Agent, Microsoft Distrinuted Transaction Coordinator (MSDTC)
- - MSSQL Server: là RDBMS, xử lý các phát biểu Transact-SQL và quản lý các
file lưu trữ các CSDL.
-
- SQL Server Agent: dùng lập lịch thực hiện tự động các công việc như Backup dữ liệu,
Replication, ...
- - Microsoft Distributed Transaction Coordinator (MSDTC): Hỗ trợ cho các ứng
dụng Client làm việc với các dữ liệu được phân bổ trên nhiều máy tính.
- Combo Server: Chứa danh sách các SQL Server có khả năng truy xuất từ máy của bạn.
Tuy nhiên, bạn có thể gõ tên máy chủ cần kết nối không có trong danh sách và click nút
mũi tên trên hộp Services, tiện ích Services Manager sẽ cố gắng kết nối tới máy chủ ở xa.