SQL Server 2000 Administrator's Pocket Consultant: Database Automation and Maintenance

In Enterprise Manager, access the Management folder on the database server instance you want to work with.

Select the SQL Server Agent entry in the left pane.

You should see entries for Alerts, Operators, and Jobs. Select one of these entries in the left pane to display its properties in the right pane, as shown in Figure 12-1.

Double-click an alert, operator, or job entry to access its associated properties dialog box.

Figure 12-1: Summary entries for alerts provide an overview of each individual alert. You can also access summaries for operators and jobs.
See full-sized image.

Use the Server selection list to choose an available server or enter the server name.

From the Services selection list, choose SQL Server Agent.

Choose Start to start the service, if necessary.

Choose Auto-Start Service When OS Starts. This will cause SQL Server Agent service to start automatically when the system boots.

Click the General tab of the SQL Server Agent Properties dialog box.

Choose System Account to use the built-in system account as the startup account. Otherwise select the This Account option button and then type the username and password of a Windows domain account.

Figure 12-2: Configure SQL Server Agent properties using the available tabs and fields in the SQL Server Agent Properties dialog box.
See full-sized image.

If the startup account doesn't have appropriate permissions for making connections to SQL Server, access the Connection tab and then select an authentication method. With SQL Server authentication, you'll also need to specify a login account and password.

Click OK.

Access the Advanced tab of the SQL Server Agent Properties dialog box.

Select Auto Restart SQL Server If It Stops Unexpectedly. Select Auto Restart SQL Server Agent If It Stops Unexpectedly.

Click OK.

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.

Right-click SQL Server Agent and then, from the shortcut menu, choose Display Error Log.

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.

Select the SQL Server Agent entry in the left pane.

Right-click Alerts and then, from the shortcut menu, choose New Alert. This opens the New Alert Properties dialog box.

Type a short but descriptive name for the alert in the Name field, as shown in Figure 12-3.

In the Type selection list, choose SQL Server Event Alert. You can now set alerts according to the number or severity level of error messages.

To set alerts by error number, choose Error Number and then type an error number in the related field. To search for an error number, click the build button (…) and then use the Manage SQL Server Messages dialog box to find an appropriate message.

To set alerts by severity level, choose Severity and then use the related selection list to choose a severity level that triggers the alert. You'll usually want to configure alerts for severity levels 19–25.

Use the Database Name selection list to choose the database in which the error must occur in order to trigger the alert. To specify all databases on the server, select the (All Databases) option.

To restrict alerts to messages containing specific text strings, type the filter text in the Error Message Contains This Text field.

Figure 12-3: Use the New Alert Properties dialog box to configure new alerts for error messages.
See full-sized image.

Click Apply and then configure the alert response as explained in the next section of this chapter, "Handling Alert Responses."

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.

Select the SQL Server Agent entry in the left pane.

Select the Alerts entry in the left pane and then double-click the alert you want to configure. Then click the Response tab, as shown in Figure 12-4.

To execute a job in response to the alert, select Execute Job and then use the related selection list to select a job to execute. If you want to create a new job, choose the (New Job) entry and then configure the job as discussed in the section of this chapter entitled "Scheduling Jobs."

Operators configured to handle alerts and schedule jobs are shown in the Operators To Notify area. The available notification methods depend on how the operator account is configured. You can select E-Mail, Pager, or Net Send notification, or all three.

Figure 12-4: You can respond to alerts by executing jobs or notifying operators of the alert, or both.
See full-sized image.

Use the Include Alert Error Text In check boxes to specify whether error text is sent with the notification message. By default, error text is sent only with E-Mail and Net Send notifications.

Set an additional message to operators using the Additional Notification Message To Send text box.

Set the delay between responses for subsequent alert notifications using the Delay Between Responses fields labeled Minutes and Seconds.

Tip To limit the number of alert responses triggered, you'll probably want to set a delay response value of five minutes or more.

Click OK to complete the configuration.

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.

Select the SQL Server Agent entry in the left pane and then select Alerts.

To enable or disable an alert, double-click its entry in the Alerts list to open the related properties dialog box. Then, in the General tab, select or clear the Enabled check box to enable or disable the alert.

To delete an alert, click it and then press Delete. When prompted to confirm the deletion, choose Yes.

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.

Select the SQL Server Agent entry in the left pane.

