Lesson Administering and Troubleshooting SQL Server 2000: Part 2C

Chia sẻ: Đinh Gấu | Ngày: | Loại File: PDF | Số trang:46

Thêm vào BST

Báo xấu

39
lượt xem 2
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

In the knowledge byte section, tell the students about how to use the copy database wizard to copy or move databases and related objects to a different database server or another SQL Server instance. When using the copy database wizard you must have database administrator permissions on both the source and destination servers. Also, ensure that the second instance of SQL Server is installed, with the name SERVER\SECOND.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Lesson Administering and Troubleshooting SQL Server 2000: Part 2C

LESSON 2C COLLABORATE 2C. 1 Administering and Troubleshooting SQL Server 2000
INSTRUCTOR NOTES In the knowledge byte section, tell the students about how to use the Copy Database Wizard to copy or move databases and related objects to a different database server or another SQL Server instance. When using the Copy Database Wizard you must have database administrator permissions on both the source and destination servers. Also, ensure that the second instance of SQL Server is installed, with the name SERVER\SECOND. Ensure that the following datafiles are installed on the student nodes: Sales.mdb Administering and Troubleshooting SQL Server 2000 2C.2
KNOWLEDGE BYTE Collaborate Knowledge Byte In this section, you will learn about: • Copy Database Wizard • System Database Recovery • Severity Levels of Errors ©NIIT Collaborate Lesson 1C / Slide 1 of 23 In this section, you will be introduced to: Copy Database wizard. system databases for recovery operations. various error levels. 2C. 3 Administering and Troubleshooting SQL Server 2000
Using the Copy Database Wizard Collaborate Using the Copy Database Wizard • You use the Copy Database wizard to copy or move databases and related objects, such as tables and views from one server to another. For example, if a company wants to move to a new location, the database administrator can use the Copy Database Wizard to copy the current database to a new location. • You can also use the Copy Database Wizard if you want to: • move a database to another database server before you upgrade or perform maintenance tasks. • create a backup of your database on another computer. • modify the database properties without affecting the live database. ©NIIT Collaborate Lesson 1C / Slide 2 of 23 You use the Copy Database Wizard to copy or move databases and related objects, such as tables and views from one server to another. For example, if a company wants to move to a new location, the database administrator can use the Copy Database Wizard to copy the current database to a new location. You can also use the Copy Database Wizard to: move a database to another database server before you upgrade or perform maintenance tasks. create a backup of your database on another computer. modify the database properties without affecting the live database. The database administrator at Bluemun Inc. decides to move the database, HEADDB, from the default instance of the SQL Server, SERVER, to another instance, SERVER- _SECOND. To move the database to another instance of SQL Server, the database administrator performs the following steps: 1. Select StartÆAll ProgramsÆMicrosoft SQL ServerÆEnterprise Manager to open the Enterprise Manager. 2. Select the Wizards option from the Tools menu, to invoke the Select Wizard dialog box, as shown. Administering and Troubleshooting SQL Server 2000 2C.4
3. Expand the Management option and select the Copy Database Wizard option in the Select Wizard dialog box to start the Copy Database Wizard, as shown. 2C. 5 Administering and Troubleshooting SQL Server 2000
4. Click the OK button on the Select Wizard dialog box to continue. The Welcome to the Copy Database Wizard window appears, as shown. Administering and Troubleshooting SQL Server 2000 2C.6
5. Click the Next button to open the Select a Source Server page. Next, select the default instance of the Source server and verify that the Use Windows Authentication option is selected to specify windows authentication for authorizing a user, as shown. 6. Click the Next button to open the Select a Destination Server page. Specify the name of the destination server where the database is to be moved. For example, in the Destination server list box, specify the name of the destination server as SERVER\SECOND, where SERVER\SECOND is the name of the second instance that has already been created. 7. Select the Use Windows Authentication option to allow the user to connect to the database server using a Windows user account, as shown. 2C. 7 Administering and Troubleshooting SQL Server 2000
Administering and Troubleshooting SQL Server 2000 2C.8
8. Click the Next button to open the Select the Databases to Move or Copy page. Select the name of the database to move. Then, select the Move checkbox to move the HEADDB database to a new destination. 9. Click the Next button to move to the Database File Location page. Select the default destination of the database files to be copied. You can also click the Modify button to change the location of the database files. In addition, you can click the Refresh button to ensure that the destination drive and status are updated. 2C. 9 Administering and Troubleshooting SQL Server 2000
10. Click the Next button to move to the Select Related Objects page. In the Copy section, verify that all the four checkboxes are selected by default, as shown. Administering and Troubleshooting SQL Server 2000 2C.10
The various options available in the Select Related Objects page are: The Logins (recommended) option specifies the logins to be moved or copied to the destination. x The All logins detected at package runtime option copies all the logins from the source server to the destination server. x The Only logins used by the selected databases option copies only the logins that are used by the selected database. The Shared stored procedures from the master database (optional) option consists of the master databases that are included in the move or copy operation. x The All stored procedures detected at package runtime option copies all the stored procedures from the source to the destination server. x The User-selected stored procedures option copies only those stored procedures that are selected by the user. The Jobs from msdb (optional) option includes the jobs from msdb database in the move or copy operation. x The All jobs detected at package runtime option copies all the jobs from the msdb database source to the destination server. x The User-selected jobs option copies only those jobs that are selected by the user to the destination server. The User-defined error messages (optional) option includes user-defined error messages during the move or copy operation. x The All error messages detected at package runtime option copies all the error messages to the destination server. x The User-selected error messages option copies only those error messages that are selected by the user to the destination server. 11. Click the Next button in the Select Related Objects page to open the Schedule the DTS Package page. Select the Run immediately option to run the DTS job created by the wizard. Alternatively, you can schedule the DTS package to run later. 2C. 11 Administering and Troubleshooting SQL Server 2000
12. Click the Next button to open the Completing the Copy Database Wizard page. Verify the settings and the options that you selected. In case you want to modify the settings, you need to click the Back button. Administering and Troubleshooting SQL Server 2000 2C.12
13. Finally, click the Finish button to move the HEADDB database to the specified destination database server. Notice that the Log Detail dialog box appears. In the Log Detail dialog box, you can view the job that is being executed. You can click the More Info button to view the information and outcome of each step of the job, as shown. 2C. 13 Administering and Troubleshooting SQL Server 2000
14. A message indicating the successful transfer of the database appears after the job is competed, as shown. 15. Click the OK button to close the dialog box. Finally, click the Close button on the Log Detail dialog box to complete the process. System Database Recovery Collaborate System Database Recovery • After SQL Server 2000 is successfully installed, the setup wizard creates the system and sample databases. • SQL Server 2000 has the following system databases: • master • tempdb • model • msdb ©NIIT Collaborate Lesson 1C / Slide 3 of 23 Administering and Troubleshooting SQL Server 2000 2C.14
Collaborate System Database Recovery (Contd.) • As a database administrator you would make backup of the system database only when you: • create or modify a database • create new logins • change the configuration ©NIIT Collaborate Lesson 1C / Slide 4 of 23 2C. 15 Administering and Troubleshooting SQL Server 2000
After SQL Server 2000 is successfully installed, the setup wizard creates the system and sample databases. SQL Server 2000 has the following system databases: master: Records system level information, such as login accounts, configuration settings, and the location of the database files tempdb: Holds the temporary files and stored procedures model: Serves as a template for all the databases created on the database server. When a database is created, the schema of the model database is copied and the remaining part of the new database is filled with empty pages. msdb: Schedules operators, jobs, and alerts Users can use sample databases, such as Pubs and Northwind in SQL Server 2000 as learning tools. The backup and recovery requirements for system and user databases can be different. For example, database administrators may want to make a backup of user databases on a daily basis. However, in the case of system databases, administrators may want to make backups only on a weekly or monthly basis. Typically, you would make a backup of a system database only when you: create or modify a database. create new logins. change the configuration settings. Administering and Troubleshooting SQL Server 2000 2C.16
Severity Levels of Errors Collaborate Severity Levels of Errors • Severity levels indicate the type of problem, based on the error message. Error severities in SQL Server 2000 have their numeric equivalents that indicate how severe an error is. • For example, the severity level, 23, indicates that the entire database has been damaged and the hardware needs to be checked. ©NIIT Collaborate Lesson 1C / Slide 5 of 23 Severity levels indicate the type of problem, based on the error message. Error severities in SQL Server 2000 have their numeric equivalents that indicate how severe an error is. For example, the severity level, 23, indicates that the entire database has been damaged and the hardware needs to be checked. Whenever an error occurs, an alert message is displayed. The alert message indicates the severity level of the error. You can create an alert in SQL Server 2000 to trigger a message to a database user when the database fails or uses all the available free space. SQL Server 2000 issues alerts to a database user through email, pager, or the net send network command. Alerts are based on an error number, error security level, and performance counter. The error number is a pre-defined number associated with an error message. For example, 007 is the error number for the error message: “Notification: Status Information”. 2C. 17 Administering and Troubleshooting SQL Server 2000
The table below describes some of the common severity levels: Severity Level Description 10 Provides information about the errors in the data entered by the user 11-16 Indicates that the users can correct the error 22 Indicates that the table or index is damaged 23 Indicates that the entire database is damaged 24 Indicates hardware failure INSTRUCTOR NOTES In this section, tell the students about the best practices to perform backup operations and create alerts. In addition, present tips to the students about the system database recovery and backup processes. Administering and Troubleshooting SQL Server 2000 2C.18
FROM THE EXPERT’S DESK Collaborate From the Expert’s Desk • Best Practices • Tips • FAQs • Challenge ©NIIT Collaborate Lesson 1C / Slide 6 of 23 In this section, you will learn how to efficiently make a backup of the entire database using the Server-less snapshot feature. You will also learn to create alerts for error numbers. In addition, you will identify tips on system database recovery and backup. 2C. 19 Administering and Troubleshooting SQL Server 2000
Best Practices Server-less Snapshot Backup Collaborate Best Practices Server-less snapshot backup • Server-less snapshot backup is a new feature in SQL Server 2000 that allows you to make a backup of the entire database on the server. Database administrators use the Server-less snapshot backup in organizations that maintain large volumes of data and have a large number of transactions. • A Server-less snapshot backup provides the following advantages: • consumes low resources • maintains the performance of the server • restores the primary server quickly ©NIIT Collaborate Lesson 1C / Slide 7 of 23 Server-less snapshot backup is a new feature in SQL Server 2000 that allows you to make a backup of the entire database on the server. Database administrators use the Server-less snapshot backup in organizations that maintain large volumes of data and have a large number of transactions. Typically, such organizations require alternative servers if the primary server becomes unavailable. Making a backup ensures that users can continue working with the databases. A Server-less snapshot backup enables the database administrators to create a standby server that can be accessed when the primary server is down. A Server-less snapshot backup provides the following advantages: Consumes low resources Maintains the performance of the server Restores the primary server quickly Administering and Troubleshooting SQL Server 2000 2C.20