Exercise 1: Configuring SQL Server Agent

Scenario

You have been asked to confirm that the SQL Server Agent on NY-SQL-01 is properly configured. Then, prepare a profile that will be used to send database mail when the SQL Agent completes jobs

Exercise 1: Configuring SQL Server Agent

Task 1: Verify the configuration of the SQL Server Agent

  1. In the Lab Launcher, next to 6231A-NY-SQL-01, click Launch.
  2. Log on to NY-SQL-01 as Administrator using the password Pa$$w0rd.
  3. Click Start, point to All Programs, click Microsoft SQL Server 2008, click Configuration Tools, and then click SQL Server Configuration Manager.
  4. In Microsoft SQL Server Configuration Manager, in the left pane, click SQL Server Services, and then in the right pane, double-click SQL Server Agent (MSSQLSERVER).
  5. In the SQL Server Agent (MSSQLSERVER) Properties dialog box, click the Service tab, and then verify that the Start Mode property is set to Automatic and that the State property indicates that the current state is Running.
  6. Click the Log On tab, verify that the SQL Server Agent service is configured to run using a local user account called sqlserver, and then click Cancel.
  7. Close SQL Server Configuration Manager.

Task 2: Configure and Test Database Mail

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
  2. In the Connect to Server dialog box, click Connect.
  3. If Object Explorer is not visible, on the View menu, click Object Explorer.
  4. In Object Explorer, expand Management, right-click Database Mail, and then click Configure Database Mail.
  5. On the Welcome to Database Mail Configuration Wizard page, click Next.
  6. On the Select Configuration Task page, ensure that Set up Database Mail by performing the following tasks is selected, and then click Next.
  7. In the Microsoft SQL Server Management Studio message box, click Yes to enable Database Mail.
  8. On the New Profile page, in the Profile name box, type SQL Server Agent Mail Profile.
  9. In the Description box, type Mail profile for SQL Server Agent, and then click Add to add an account to the profile.
  10. In the New Database Mail Account dialog box, specify the details in the following table, and then click OK.

Property / Value or action
Account name / SQLServer
Description / SQL Server e-mail account
E-mail address /
Display name / SQL Server
Reply e-mail /
Server name / NY-SQL-01
Port number / 25
This server requires a secure connection (SSL) / Cleared
Anonymous authentication / Selected
  1. On the New Profile page, click Next.
  2. On the Manage Profile Security page, click the Private Profiles tab, and then select the Access check box.
  3. In the Default Profile list, click Yes, and then click Next.
  4. On the Configure System Parameters page, accept the default values, and then click Next.
  5. On the Complete the Wizard page, click Finish.
  6. On the Configuring page, verify that all actions complete successfully, and then click Close.
  7. In Object Explorer, right-click Database Mail, and then click Send Test E-mail.
  8. In the Send Test E-Mail from NY-SQL-01 dialog box, in the To box, type administrator@NY-SQL-01, and then click Send Test E-Mail.
  9. In the Database Mail Test E-Mail message box, click OK.
  10. Click Start, and then click Computer.
  11. In Windows Explorer®, browse to C:\inetpub\mailroot\Drop.
  12. Right-click the file of type Internet E-mail Message (file name is a string of letters and numbers), click Open With.
  13. In the Windows dialog box, click Select a program from a list of installed programs, and then click OK.
  14. In the Open With dialog box, click Notepad, and then click OK.
  15. Close Notepad and switch back to SQL Server Management Studio.

Task 3: Configure SQL Server Agent to use Database Mail

  1. In Object Explorer, expand Databases, expand System Databases, expand msdb, and then expand Security.
  2. Expand Roles, expand Database Roles, right-click DatabaseMailUserRole, and then click Properties.
  3. In the Database Role Properties - DatabaseMailUserRole dialog box, click Add.
  4. In the Select Database User or Role dialog box, in the Enter the object names to select box, type NY-SQL-01\SQLServer, and then click OK.
  5. In the Database Role Properties - DatabaseMailUserRole dialog box, click OK.
  6. In Object Explorer, right-click SQL Server Agent, and then click Properties.
  7. In the SQL Server Agent Properties - NY-SQL-01 dialog box, click the Alert System page.
  8. On the Alert System page, in the Mail session section, select the Enable mail profile check box.
  9. Ensure that the Mail system list is set to Database Mail, the Mail profile list is set to SQL Server Agent Mail Profile, and then click OK.
  10. In Object Explorer, right-click SQL Server Agent, and then click Restart.
  11. In the Microsoft SQL Server Management Studio dialog box, click Yes to restart the SQLSERVERAGENT service