Right-click the Operators entry in the left pane and then, from the shortcut menu, choose New Operator. This opens the New Operator Properties dialog box shown in Figure 12-5.

Type a name for the operator in the Name field.

Specify E-Mail, Pager, or Net Send accounts (or all three) to notify. If you can't remember the account you want to use, click the build button (...) to display the Contacts list from your address book, and then select the account to use.

Click the Notifications tab to specify existing alerts that the operator should receive (if any). Existing alerts are listed in the Alert Name column. If you find an alert that the operator should receive, select the corresponding check boxes in the E-Mail, Pager, and Net Send columns as appropriate.

Once you've configured the Notifications tab, you can click Send E-Mail to send the operator an e-mail that lists that operator's alert responsibilities.

Tip If you specify a pager account for the operator, you can set a duty schedule for the pager using the Pager On Duty Schedule area's fields and check boxes. This option is helpful if you have operators who should be notified only during working hours. To set default configuration settings for pagers, access the Alert System tab of the SQL Server Agent Properties dialog box.

Click OK to register the operator.

Figure 12-5: Operators can receive alert and job notifications. To associate the operator with multiple users, reference a group e-mail account.
See full-sized image.

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.

Select the SQL Server Agent entry in the left pane and then select Operators.

To disable an operator, double-click the operator entry in the right pane. This opens the Operator Properties dialog box. Now click the Notifications tab and then clear Operator Is Available To Receive Notifications.

To delete an operator, click its entry in the right pane and then press Delete. If the operator has been selected to receive alert or job notifications, you'll see the Delete Operator dialog box shown in Figure 12-6. To reassign notification duty, select a different operator using the Re-Assign To selection list and then click Reassign.

Figure 12-6: In the Delete Operator dialog box you can reassign notification duties when deleting an operator registration.
See full-sized image.

Right-click the SQL Server Agent entry in Enterprise Manager's Management folder and then select Properties.

In the SQL Server Agent Properties dialog box, click the Alert System tab and then use the Operator selection list to choose an operator to designate as the fail-safe operator. You can reassign the fail-safe duty by selecting a different operator or disable the feature by selecting (No Fail-Safe Operator).

Use the Notify Using check boxes to determine how the fail-safe operator is notified.

Click Apply.

In Enterprise Manager, access the Management folder on the server running SQL Server Agent.

Select the SQL Server Agent entry in the left pane and then double-click Jobs.

Existing jobs are shown in the right pane. Double-click a job to access its related properties dialog box, which is essentially the same as the New Job Properties dialog box shown in Figure 12-7.

To create a new job, right-click the Jobs entry and, from the shortcut menu, choose New Job. This displays the New Job Properties dialog box shown in Figure 12-7.

In the Name field, type a descriptive name for the job. The name can be up to 128 characters long. If you change the name of an existing job, the job is displayed with the new name. Any references to the old job name in logs or history files remain the same and aren't modified.

If job scheduling across multiple servers is configured, select the target server. The target server is the server on which the job runs. To run on the currently selected server, select Target Local Server. To run on multiple servers, select Target Multiple Servers and then choose the target servers.

Figure 12-7: Use the General tab of the New Job Properties dialog box to name, describe, and categorize the job.
See full-sized image.

Job categories help to organize jobs so they can be easily searched and differentiated. The default category is Uncategorized (Local). Use the Category selection list to choose a different category for the job.

Note: Job categories are created and managed through a separate process. To create a new job category or update an existing category, use the techniques described in the section of this chapter entitled "Managing Job Categories."

By default, the current user owns the job. Administrators can reassign jobs to other users. To do this, use the Owner selection list. You can use only predefined logons. If the logon you want to use isn't available, you'll need to create a logon for the account.

Type a description of the job in the Description text box. You can use up to 512 characters.

Click Apply to create the job definition. Then set Steps, Schedules, and Notifications as explained in the following sections.

Type a short but descriptive name for the step in the Step Name field.

Use the Type selection list to choose a step type. Each step can

