Tài liệu SQL Server

Tài liệu SQL Server 1. Giới thiệu (Introduction) 1.1. Phiên bản (Edition) Doanh nghiệp (Enterprise) - Chứa đầy đủ các đặc trưng của SQL Server và có thể chạy tốt trên hệ thống lên đến 32 CPUs và 64GB RAM. Thêm vào đó nó có các dịch vụ giúp cho việc phân tích dữ liệu rất hiệu quả (Analysis Services). Chuẩn (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. Cá nhân (Personal) - Được tối ưu hóa để chạy trên PC nên có thể cài đặt trên hầu hết các phiên bản Windows kể cả Windows 98. Phát triển (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à edition mà các bạn muốn học SQL Server cần có. Chúng ta sẽ dùng edition này trong suốt khóa học. Edition này có thể cài trên Windows 2000 Professional hay Win NT Workstation. Desktop Engin (MSDE) - Đây chỉ là một engine chạy trên desktop và không có user interface (giao diện). Thích hợp cho việc triển khai ứng dụng ở máy client. Kích thước database bị giới hạn khoảng 2 GB. Win CE - Dùng cho các ứng dụng chạy trên Windows CE. Dùng thử (Trial) - Có các tính năng của Enterprise Edition, download free, nhưng giới hạn thời gian sử dụng. 1.2. Yêu cầu phần cứng (Hardware requirement) 1.3. Yêu cầu hệ điều hành (OS requirement) 2. Cài đặt (Setup) 2.1. 2000 Màn hình khởi động (Startup screen) - Chọn SQL Server 2000 Components. Biên soạn: Nguyễn Viết Nam 1 Tài liệu SQL Server Cài đặt thành phần (Install Component) - Chọn Install Database Server. Tên máy tính (Computer Name) - Chọn + Hộp nếu cài trên máy cục bộ. + Hộp nếu cài trên máy từ xa. Lựa chọn cài đặt (Install Selection) - Chọn: + Hộp để cài mặc định. + Hộp để cài tùy chọn. Thông tin người dùng (User Information) - Nhập tên người dùng vào hộp . - Nhập tên công ty vào hộp . Biên soạn: Nguyễn Viết Nam 2 Tài liệu SQL Server Ý kiến bản quyền phần mềm (Software License Agreement) - Chọn Yes. Khóa đĩa (CD-Key) - Nhập CD-Key. Định nghĩa cài đặt (Installation Definition) - Chọn: + Hộp nếu chỉ cài công cụ trên máy khách. + Hộp nếu cài công cụ trên máy khách lẫn máy chủ. + Hộp nếu chỉ cài phần kết nối. Tên SQL Server (Instance Name) - Chọn hộp nếu dùng tên mặc định (ISCN-Named of SQL server) nhận diện bởi tên máy. - Bỏ chọn hộp nếu dùng tên tự đặt và nhập tên đó vào hộp . Biên soạn: Nguyễn Viết Nam 3 Tài liệu SQL Server Loại cài đặt (Setup Type) - Chọn loại cài đặt: Typical, Minimum, Custom. - Chọn thư mục cài đặt: - Chứa file chương trình ở mục . - Chứa file dữ liệu ở mục . Tài khoản (Services Accounts) - Chọn loại tài khoản: + Hộp nếu chọn tài khoản của máy cục bộ. + Hộp nếu chọn tài khoảng của máy mạng. Chế độ chứng thực (Authentication Mode) - Chọn loại chứng thực: + Hộp nếu chứng thực bằng người dùng Windows (User Windows). + Hộp nếu chứng thực bằng người dùng SQL Server (User SQL Server) lẫn người dùng Windows (User Windows). + sa (System Administrator) là tài khoản có quyền cao nhất -> nên cài mật khẩu ngay. Thông báo bắt đầu cài đặt (Start Copying Files) Chọn chế độ bản quyền - Chọn chế độ bản quyền: + Per Seat -> theo số lượng thiết bị (device) truy cập. Biên soạn: Nguyễn Viết Nam 4 Tài liệu SQL Server (Choose License Mode) + Processor -> theo số lượng bộ xử lý (CPU) trên máy. Hoàn tất cài đặt (Setup Complete) Lưu ý-Remark - Khi cài đặt SQL Server 2000 sử dụng tài khoản của hệ điều hành. Sau này nếu vì lý do gì phải thay đổi mật khẩu của tài khoản này trong Windows, thì ta cũng phải cập nhật mật khẩu này trong SQL Server. + Enterprise Manager -> Tools -> SQL Server Configuration Properties -> Security -> Startup service account -> điều chỉnh lại mật khẩu. - Nếu ta đổi tên máy tính (Computer Name) của máy đang cài SQL Server, cần cập nhật lại thông tin này trong table hệ thống sysservers, bằng cách thi hành 2 thủ tục sau: sp_dropserver old_name Go Sp_addserver new_name, local Go Ý kiến bản quyền của người dùng cuối (End User License Agreement) - Chọn hộp . Điều kiện cài đặt (Installing prerequisites) - Chọn Next. Biên soạn: Nguyễn Viết Nam 5 Tài liệu SQL Server Màn hình chào đón (Welcome screen) - Chọn Next. Kiểm tra cấu hình hệ thống (System Configuration Check) - Chọn Next. Thông tin đăng ký (Registration Information) - Nhập tên người dùng vào hộp . - Nhập tên công ty vào hộp . Thành phần để cài đặt (Components to Install) - Chọn tất cả hộp. Biên soạn: Nguyễn Viết Nam 6 Tài liệu SQL Server Tên SQL Server (Instance Name) - Chọn hộp nếu dùng tên mặc định (ISCN-Named instance of SQL server) nhận diện bởi tên máy. - Chọn hộp nếu dùng tên tự đặt và nhập tên đó vào hộp . Thành phần tồn tại (Existing Components) - Chọn Next. Tài khoản dịch vụ (Service Account) - Chọn loại tài khoản: + Hộp nếu chọn tài khoản của máy cục bộ. + Hộp nếu chọn tài khoảng của máy mạng. Biên soạn: Nguyễn Viết Nam 7 Tài liệu SQL Server Tùy chọn cài đặt báo cáo máy chủ (Report Server Installation Options) - Chọn Next. Thiết lập báo cáo sử dụng và lỗi (Error and Usage Report Settings) - Chọn Next. Bắt đầu cài đặt (Ready to Install) - Chọn Install. Hoàn tất cài đặt (Completing Setup) - Chọn Finish. Biên soạn: Nguyễn Viết Nam 8 Tài liệu SQL Server 2.2. 2005 3. Cửa sổ làm việc (Window) 3.1. Khởi động (Open),Thoát (Exit) 3.1.1. 2000 Giới thiệu- Introduction - Là 1 hệ quản trị cơ sở dữ liệu của Microsoft. Khởi động-Open - Click chọn nút nút > chọn > chọn Microsoft SQL Server > chọn + Enterprise Manager: Quản lý SQL Server. + Query Analyzer: Nhập và thực hiện các câu lệnh SQL. hay chọn menu > chọn SQL Query Analyzer. + Client Network Utility: Cấu hình để kết nối với các SQL Server khác trên mạng. + Server Network Utility: Cấu hình Server để cho phép các máy khác kết nối vào. + Configure SQL XML Support in IIS: Cấu hình hỗ trợ SQL XML trong IIS. + Import and Export Data: Nhập / Xuất dữ liệu từ các CSDL khác. + Service Manager: Khởi động hay dùng các dịch vụ SQL Server, SQL Server Agent. Thoát-Exit 3.1.2. 2005 Giới thiệu- Introduction - Là 1 hệ quản trị cơ sở dữ liệu của Microsoft. Khởi động-Open - Click chọn nút nút > chọn > chọn Microsoft SQL Server 2005 > chọn + SQL Server Management Studio: Quản lý SQL Server. Thoát-Exit Biên soạn: Nguyễn Viết Nam 9 Tài liệu SQL Server 3.2. Kết nối (Connect) 3.2.1. 2000 Quản lý dịch vụ (Service Manager) Dịch vụ (Service) Lưu ý - Khi cài đặt SQL Server 2000 sử dụng tài khoản của hệ điều hành. Sau này nếu lý do gì phải thay đổi mật khẩu của tài khoản này trong Windows, thì ta cũng phải cập nhật mật khẩu này trong SQL Server. Enterprise Manager > Tools > SQL Server Configuration Properties > Security > Startup service account > điều chỉnh lại mật khẩu. - Nếu ta đổi tên máy tín (Computer Name) của máy đang cài SQL Server, cần cập nhật lại thông tin này torng table hệ thống syssevers, bằng cách thi hành 2 thủ tục sau: sp_dropserver old_name Go Sp_addserver new_name, local Go 3.2.2. 2005 Kết nối-Connect Dịch vụ-Service 3.3. Đóng kết nối (Disconnect) 3.4. Ngôn ngữ (Language) VB (ADO) rs.Close cn.Close Set pr = Nothing Set cm = Nothing Biên soạn: Nguyễn Viết Nam 10 Tài liệu SQL Server Set rs = Nothing Set cn = Nothing 3.5. Kiến trúc (Architecture) Relational Database Engine - Đây là phần cốt lõi của SQL Server. - Engine có khả năng chứa data ở các quy mô khác nhau dưới dạng table. - Hỗ trợ hầu hết các kiểu kết nối (Data connection) thông dụng của Microsoft như ADO (ActiveX Data Objects), OLE DB, and ODBC (Open Database Connectivity). - Có khả năng tự điều chỉnh (tune up) như sử dụng thêm các tài nguyên máy khi cần và trả lại tài nguyên cho hệ điều hành khi một user log off. Replication - Cơ chế tạo bản sao (Replica). - Ta có thể tạo một database dùng để chứa dữ liệu được các ứng dụng thường xuyên cập nhật. Đồng thời lại tạo thêm một database giống y hệt như thế trên một server khác để chạy báo cáo (report database) (cách làm này thường dùng để tránh ảnh hưởng đến hiệu suất của server chính). - Vấn đề là report database cần phải được cập nhật thường xuyên để bảo đảm tính chính xác của các báo cáo. Ta không thể dùng cơ chế backup và restore trong trường hợp này -> cơ chế replication của SQL Server sẽ được sử dụng để bảo đảm cho dữ liệu ở 2 database được đồng bộ (synchronized). Data Transformation Service (DTS) - Dịch vụ chuyển dịch dữ liệu. - Giả sử ta đang làm việc trong một công ty lớn, trong đó dữ liệu được chứa trong nhiều nơi khác nhau và ở các dạng khác nhau như Oracle, DB2 (của IBM), SQL Server, Microsoft Access … - Chắc chắn ta sẽ có nhu cầu di chuyển dữ liệu (migrate hay transfer) giữa các server này, thậm chí ta còn muốn định dạng (format) nó trước khi lưu vào database khác -> giải pháp DTS sẽ giúp ta giải quyết công việc này một cách dễ dàng. Analysis Service - Dịch vụ phân tích dữ liệu của Microsoft. - Dữ liệu (Data) chứa trong database sẽ chẳng có ý nghĩa gì nếu như ta không thể lấy những thông tin (Information) bổ ích từ đó. - Microsoft cung cấp cho ta một công cụ rất mạnh Analysis Service giúp cho việc phân tích dữ liệu trở nên dễ dàng và hiệu quả với khái niệm hình khối nhiều chiều (Multi-demension cubes) và kỹ thuật “khai phá mỏ dữ liệu” (Data mining). Một số dịch vụ khác - English Query: + Dịch vụ này giúp ta có thể truy vấn dữ liệu bằng plain English. - Meta Data Service: + Dịch vụ này giúp cho việc quản lý Meta data dễ dàng hơn. + Meta data là những thông tin mô tả về cấu trúc của data trong database như data thuộc loại nào? String, Integer, … một cột nào đó có phải là Primary Key hay không? 4. Định nghĩa dữ liệu (Data Difinition) 4.1. Cơ sở dữ liệu (Database) – MDF & LDF 4.1.1. Kiến trúc (Architecture) Giới thiệu (Introduction) - Mỗi database của SQL Server được tạo ra từ nhiều tập tin của hệ điều hành. Các tập tin này có thể nhóm lại vào nhóm tập tin (filegroups) để dễ dàng quản lý và nâng cao hiệu suất. Files - Một database file có thể là một data file hay một log file. - Data files được dùng để lưu trữ dữ liệu (data) và đối tượng (objects) như tables, indexes, views, triggers, stored procedures. Có 2 loại data files: primary và secondary. - Mỗi database phải được tạo ra với ít nhất một data file và một log file. Mỗi files chỉ được sử dụng bởi một database, database không thể chia sẻ files. - Có 3 loại files: + Primary data file: Chứa tất cả các thông tin khởi động của database cùng các table và Biên soạn: Nguyễn Viết Nam Phân loại Primary data file Secondary data file Transaction log file 11 Tài liệu SQL Server đối tượng hệ thống. Nó trỏ đến toàn bộ các file còn lại tạo ra trong database. Nó có thể lưu các table và đối tượng của người dùng. Mỗi database có đúng một primary file. Phần mở rộng là .mdf. + Secondary data file (tùy chọn): Có thể dùng lưu trữ dữ liệu và đối tượng không có trong primary file. Database có thể không có secondary files nếu tất cả dữ liệu của nó lưu trong primary file. Một số khác lại cần nhiều secondary files để trải dữ liệu trên nhiều đĩa nhằm tăng hiệu suất. Phần mở rộng thường là .ndf. + Transaction log file: Lưu tất cả các thông tin về transaction log để phục hồi (recover) database. Mỗi database phải có ít nhất một log file và có thể có nhiều log files. Phần mở rộng là .ldf. - Kích thước tối đa của data file là 32 Terabytes (TB) của một log file là 4TB. - Một database có thể chỉ có một primary data file, và một transaction log file. - Database phức tạp hơn có thể có 1 primary data files, 5 secondary data files, và 2 transaction log files. - Làm thế nào để dữ liệu có thể trải trên các data files? Câu trả lời là dùng filegroups. Filegroups - Filegroups cho phép ta nhóm các file để dễ dàng quản lý. - Filegroups có thể nâng hiệu suất bằng cách cho phép một database có thể trải trên nhiều đĩa hoặc hệ thống RAID. - Có 3 loại filegroups: + Primary filegroups: Chứa primary data file và tất cả các file không được đặt vào filegroup khác. o System tables: Dùng để định nghĩa users, objects, và quyền trên database. System tables được đặt trên primary filegroup của database đó. SQL Server tự động tạo ra system tables khi chúng ta tạo database. + User-defined filegroups: Bao gồm các filegroups do user tạo ra. Một table hay index có thể được tạo ra và đặt trong các filegroup riêng của user. + Default filegroups: Chứa các tables và indexes khi tạo không chỉ rõ filegroup. Default filegroup mặc định là primary filegroup. Thành viên của db_ower có thể thay đổi default filegroup. - Để tăng hiệu suất, ta có thể tạo tables và indexes trên các filegroups khác nhau: Ví dụ: Ta có thể đặt một table được sử dụng rất nhiều vào một filegroup trên một disk array lớn (vd: bao gồm 10 disk drives) và đặt table khác có lượng sử dụng ít hơn vào filegroup đặt trên một đĩa khác. Như vậy table có lượng truy cập nhiều sẽ được trải trên một lượng lớn đĩa, nhờ đó có thể tăng tính song song cho vấn đề I/O của đĩa. - Một lợi điểm khác khi dùng filegroups là SQL Server cho phép ta có thể backup database dựa trên file hay filegroups (khi database quá lớn ta không thể backup cùng lúc được). Một số quy tắc Ta nên có chiến lược thiết kế tốt cho việc sử dụng từng files và filegroups trước khi tạo database. Để làm điều này, cần nắm 1 quy tắc sau: - File và filegroup không thể tạo ra bởi nhiều database. - Một file chỉ có thể là thành viên (member) của một filegroup. - Data và thông tin không thể cùng một file. - Transaction log files không thể là thành phần của một filegroups. - Khi 1 file được gán là thành phần của một filegroup, thì nó không thể di chuyển sang một filegroup khác. Một số lời khuyên - Hầu hết database có thể làm việc tốt chỉ với 1 primary data file và 1 transaction log file. Nếu ta gặp vấn đề về I/O, có thể dùng filegroup để trải dữ liệu trên nhiều đĩa. - Luôn luôn đặt log files trên một đĩa vật lý (physical disks) khác với đĩa data files. - Có thể dùng primary data file cho các tables và objects hệ thống, đồng thời tạo ra 1 hoặc nhiều secondary data files để chứa dữ liệu và đối tượng của người dùng. - Đặt nonclustered indexes cho table dùng nhiều trong 1 filegroup riêng và trên một đĩa (vật lý) riêng biệt với đĩa chứa dữ liệu của nó trong table. - Đặt các table dùng trong cùng câu query trên những đĩa khác nhau, để tận dụng I/O song song khi search engine tìm kiếm. Chế độ Automatic File Growth - SQL Server cho phép file tự động tăng kích thước khi cần thiết -> giảm gánh nặng cho người quản trị phải theo dõi và tăng kích thước file. - Một file được tạo ra với kích thước ban đầu (Initial file). Khi dữ liệu đầy, SQL Server sẽ tăng kích thước một lượng, gọi là growth increment. Khi vùng này đầy SQL Server lại tiếp tục cấp phát một Biên soạn: Nguyễn Viết Nam Phân loại Primary filegroups User-defined filegroups Default filegroups 12 Tài liệu SQL Server lượng growth increment khác, cho đến khi hết đĩa hoặc đạt tới kích thước tối đa (maximum file size). - Để tránh tình trạng hết đĩa dẫn đến lỗi, hãy cài kích thước tối đa cho mỗi file. Hoặc tạo file mới thuộc cùng filegroup. - Quy tắc: nên dùng automatic file growth và maximum file sizes khi tạo một database, xác định kích thước lớn nhất mà ta nghĩ file có thể đạt đến. Dù sao vẫn phải kiểm tra sự tăng trưởng của database hàng ngày hoặc hàng tuần. System database - Master: + Lưu các thông tin của hệ thống như thông tin khởi động, cấu hình SQL Server, các login accounts, sự tồn tại của tất cả các database, vị trí của primary file, luôn giữ một bản backup gần nhất của master database. - Tempdb: + Giữ bảng tạm (temporary tables) và thủ tục lưu trữ tạm (temporary stored procedures). Database này cũng dùng cho các lưu trữ tạm khi SQL Server cần, ví dụ cho việc sắp xếp dữ liệu. Một bản sạch (clean copy) của tempdb database được tạo lại mỗi khi SQL Server khởi động lại. - Model: + Dùng như một khuôn mẫu (template) cho tất cả các database tạo ra trong hệ thống. Model database phải tồn tại vì nó được dùng để tạo lại tempdb mỗi khi SQL Server được khởi động. Nếu ta sửa trong model database -> các database tạo ra đều có các đặc tính này. - Msdb: + Giữ các table mà SQL Server Agent dùng để định thời các job và cảnh báo cho các tác vụ. Mỗi system database có riêng primary data file và log file. Các databases được lưu trong folder mà ta đã chỉ định khi cài đặt SQL Server. Biên soạn: Nguyễn Viết Nam Phân loại Master Tempdb Model Msdb 13 Tài liệu SQL Server 4.1.2. Thao tác (Operation) 4.1.2.1. 2000 Tạo (Create) Ẩn CDSL hệ thống - Nhấp chuột phảI lên mục > chọn Edit SQL Server Registration properties > bỏ chọn hộp . Thiết kế (Design) - Click chuột phải lên mục trong khung Console Root > chọn New Database. - Sau đó xuất hiện hộp thoại Database Properties như hình sau: + Nhập tên CSDL vào hộp . + Chọn thẻ để thiết lập thuộc tính cho file dữ liệu SQL Server. + Chọn thẻ để thiết lập cho file nhật ký SQL Server. Viết lệnh SQL Cú pháp: Create DataBase Tên_CSDL On Biên soạn: Nguyễn Viết Nam 14 Tài liệu SQL Server (SQL Statement) ( Name = Tên_do_SQL_quản_lý, FileName = Đường_dẫn_và_tên_tập_tin_dữ_liệu, Size = Kích_thước_tập_tin_dữ_liệu_ban_đầu, MaxSize = Kích_thước_tối_đa_của_tập_tin_dữ_liệu, FileGrowth = Kích_thước_sẽ_được_thêm_vào_khi_cần_thiết ) Log On ( Name = Tên_do_SQL_quản_lý, FileName = Đường_dẫn_và_tên_tập_tin_nhật_ký, Size = Kích_thước_tập_tin_nhật_ký_ban_đầu, MaxSize = Kích_thước_tối_đa_của_tập_tin_nhật_ký, FileGrowth = Kích_thước_sẽ_được_thêm_vào_khi_cần_thiết ) Chú ý: * Các phần ON và LOG ON là các phần tùy chọn. Tuy nhiên nếu có sử dụng phần ON và LOG ON thì 2 thông tin NAME và FILENAME là bắt buộc. * Phần ON mô tả các thông tin về tập tin dữ liệu, LOG ON mô tả các thông tin về tập tin nhật ký. * Mặc định, phần mở rộng của tập tin dữ liệu là .mdf và tập tin nhật ký là .ldf. --Dùng db Master Use Master Go --Nếu tồn tại db ... thì xóa If Exists (Select Name From SysDataBases Where Name='db...') Drop DataBase db... Go --Tạo mới db ... Create DataBase db... On ( Name=db..., FileName='db.mdf' Size=10, MaxSize=50, FileGrowth=5 ) Log On ( Name='db...Log', FileName='db.ldf' Size=5MB, MaxSize=25MB, FileGrowth=5MB ) Go --Sau khi tạo xong thì dùng nó Use db... Go --====================================== --Nếu không tồn tại db... thì phục hồi db (= 2 file hay 1 file) If Not Exists (Select Name From Databases Where Name='db...') Exec Sp_Attach_DB 'db...','...:\db.mdf','...:\dbLog.ldf' --Exec Sp_Attach_Single_File_DB 'db...','...:\db.mdf' Go --====================================== If Exists (Select * From SysObjects Where Name Like 'Sp_SaoLuuDVL') Drop Procedure Sp_SaoLuuDVL Go Create Pro Sp_SaoLuuDVL (@TenCSDL VarChar(20),@TenTapTin VarChar(100)) As Backup Database @TenCSDL To Disk = @TenTapTin Biên soạn: Nguyễn Viết Nam 15 Tài liệu SQL Server Go --Sao lưu CSDL tên là db... lên đĩa vật lí với tên tập tin là db.bak Exec Sp_SaoLuuDVL 'db...','...:\db.bak' If Exists (Select * From SysObjects Where Name Like 'Sp_PhucHoiDVL') Drop Procedure Sp_PhucHoiDVL Go Create Proc Sp_PhucHoiDVL (@TenCSDL VarChar(20),@TenTapTin VarChar(100)) As Restore Database @TenCSDL From Disk = @TenTapTin Go --Phục hồi CSDL tên là db... từ đĩa vật lí với tên tập tin là db.bak Exec Sp_PhucHoiDVL @TenDVL,'...:\db.bak' --====================================== If Exists (Select * From SysObjects Where Name Like 'Sp_XoaTBCu') Drop Procedure Sp_XoaTBCu Go Create Proc Sp_XoaTBCu (@TenTB VarChar(20)) As If Exi