Note: Ensure that SMTP service is running on your computer

Exercise 2: Creating Operators and Jobs

Scenario

Adventure Works wants to create a regular index maintenance job. When it is completed, an email needs to be sent to the administrator mailbox.

Task 1: Create the AWAdmin operator

  1. In Object Explorer, expand SQL Server Agent, right-click Operators, and then click New Operator.
  2. In the New Operator dialog box, specify the details in the following table, and then click OK.:

Property / Value or action
Name / AWAdmin
E-mai name / administrator@NY-SQL-01
Enabled / Selected

Task 2: Create the Adventure Works Index Maintenance Job

  1. In the SQL Server Management Studio, expand Databases, expand AdventureWorks2008, expand Tables, expand Production.ProductModel and then expand Indexes.
  2. Right-click AK_ProductModel_Nameand then click Rebuild.
  3. The Rebuild Indexes window appears. Click the arrow next to Script and then click Script Action to Job.
  4. In the Select a Page pane, click Steps.
  5. Click New.
  6. In the New Job Step dialog box, specify the details in the following table.

Property / Value or Action
Step Name / Check consistency of Adventureworks2008 database
Type / Transact-SQL script (T-SQL)
Run as / Leave blank
Database / Adventureworks2008
Command / DBCC CheckDB(‘AdventureWorks2008’)
  1. In the Select a page pane, click Advanced.
  2. In the Output file field, type E:\Mod06\Labfiles\CheckDBResults.txt.
  3. Click Append output to existing file.
  4. Click OK.
  5. In the New Job dialog box, in the Select a page pane, click the Notifications page.
  6. On the Notifications page, select the E-mail check box.
  7. In the first drop-down list, click AWAdmin, and then in the second drop-down list, click When the job completes.
  8. In the Select a page pane, click the Schedules page.
  9. On the Schedules page, click New.
  10. In the New Job Schedule dialog box, add a new schedule with the properties in the following table, and then click OK.

Property / Value or action
Name / AW index maintenance schedule
Schedule type / One time
Enabled / Selected
Date / Leave unchanged
Time / Two minutes after the current time
  1. In the New Job dialog box, click OK.

Task 3: Verify that the Index job ran successfully.

  1. Switch to Windows Explorer, confirm the log has been posted to at E:\MOD06\Labfiles\CheckDBResults.txt.
  2. Confirm the new outgoing email exists at C:\Inetpub\mailroot\Drop.
  3. Switch back to SQL Server Management Studio.

Exercise 3: Creating Alerts

Scenario

A recurring SQL Server error has been creating some operational problems for the administrators. In order to respond more quickly, an operator needs to be notified by email when the error occurs

Exercise 3: Creating Alerts

Task 1: Create the AWResource Error Alert.

  1. In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert.
  2. In the New Alert dialog box, add a new alert with the properties in the following table.

Property / Value or action
Name / AW Resource Error
Type / SQL Server event alert
Database name / AdventureWorks2008
Severity / 017 - Insufficient Resources
Raise alert when message contains / Leave cleared
  1. In the Select a page pane, click the Response page.
  2. On the Response page, select the Notify operators check box, and then select the E-mail check box for the AWAdmin operator.
  3. In the Select a page pane, click the Options page.
  4. On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: An error occurred in the AdventureWorks database, and then click OK.

Task 2: Test the AWResource Error Alert.

  1. On the File menu, point to New, and then click Query with Current Connection.
  2. In the Query pane, type the following Transact-SQL code.

USE AdventureWorks

GO

RAISERROR ('Test Error', 17, 1) WITH LOG

  1. On the Query menu, click Execute.
  2. Switch to Windows Explorer, browse to C:\Inetpub\mailroot\Drop and verify the new mail exists