•	Execute Transact-SQL commands Type Transact-SQL commands in the Command area or load the statements from a Transact SQL script. To load commands from a script, click Open and then select the Transact-SQL script you want to use. The entire contents of the script are then stored with this step.
•	Run ActiveX scripts You can write ActiveX scripts in VBScript, JScript, or another active scripting language configured for use on the system. Enter script statements directly into the Command area or load the statements from a script file. Again, the entire contents of the script are then stored with this step, and later changes to the script file aren't updated automatically.
•	Execute operating system commands Enter the operating system commands on a separate line, making sure that you specify the full path to commands and in command parameters. Commands can run batch scripts, Windows scripts, command-line utilities, or applications. Figure 12-9: Set summary information and commands to execute for the step you're configuring using the fields in the General tab of the New Job Step dialog box. See full-sized image.
•	Pass Transact-SQL commands to replication agents You can script the Distributor, Snapshot, Merge, and Transaction LogReader agents with Transact-SQL commands. To see examples, refer to the existing jobs that are configured to handle replication, distribution, and subscription processes on the server (if available).

Tip Subsequent changes to scripts aren't updated automatically. You'll need to edit the step properties and reload the script file. Additionally, you shouldn't edit existing replication jobs. Instead, modify the replication process as described in Chapter 9, "Configuring Snapshot, Merge, and Transactional Replication."

When executing Transact-SQL commands, use the Database selection list to set the database on which the commands are executed.

Click the Advanced tab as shown in Figure 12-10.

In the On Success Action selection list, set the action to take when the step succeeds. You can

Figure 12-10: Control the behavior and logging of the step using the fields in the Advanced tab.
See full-sized image.

By default, Retry Attempts is set to zero and SQL Server Agent doesn't try to execute steps again. You can change this behavior by setting the number of retry attempts and a retry interval. You do this by using the Retry Attempts and Retry Interval (Minutes) fields, respectively. The retry interval is the delay in minutes between retries.

If the job fails on all retry attempts (if any), the action set in the On Failure Action selection list is executed. The available options are the same as those for success.

If desired, configure a file for logging output from Transact-SQL and CmdExec commands. Type the filename and path in the Output File field or use the find file button (…) to search for an existing file. An alternative is to append output to the step history.

Tip You may want to create a central log file for the output of all jobs or only all jobs in a particular category. If you do this, be sure to select the Append option button rather than the Overwrite option button. This ensures that the output file doesn't get overwritten.

Set the login to use when executing commands using the Run As User selection list. By default, commands are run using the current login ID.

Choose Apply to complete the step configuration.

Click New Schedule to open the New Job Schedule dialog box, or click Edit to open the Edit Job Schedule dialog box. These dialog boxes are essentially the same except for the title. Figure 12-12 shows the New Job Schedule dialog box.

Type a name for the schedule and then select one of the following schedule types:

•	Start Automatically When SQL Server Agent Starts Runs the job automatically whenever SQL Server Agent starts.
•	Start Whenever The CPU(s) Become Idle Runs the job whenever the CPU is idle. CPU idle time is specified in the Advanced tab of the SQL Server Agent Properties dialog box. Figure 12-12: Use the New Job Schedule dialog box to set the schedule name and type. See full-sized image.
•	One Time Runs the job once at the date and time specified in the On Date and At Time fields.
•	Recurring Runs the job according to the recurring schedule displayed.

Recurring jobs are the ones that need the most explanation. When you click Change, you'll see the Edit Recurring Job Schedule dialog box shown in Figure 12-13. You can schedule recurring jobs to run on a daily, weekly, or monthly basis.

To run the job on a daily basis, select the Daily option button. Then use the Every Day field to set when the job runs. Daily recurring jobs can run every day, every other day, or every Nth day.

To run the job on a weekly basis, select the Weekly option button. Then configure the job using these fields:

To run the job on a monthly basis, select the Monthly option button. Then configure the job using these fields:

Figure 12-13: Configure a recurring schedule in the Edit Recurring Job Schedule dialog box.
See full-sized image.

Set the Daily Frequency for the daily, weekly, or monthly job. You can configure jobs to run one or more times on their scheduled run date. To run the job once on a given date, select Occurs Once At and then set a time. To run the job several times on a given date, select Occurs Every and then set a time interval in hours or minutes. Afterward, set a start and end time, such as from 7:30 A.M. to 5:30 P.M.

By default, schedules begin on the current date and don't have a designated end date. To change this behavior, select the End Date option button and then use the Start Date and End Date fields to set a new duration for the schedule.

Click OK to close the Edit Recurring Job Schedule dialog box and then click OK again to close the New Job Schedule dialog box, which completes the schedule process.

Access the Notifications tab of the job you want to configure. This tab is shown in Figure 12-14.

