Tiểu luận Tổng quan về Microsoft sql server

1 HỌC VIỆN AN NINH NHÂN DÂN TỔNG QUAN VỀ MICROSOFT SQL SERVER Bài tiểu luận môn học: Hệ quản trị CSDL Nhóm thực hiện: Nhóm 5 – B2Tin44 Nông Ngọc Hoài Nguyễn Văn Huy Đinh Xuân Hùng Nguyễn Tùng Lâm HÀ NỘI, THÁNG 11/2014 2 I. GIỚI THIỆU TỔNG QUAN Microsoft SQL Server (MS SQL Server) là một hệ quản trị cơ sở dữ liệu (CSDL) quan hệ do hãng Microsoft phát triển sử dụng ngôn ngữ truy vấn Transact- SQL (T-SQL) để trao đổi dữ liệu giữa máy khách (Client) và máy chủ (Server) – Mô hình Client-Server. Hiện nay MS SQL Server có thể thao tác với các CSDL có kích thước cực lớn, phục vụ nhiều máy khách cùng lúc và kết hợp ăn ý với các chương trình khác (như Microsoft Internet Infomation Server – IIS, Microsoft Team Foundation Server – TFS, Microsft Visual Studio - VS,). MS SQL Server chỉ chạy trên hệ điều hành windows của Micrisoft. Nhưng việc kết nối đến MS SQL Server có thể thực hiện từ các hệ điều hành khác nhau dựa vào thư viện các nhà phát triển cung cấp. Phiên bản mới nhất hiện nay của MS SQL Server là phiên bản 12.0 (SQL Server 2014, mã Hekaton). + T-SQL: là ngôn ngữ SQL mở rộng dựa trên chuẩn ISO và ANSI. + SQL (Structured Query Language): là ngôn ngữ để tạo, thêm, sửa, xóa dữ liệu trên một hệ quản trị CSDL quan hệ. + Chuẩn ISO (International Organization for Standardization): là chuẩn của cơ quan thiết lập tiêu chuẩn quốc tế (bao gồm đại diện từ các tổ chức tiêu chuẩn các quốc gia). + Chuẩn ANSI (American National Standards Institute) là chuẩn viện chuẩn quốc gia Hoa Kỳ) về truy xuất các hệ thống CSDL. 3 1. Lịch sử phát triển - 1970: IBM tạo ra ngôn ngữ truy vấn trong CSDL (SEQUEL). - 1985: IBM cùng Microsoft tạo ra HĐH OS/2. - 1987: IBM phát hành OS/2 cải tiến. Trước tình hình mất khách MS đã hợp tác cùng công ty Sysbase tạo ra một sản phẩm CSDL (Ashton-Tale). - 1992: MS SQL Server ra đời HĐH Winwdows NT Server, và SQL Server (lúc này là phiên bản 4.2) hoạt động độc lập trên HĐH này. Sau đó các phiên bản tiếp tục được phát triển: 4.21, 6.0, 6.5,SQL Server 2014. Phiên bản Năm phát hành Tên phát hành Mã 1.0 1989 SQL Server 1.0 - 1.1 1991 SQL Server 1.1 - 4.2 1992 SQL Server 4.2 - 4.21 1993 SQL Server 4.21 SQLNT 6.0 1995 SQL Server 6.0 SQL95 6.5 1996 SQL Server 6.5 Hydra 7.0 1998 SQL Server 7.0 Sphinx 8.0 2000 SQL Server 2000 Shiloh 9.0 2005 SQL Server 2005 Yukon 10.0 2008 SQL Server 2008 Katmai 10.25 2010 SQL Azure DB CloudDatabase 10.5 2010 SQL Server 2008 R2 Kilimanjaro 11.0 2012 SQL Server 2012 Denali 12.0 2014 SQL Server 2014 Hekaton 4 - Các ấn bản của MS SQL Server: + Enterprise: Thích cho công ty, doanh nghiệp lớn, chứa đầy đủ các tính năng của MS SQL Server. Có dịch vụ phân tích dữ liệu (Analysis Services) rất hiệu quả. + Standard: Thích hợp cho công ty, doanh nghiệp vừa và nhỏ. Giá rẻ hơn bản Enterprise, nhưng không có các chức năng nâng cao. + Persional: Ấn bản này dành cho cá nhân có thể chạy được trên hầu hết các phiên bản của hệ điều hành windows. + Deverloper: Giống Enterprise nhưng giới hạn số lượng người kết nối. + Desktop Engine: Ấn bản này không có giao diện người dùng, giới hạn kích thước dữ liệu là 2GB. + Win CE: Dùng cho các ứng dụng trên Windows CE. + Trail: Ấn bản dùng thử, giống như bản enterprise nhưng giới hạn thời gian sử dụng (Từ 30 đến 90 ngày tùy phiên bản). 2. Các thành phần của một SQL Server Các thành phần của một server: Database, Security, Server objects, Replication, Management. - Database: thư mục chứa các CSDL của server này. - Security: chứa các tài khoản người dùng (Logins), nhóm quyền (Server Roles) của server này. - Server objects Gồm Backup Devides, Linked Server, Trigger. + Backup devide: thực hiện sao lưu server này vào 1 file .bak. + Lineked Server: kết nối đến nhiều CSDL khác. Các CSDL này có thể nằm trên một hệ quản trị CSDL khác MS SQL Server. 5 + Database Server Tier: Tầng CSDL. Tầng này chứa CSDL trên các hệ quản trị CSDL khác nhau: SQL Server, Oracle, Acess, ODBC. + Server Tier: Tại tầng này, SQL Server kết nối đến CSDL ở tầng CSDL thông quan các hàm mà OLE DB cung cấp. OLE DB là một tập các hàm (API) được thiết kế bởi Microsoft để truy cập dữ liệu từ nhiều nguồn khác nhau. + Client Tier: Tầng các máy khách. Các máy trong tầng này kết nối đến MS SQL Server kết nối đến và thao tác với CSDL. + Trigger - Replication Đây là một giải pháp tạo ra các bản sao dữ liệu giống hệt nhau, di chuyển các bản sao này đến những máy chủ khác nhau tại các vùng khác nhau mà vẫn đảm bảo đồng bộ hóa dữ liệu một cách tự động (các bản sao có cùng giá trị dữ liệu) Các vai trò trong Replication: + Publisher (người phát hành): là người lưu trữ CSDL nguồn, làm cho CSDL có khả năng nhân bản. + Distributor (người phân phối): Nhận và lưu những thay đổi rồi chuyển nó tới những người đăng ký (Subsciber). Mô hình Replication Linked Server 6 + Subsciber (người đăng ký): nhận những thay đổi dữ liệu, lưu trữ dữ liệu đã được nhân bản. Ví dụ: có 1 CSDL thường xuyên được cập nhật (dữ liệu chứng minh nhân dân, bán vé tàu,) và chúng ta muồn 1 CSDL giống hệt như vậy trên một máy chủ khác mà vẫn muốn cập nhật, báo cáo hàng ngày thì Replication chính là giải pháp mà ta cần. - Management: Gồm: Policy Management, Extened Events, SQL Server Logs. Là các phương thức quản lý server, lưu trữ lịch sử sử dụng server. 3. Các thành phần của một CSDL Các thành phần của một CSDL gồm: Database diagram, Tables, Views, Synonyms, Programmability, Server Broker, Storage, Security. - Tables: chứa các bảng trong CSDL này. VD các bảng tbAnh, tbCmnd, tbDanToc, tbTinh,.. + Bảng được tạo thông qua câu lệnh CREATE TABLE. + Mỗi bảng đại diện cho một thực thể, các trường của bảng đại diện cho các thuộc tính của thực thể. 7 - Database diagram: hiển thị biểu đồ các bảng các liên kết giữa các bảng của CSDL. Biểu đồ CSDL ChungMinhNhanDan + Các liên kết được tạo thành từ việc kết nối khóa chính với khóa ngoại bằng câu lệnh CONSTRAIN. - Views: Cho phép ta quan sát dữ liệu thông qua việc định nghĩa các khung nhìn. Một view tương tự một bảng (cũng chứa các thuộc tính, giá trị, kiểu dữ liệu nên có thể gọi nó là một bảng ảo) nhưng nó được tạo thành từ việc tham chiếu đến dữ liệu của các bảng khác – tạo thành một khung nhìn (bằng câu lệnh SELECT). Các bảng ảo View_Cmnd, View_DanToc, 8 Ví dụ View: + Chọn (SELECT) trường HoTen của bảng tbCmnd và trường DanToc của bảng tbDanToc ta có View_Cmnd hiển thị họ tên và dân tộc một cách rõ ràng. - Synonyms: Quản lý các tên tham chiếu. Ví dụ cần lấy giá trị HoTen trên bảng tbCmnd của Server1 ta cần viết: Server1.ChungMinhNhanDan.tbCmnd.HoTen nhưng thông quan Synonyms tham chiếu HT thành đến HoTen trên bảng tbCmnd của Server1 thì muốn lấy giá trị của HoTen ta chỉ cần viết HT. Synonyms - Programmability: Chứa các thủ tục (Procedure), hàm (funtions), các kiểu dữ liệu (data types), Programmability tbCmnd tbDanToc View_Cmnd tham chiếu đến tbCmnd và tbDanToc để hiển thị dân tộc, tôn giáo 9 + Ví dụ về một số kiểu dữ liệu có sẵn trong CSDL: + Character Strings: các kiểu dữ liệu ký tự + Date and Time: các kiểu dữ liệu thời gian (ngày, giờ). + Hoặc các kiểu người dùng tự định nghĩa: User-Defined Data Types + + Một số hàm sẵn có của hệ thống: + Avg(): tính giá trị trung bình + Count(): tình số lượng giá trị + + - Server Broker: Chứa hàng đợi giúp thực hiện các thao tác một cách tuần tự không xung đột nhau. Ví dụ với CSDL bán hàng, khi khách mua hàng thì các mặt hàng sẽ không lưu vào CSDL ngay mà lưu vào một cuối hàng đợi. Khi các dữ liệu trước nó trong hàng đợi đã thực hiện xong thì đợt mua hàng này mới được xử lý. Server Broker Các kiểu dữ liệu Các hàm 10 - Storage + Full text catalogs: phục vụ cho việc tạo Full text search. Giúp cải thiện việc tìm kiếm dữ liệu trên CSDL lớn. + Full text stoplist Storage - Security Quản lý tài khoản, nhóm quyền của CSDL này. Security + Một số tài khoản: + TaiKhoan1 + MonHocHeQuanTri + + Một số nhóm quyền: + db_owner: chủ sở hữu CSDL + db_securityadmin: quản trị mảng bảo mật + db_denydatawriter: nhóm quyền này không được phép ghi dữ liệu. + db_accessadmin: quyền quản trị 11 4. T-SQL Là ngôn ngữ SQL mở rộng. Được chia làm 3 nhóm: DDL, DCL, DML - DDL (Data Defination Language): những lệnh dùng để quản lý các thuộc tính của database. Gồm Create, Alter, Drop. - DCL (Data Control Language): những lệnh quản lý đến từng đối tượng (bảng- table, bảng ảo-view, ). Gồm Grant, Revoke, Deny . - DML (Data Manipulation Language): những lệnh phổ biến để xử lý dữ liệu như Select, Update, Insert, Delete. - 5. Kiến trúc lưu trữ Mỗi server gồm nhiều CSDL mỗi CSDL được lưu trữ thành Data file và Log file. - Data file (tệp .mdf): là tệp để chứa dữ liệu (table, view, user,). - Log file (tệp .ldf): là tệp lưu thông tin các hoạt động, thao tác với CSDL. Dùng để giám sát và phục hồi CSDL. II. MÔ HÌNH KHÁCH-CHỦ VÀ THAO TÁC CSDL TỪ XA 1. Vấn đề - Không đủ điều kiện để cài MS SQL Server. Ví dụ MS SQL 2012 yêu cầu cấu hình máy tính cài HĐH Windows 7 hoặc mới hơn, tốc độ CPU nhanh hơn 1GHz, ổ đĩa còn trống ít nhất 2.2GB, RAM từ 512MB trở lên. - Yêu cầu dữ liệu không được trùng. Ví dụ với việc cấp giấy chứng minh nhân dân, các tỉnh khác nhau với CSDL khác nhau thì khi cấp các số cmnd sẽ có thể bị trùng. - Hạn chế số người quản lý CSDL. Ví dụ với CSDL điểm thi, mỗi khoa bộ môn sẽ cần có 1 người quản lý CSDL này thay vì chỉ cần 1 người quản lý chung toàn bộ các CSDL - 12 - Giải pháp đơn giản để khắc phục các vấn đề này là tạo 1 CSDL chung, các máy khách sẽ kết nối đến máy chủ và sử dụng chung CSDL này (mô hình khách-chủ). Thật may mắn là MS SQL Server có hỗ trợ việc kết nối và thao tác với dữ liệu từ xa thông qua mạng (cục bộ, internet,). 2. Mô hình khách-chủ - Có mô hình 2 lớp, 3 lớp,,nhiều lớp. Mô hình client-server 2 lớp - Các thức hoạt động của mô hình 2 lớp: + Máy chủ: quản lý dữ liệu, bảo mật dữ liệu, thực hiện truy vấn, lưu trữ, điểu khiển lỗi. + Máy khách: Thông quan giao diện người dùng (ứng dụng, web,) thao tác (thêm, sửa, xóa) với CSDL trên máy chủ. - Ở mô hình 3 lớp có thêm lớp giao dịch cung cấp các dịch vụ quản trị, tổ chức và khai thác CSDL - Vì máy chủ phải thực hiện công việc thay cho từng đấy máy khách nên chắc chắn khi nhiều máy khách cũng kết nối sẽ làm máy chủ bị chậm. Vì vậy tốc độ thực hiện công việc phụ thuộc rất lớn vào năng lực, cấu hình của máy chủ, người quản lý. Ảnh Minh họa công việc quá tải 13 3. Cài đặt - Bước 1: cho phép kết nối đến MS SQL Server thông quan giao thức TCP/IP TCP/IP: Enables. TCP Port là 6969 - Bước 2: Mở một cổng trên tường lửa cho phép kết nối đi qua cổng 6969 này. Thêm một cổng (add port) 14 - Bước 3:Chuyển chế độ xác thực của MS SQL Server về SQL Server and Windows Authentication mode Chuyển chế độ xác thực của MS SQL Server - Bước 4: Máy khách kết nối đến máy chủ Thông qua chuỗi kết nối (Connection string) để kết nối từ ứng dụng đến CSDL trên máy chủ. - Cấu trúc chuỗi kết nối: Data Source=[Địa_ chỉ_IP_máy_chủ]; Initial Catalog=[Tên_CSDL]; User ID=[Tài_khoản]; Password=[Mật_khẩu] + Trong đó Tài_Khoản và Mật_Khẩu là do người quản trị cấp.