MASTERING SQL SERVER 2000- P13

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
35
lượt xem
5
download

MASTERING SQL SERVER 2000- P13

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

Tham khảo tài liệu 'mastering sql server 2000- p13', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: MASTERING SQL SERVER 2000- P13

  1. 620 CHAPTER 16 • BASIC ADMINISTRATIVE TASKS 13. Click OK on the message that informs you of success and then click Close on the Log Detail screen to complete the Wizard. The Copy Database Wizard is a simple tool that makes a complex task much easier. Summary In this chapter, we talked about how to administer and maintain your databases so that they will always be running in top condition. The first topic to come up was backups; there are many reasons to back up data: natural disaster, hardware malfunction, even people with malicious intent. If you per- form regular backups, you can overcome these problems. There are four types of backups to help you thwart the evils that would claim your data. First, there is the full backup, the basis of all other backups, which makes a copy of the entire database. Next, the differential backup grabs all of the changes made to
  2. SUMMARY 621 the database since the last full backup. The transaction log backup came next and is PA R T very useful for quick backup strategy, point-in-time restores, and clearing the transac- IV tion log on a periodic basis. Finally, there is the filegroup backup, used to make back- ups of small chunks of very large databases. After a discussion of backups, we hashed out the fine points of index maintenance. Administering SQL It is very important to keep your indexes up to snuff so that data access will always be fast. To keep them in top shape, use DBCC SHOWCONTIG to determine fragmenta- tion and then use CREATE INDEX with the DROP_EXISTING option or DBCC DBREINDEX to reconstruct fragmented indexes. Server After that, we looked at the importance of monitoring the SQL Server event logs as well as the mechanics of doing so. Finally, we discussed the Copy Database Wizard. Now that you know that you need to perform all of these tasks, probably on a nightly or weekly basis, wouldn’t it be nice if you could have someone else do it for you? In the next chapter, we’ll discuss automation; you will learn how to make SQL Server do a lot of your work for you, including backups.
  3. This page intentionally left blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. CHAPTER 17 Automating Administration F E AT U R I N G : Automation Basics 625 Configuring Mail Support 627 Creating Operators 629 Creating Jobs 631 Creating Alerts 647 Using the Database Maintenance Plan Wizard 660 Working with SQL Mail 671 Summary 673
  5. T hroughout this book, we have discussed administrative activities that would best be performed during off-hours. These activities include backing up data- bases, creating large databases, reconstructing indexes—the list goes on. Most of these activities will need to be performed on a regular basis, not just once. For example, you will need to back up at frequent intervals. Because most administrators would rather not need to stand at the SQL Server to start the task in question, SQL Server has the built-in capability to automate tasks. The first thing we need to discuss is the basics of how automation works in SQL Server. We’ll explain some of the basic concepts of automation and how the SQLServerAgent service plays a part. After we discuss the basics of automation, we will need to set up and configure e-mail support, because SQL Server is capable of sending you e-mail when there is a problem as long as e-mail is configured properly. Not only that, but SQL Server can receive and process queries via e-mail, and send the result set back in an e-mail to the sender. However, all of this can be done only when e-mail is configured. Next we will configure operators. An operator is a person who is able to receive messages from SQL Server via e-mail, pager, or Net Send. Configuring an operator tells SQL Server whom to contact and when they are available. After we have operators in place, we can start creating jobs, the heart of automa- tion. Jobs are the activities that you need to administer, such as database backups or index reconstruction. We will discuss each part of a job, the steps required to com- plete the job, and the schedules that tell SQL Server when to run the job. We will also go over the process of creating multiserver jobs, which can be created on one server and run on multiple servers over a network. Next we will configure alerts, which are used to warn you of problems or events that have occurred on the server. Not only will we discuss how to configure standard SQL Server alerts, but we will discuss the methods for creating your own user-defined alerts to cover any possible event that may occur on your server. After all of this, we will discuss the Database Maintenance Wizard. This special Wizard is designed to automate all of the standard database maintenance procedures such as backups, index reconstruction, transaction log backup, etc. Finally, we will discuss the uses and configuration of SQL Mail. Using this tool, you can e-mail a query to SQL Server and have it respond with a result set via e-mail. This tool can potentially save you a lot of time and effort with reporting when used properly. We’ll start this chapter with a discussion of the basics of automation.
  6. AUTOMATION BASICS 625 Automation Basics PA R T IV Nearly any administrative task you can think of can be automated through SQL Server. True, that may sound like an exaggeration, but look at the things that you can Admninistering SQL automate: • Any Transact-SQL code • Scripting languages such as VBScript or JavaScript • Operating system commands Server • Replication tasks (which we’ll learn about in Chapter 27) Some popular tasks to automate using this functionality are as follows: • Database backups • Index reconstruction • Database creation (for very large databases, or VLDBs) • Report generation • Web-page creation (as seen in Chapter 23) Because this functionality is so powerful, it is easy to see why you need to use SQL Server’s automation capabilities. However, before you start to use this functionality, you need to know how it works. At the very heart of SQL Server’s automation capability is the SQLServerAgent ser- vice (also referred to as the agent). In fact, automation and replication are the sole functions of that service. This service uses three subcomponents to accomplish its automation tasks: alerts, operators, and jobs. Alerts: An alert is an error message or event that occurs in SQL Server and is recorded in the Windows NT Application log. Alerts can be sent to users via e-mail, pager, or Net Send. If an error message is not written to the Windows NT application log, an alert will never be fired off. Operators: When an alert is fired, it can be sent to a user. Users who need to receive these messages are known in SQL Server as operators. Operators are used to configure who will receive alerts and when they are available to receive these messages. Jobs: A job is a series of steps that define the task to be automated. It also defines schedules, which dictate when the task is to be executed. Such tasks can be run only one time or on a recurring basis.
  7. 626 CHAPTER 17 • AUTOMATING ADMINISTRATION These three components work together to complete the tapestry of administration. Here is an example of what may happen: 1. A user may define a job that is specified to run at a certain time. 2. When the job runs, it fails and thus writes an error message to the Windows NT event log. 3. When the SQLServerAgent service reads the Windows NT event log, the agent finds the error message that the failed job wrote and compares that to the sysalerts table in the MSDB database. 4. When the agent finds a match, it fires an alert. 5. The alert, when fired, can send an e-mail, pager message, or Net Send message to an operator. 6. The alert can also be configured to run another job, designed to repair the prob- lem that caused the alert. For any of this to function, though, the SQLServerAgent service must be properly configured. To start, the agent must be running for automation to work. There are three ways to verify this: First, you can open Enterprise Manager, expand Manage- ment, and notice the SQL Server Agent icon—if it is a red square, the service is stopped; if it is a green arrow, the service is started. You can even start the service by right-clicking the icon and selecting Start. Other methods of checking and changing the state of the service are by using the Service Manager (which can be found in the task tray of the Start bar) or by using the Services applet in the Control Panel. Not only should the agent be running, but it is best to have it log on with a domain account as opposed to a local system account, because using the local system account will not allow you to work with other SQL Servers on your network. This means that you will not be able to perform multiserver jobs (discussed later in this chapter), replication (discussed in Chapter 27), or use SQL Server’s e-mail capabilities. To make sure the agent is logging on with a domain account, you should open the Services applet in Control Panel (if you are using Windows 2000, you will find it in Administrative Tools under Programs on the Start menu), double-click the SQLServer- Agent service, and select a domain account by clicking the ellipsis box next to This Account. Once all of this is in place, you are nearly ready to begin working with automation. First, you should configure SQL Server to be able to send and receive e-mail.
  8. CONFIGURING MAIL SUPPORT 627 Configuring Mail Support PA R T IV The services that comprise SQL Server can send and receive e-mail. Specifically, the SQLServerAgent service works with SQLAgentMail, which the agent uses to send Admninistering SQL e-mail to administrators when an alert has fired. The MSSQLServer service works with SQL Mail, which the service uses to receive queries from users and reply with a result set; it is a lot like executing a query through Query Analyzer, only via e-mail. To configure either of these types of mail, you must have a mail account some- where. Exchange works best because both Exchange and SQL Server are parts of the Server Microsoft BackOffice family, but it is also possible to use an Internet e-mail account. The first step in making this work is to create a mailbox on the e-mail server to which you will be connecting. If you are using Microsoft Exchange 5.5, this is what you do: 1. Open the Exchange Administrator from the Microsoft Exchange 5.5 group under Programs on the Start menu. 2. From the File menu, select New User. 3. Select the user account that the SQL Server services use to log on as the Primary Account for the mailbox. 4. Fill in the remaining information as appropriate. If you are using Exchange 2000: 1. Open up Active Directory Users and Computers. 2. Right-click the account that the SQL Server services use to log on and select Exchange Tasks. 3. Follow the steps in the subsequent Wizard to Mail-Enable the account. Once you have a mailbox on the server, you need to install Microsoft Outlook so that the server can make a MAPI connection to the server with which it will be work- ing. The process of installing Outlook is a little outside the scope of this book, but it is a relatively easy process driven by a Wizard. After Outlook has been successfully installed, you need to create a mail profile for the SQL Server account: 1. Log on to the SQL Server as the SQL Server services account. 2. Open Outlook. 3. Outlook will come up with an error message stating that it is improperly config- ured. Click OK.
  9. 628 CHAPTER 17 • AUTOMATING ADMINISTRATION 4. A dialog box will pop up asking you which mail transports you would like to use. If you have an Exchange server, select it from the list. If not, you can select Internet Mail from the list. 5. If you selected Exchange, a dialog box will appear asking you for the names of the mailbox and e-mail server. Fill both of these in and click OK. 6. If you are using Internet Mail, you will be asked for your Internet mail account information. 7. Outlook will now configure a series of sample messages, and the Microsoft Office Assistant should appear (only if you opted to install it). Click the Start Using Outlook choice. 8. Close Outlook and log off Windows. Once Outlook is installed and you have a mail profile created, you can configure the SQLServerAgent and MSSQLServer services to start using the new profile to send and receive mail: 1. Open Enterprise Manager by selecting it from the SQL Server 2000 group under Programs on the Start menu. 2. Expand your server, then Management. 3. Right-click the SQLServerAgent and select Properties. 4. In the Properties dialog box, select the mail profile that you created while logged in as the SQL Server service account.
  10. CREATING OPERATORS 629 5. Click Test to verify that the mail profile works. PA R T 6. Click OK at the bottom of the dialog box, and then click OK when asked to stop IV and restart the SQLServerAgent service. With a mail profile successfully configured, you can now create operators that will Admninistering SQL receive e-mail from the SQL Server. Creating Operators Server Several settings need to be configured for SQL Server to be able to contact you when there are problems. Such settings include whom to contact, when they are available, how those people should be contacted (via e-mail, pager, or Net Send), and of what problems should they be alerted. An operator is the object used in SQL Server to con- figure all of these settings. NOTE Net Send messages are messages that are sent from a source machine to a des- tination machine, where they pop up on a user’s screen as a dialog box over all of the open applications. Suppose, for example, that there are several people in your company who need to be alerted when there is a problem with SQL Server, each of them needing to be alerted for different problems and in various ways. Your database administrator may need to be alerted of any administration issues (for example, a failed backup or full transaction log) via e-mail and pager. Your developers may need to be alerted to pro- gramming issues (for example, deadlocks) via e-mail. Perhaps managers in your com- pany need to know of other issues, such as when a user deletes a customer from a customer database, and they want to be alerted by a Net Send message. These types of users would be handled by creating separate operators for each and configuring the desired settings. Let’s configure an operator here to demonstrate: 1. Open Enterprise Manager by selecting it from the SQL Server 2000 group under Programs on the Start menu. 2. Expand your server, then Management, then the SQLServerAgent. 3. Click the Operators icon and select New Operator from the Action menu. 4. In the Name box, enter Administrator.
  11. 630 CHAPTER 17 • AUTOMATING ADMINISTRATION 5. If you configured your system to use SQLServerAgent Mail, enter your e-mail address as the e-mail name. If you did not configure your system to use e-mail, skip this step. 6. Type the name of your machine in the Net Send box. This can be found by right-clicking the My Computer icon on the desktop and selecting Properties, then the Network Identification tab. The computer name is the first section of the full computer name (before the first period). If your full computer name is instructor.domain.com, the computer name is instructor. 7. At the bottom of the screen, select the days and times this operator is available for notification. If a day is checked, the operator will be notified on that day between the start and end times noted under Workday Begin and Workday End. 8. To test the operator, click the Test buttons next to each of the three notification methods. The e-mail and pager tests will both send an e-mail, and the Net Send test will cause a dialog box to pop up on your screen. 9. We’ll discuss the Notifications tab later; for now, click OK to create the operator. Because operators can be made active at different times, it is possible to acciden- tally leave a small period of time uncovered. If there is an error in that window of time, no operator would receive the alert, because none are on duty. To avoid such a
  12. CREATING JOBS 631 problem, you should create a fail-safe operator, which is designed to receive alerts PA R T when no one is scheduled to be on duty. Here is how to create one: IV 1. In Enterprise Manager, right-click the SQL Server Agent icon under Manage- ment and select Properties. Admninistering SQL 2. On the Alert System tab, select yourself in the drop-down list next to Fail-Safe Operator. 3. Check the box next to Net Send so that you will receive Net Send messages as a fail-safe operator. Server 4. Click OK to apply the changes. With an operator in place, you are ready to start creating jobs to automate tasks. Creating Jobs A job is a series of tasks that can be automated to run whenever you need them to. It may be easier to think of it as being somewhat like cleaning your house. Most of us think of cleaning our house as one big job that needs to be done, but it is really just a series of smaller tasks such as dusting the furniture, vacuuming the carpet, doing the dishes, etc. Some of these steps need to be accomplished in succession (for example, dusting before vacuuming); others can happen anytime (for example, the dishes don’t need to be done before you can wash the windows). Any job on SQL Server works in much the same way. Take, for example, a job that creates a database. This is not just one big job with one step to accomplish before you’re done; there are several steps that should take place. Step one would be to create the database. The next step would be to back up the newly created database, because it is in a vulnerable state until it is backed up. After the database has been backed up, you can create some tables in it and then perhaps import data into those tables from text files. Each of these tasks is a separate step that needs to be completed before the next can be started, but not all jobs are that way. By controlling the flow of the steps, you can build error correction into your jobs. For example, in the create-database job listed above, each step would have simple logic that states on success go to the next step; on failure quit the job. So if the hard disk turned out to be full, the job would stop. If you create a step at the end of the job that is designed to clear up some hard-disk space, you could create logic that states if step one fails, go to step five; if step five succeeds, go back to step one. With the steps in place, you are ready to tell SQL Server when to start the job.
  13. 632 CHAPTER 17 • AUTOMATING ADMINISTRATION To tell SQL Server when to run a job, you need to create schedules, and you have a lot of flexibility there. With a job that creates a database, it would not make much sense to have it run more than once, so you would create a single schedule that will activate the job after-hours. If you were creating a job that is designed to perform transaction log backups, you would want a different schedule. You may want to per- form these backups every 2 hours during the day (from 9:00 A.M. to 6:00 P.M.) and then every 3 hours at night (from 6:00 P.M. to 9:00 A.M.). In this instance, you would need to create two schedules, one that is active from 9:00 A.M. to 6:00 P.M. that acti- vates the job every 2 hours and another that is active from 6:00 P.M. to 9:00 A.M. that activates the job every 3 hours. If you think that’s fancy, you’ll love this next part. Not only can you schedule a job to activate at certain times of the day, you can schedule them to activate only on certain days of the week (for example, every Tues- day), or you can schedule them to run only on certain days of the month (for example, every third Monday). Jobs can be scheduled to run every time the SQLServerAgent service starts up, and they can even be scheduled to run every time the processor becomes idle. Schedules can be set to expire after a certain amount of time, so if you know you are going to be done with a job after a few weeks, you can set it to expire—it will automatically be disabled (not deleted, just shut off). You also have the capacity to be notified of the outcome of a job. On the final tab of the Create Job dialog (which you will see very soon), you can add an operator to the job that can be notified on success, on failure, or on completion (no matter whether it failed or succeeded). This comes in very handy when the job you are run- ning is critical to your server or application. With the ability to change the logical flow of steps, schedule jobs to run whenever you want, and have them notify you on completion, you can see how complex jobs can become. With this complexity in mind, it is always a good idea to sit down with pencil and paper, and plan out your jobs before creating them; it will make your job easier in the long run. There are two types of jobs in SQL Server, local and multiserver. Let’s look at each of these, starting with local jobs. Creating Local Server Jobs Local jobs are standard jobs with a series of steps and schedules. They are designed to run on the machine where they are created, hence the name local jobs. To demon- strate local jobs, let’s create one that will create a new database and then back it up: 1. Open Enterprise Manager by selecting it from the SQL Server 2000 group under Programs on the Start menu. 2. Expand your server, then Management, then SQLServerAgent. 3. Select Jobs and, from the Action menu, select New Job.
  14. CREATING JOBS 633 4. In the Name box, type Create Test Database (leave the rest of the boxes on PA R T this tab with the default settings). IV Admninistering SQL Server 5. Go to the Steps tab and click the New button to create a new step. 6. In the Step Name box, type Create Database. 7. Leave the type as Transact-SQL and enter the following code to create a database named Test on the C: drive: CREATE DATABASE TEST ON PRIMARY (NAME=test_dat, FILENAME=’c:\test.mdf’, SIZE=10MB, MAXSIZE=15, FILEGROWTH=10%)
  15. 634 CHAPTER 17 • AUTOMATING ADMINISTRATION 8. Click the Parse button to verify that you entered the code correctly, then move to the Advanced tab. 9. On the Advanced tab, verify that the On Success Action is set to Go to the Next Step and that the On Failure Action option is set to Quit the Job Reporting Fail- ure, then click OK. 10. To create the second step of the job, click the New button. 11. In the Name box, enter Backup Test. 12. Leave the Type as Transact-SQL Script and enter the following code to back up the database once it is created: EXEC sp_addumpdevice ‘disk’, ‘Test_Backup’, ‘c:\Test_Backup.dat’ BACKUP DATABASE TEST TO Test_Backup
  16. CREATING JOBS 635 13. Click OK to create the step. PA R T 14. Move to the Schedules tab and click the New Schedule button to create a schedule, IV which will instruct SQL Server when to fire the job. 15. In the Name box, type Create and Backup Database. Admninistering SQL 16. Under Schedule Type, select One Time and set the time to be 5 minutes from the time displayed in the system tray (the indented part of the Start bar, usually at the bottom right of your screen). Server 17. Click OK to create the schedule, and move to the Notifications tab. 18. On the Notifications tab, check the boxes next to E-Mail Operator (if you con- figured SQL Agent Mail earlier) and Net Send Operator, choosing yourself as the operator to notify. Next to each, select Whenever the Job Completes from the listbox (this will notify you no matter what the outcome of the job is).
  17. 636 CHAPTER 17 • AUTOMATING ADMINISTRATION 19. Click OK to create the job and wait until the time set in step 16 to verify comple- tion. You should see a message pop up on your screen notifying you of completion. So what just happened? You created a job with two steps; the first step created a new database named Test, and the second step backed up the database to a new backup device. This job was scheduled to run only one time and notify you of com- pletion (whether or not it was a success). The two steps in this job were Transact-SQL type jobs, which means that they were just standard Transact-SQL statements, much like you have been using throughout this book. You can run any Transact-SQL state- ment in this fashion, but that’s not all. Not only can you schedule Transact-SQL statements, you can schedule any active scripting language: VBScript, JavaScript, Perl, etc. This frees you from the boundaries of Transact-SQL, because the scripting languages have features that SQL Server does not implement. For example, you cannot directly access the file structure on the hard disk using Transact-SQL (to create a new text file, for example), but you can with a scripting language. Listing all of the advantages of scripting languages goes beyond the scope of this book, but to demonstrate how SQL Server schedules such tasks, let’s create a job that prints a statement: 1. Open Enterprise Manager by selecting it from the SQL Server 2000 group under Programs on the Start menu. 2. Expand your server, then Management, then SQLServerAgent. 3. Select Jobs and, from the Action menu, select New Job. 4. In the Name box, type VBTest (leave the rest of the boxes on this tab with the default settings). 5. Go to the Steps tab and click the New button to create a new step.
  18. CREATING JOBS 637 6. In the Step Name box, type Print. PA R T 7. Select Active Script as the Type and then check VBScript. IV 8. Enter the following code in the Command box: sub main() Admninistering SQL Print “Your job was successful” end sub Server 9. Click the Parse button to verify that you entered the code correctly, then click OK. 10. Move to the Schedules tab and click the New Schedule button. 11. In the Name box, type Run Print. 12. Under Schedule Type, select One Time and set the time to be 5 minutes from the time displayed in the system tray (the indented part of the Start bar, usually at the bottom right of your screen).
  19. 638 CHAPTER 17 • AUTOMATING ADMINISTRATION 13. Click OK to create the job and wait until the time set in step 12 to verify completion. Now that you have created a VBScript job, you need to know whether it ran suc- cessfully. True, you could have set a notification for yourself, but there is another way to verify the status of a job. SQL Server keeps track of the job’s history, when it was activated, whether it succeeded or failed, and even the status of each step of each job. To verify whether your VBScript job succeeded, let’s check the history of the job: 1. In Enterprise Manager, right-click the VBTest job and select View Job History. 2. To show the status of each step of the job, check the box at the top right of the Job History dialog box labeled Show Step Details. 3. Select the Print step and look for the text “Your job was successful” at the bot- tom of the dialog box in the Errors and/or Messages box. This is the text gener- ated by the VBScript function. 4. Click Close to exit the dialog box. The history for each job is stored in the MSDB database. By default, 1000 lines of total history can be stored, and each job can take up to 100 of those records. If you need to change those defaults, follow these steps: 1. In Enterprise Manager, right-click the SQLServerAgent and select Properties.
  20. CREATING JOBS 639 2. Select the Job System tab. PA R T 3. To change the amount of data saved for all jobs, modify the Maximum Job His- IV tory Log Size. 4. To change the number of rows that each job can take, change the Maximum Job Admninistering SQL History Rows per Job. 5. Clicking the Clear Log button will erase all of the history for all jobs on the server. Server 6. Click OK when you have made the necessary changes. It’s not hard to see the value of creating local jobs on SQL Server, but there is more. Multiserver jobs are designed to make automation easier across multiple servers. Creating Multiserver Jobs A growing number of companies today have multiple database servers. Each of these servers will require jobs; some are unique to the server, but many are repetitive, each server having the same job. One way to solve this problem is to create local jobs on each server separately, but this is time-consuming and hard to manage. A better way to make this happen is to create multiserver jobs.
Đồng bộ tài khoản