You can notify operators by e-mail, pager, or Net Send message. Select the check box for the technique you want to use. Choose an operator to handle the notification. Then choose a notification type. Repeat this process to configure other notification methods.

Figure 12-14: Using the Notifications tab, you can send notification messages to operators, log them in the event log, or use them to automatically delete a job.
See full-sized image.

To log a particular type of notification message in the event log, select Write To Windows Application Event Log and then select the notification type to log. Usually, you'll want to log failure, so select When The Job Fails.

To delete a job upon notification, select Automatically Delete Job and then choose the notification type that triggers the deletion.

Choose Apply.

Access the SQL Server Agent window in the console root. You'll see an entry labeled Jobs.

Select Jobs in the left pane and you'll see existing jobs in the right pane.

You can now double-click a job entry to access its related properties dialog box or right-click a job entry to display a shortcut menu. Key options on the shortcut menu are

In Enterprise Manager, access the SQL Server Agent window in the console root.

Right-click the Jobs node. Select All Tasks, and then choose Manage Job Categories. This displays the Job Categories dialog box.

You can delete a category by selecting it and then pressing Delete.

You can view the properties of a category by selecting it and then clicking Properties.

To add categories or to change the properties of a category, follow the steps outlined in the following sections, "Creating Job Categories" or "Updating Job Categories," respectively.

Access the Job Categories dialog box as explained previously. Click Add to display the New Job Categories dialog box.

Type a name for the category in the Name field, and then select Show All Jobs.

All jobs defined on the current server should now be listed. Add a job to the new category by selecting the corresponding check box in the Member column. Remove a job from the new category by clearing the corresponding check box in the Member column.

Click OK when you're finished.

Access the Job Categories dialog box as explained previously. Click Properties to display a properties dialog box.

Select Show All Jobs. All jobs defined on the current server should now be listed.

Add a job to a new category by selecting the corresponding check box in the Member column. Remove a job from the category by clearing the corresponding check box in the Member column.

Click OK when you're finished.

Start Enterprise Manager and then access the server you want to work with.

Right-click the server's Data Transformation Services folder and then select New Package. This displays the DTS Designer window in Enterprise Manager.

In DTS Designer, choose Task and then click Transfer Logins Task. This adds the Transfer Logins Task icon to the DTS Designer window.

Double-click the Transfer Logins Task icon. This displays the Transfer Logins Properties dialog box shown in Figure 12-15.

Figure 12-15: Use the Transfer Logins Properties dialog box to configure the task options. You can select all logins for transfer or logins for selected databases.
See full-sized image.

In the Source tab, use the Source Server selection list to specify the SQL Server instance that contains the logins you want to transfer. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select Use SQL Server Authentication and then enter the user name and password of a login that's a member of the sysadmin role.

In the Destination tab, use the Destination Server selection list to specify the SQL Server instance to which you want to transfer logins. Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.

Click the Logins tab as shown in Figure 12-16. You can

Click OK to close the Transfer Logins Properties dialog box.

To run the package immediately, click the Execute button (the green arrow button located on the menu bar) or choose Package from the menu and then click Execute. If you want to save the package for later use or for scheduling, continue with the remaining steps. Otherwise skip the remaining steps.

Figure 12-16: Use the Logins tab to specify the logins to transfer.
See full-sized image.

To save the package for later use, choose Package and then click Save. You'll see the Save DTS Package dialog box as shown in Figure 12-17.

Type a name for the package in the Package Name field. The package name should be unique for the target location.

If you're saving the package to SQL Server, you can password-protect the package and prevent unauthorized users from working with it. Type an owner password and a user password in the fields provided. Anyone with the owner password can design, schedule, and execute the package. Anyone with the user password can only schedule or execute the package.

Use the Location field to specify where the package should be saved. The available locations are

The lower portion of the Save DTS Package dialog box changes according to the location selection. When you save to SQL Server or to SQL Server Meta Data Services, you must specify the target server and authentication information. When you save to a Structure Storage file or a Visual Basic file, you must specify the file name and path.

Figure 12-17: Use the Save DTS Package dialog box to configure the save options for the package.
See full-sized image.

Click OK to save the package to the specified location. Once you've saved the package, you can run or schedule the package for execution as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

Start Enterprise Manager and then access the server you want to work with.

Right-click the server's Data Transformation Services folder and then select New Package. This displays the DTS Designer window in Enterprise Manager.

In DTS Designer, choose Task and then click Transfer Jobs Task. This adds the Transfer Jobs Task icon to the DTS Designer window.

Double-click the Transfer Jobs Task icon. This displays the Transfer Msdb Jobs dialog box shown in Figure 12-18.

Figure 12-18: Use the Transfer Msdb Jobs dialog box to configure the task options. You can select all jobs for transfer or specify jobs to transfer individually.
See full-sized image.

In the Source tab, use the Source Server selection list to specify the SQL Server instance that contains the jobs you want to transfer. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select Use SQL Server Authentication and then enter the username and password of a login that's a member of the sysadmin role.

In the Destination tab, use the Destination Server selection list to specify the SQL Server instance to which you want to transfer jobs. Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.

Click the Jobs tab as shown previously in Figure 12-18. You can

Click OK to close the Transfer Jobs dialog box.

To run the package immediately, click the Execute button (the green arrow button located on the menu bar) or choose Package and then click Execute. If you want to save the package for later use or for scheduling, continue with the remaining steps. Otherwise skip the remaining steps.

To save the package for later use, choose Package and then click Save. As shown previously in Figure 12-17, you'll see the Save DTS Package dialog box.

Type a name for the package in the Package Name field. The package name should be unique for the target location.

If you're saving the package to SQL Server, you can password-protect the package and prevent unauthorized users from working with it. Type an owner password and a user password in the fields provided. Anyone with the owner password can design, schedule, and execute the package. Anyone with the user password can only schedule or execute the package.

Use the Location field to specify where the package should be saved. The available locations are

The lower portion of the Save DTS Package dialog box changes according to the location selection. When you save to SQL Server or to SQL Server Meta Data Services, you must specify the target server and authentication information. When you save to a Structure Storage file or a Visual Basic file, you must specify the file name and path.

Click OK to save the package to the specified location. Once you've saved the package, you can run or schedule the package for execution as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

Start Enterprise Manager and then access the server you want to work with.

Right-click the server's Data Transformation Services folder and then select New Package. This displays the DTS Designer window in Enterprise Manager.

In DTS Designer, choose Task and then click Transfer Master Stored Procedures Task. This adds the Transfer Master Stored Procedures Task icon to the DTS Designer window.

Double-click the Transfer Master Stored Procedures Task icon. This displays the Transfer Master Stored Procedures dialog box as shown in Figure 12-19.

In the Source tab, use the Source Server selection list to specify the SQL Server instance that contains the shared stored procedures you want to transfer. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select Use SQL Server Authentication and then enter the username and password of a login that's a member of the sysadmin role.

In the Destination tab, use the Destination Server selection list to specify the SQL Server instance to which you want to transfer shared stored procedures. Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Figure 12-19: Use the Transfer Master Stored Procedures dialog box to configure the task options. You can select all shared stored procedures for transfer or specify shared stored procedures to transfer individually.
See full-sized image.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.

Click the Stored Procedures tab as shown previously in Figure 12-19. You can

Click OK to close the Transfer Master Stored Procedures dialog box.

To run the package immediately, click the Execute button (the green arrow button located on the menu bar) or choose Package and then click Execute. If you want to save the package for later use or for scheduling, continue with the remaining steps. Otherwise skip the remaining steps.

To save the package for later use, choose Package and then click Save. As shown previously in Figure 12-17, you'll see the Save DTS Package dialog box.

Type a name for the package in the Package Name field. The package name should be unique for the target location.

If you're saving the package to SQL Server, you can password-protect the package and prevent unauthorized users from working with it. Type an owner password and a user password in the fields provided. Anyone with the owner password can design, schedule, and execute the package. Anyone with the user password can only schedule or execute the package.

Use the Location field to specify where the package should be saved. The available locations are

The lower portion of the Save DTS Package dialog box changes according to the location selection. When you save to SQL Server or to SQL Server Meta Data Services, you must specify the target server and authentication information. When you save to a Structure Storage file or a Visual Basic file, you must specify the file name and path.

Click OK to save the package to the specified location. Once you've saved the package, you can run or schedule the package for execution as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

Start Enterprise Manager and then access the server you want to work with.

Right-click the server's Data Transformation Services folder and then select New Package. This displays the DTS Designer window in Enterprise Manager.

In DTS Designer, choose Task and then click Transfer Error Messages Task. This adds the Transfer Error Messages Task icon to the DTS Designer window.

Double-click the Transfer Error Messages Task icon. This displays the Transfer Error Messages dialog box as shown in Figure 12-20.

In the Source tab, use the Source Server selection list to specify the SQL Server instance that contains the user-defined error messages you want to transfer. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select use SQL Server Authentication and then enter the user name and password of a login that's a member of the sysadmin role.

Figure 12-20: Use the Transfer Error Messages dialog box to configure the task options. You can select all user-defined error messages for transfer or specify user-defined error messages to transfer individually.
See full-sized image.

In the Destination tab, use the Destination Server selection list to specify the SQL Server instance to which you want to transfer user-defined error messages. Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.

Click the Error Messages tab as shown previously in Figure 12-20. You can

Click OK to close the Transfer Error Messages dialog box.

To run the package immediately, click the Execute button (the green arrow button located on the menu bar) or choose Package and then click Execute. If you want to save the package for later use or for scheduling, continue with the remaining steps. Otherwise skip the remaining steps.

To save the package for later use, choose Package and then click Save. As shown in previously Figure 12-17, you'll see the Save DTS Package dialog box.

Type a name for the package in the Package Name field. The package name should be unique for the target location.

If you're saving the package to SQL Server, you can password-protect the package and prevent unauthorized users from working with it. Type an owner password and a user password in the fields provided. Anyone with the owner password can design, schedule, and execute the package. Anyone with the user password can only schedule or execute the package.

Use the Location field to specify where the package should be saved. The available locations are

The lower portion of the Save DTS Package dialog box changes according to the location selection. When you save to SQL Server or to SQL Server Meta Data Services, you must specify the target server and authentication information. When you save to a Structure Storage file or a Visual Basic file, you must specify the file name and path.

Click OK to save the package to the specified location. Once you've saved the package, you can run or schedule the package for execution as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

At the beginning of a copy, the wizard attempts to put the database in single-user mode. The wizard can only do this when there are no active sessions in the database.

Once the database is in single-user mode, the wizard detaches the source database, and then, if successful in detaching the database, the wizard copies the database files associated with the database to the destination server.

Once the files are copied to the destination server, the wizard attaches the database on the source and then attaches the database on the destination server.

The wizard then tests the destination database by logging in as the database administrator.

At the beginning of a move, the wizard attempts to put the database in single-user mode. The wizard can do this only when there are no active sessions in the database.

Once the database is in single-user mode, the wizard detaches the source database, and then, if successful in detaching the database, the wizard copies the database files associated with the database to the destination server.

Once the files are copied to the destination server, the wizard attaches the database on the destination server but doesn't reattach the database on the source server. The database files on the source still exist and could be used to reattach the database. Or you could manually delete the files.

The wizard then tests the destination database by logging in as the database administrator.

Start Enterprise Manager and then access the server you want to work with. If the database you want to use has active user sessions, you'll need to terminate these sessions before continuing. The wizard won't complete the operation when there are active sessions.

Right-click the source server entry, point to All Tasks, and then choose Copy Database Wizard.

You'll see the Copy Database Wizard dialog box. Read the welcome page and then click Next.

Use the Source Server selection list to specify the SQL Server instance that contains the database(s) you want to copy or move. Afterward, specify the authentication technique to use when connecting to the server. If you're logged on using a Windows domain account that's a member of the sysadmin role on the source server, select Use Windows Authentication. Otherwise select Use SQL Server Authentication and then enter the username and password of a login that's a member of the sysadmin role.

Click Next and then use the Destination Server selection list to specify the SQL Server instance to which you want to copy or move the selected database(s). Afterward, specify the authentication technique to use when connecting to the server. Again, make sure you use a login that's a member of the sysadmin role.

Note: You can't set the source and destination server to the same value. You must configure the transfer from one server to another.

Select the user-defined databases to move or copy as shown in Figure 12-21. You can't select system databases, and you can't select databases that already exist on the destination server.

Figure 12-21: Select the user-defined databases to move or copy.
See full-sized image.

As Figure 12-22 shows, the next dialog box summarizes the database file information for the copy and move operations you're performing. You use the key fields in the dialog box as follows:

•	Files Specifies the type of database file.
•	Destination Drives Specifies the destination drive on the target server.
•	Size Specifies the total disk space the files will use on the destination drives.
•	Status Specifies the file status. A green checkmark indicates the files are ready to move or copy. A red X indicates a problem. Figure 12-22: The Database File Location dialog box provides a summary for the database files you're working with. See full-sized image.
•	Modify Displays the Database Files dialog box, which you can use to get more detailed file information and to set new source file names and destinations for database files.
•	Refresh Refreshes the file and drive usage information.

As shown in Figure 12-23, select the related objects that you want to copy or move with the selected database(s). The objects you can select are: Logins, Shared Stored Procedures, Jobs, and User-Defined Error Messages. All related objects are selected by default. To cancel an object, clear the related check box.

Figure 12-23: Select the related objects that you want to copy or move with the selected database(s).
See full-sized image.

The option buttons in the Select Related Objects dialog box control which objects of a particular type are copied or moved. Changing the options changes which of the related objects are used and may also cause additional dialog boxes to be displayed in later steps. The options are

After you configure any special dialog boxes displayed as a result of object selections, you'll have the option of scheduling the DTS package to run. You can

Click Next and then click Finish to complete the process. If you elected to run the package immediately, SQL Server runs the package. As each step is completed (or fails), the status is updated. If an error occurs, you can double-click its entry to view a detailed description of the error. Errors may halt execution of the package and if they do, you'll need to recreate the package. If you scheduled the package to run later, you can manipulate the package as described in the section of Chapter 7 entitled "Examining, Running, and Scheduling Packages."

Access the Advanced tab of the SQL Server Agent Properties dialog box, as shown in Figure 12-24.

Select Forward Events To A Different Server.

Use the Server selection list to choose the server that will handle the events. Choose <New Forwarding Server> to register a new server.

Set the type of events to forward by selecting Unhandled Events or All Events. An unhandled event is one that you haven't configured alerts for on the current server.

Figure 12-24: Event forwarding can be helpful in reducing the time you spend configuring alerts on individual servers.
See full-sized image.

In the If Event Has Severity Of Or Above selection list, determine the severity threshold for events that are forwarded.

Tip To reduce network traffic caused by event forwarding, set the severity threshold to a fairly high value. Fatal errors have a severity level of 19–25.

Click Apply.

In Enterprise Manager, access the SQL Server Agent window on the server you want to configure as the master.

Right-click SQL Server Agent, point to Multiserver Administration, and then select Make This A Master. This starts the Make MSX Wizard.

Read the welcome dialog box and then click Next.

As shown in Figure 12-25, create a special operator to handle multiserver job notifications. This operator, called MSXOperator, is created on the master and all target servers that use this master. Set an e-mail, pager, and Net Send address, as appropriate. You can change this information later by editing the MSXOperator properties on the master server.

Select the target servers to associate with this master server. The process of associating target servers with a master is called enlisting. Later, you can remove the association by right-clicking SQL Server Agent in Enterprise Manager, selecting Multi Server Administration, and then selecting Manage Target Servers.

Figure 12-25: Use the Make MSX Wizard to configure the MSXOperator to handle multiserver job notifications.
See full-sized image.

Click Next and then click Finish. The wizard performs the necessary tasks and reports its progress. You'll be notified of any errors.

In Enterprise Manager, access the SQL Server Agent window on the server you want to configure as the target.

Right-click SQL Server Agent and then select the Multiserver Administration option. From the shortcut menu, choose Make This A Target. This starts the Make TSX Wizard.

Read the welcome dialog box and then click Next.

Set a valid Windows domain account that can be used to access network resources. Type an account name and a password.

In the next dialog box, type the name of the master server for this target.

Click Next and then click Finish. The wizard performs the necessary tasks and reports its progress. You'll be notified of any errors, and you can click Pause to stop the operation.

In Enterprise Manager, access the Management folder on the server you want to work with.

Right-click Database Maintenance Plans and then select New Maintenance Plan. This starts the Database Maintenance Plan Wizard.

Read the welcome dialog box and then click Next.

As shown in Figure 12-26, select the databases that'll use this mainten- ance plan. You can use the maintenance plan with all databases, all system databases, all user databases, or a combination of one or more individual databases.

Tip For most installations, I recommend configuring separate maintenance plans for system and user databases. This gives you greater flexibility when it comes to how and when maintenance operations are performed. For large installations you may want to have separate maintenance plans for each database. This way you can work with different databases on different days or at different times of the day.