SQL Server Administration

What Needs to Be Maintained

The core tasks related to the maintenance of a SQL Server database are backing up the database and log (including shipping the logs to a standby server), rebuilding indexes, updating statistics, and running integrity checks against the database. These are the ongoing repetitive tasks, which are best run on a scheduled basis and are the backbone of the maintenance plan. Other tasks related to maintenance involve managing access by the users, maintaining data files, and monitoring performance. These tasks are more apt to be performed on an ad hoc basis when the need arises.

Selecting Databases

The first screen allows you to select which databases to back up. The option buttons allow you to select all databases, all system databases, or all user databases, or to build a custom selection of databases. New for SQL Server 2000 is the check box to ship the logs to another server. If this is selected, you will be presented with a new set of screens in the log section of the wizard where you can choose a network share from which to ship the logs, and a remote server and database to act as the standby database. Log shipping is used to keep a standby server up to date. This check box is disabled if the database is in simple recovery mode

SQL Agent Overview

The SQL Server Agent is responsible for automation tasks in SQL Server. The Agent, which runs as a Windows service, is responsible for running scheduled tasks, notifying operators of events, and responding with a predefined action to errors and performance conditions. SQL Server Agent performs these functions using alerts, jobs, and operators. Alerts respond to SQL Server or user-defined errors, and can also respond to performance conditions. An alert can be configured to run a job as well as notify an operator. A job is a predefined operation or set of operations, such as transferring data or backing up a transaction log. A job can be scheduled to run on a regular basis, or called to run when an alert is fired. An operator is a user to notify when an alert fires or a job requests notification. The operator can be notified by e-mail, pager, or the NET SEND command.

Jobs

Jobs are the cornerstone of SQL Server automation. Managed from the Jobs folder of the SQL Server Agent folder, jobs can be single or multistep operations, involving any combination of T-SQL, operating system commands, ActiveX scripts, or replication tasks.

Defining Job Properties

Right-clicking the Jobs folder and selecting New Job will bring up the Job Property dialog box. On the General tab, enter a name for the job and make sure the Enabled check box is selected. You can then select a category for the job. Categories are used to organize similar jobs; after you select a category, clicking the Ellipse button (…) beside it will show other jobs in the same category. Next is the Owner drop-down box. By default, this is the ID of the SQL Login creating the job, but if you are the administrator, you can assign the job to someone else. A Description field is also available and, if multiserver jobs are enabled, you can select on which servers the job will run. Multiserver jobs are covered in a following section of this chapter.

Operators

Operators are the accounts that will receive notification when an event occurs, such as the completion of a job or the firing of an alert. Operators are configured from the Operators folder under SQL Server Agent in Enterprise Manager. To create a new operator, right-click the Operators folder and select New Operator. To manage an existing operator, double-click the operator in the Details pane. This will bring up the Properties dialog box for the operator

Setting Up an E-Mail Client/Profile

Before you can use SQL Mail or SQLAgentMail, a MAPI-enabled e-mail client must be installed on the same server as SQL Server 2000. Although this can be any MAPI-enabled mail provider, it is recommended that you install Outlook 2000. Next, you must have an e-mail account on an available mail server (either a Microsoft Exchange or Internet mail server) that is linked to the domain user account under which SQL Server is running.

The following are the steps to follow for setting up an e-mail profile to support SQL Mail or SQLAgentMail:

1. Install a MAPI-compliant mail client that can connect to your mail host. Outlook 2000 is recommended because it can connect to either Exchange servers or Internet Mail providers.

2. Make sure that the SQL Server and SQL Agent services are configured to run under a user account that has access to the e-mail system rather than the default local System Account. This account, whether a domain user ID or local user ID, will need to have administrative rights on the SQL Server machine. For more information on setting up SQL Server and SQL Agent to run under a user account,
   

3. Make sure an e-mail account is set up on your mail server for the SQL Server user account. Details on how to set up these accounts for Exchange Server, Internet Mail servers, and Lotus Notes are covered later in this section.

4. Log into the server on which SQL Server is running using the user account specified as the startup account for SQL Server and SQL Agent.

5. After you are logged in, open the Mail Control Panel and click on the Add button to create a mail profile for the SQL Server user account. Choose a profile name that will be meaningful to the recipients of the e-mail messages. Configure an e-mail account for the mail profile that SQL Mail and SQLAgentMail will use for sending e-mail. This can be an account for an Exchange Server, a POP3 server, an IMAP server, an HTTP server, or another installed mail provider such as Lotus Notes. If any of your mail servers require a password for sending e-mail, be sure to save the password for the e-mail account in the mail profile when you set up the account as SQL Mail and SQLAgentMail. Do not prompt for a password when sending e-mail from SQL Server.