Chương 9. Chức năng quản trị SQL Server

10/03/2015 1 Chương 9. Chức năng quản trị SQL Server TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH GV: Lê Thị Minh Nguyện Email: leminhnguyen@itc.edu.vn NỘI DUNG  Tại sao phải backup dữ liệu  Các loại backup  Attach và Detach cơ sở dữ liệu  Copy cơ sở dữ liệu  Import và Export cơ sở dữ liệu  Chức năng tự động  Cấu hình IP và Port 10/03/2015 2 1.Tại sao phải backup dữ liệu  Ðĩa chứa data file hay Transaction Log File hay system file bị mất  Server bị hư hỏng  Những thảm họa tự nhiên như bão lụt, động đất, hỏa hoạn  Toàn bộ server bị đánh cắp hoặc phá hủy  Các thiết bị dùng để backup - restore bị đánh cắp hay hư hỏng  Những lỗi do vô ý của user như lỡ tay delete toàn bộ table chẳng hạn  Những hành vi mang tính phá hoại của nhân viên như cố ý đưa vào những thông tin sai lạc.  Bị hack (nếu server có kết nối với internet). 3 2.Các loại backup Các đối tượng backup dữ liệu Các kiểu backup Tạo thiết bị backup dữ liệu Phục hồi CSDL (Restore Database) 4 10/03/2015 3 2.1.Các đối tượng backup dữ liệu Tạo bảng sao của các đối tượng CSDL như table, view, stored procedure, function, trigger, user, role, rule, default, các ràng buộc, nhật ký giao tác Thiết bị backup:  File trên đĩa cục bộ  File trên mạng  Tape 2.2.Các kiểu Backup  Full Backup : backup toàn bộ dữ liệu tại một thời điểm  Differential Backup: backup dữ liệu phát sinh sau thời điểm xảy ra Full Backup  File/Filegroup Backup: backup những file và filegroup  Transaction Log Backup: backup các log record hiện có trong log file, nghĩa là nó sao lưu các hành động (các thao tác xảy ra đối với database) chứ không sao lưu dữ liệu.  Partial Backup: sao lưu một phần  Copy Only Backup:Chỉ sao chép các bản sao lưu có thể được thực hiện trên các tập tin cơ sở dữ liệu và các bản ghi giao dịch để tạo ra một bản sao lưu mà không ảnh hưởng tới chuỗi của các sao lưu cần thiết để khôi phục lại một cơ sở dữ liệu. 6 10/03/2015 4 FULL BACKUP Cú pháp Backup database TO Ví dụ BACKUP DATABASE AdventureWorks TO DISK = ‘D:\Backup\AdventureWorks.bak’ 7 2.2.Các kiểu Backup Differential Backup Cú pháp: Backup database TO WITH DIFFERENTIAL Ví dụ BACKUP DATABASE AdventureWorks TO DISK = ‘D:\Backup\AdventureWorks.bak’ WITH DIFFERENTIAL 8 2.2.Các kiểu Backup 10/03/2015 5 File/Filegroup Backup --Backup of the "SWUserData1" User-Defined Filegroup BACKUP DATABASE SmallWorks FILEGROUP = ‘SWUserData1' TO DISK=‘D:\Backup\SmallWorksUserData1FG.BAK’ --Differential Filegroup Backup of the "SWUserData1" User-Defined Filegroup BACKUP DATABASE SmallWorks FILEGROUP = 'SWUserData1' TO DISK = ‘D:\Backup\SmallWorksUserData1FGDIFF.BAK' WITH DIFFERENTIAL 9 2.2.Các kiểu Backup Transaction Log Backup Cú pháp: BACKUP LOG TO Ví dụ: BACKUP LOG AdventureWorks TO DISK = „C:\Backup\AdventureWorks.bak‟ 10 2.2.Các kiểu Backup 10/03/2015 6 Copy Only Backup Cú pháp: Backup database TO WITH COPY_ONLY Ví dụ: BACKUP DATABASE SmallWorks TO DISK = 'C:\SQLData\SmallWorksCopyOnly.BAK' WITH COPY_ONLY 11 2.2.Các kiểu Backup 2.3.Tạo thiết bị backup dữ liệu Nếu backup nhiều cơ sở dữ liệu sử dụng backup device Cú pháp: Sp_addumpdevice „device_type‟, „logical_name‟, „physical_name‟ device_type: có thể là (disk, pipe, tape) Dùng MS 12 10/03/2015 7 2.4.Phục hồi csdl (restore databse) Phục hồi tòan bộ CSDL Restore Database From Ví dụ: RESTORE DATABASE AdventureWorks FROM DISK =‘C:\Backup\AdventureWorks.bak’ Phục hồi giao tác Restore Log From 13 Khi CSDl bị nguy hiểm, qúa trình phục hồi nên theo các bước sau:  Backup nhật ký các giao tác Backup log qltv to qltvLog with no_truncate  Phục hồi CSDL từ bảng backup gần nhất  Phục hồi nhật ký các giao tác Sử dụng khi backup 1 csdl đang trong trình trạng không họat động 14 2.4.Phục hồi csdl (restore databse) 10/03/2015 8 3.Attach và detach cơ sở dữ liệu Detach là quá trình gỡ bỏ csdl ra khỏi Instance của SQL Server. Cú pháp detach: sp_detach_db ‘tên_database’ Dùng MS  Chọn tên csdl  Chép tập tin .mdf và .ldf 15 Cú pháp detach: sp_attach_db „tên_database’ , „đường_dẫn_và_tên_tập_tin_mdf’, „đường_dẫn_và_tên_tập_tin_ldf’ Lưu ý: chép tập tin .mdf và .ldf vào thư mục MSSQL\Data trước khi thực hiện attach 16 3.Attach và detach cơ sở dữ liệu 10/03/2015 9 4.Copy cơ sở dữ liệu 17 5.Import và Export dữ liệu 18 10/03/2015 10 6.Quy trình backup tự động SQL Server Thiết lập quyền chạy service SQL Server Agent  Bước 1: Vào Start->Run->gõ cmd -> gõ Services.msc, kiểm tra service SQL Server Agent(SQL2005) đã được chạy với account admin của mạng chưa? Nếu chưa thì tiến hành thiết lập account admin của mạng cho service này.  Bước 2: Thiết lập quyền chạy service SQL Server Agent  Click chuột phải vào service SQL Server Agent(SQL2005). 19  Chọn tab Log On, Chọn option Log on as this account. Nhập tên đăng nhập và mật khẩu admin mạng của máy tính cài đặt SQL.  Click OK khi thực hiện nhập xong, tiến hành restart lại service.  Lưu ý: Trong trường hợp người quản trị máy chủ thay đổi mật khẩu của account quản trị máy tính đó thì phải tiến hành nhập lại mật khẩu cho service SQL Server Agent. 20 6.Quy trình backup tự động SQL Server 10/03/2015 11 Backup tự động cơ sở dữ liệu SQL Server 2005(Full Backup)  Buớc 1: Vào SQL Server management Studio (Vào Start- >Program->Microsoft SQL Server 2005 ->SQL Server Management Studio) 21 Login với tài khoản quản trị cơ sở dữ liệu(sa) hoặc account NT 6.Quy trình backup tự động SQL Server Bước 2: Mở rộng mục Tên máy -> Management -> Maintenance Plans. Kích phải chuột để chọn Maintenance Plans Wizard. 22 6.Quy trình backup tự động SQL Server 10/03/2015 12 23 - Nhập tên, Description. - Chọn tùy chọn: Single schedule for the entire plan or no schedule - Click vào nút “Change” để thay đổi lịch backup dữ liệu. Cửa sổ hiển thị 6.Quy trình backup tự động SQL Server 24 - Nhập các thông tin như bên trên bao gồm: Thời gian backup hàng tuần, ngày backup là Chủ nhật, thời điểm backup là 12 giờ đêm - Click OK sau khi thực hiện cấu hình các thông số. Click Next 6.Quy trình backup tự động SQL Server 10/03/2015 13  Cửa sổ Chọn Maintenance Task: 25 Chọn task “Back Up Database(Full)” và “Maintenance Cleanup Task” 6.Quy trình backup tự động SQL Server Chọn Next tới của sổ tên Select Maintenance Task Order. 26 6.Quy trình backup tự động SQL Server 10/03/2015 14 Tiếp tục click Next Đến cửa sổ: Define Back Up Database(Full) Task 27 Thực hiện các thiết lập như sau: - Chọn Database cần backup (check vào những database nào cần backup) - Chọn tùy chọn “Create a backup file for every database”: Chọn đến folder chỉ định để lưu file backup - Chọn định dạng file mở rộng là bak 6.Quy trình backup tự động SQL Server Tiếp tục click Next Đến cửa sổ: Define Maintenance Cleanup Task 28 Cửa sổ thiết lập các cấu hình về việc xóa file backup định kỳ. - Chọn các tùy chọn hình bên trên. - Chọn folder chứa file backup - Chọn định dạng file mở rộng là bak - Thiết lập thời gian xóa file backup cũ sau 4 tuần 6.Quy trình backup tự động SQL Server 10/03/2015 15 Tiếp tục click Next Đến cửa sổ: Select Report Options 29 § Bỏ chọn tất cả § Click Next Đến cửa sổ Finish. Click finish để kết thúc quá trình 6.Quy trình backup tự động SQL Server 30 6.Quy trình backup tự động SQL Server 10/03/2015 16 7.Cấu hình IP và Port Khi phát triển các ứng dụng Web, hoặc các ứng dụng desktop đòi hỏi dữ liệu tập trung tức thì để phục vụ cho việc ra báo cáo mà chương trình thì lại cách xa nhau về mặt địa lý, thì bạn sẽ cần đến tính năng kết nối từ xa của SQL Server. Tính năng này có trên tất cả các phiên bản SQL Server. 31 Mục đích là kích hoạt tính năng cho phép kết nối từ xa của SQL Server và thiết lập cổng nghe (Listenning Port) cho SQL Server.  Vào Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager.  Ở menu bên trái, chọn vào mục SQL Server 2005 Services -> Bên phải tìm mục SQL Server (MSSQLSERVER) (chú ý đây là dịch vụ cơ bản của SQL Server, tên của nó tùy thuộc vào Instant của SQL mà bạn cài vào máy. Click phải vào và chọn Properties 32 7.Cấu hình IP và Port 10/03/2015 17 33 7.Cấu hình IP và Port Trong Tab Log On, click chọn vào Built-In Account, chọn vào Network Service như trong hình, Click OK 34 7.Cấu hình IP và Port 10/03/2015 18  Quay lại màn hình SQL Server Configuration Manager - > ở menu bên trái tiếp tục click vào mục SQL Server 2005 Network Configuration để mở ra menu Protocols for 35 7.Cấu hình IP và Port Trong cửa sổ mở ra, ở Tab Protocols, mục Enabled chọn vào Yes 36 7.Cấu hình IP và Port 10/03/2015 19  Tiếp tục click qua Tab IP Address -> Sẽ xuất hiện list các IP (IP1, IP2,) đây là danh sách các IP hình thành khi máy bạn có kết nối vào các mạng LAN khác nhau Hãy chọn một IP nào đó bất kỳ · Ở mục IP Address bạn xóa đi và gõ lại địa chỉ IP của máy bạn trong mạng LAN (chú ý đây là địa chỉ IP của máy bạn trong mạng LAN – ví dụ của tôi là 192.168.1.2) · Ở mục Active - chọn Yes, mục Enabled - chọn Yes · Ở mục TCP Port, khai báo cổng share mặc định của SQL, bạn có thể để mặc định là 1433 · Click OK Restart lại SQL Server. (bằng cách chọn vào mục SQL Server 2005 Services, bên phải click phải vào mục SQL Server (MSSQLSERVER) -> Chọn Restart)] 37 7.Cấu hình IP và Port Bước 2: Tạo một user để thực hiện kết nối từ xa 38 7.Cấu hình IP và Port 10/03/2015 20 Gõ vào Tên User, Password như trong hình, bỏ dấu ở mục Enforce password Expiration -> Nhấn OK 39 7.Cấu hình IP và Port Bước 4: Cấu hình Firewall cho phép nhận kết nối đến qua cổng share của SQL · Vào Control Panel -> Windows Firewall · Trong Tab Exceptions, click vào nút Add Port để thêm cổng 1433 · Hộp thoại Add Port hiện ra, trong mục Name gõ tên bất kỳ, Port Number gõ số 1433, click chọn vào mục TCP (xem hình dưới) · Nhấn OK · Log Off hoặc Restart lại máy 40 7.Cấu hình IP và Port 10/03/2015 21 41 Cấu hình IP và Port Trên Windows Vista, Windows 7: Vào Start -> Control Panel -> Windows Firewall -> Ở menu bên trái chọn Advanced settings Menu bên trái, click vào mục Inbound Rules Tiếp tục ở Menu bên phải, click vào mục New Rule Trong cửa sổ mới hiện ra, đánh dấu vào mục Port như hình -> Nhấn Next 42 7.Cấu hình IP và Port 10/03/2015 22 43 7.Cấu hình IP và Port 44 Cấu hình IP và Port 10/03/2015 23 Bước 4: Cấu hình Router cho phép kết nối qua Port 1433 Mục đích của việc này: · Mở cổng của Router trên mạng của bạn để cho phép nhận kết nối từ bên ngoài mạng (từ Internet) qua cổng kết nối của SQL Server (Port 1433). · Chuyển hướng (Forward) kết nối về đến đúng máy mà bạn làm Server khi Router nhận được yêu cầu qua cổng 1433 45 7.Cấu hình IP và Port Tùy loại Router mà bạn đang sử dụng, việc cấu hình có thể khác nhau đôi chút. Ở đây tôi đang thực hiện config trên một loại Router của Dlink. Các loại Router khác bạn có thể tự tìm hiểu thêm. Cách thiết lập trên các loại Router cơ bản là giống nhau, chỉ khác nhau về cách bố trí trên menu thôi. Đầu tiên mở IE ra và gõ địa chỉ của Gateway -> Enter và đăng nhập vào quản trị Router Ví dụ: Gateway của tôi là 192.168.1.1 thì tôi gõ vào Address của IE là -> Enter Hãy đọc hướng dẫn sử dụng kèm theo router của bạn để biết được địa chỉ gateway cũng như user và password đăng nhập vào quản trị. 46 7.Cấu hình IP và Port 10/03/2015 24 47 7.Cấu hình IP và Port  · Custom Server: Tên của thiết lập, do bạn tự đặt tùy ý · Server IP Address: Đây là địa chỉ IP của máy mà bạn cài làm máy chủ. Ví dụ của tôi là 192.168.1.2 Chú ý: Đây chính là IP của máy mà yêu cầu sẽ được Router chuyển đến. Do đó bạn nên khai báo IP tĩnh cho máy bạn cài làm máy chủ để khỏi phải vào mục này thiết lập lại mỗi khi restart máy. Xin nhớ rằng đây là IP của máy trong mạng LAN chứ không phải là IP đối với mạng Internet. 48 7.Cấu hình IP và Port 10/03/2015 25 · Port Start, Port End là số hiệu của cổng nhận yêu cầu, đều khai báo là 1433 Ở đây sở dĩ có Port Start và Port End là vì Router cho phép bạn Forward trong cả một dải các cổng (từ cổng đến cổng). Đối với kết nối SQL Server chúng ta đang thực hiện thì chỉ cần Port 1433 thôi. Một số loại router có mục Single Port Forwarding – cho phép bạn chỉ cần config một cổng thôi là đủ. · Mục Protocol: Chọn phương thức kết nối là TCP 49 7.Cấu hình IP và Port Để test kết quả: · Hãy ngồi ở một máy tính nào đó ngoài mạng LAN của bạn, có kết nối Internet. · Dùng một chương trình quản lý SQL Server (có thể dùng SQL Server Management Studio), gõ vào các thông tin kết nối -> Nhấn Connect 50 7.Cấu hình IP và Port 10/03/2015 26 TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH